如何在MySQL 8.0中查找表记录间超过8秒的时间间隔及间隔长度

实现SQL #

你可以直接使用MySQL 8.0支持的窗口函数实现需求，把代码里的表名t替换为你实际的表名即可运行：

WITH distinct_time AS (
    SELECT DISTINCT `time` 
    FROM t 
    ORDER BY `time`
),
time_with_prev AS (
    SELECT 
        `time` AS current_time,
        LAG(`time`, 1) OVER (ORDER BY `time`) AS prev_time
    FROM distinct_time
),
valid_gap AS (
    SELECT 
        prev_time AS gap_time_start,
        TIMESTAMPDIFF(SECOND, prev_time, current_time) AS gap_length
    FROM time_with_prev
    WHERE TIMESTAMPDIFF(SECOND, prev_time, current_time) > 8
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY gap_time_start) AS gap_id,
    gap_time_start,
    gap_length
FROM valid_gap;

逻辑说明 #

1. 第一层CTE先对表中time字段去重，得到所有唯一的时间组，避免重复计算同一组的3条记录
2. 第二层CTE通过LAG窗口函数，把当前时间组和上一个时间组拉到同一行，方便计算间隔
3. 第三层CTE用TIMESTAMPDIFF计算两个时间的秒数差，筛选出间隔超过8秒的记录
4. 最后给符合要求的间隔生成连续序号作为gap_id输出，和你给出的示例结果完全匹配

内容的提问来源于stack exchange，提问作者J.Doe