如何按时间段分组查询10秒内存在2条及以上消息的会话ID？

核心思路说明 #

不要使用固定时间切片分组的逻辑，该方案会漏掉跨切片的相邻消息（比如13:09:09和13:09:11间隔仅2秒，但是如果按10秒切片会被分到两个组，无法匹配）。推荐用滑动时间窗口匹配的逻辑实现，以下是两种常用实现方式：

方案1：自关联匹配（兼容所有SQL版本） #

通过相同会话ID自关联，判断两条消息的时间差是否在10秒以内，只要存在匹配项就符合要求：

SELECT DISTINCT t1.id
FROM t t1
INNER JOIN t t2
  ON t1.id = t2.id
  -- 避免同一条消息自己匹配自己，也避免重复配对
  AND t1.message_id < t2.message_id
  -- 计算时间差，转成秒数后差值≤10即符合要求
  AND ABS(TIME_TO_SEC(t1.time) - TIME_TO_SEC(t2.time)) <= 10;

方案2：开窗函数实现（适用于MySQL8.0+/PostgreSQL等支持开窗的数据库） #

用LAG开窗函数取同一会话下上一条消息的时间，计算相邻消息的时间差，只要存在差≤10秒的会话就符合要求，性能比自关联更高：

WITH msg_time_diff AS (
  SELECT
    id,
    -- 计算当前消息和上一条同会话消息的秒差
    TIME_TO_SEC(`time`) - LAG(TIME_TO_SEC(`time`)) OVER (PARTITION BY id ORDER BY `time`) AS time_diff
  FROM t
)
SELECT DISTINCT id
FROM msg_time_diff
WHERE time_diff <= 10;

两种方案执行后都可以得到你预期的1、3两个会话ID的结果。

内容的提问来源于stack exchange，提问作者user458