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

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

"计算每个用户连续活跃的最大天数"——这道 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 处理。