Блог

Главная - Вопросы по программированию - Получите количество комментариев с интервалом не менее одного часа

Получите количество комментариев с интервалом не менее одного часа

#mysql #sql

Вопрос:

Я хочу получить количество комментариев с интервалом не менее одного часа. Я написал этот запрос, и он отлично работает.

 SELECT COUNT(`c`.`id`) `count`
FROM `comments` `c`
WHERE `user_id` = '28' AND (TIMESTAMPDIFF(SECOND, (
    SELECT MAX(`created_at`)
    FROM `comments`
    WHERE `user_id` = '28' AND `created_at` < `c`.`created_at`
), `c`.`created_at`) > 3600 OR TIMESTAMPDIFF(SECOND, (
    SELECT MAX(`created_at`)
    FROM `comments`
    WHERE `user_id` = '28' AND `created_at` < `c`.`created_at`
), `c`.`created_at`) IS NULL)

Но, как вы можете видеть, это утверждение повторяется два раза:

     SELECT MAX(`created_at`)
    FROM `comments`
    WHERE `user_id` = '28' AND `created_at` < `c`.`created_at`

Как я могу написать его чище?

Скрипка SQL

Ответ №1:

Вы снова используете подзапрос, просто чтобы сравнить его с NULL .
Вы можете избежать этого , применив COALESCE() его при первом использовании, который вернет число, превышающее 3600, в случае его возврата NULL , чтобы он мог пройти сравнение:

 SELECT COUNT(c.id) count
FROM comments c
WHERE c.user_id = '28' 
  AND COALESCE(
        TIMESTAMPDIFF(
          SECOND, 
          (
            SELECT MAX(cc.created_at)
            FROM comments cc
            WHERE cc.user_id = c.user_id AND cc.created_at < c.created_at
          ), 
          c.created_at
        ), 
        3601
      ) > 3600

Если ваша версия MySQL 8.0 , вы можете использовать функцию окна LAG() вместо подзапроса:

 WITH cte AS (
  SELECT *, 
         TIMESTAMPDIFF(
          SECOND,
          LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at),
          created_at
        ) diff
  FROM comments
)
SELECT COUNT(id) count
FROM cte
WHERE user_id = '28' AND (diff IS NULL OR diff > 3600)

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

Метки: