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

计算两个连续事件之间的时间间隔，在SQL里其实是个很常见的需求。业务系统里经常要算“两次登录之间隔了多久”、“用户下单和付款之间差了几分钟”之类的逻辑。看起来简单，但实际操作中踩坑的人真不少。今天就说几个核心要点。

先说第一条原则：直接上LAG()窗口函数，比硬写相关子查询要靠谱得多。

很多新手朋友一上来就想着用(SELECT time FROM events e2 WHERE e2.id < e1.id ORDER BY id DESC LIMIT 1)这种写法去抓上一条记录。实话说，在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），SQL Server则需要用DATEDIFF。所以，硬写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'过滤一下，别让窗口函数去扫全表。

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