Блог

Главная - Вопросы по программированию - Напишите запрос для определения частых плакатов

Напишите запрос для определения частых плакатов

#mysql

Вопрос:

Я пытаюсь написать запрос, который найдет user_id имена всех пользователей, создавших минимум два сообщения максимум за 1 час.

Вот легкий пример данных:

 CREATE TABLE tbl_posts
    (`id` int, `user_id` int, `created_date` datetime);

INSERT INTO tbl_posts
    (`id`, `user_id`, `created_date`)
    VALUES
    (1, 1, '2021-07-01 09:00'),

    (2, 2, '2021-07-01 10:15'), -- *
    (3, 2, '2021-07-01 11:00'), -- * user posted twice within an hour.

    (4, 3, '2021-07-01 13:00'), 
    (5, 3, '2021-07-01 15:00'),
    (6, 3, '2021-07-01 18:00'),
    
    (7, 4, '2021-07-01 11:00'),
    (8, 4, '2021-07-02 11:30'),
    (9, 4, '2021-07-03 12:30'), -- *
    (10, 4, '2021-07-03 12:45'); -- * user posted twice within an hour.

http://sqlfiddle.com/#!9/0e7cba

Ожидаемый результат запроса таков

 2, 4

Этот результат ожидается, потому что пользователи 2 и 4 опубликовали по крайней мере дважды менее чем за час.

Я не знаю, с чего начать с этого в MySQL. Я могу экспортировать данные и получить результат процедурно в чем-то вроде C или Python, но я уверен, что это возможно в MySQL, и мне любопытно знать, как это сделать. Может быть, мне нужна функция окна?

Комментарии:

1. Что вы пробовали до сих пор? Вы знаете о GROUP BY и HAVING ?

Ответ №1:

Воспользуйся EXISTS :

 SELECT DISTINCT t1.user_id
FROM tbl_posts t1
WHERE EXISTS (
  SELECT 1
  FROM tbl_posts t2
  WHERE t2.user_id = t1.user_id
    AND t1.created_date < t2.created_date
    AND TIMESTAMPDIFF(SECOND, t1.created_date, t2.created_date) <= 60 * 60
)

Или, если ваша версия MySQL 8.0 , используйте LEAD() функцию окна:

 SELECT user_id
FROM (
  SELECT *, TIMESTAMPDIFF(
              SECOND, 
              created_date, 
              LEAD(created_date) OVER (PARTITION BY user_id ORDER BY created_date)
            ) diff
  FROM tbl_posts          
) t
GROUP BY user_id
HAVING MIN(diff) <= 60 * 60

Смотрите демонстрацию.

Ответ №2:

 select distinct p.user_id from tbl_posts p
inner join tbl_posts p2 on p.user_id = p2.user_id 
and p.created_date < p2.created_date
and DATE_ADD(p.created_date,interval 1 hour) >= p2.created_date
Метки: