如何计算与上条消息间隔10秒内的消息数并筛选符合条件的聊天ID

解法1：窗口函数实现（支持MySQL 8.0+、PostgreSQL等主流SQL引擎） #

SELECT DISTINCT id
FROM (
    SELECT 
        id,
        TIMESTAMPDIFF(SECOND, `time`, LEAD(`time`) OVER (PARTITION BY id ORDER BY `time`)) AS interval_sec
    FROM t
) tmp
WHERE interval_sec <= 10;

逻辑说明：

• 内层子查询按id分组，组内按消息时间升序排列
• 用LEAD窗口函数取同id下一条消息的时间，计算两条消息的秒级间隔
• 外层过滤出间隔不超过10秒的记录，对id去重后得到符合要求的结果

解法2：自关联实现（兼容不支持窗口函数的低版本SQL引擎） #

SELECT DISTINCT t1.id
FROM t t1
JOIN t t2 
ON t1.id = t2.id 
AND t1.`time` < t2.`time`
AND TIMESTAMPDIFF(SECOND, t1.`time`, t2.`time`) <= 10;

你之前的GROUP BY思路不适用该场景：该方案需要预设固定的时间分组边界，无法覆盖任意两条间隔10秒以内的消息匹配，直接计算相邻消息时间差的方案更通用高效。

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