LAG()比ROW_NUMBER()更适合查登录断点，因LAG()可直接计算相邻登录日期差值精准定位缺口，而ROW_NUMBER()差值法仅分组连续段却不标异常；需先去重、TRUNC()截断时间、建函数索引保障性能。
直接结论：用 LAG() + ROW_NUMBER() 组合能快速定位“本该连续却断开”的用户，关键不是算最长天数，而是识别断点位置。
为什么不能只用 ROW_NUMBER() 差值法查异常
差值法（login_date - ROW_NUMBER()）擅长分组连续段，但它对“异常”不敏感——它把每一段都当正常处理，不会主动标出哪里断了。排查异常的核心是“找缺口”，而不是“数长度”。比如用户 A 在 6月1日、2日、4日登录，差值法会拆成两段（1–2日、4日），但你得手动比对才能发现缺了3日。这在千级用户里根本不可行。
真正有效的排查方式，是让每一行自己回答：“我跟上一次登录隔了几天？”
-
LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)能拿到上一行的日期 - 再用
login_date - LAG(...)直接算间隔天数，结果为2就说明中间断了一天 - 这个计算必须在去重后做，否则同天多次登录会导致
LAG()拿到相同日期，间隔算成0
LAG() 计算间隔时必须加 TRUNC()
Oracle 的 LOGIN_TIME 通常是 TIMESTAMP 类型，哪怕同一天不同时间登录，相减也会带小时分钟秒，导致 login_date - LAG(...) 出现 1.32 这种小数。业务定义的“连续”是自然日连续，不是 24 小时滚动连续。
所以必须先统一截断到日：
- 写法是
TRUNC(login_time)，不是TO_DATE(...)或隐式转换 - 如果表里没存日期字段，
TRUNC()必须放在最外层子查询或 CTE 里，否则LAG()会基于原始时间戳计算 - 错误示例：
LAG(login_time) OVER ...→ 得到的是带时分秒的时间戳，相减结果不可控
如何一次性标出所有断点并过滤出高风险用户
异常用户不是看“有没有断”，而是看“断得是否频繁/是否在关键节点”。比如运营要推 7 日签到奖，那断在第 6 天的用户比断在第 2 天的更值得干预。
推荐写法（Oracle 12c+）：
WITH dedup AS (
SELECT DISTINCT user_id, TRUNC(login_time) AS login_date
FROM user_login_records
WHERE login_time <= SYSDATE
),
with_lag AS (
SELECT user_id,
login_date,
LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date,
login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS gap_days
FROM dedup
)
SELECT user_id,
login_date,
prev_date,
gap_days
FROM with_lag
WHERE gap_days > 1
AND login_date >= SYSDATE - 30;-
gap_days > 1表示至少缺一天；若要抓“刚断开”的用户，可加prev_date >= SYSDATE - 7 - 最后的
AND login_date >= SYSDATE - 30是防止历史脏数据干扰，实际使用时按需调整时间窗口 - 不要在
WHERE里直接过滤gap_days IS NOT NULL——首条记录的LAG()必然为空，这不是异常
容易被忽略的索引和性能坑
当用户量超过百万，LAG() 窗口函数本身不慢，慢在排序和去重。Oracle 不会自动用 (user_id, login_time) 索引加速 TRUNC(login_time) 上的排序。
- 必须建函数索引：
CREATE INDEX idx_user_trunc_time ON user_login_records(user_id, TRUNC(login_time)); - 如果表很大且只查近期数据，优先加时间分区（按月或按周），比全局索引更有效
- 别在
ORDER BY里写TRUNC(login_time) DESC后又用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW——这会让优化器放弃索引，改用 SORT ORDER BY
真正卡住排查效率的，往往不是 SQL 写法，而是没意识到 TRUNC() 和索引的绑定关系。一个没建对的函数索引，能让 5 秒的查询变成 40 秒。
