当前位置：首页 > news >正文

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

news 2026/7/7 9:56:21

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.0），LAG+ 条件判断的方案在某些索引条件下比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处理。