如何用SQL子查询计算两个连续事件的时间间隔

直接用 LAG() 窗口函数最稳妥；需先按时间排序、注意时区一致性，并确保索引覆盖排序字段，否则结果可能出错且难以排查。

子查询里怎么拿到“上一条记录”的时间？

直接用 LAG() 窗口函数最稳妥，别硬写相关子查询。很多新手试图用 (SELECT time FROM events e2 WHERE e2.id 去找上一行，但这样在 MySQL 5.7 或 SQL Server 旧版本里会报错或极慢——因为没索引支撑的子查询在大数据量下是 O(n²)。

真正可行的做法是：先用窗口函数生成带偏移时间的临时列，再做减法。PostgreSQL、MySQL 8.0+、SQL Server 2012+ 都支持 LAG()，语法统一且可读性强。

• LAG(time, 1) OVER (ORDER BY event_time) 拿上一行时间，注意必须明确 ORDER BY 字段（通常是时间戳或自增 ID）
• 如果事件按用户分组（比如每个用户的操作流），得加 PARTITION BY user_id，否则跨用户混算
• 首条记录的 LAG() 返回 NULL，减法结果也是 NULL，需用 COALESCE() 或 WHERE lagged_time IS NOT NULL 过滤

时间相减后单位不对怎么办？

不同数据库对时间相减的返回类型差异很大：PostgreSQL 返回 INTERVAL，MySQL 返回秒数（TIMESTAMPDIFF(SECOND, ...)），SQL Server 得用 DATEDIFF(second, ...)。硬写 time_col - lagged_time 在多数引擎里会报错或隐式转成天数。

• MySQL 推荐用 TIMESTAMPDIFF(second, LAG(event_time) OVER (ORDER BY event_time), event_time)
• PostgreSQL 直接 EXTRACT(EPOCH FROM (event_time - LAG(event_time) OVER (ORDER BY event_time))) 得到秒数
• SQL Server 必须用 DATEDIFF_BIG(millisecond, LAG(event_time) OVER (ORDER BY event_time), event_time)（DATEDIFF 有溢出风险）
• 别用 DATE_SUB/NOW() 类函数模拟，精度丢失且难维护

为什么按时间排序后还是算错了间隔？

常见原因是时间字段含毫秒但排序未精确到毫秒级，或者存在重复时间戳。比如两条事件都发生在 '2024-01-01 10:00:00'，ORDER BY event_time 无法保证稳定顺序，LAG() 可能取到“错误的上一条”。

• 务必在 ORDER BY 中加入唯一字段兜底，例如 ORDER BY event_time, id
• 检查时间字段是否为 TIMESTAMP（带时区）或 DATETIME（无时区），混用会导致跨时区计算偏差
• 如果原始数据有乱序（如日志采集延迟），先用子查询或 CTE 清洗：按业务逻辑重排时间，而不是依赖入库顺序

大表上跑子查询性能爆炸？

即使用了 LAG()，如果没索引，全表扫描照样慢。窗口函数本身不自动走索引，它依赖 ORDER BY 字段是否有有效索引。

• 给 ORDER BY 的字段建联合索引，例如 CREATE INDEX idx_events_time_id ON events(event_time, id)
• 避免在子查询里嵌套多层窗口函数，先用 CTE 提取基础行，再计算间隔
• 如果只要最近 N 条的间隔，加 WHERE event_time > NOW() - INTERVAL '7 days' 先过滤，别让窗口函数扫全表

时间间隔计算看着简单，但实际卡点都在排序稳定性、时区处理和索引覆盖上。漏掉任意一个，结果就可能错得离谱，而且很难一眼发现。