mysql - 获取至少相隔一小时的评论数

Question

我想获得至少相隔一小时的评论数量。
我写了这个查询，它工作正常。

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 Fiddle

Answer 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) 

见 demo .