SQL自关联子查询如何查找连续记录

自关联子查询并不适宜用于查找连续记录。这是因为它高度依赖于ON条件来匹配“前一天”的数据，但在实际应用中，这很容易引发诸多问题。例如，当日期字段包含时分秒信息时，如'2026-04-01 14:22:03'，减一天后变为'2026-03-31 14:22:03'，这种情况下几乎难以匹配到另一条记录，最终结果往往全为NULL；又如，若同一天存在多条记录，比如用户当天登录了3次，此时a.date = b.date - INTERVAL '1 day'会触发笛卡尔积式匹配，一条b行可能会与多条a行相连，从而导致重复计数；再如，一旦某天的数据缺失，像用户4月2日没有登录，那么4月3日就无法找到“4月2日”的前驱行，后续的连续段便会直接断裂，而且还无法知晓断点的具体位置。

相比之下，LAG()函数是按照排序取物理上的前一行，稳定性要高得多。不过，在使用LAG()函数时，必须注意一些实操细节。首先，需要建立(user_id, date)联合索引，并通过ORDER BY date NULLS LAST确保排序的唯一性。这样做的目的是为了避免在同一天存在多条记录时，数据库每次返回的顺序不一致，从而导致LAG()结果不稳定。此外，还需要使用累计和SUM(断点标识) OVER生成连续段ID，以便后续对连续段进行识别和处理。

自关联子查询不适合查连续记录——它容易漏行、错配、性能差，真要查连续性，优先用 LAG() 或 LEAD()。

为什么自关联子查询在连续日期场景下大概率出错

常见写法是：LEFT JOIN t a ON a.date = b.date - INTERVAL '1 day'。表面看是“找前一天”，但实际运行时问题一堆：

• 日期字段含时分秒（比如 '2026-04-01 14:22:03'），减一天后变成 '2026-03-31 14:22:03'，几乎不可能匹配到另一条记录，结果全为 NULL
• 同一天有多条记录（比如用户当天登录 3 次），a.date = b.date - INTERVAL '1 day' 会触发笛卡尔积式匹配，一条 b 行可能连上多条 a 行，导致重复计数
• 某天数据缺失（比如用户 4 月 2 日没登录），那 4 月 3 日就找不到“4 月 2 日”的前驱行，后续连续段直接断裂，且无法感知断点位置

LAG() 怎么写才不踩坑

LAG() 不依赖值匹配，只按 ORDER BY 取物理上一行，稳定性高得多。但必须注意几个实操细节：

• 排序必须唯一：ORDER BY date, id，光写 date 不够——同天多条记录时，数据库可能每次返回不同顺序，导致 LAG() 结果不稳定
• 别用字符串比较：date - LAG(date) OVER (...) = INTERVAL '1 day'，不是 LAG(date) = '2026-04-01' 这种字符串等值判断
• PostgreSQL 要加 NULLS LAST：ORDER BY date NULLS LAST，否则 NULL 排最前，首行 LAG() 拿不到值，导致第一行永远判为“不连续”
• MySQL 8.0+ 不支持 ::int 类型转换，断点标识得写成：IF(date != LAG(date) OVER (PARTITION BY user_id ORDER BY date), 1, 0)

怎么从“是否连续”升级到“圈出完整连续段”

LAG() 只能回答“当前行是否连上前一行”，但业务要的是“用户 A 在 4 月 1–3 日连续登录了几天”，这就得生成连续段 ID：

• 先算断点：当 date != LAG(date) OVER (...) + INTERVAL '1 day' 时标记为 1，否则为 0
• 再用累计和：SUM(断点标识) OVER (PARTITION BY user_id ORDER BY date ROWS UNBOUNDED PRECEDING)，每次断点就让编号 +1，相同编号的行属于同一连续段
• 最后 GROUP BY user_id, 连续段ID，就能算每段的 MIN(date)、MAX(date)、COUNT(*)

索引和性能陷阱必须提前防住

LAG() 慢不是函数本身慢，是它强制排序。没索引时，百万行表可能全表排序卡死：

• 必须建联合索引：CREATE INDEX idx_user_date ON events(user_id, date)，单列 date 索引无效
• 执行前跑 EXPLAIN，确认计划里没有 Sort 节点，且是 Index Scan 而非 Seq Scan
• 如果只查最近 7 天，先 WHERE date >= CURRENT_DATE - INTERVAL '7 days' 再开窗口，别让窗口函数扫全表

真正难的不是写出 LAG()，而是确保它背后有索引支撑、排序唯一、断点逻辑能覆盖业务定义的“连续”——这些地方一松懈，结果就 quietly 错了。