SQL 窗口函数进阶实战：连续活跃天数计算，别再只用子查询嵌套

SQL 窗口函数进阶实战连续活跃天数计算别再只用子查询嵌套一、一个面试高频题藏着的工程思维差距计算每个用户连续活跃的最大天数——这道 SQL 题在数据分析面试中出现的频率大概是 90%。大多数候选人的解法是子查询 自关联代码嵌套三四层逻辑绕得自己都要捋半天。但实际上用窗口函数三行核心代码就能搞定而且执行效率高出几倍。这不仅仅是一道面试题。在实际业务中连续活跃天数是用户健康度的核心指标几乎每个 DAU 看板都在算。更广义地说所有连续区间类问题——连续签到、连续消费、连续打卡——本质上是同一类问题只是字段名不一样。flowchart TD A[原始数据: 用户ID 活跃日期] -- B[ROW_NUMBER按用户分组 按日期排序] B -- C[用日期减去行号 得到连续标识] C -- D{连续标识相同的行} D --|属于同一连续区间| E[分组计数 连续天数] D --|标识不同| F[新区间开始] E -- G[按用户取MAX 最大连续天数]二、从子查询到窗口函数思维模型的转变传统子查询方案的核心逻辑是对于每一行找下一行是否与当前行连续——这本质上是在用行级遍历的思维处理关系型数据。窗口函数的思维模型则是先定义一个分组基准再在分组内做计算。对于连续区间问题关键是构造一个同属一个区间的行具有相同标识的辅助列。核心技巧日期减去其在用户分组内的序号。WITH user_activity AS ( SELECT user_id, active_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_date) AS rn FROM user_daily_active WHERE active_date 2026-06-01 ), -- 关键一步日期减去行号连续的日期组会产生相同的差值 consecutive_groups AS ( SELECT user_id, active_date, DATE_SUB(active_date, INTERVAL rn DAY) AS grp FROM user_activity ) -- 按差值分组计数就是连续天数 SELECT user_id, MAX(consecutive_days) AS max_consecutive_days FROM ( SELECT user_id, grp, COUNT(*) AS consecutive_days FROM consecutive_groups GROUP BY user_id, grp ) t GROUP BY user_id;这个思路不是技巧而是一种思维模式当你识别出同一类行的 ID 相同这个模式后所有连续区间问题都能秒解。三、窗口函数的性能陷阱排序才是隐藏的成本窗口函数写起来很优雅但执行计划里藏着一个容易被忽视的成本——排序。ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_date)这条语句背后数据库需要先按(user_id, active_date)做一次全局排序。如果user_daily_active表有 2000 万行这个排序操作的内存消耗和耗时不容小觑。优化策略利用索引避免排序。如果(user_id, active_date)上有联合索引而且表本身就是按这个顺序物理存储的排序步骤可以直接省略。减少分区数量。PARTITION BY user_id会生成与用户数量相等的分区。如果活跃用户有 100 万窗口函数实际上在 100 万个分组内各做一次排序——每个分组内的排序成本可以忽略但 100 万次的开销累积起来依然可观。如果业务上不需要计算每个用户的连续天数应该用WHERE先过滤掉不活跃的用户。考虑使用LAG替代方案。在某些数据库中尤其是 MySQL 8.0LAG 条件判断的方案在某些索引条件下比ROW_NUMBER DATE_SUB更快WITH marked AS ( SELECT user_id, active_date, LAG(active_date) OVER (PARTITION BY user_id ORDER BY active_date) AS prev_date FROM user_daily_active ), interval_start AS ( SELECT user_id, active_date, CASE WHEN DATEDIFF(active_date, prev_date) 1 OR prev_date IS NULL THEN 1 ELSE 0 END AS is_new_interval FROM marked ) SELECT user_id, MAX(consecutive_days) AS max_consecutive_days FROM ( SELECT user_id, SUM(is_new_interval) OVER (PARTITION BY user_id ORDER BY active_date) AS interval_id, COUNT(*) OVER (PARTITION BY user_id, SUM(is_new_interval) OVER (PARTITION BY user_id ORDER BY active_date)) AS consecutive_days FROM interval_start ) t GROUP BY user_id;上面这个方案执行计划更复杂但在某些场景下因为排序压力分散而更快。没有绝对的哪个方案一定更好需要用EXPLAIN查看执行计划后按实际数据量做选择。四、窗口函数的更多实战场景累计求和与移动平均SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)计算最近 7 天的累计消费。注意ROWS BETWEEN和RANGE BETWEEN的区别——前者按物理行数计算窗口后者按值的范围计算在日期可能有缺失时行为完全不同。排名与百分位PERCENT_RANK()比手动(rank-1)/(total-1)更简洁而且数据库内部优化过的实现通常比手算快。同比环比计算LAG(metric, 7) OVER (PARTITION BY metric_name ORDER BY dt)取 7 天前的值一行代码搞定周环比。LAG(metric, 1)的默认值是NULL对于首行没有前一天的情况需要做好COALESCE处理。五、总结窗口函数的核心竞争力不在于能写出复杂的 SQL而在于用更少代码表达更清晰的意图同时获得更好的执行性能。连续区间问题的通用解法日期 - ROW_NUMBER()构造分组标识然后对标识做聚合。这个模式可以迁移到任何判断连续的场景。选择窗口函数方案时关注两个成本排序成本是否有索引覆盖和分组粒度PARTITION BY 的分组数。这两个因素决定了你写的窗口函数是在 2 秒内返回还是在 2 分钟内超时。