<p>LAG() 配合日期相减易算错连续天数，因未加 PARTITION BY user_id 会导致跨用户误关联，且需 TRUNC(sign_date) 去时间干扰、用 ROW_NUMBER() 差值法（sign_date - 基准日 - ROW_NUMBER()）生成连续段标识 grp，再 GROUP BY 统计；性能关键在组合索引 user_id + TRUNC(sign_date)。</p>
为什么 LAG() 配合日期相减容易算错连续天数
直接用 LAG(sign_date) 拿上一条记录的日期，再用当前 sign_date - LAG(sign_date) 判断是否等于 1，看似合理，但实际会漏掉用户中间断签后重新开始的连续段。Oracle 不会自动按用户分组重置窗口，也不处理多用户混排时的顺序错乱——如果没显式写 PARTITION BY user_id ORDER BY sign_date，LAG() 就在整个结果集里瞎跳。
实操建议：
- 必须加
PARTITION BY user_id，否则不同用户的签到日会被错误关联 -
ORDER BY sign_date要确保是严格升序；若同一天有多条记录，得先去重或加次级排序（如ORDER BY sign_date, id） - 用
sign_date - LAG(sign_date) OVER (...) = 1只能标记“是否接续”，不能直接得出“当前属于第几段连续”
用 ROW_NUMBER() 差值法识别连续区间
核心思路是：对每个用户按签到日排序，生成自然序号 r1；同时把签到日转成距某个基准日的天数差（比如 sign_date - DATE '2000-01-01'），记为 d；连续日期的 d 和 r1 会同步递增，差值 d - r1 恒定——这个恒定值就是连续段的唯一标识。
示例片段（关键逻辑）：
SELECT user_id,
sign_date,
(sign_date - DATE '2000-01-01') - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) AS grp
FROM user_sign_in
WHERE sign_date >= TRUNC(SYSDATE) - 30这样每段连续签到都会得到相同 grp 值，后续只需 GROUP BY user_id, grp 再统计 COUNT(*) 即可得各段天数。
注意点：
- 基准日选太远可能触发 Oracle 的
DATE算术溢出（虽罕见，但生产环境建议用TRUNC(sign_date)自身作偏移更稳妥） - 若原始表含重复日期（同一用户当天多次签到），必须提前
DISTINCT或用GROUP BY user_id, TRUNC(sign_date)归一化
查当前最长连续签到天数：别只 MAX(COUNT(*)) 嵌套两层
很多人写成子查询套子查询，既难读又容易在 GROUP BY 维度上出错。更直接的做法是：先按 user_id 和连续段分组求长度，再用窗口函数取每个用户的最大值。
推荐写法：
WITH seg AS (
SELECT user_id,
(TRUNC(sign_date) - DATE '1970-01-01')
- ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY TRUNC(sign_date)) AS grp
FROM user_sign_in
WHERE sign_date >= TRUNC(SYSDATE) - 90
),
len AS (
SELECT user_id, COUNT(*) AS cnt
FROM seg
GROUP BY user_id, grp
)
SELECT user_id, MAX(cnt) AS max_consecutive_days
FROM len
GROUP BY user_id关键细节：
- 外层不用再
PARTITION BY，因为GROUP BY user_id已足够 - 过滤条件（如最近 90 天）一定要放在最内层，避免无效数据拖慢
ROW_NUMBER()排序 - 用
TRUNC(sign_date)而非原字段，防止时间部分干扰日期连续性判断
性能卡在 ROW_NUMBER() 排序？加索引比调优 SQL 更有效
当用户量大、签到记录多时，PARTITION BY user_id ORDER BY sign_date 的排序成本极高。此时单纯重写 SQL 收效甚微，优先检查索引。
必须存在的组合索引：
CREATE INDEX idx_user_sign_date ON user_sign_in(user_id, TRUNC(sign_date));
说明：
- Oracle 对函数索引支持良好，
TRUNC(sign_date)索引能覆盖排序和过滤 - 不要只建
(user_id)单列索引——无法加速ORDER BY阶段 - 如果业务中频繁查“某用户最近 N 天”，可考虑加
WHERE条件的函数索引，如CREATE INDEX ... ON user_sign_in(user_id, sign_date) WHERE sign_date > SYSDATE - 100（需 Oracle 12c+）
连续签到统计真正难的不是写法，而是如何让 ROW_NUMBER() 在百万级数据上不变成定时锁表任务——索引没建对，再巧的窗口函数也跑不动。
