连续签到需按用户日期排序，用lag()获取前次日期，通过日期差是否为1判断断连并分组生成streak_id，再关联积分规则表计算奖励；注意null处理、数据库函数差异及索引优化。
连续签到怎么定义？先理清业务逻辑边界
连续签到不是“日期相减=1”就完事——用户可能跨月、跨年，还可能有节假日跳过。窗口函数本身不识别日历规则，得靠 LAG() 或 ROW_NUMBER() 配合日期差来分组。关键判断点是：两个签到记录的 DATEDIFF(day, prev_date, curr_date) 是否等于 1。注意 SQL Server 用 DATEDIFF(day, ...)，PostgreSQL 要写 curr_date - prev_date = 1，MySQL 同样用 DATEDIFF()，但参数顺序是 DATEDIFF(curr_date, prev_date)。
- 确保签到表有唯一用户标识（如
user_id）和非空日期字段（如check_in_date） - 日期字段必须是
DATE类型，不能是DATETIME带时分秒，否则同一天多次签到会干扰连续性判断 - 排序必须显式用
ORDER BY user_id, check_in_date，否则LAG()结果不可靠
用 LAG() + 差值分组识别连续段
核心思路：对每个用户按日期排序，用 LAG(check_in_date) 拿到上一次签到日期，再计算与当前日期的差值；差值 ≠ 1 就代表断连，打一个新组标记，最后用 SUM() 累计生成连续段 ID。
SELECT
user_id,
check_in_date,
SUM(is_new_streak) OVER (PARTITION BY user_id ORDER BY check_in_date) AS streak_id
FROM (
SELECT
user_id,
check_in_date,
CASE
WHEN DATEDIFF(day, LAG(check_in_date) OVER (PARTITION BY user_id ORDER BY check_in_date), check_in_date) != 1
THEN 1
ELSE 0
END AS is_new_streak
FROM check_in_log
) t
-
LAG()第一次调用返回NULL，DATEDIFF(NULL, ...)在多数数据库里报错，务必加IS NULL判断或用COALESCE(LAG(...), '1970-01-01')填充 - PostgreSQL 中直接用
check_in_date - LAG(check_in_date) OVER (...) != 1更安全，不依赖函数 - 这个
streak_id是连续段的唯一标识，后续统计天数、积分都靠它分组
按连续段聚合天数并查对应积分规则
有了 streak_id，就能用 COUNT(*) 算当前连续天数，再关联积分配置表。注意：积分规则通常是阶梯制（如连续 3 天奖 5 分，7 天奖 20 分），不能只看最大天数，而要看「当前段已持续多少天」。
WITH streaked AS (
SELECT
user_id,
check_in_date,
SUM(is_new_streak) OVER (PARTITION BY user_id ORDER BY check_in_date) AS streak_id
FROM (...)
),
streak_days AS (
SELECT
user_id,
streak_id,
COUNT(*) AS days
FROM streaked
GROUP BY user_id, streak_id
)
SELECT
s.user_id,
s.check_in_date,
p.points AS reward_points
FROM streaked s
JOIN streak_days sd ON s.user_id = sd.user_id AND s.streak_id = sd.streak_id
JOIN points_rule p ON sd.days BETWEEN p.min_days AND p.max_days;
- 积分规则表
points_rule必须保证min_days/max_days不重叠且覆盖完整区间，否则JOIN会漏匹配 - 如果只要最新一次签到的奖励（比如每天只发一次），需在最外层加
WHERE s.check_in_date = (SELECT MAX(check_in_date) FROM check_in_log c2 WHERE c2.user_id = s.user_id) - 大表慎用
JOIN+ 窗口函数嵌套，建议给(user_id, check_in_date)加联合索引
性能卡在哪？三个最容易被忽略的点
窗口函数本身不慢，慢在数据量大时重复扫描和排序。真正拖慢的是没控制好分区粒度、日期范围和 JOIN 方式。
- 千万级签到表，不加
WHERE check_in_date >= DATEADD(day, -30, GETDATE())就直接扫全表，LAG()排序成本指数上升 -
PARTITION BY user_id没问题，但如果查的是「所有用户最近连续段」，别忘了加ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY check_in_date DESC)先取每用户最新一条，再进主逻辑 - MySQL 8.0+ 支持窗口函数，但
LAG()在子查询里嵌套三层以上容易触发临时表，建议拆成临时表或 CTE 物化中间结果
连续段识别看似简单，实际最常崩在边界日期处理和 NULL 安全上。别急着套模板，先拿 5 条真实数据手算一遍 LAG() 和 DATEDIFF() 输出，比调半天执行计划更省时间。
