首页 > 数据库 >SQL窗口函数实战：连续活跃天数计算，告别子查询嵌套

SQL窗口函数实战：连续活跃天数计算，告别子查询嵌套

来源：互联网 2026-07-28 21:02:03

窗口函数以日期减行号构造连续区间标识，替代多层子查询，高效计算用户最大连续活跃天数。性能需关注排序成本与分区粒度，索引可优化，工程思维在于选择合适方案而非盲目使用。

一、一个面试高频题藏着的工程思维差距

先说说一个面试高频题，这题背后藏着真正能拉开差距的工程思维。

“计算每个用户连续活跃的最大天数”——这道 SQL 题在数据分析面试中间出现的频率，说 90% 估计都不夸张。大多数面试者一上来就是子查询套自关联，代码嵌套三四层，逻辑绕得自己都得捋半天。但其实只要用上窗口函数，三行核心代码就能搞定，执行效率还高出几倍。

长期稳定更新的攒劲资源： >>>点此立即查看<<<

SQL窗口函数实战：连续活跃天数计算，告别子查询嵌套

这可不是一道面试题那么简单。在真实业务里，“连续活跃天数”是用户健康度的核心指标，几乎每个 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_daysFROM (    SELECT user_id,           grp,           COUNT(*) AS consecutive_days    FROM consecutive_groups    GROUP BY user_id, grp) tGROUP 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_daysFROM (    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) tGROUP 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 处理。