如何在日志表中识别间隔超10分钟的序列并提取起止时间？

现有一张日志表，包含user（用户名）和timestamp（记录时间戳）两列。用户活跃时系统每10分钟生成一条记录，需处理后输出用户、序列开始时间（begin）、序列结束时间（end），规则如下：

• 序列定义：用户记录按10分钟连续生成，两条记录间隔超过10分钟时，当前序列结束，下一条记录开启新序列
• 单条记录的序列：begin为该记录时间，end为begin加10分钟

输入示例表 #

预期输出表 #

这类连续时间序列分组问题的核心是为每个用户的连续序列标记分组ID，再按分组聚合计算begin和end。

步骤1：获取每条记录的上一条记录时间 #

用窗口函数LAG()按用户分组、时间排序，匹配当前记录的前一条记录时间：

SELECT 
    user,
    timestamp,
    LAG(timestamp) OVER (PARTITION BY user ORDER BY timestamp) AS prev_timestamp
FROM log_table;

步骤2：标记新序列的起始点 #

计算当前记录与上一条记录的时间差，若超过10分钟（或为第一条记录），标记为新序列的起始，累计生成序列ID：

SELECT 
    user,
    timestamp,
    SUM(CASE 
        WHEN prev_timestamp IS NULL 
             OR TIMESTAMPDIFF(MINUTE, prev_timestamp, timestamp) > 10 
        THEN 1 
        ELSE 0 
    END) OVER (PARTITION BY user ORDER BY timestamp) AS sequence_id
FROM (
    SELECT 
        user,
        timestamp,
        LAG(timestamp) OVER (PARTITION BY user ORDER BY timestamp) AS prev_timestamp
    FROM log_table
) t;

步骤3：按序列分组计算begin和end #

按用户和sequence_id分组，取最小时间作为begin；若分组内只有单条记录，end为begin加10分钟，否则取最大时间作为end：

SELECT 
    user,
    MIN(timestamp) AS begin,
    CASE 
        WHEN COUNT(*) = 1 THEN DATE_ADD(MIN(timestamp), INTERVAL 10 MINUTE)
        ELSE MAX(timestamp)
    END AS end
FROM (
    SELECT 
        user,
        timestamp,
        SUM(CASE 
            WHEN prev_timestamp IS NULL 
                 OR TIMESTAMPDIFF(MINUTE, prev_timestamp, timestamp) > 10 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY user ORDER BY timestamp) AS sequence_id
    FROM (
        SELECT 
            user,
            timestamp,
            LAG(timestamp) OVER (PARTITION BY user ORDER BY timestamp) AS prev_timestamp
        FROM log_table
    ) t1
) t2
GROUP BY user, sequence_id
ORDER BY user, begin;

注意事项 #

• 不同SQL方言的时间函数有差异：比如PostgreSQL用EXTRACT(EPOCH FROM timestamp - prev_timestamp)/60计算分钟差，需根据实际数据库调整
• 确保timestamp字段为时间类型，若为字符串需先转换（如MySQL用STR_TO_DATE(timestamp, '%m/%d/%Y %H:%i:%s,%f')）

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