W04 增长团队入场：窗口函数 · 八周 SQL 冲刺计划

WEEK 04

增长团队入场：窗口函数

公司拿到融资，增长团队进场，user_logins 登录日志接了进来。他们不问「总共多少」，问「排名第几」「比上个月涨了多少」「连续登录了多少天」。GROUP BY 不再够用--窗口函数进场，这也是面试的分水岭。

目标：这周决定你是「会 SQL」还是「SQL 不错」。经典题型形成肌肉记忆。

0 / 7 天

D 22 登录日志接入：窗口函数是什么高频 45 / 60 / 15

剧情增长团队接入 30 万行登录日志，第一个需求就让你愣住：「每条登录记录旁边，带上这个用户总共登录过几次。」--行不能被压掉，还得聚合。

学 · 45 min

OVER (PARTITION BY ... ORDER BY ...) 三个部分各自的作用
场景「每条登录记录旁边带上该用户的登录总次数」--既不能丢行，又要有聚合值。

讲解公式：聚合/排名函数 + over (...) = 窗口函数。partition by user_id 把行按用户分堆（像 group by，但只是「围出计算范围」）；order by 在堆内排序（排名、位移类函数必需）；括号空着 = 全表一大堆。
试试
```
select user_id, login_at,
       count(*) over (partition by user_id) as 该用户登录总次数
from user_logins
limit 10;
```
易错 over() 不能省：聚合函数带 over 才是窗口函数，不带就是普通聚合（受 group by 管辖）。多写一个括号，语义两个世界。
与 GROUP BY 的本质区别：不压缩行数
场景用 group by 只能拿到「每个用户登录几次」4 行；需求要的是 30 万行明细、每行带个数。

讲解 group by 把 N 行压成每组织一行；窗口函数 N 行进去 N 行出来，只是每行旁边多了几列计算结果。判断口诀：需求要「只看汇总」用 group by；要「明细和统计值同框」用窗口。以前用「group by 再 join 回去」实现的报表，全部可以一句话换成窗口。
试试
```
-- group by：1 万行（每用户一行）
select user_id, count(*) from user_logins group by user_id;
-- 窗口：30 万行（每条记录一行，旁边带总数）
select user_id, login_at, count(*) over (partition by user_id) from user_logins;
```
易错看到「group by 完又 join 回原表拿明细」的 SQL，一律是窗口函数的存量债。
窗口函数在逻辑执行顺序中的位置（HAVING 之后、ORDER BY 之前）
场景 D6 背的九步顺序，今天要插进一个新步骤。

讲解窗口函数在 SELECT 阶段计算，位置在 HAVING 之后、DISTINCT / ORDER BY 之前。这意味着两件事：① 它看到的是 WHERE / HAVING 过滤之后的行（过滤条件影响了它的计算范围）；② WHERE 执行时它还不存在（下一条的坑）。
试试
```
-- 先过滤（WHERE）再算窗口：count 数的是"过滤后"的行
select user_id, login_at,
       count(*) over (partition by user_id) as n
from user_logins
where login_at >= current_date - 30;   -- 窗口只看到最近 30 天的行
```
易错想要「全量统计 + 只显示部分行」时，别在 WHERE 里过滤--先窗口再在外层过滤（子查询包一层）。
因此不能写在 WHERE / GROUP BY / HAVING 里
场景你顺手写了 where count(*) over (...) > 5，报错 window functions are not allowed in WHERE。

讲解原因就是执行顺序：WHERE 是第 3 步，窗口值第 6 步才算出来，WHERE 执行时它根本不存在。标准修法：子查询里先算窗口，外层再过滤。这个「包一层」的动作本周要形成肌肉记忆，天天用。
试试
```
select *
from (
  select user_id, login_at,
         count(*) over (partition by user_id) as n
  from user_logins
) t
where t.n > 5;      -- 过滤窗口结果，必须在子查询外面
```
易错同理 HAVING 里也不能用窗口函数；排序键里可以用（ORDER BY 在 SELECT 之后），但几乎没这个需求。
WINDOW w AS (...) 复用窗口定义
场景同一条 SQL 里三个函数要用同一个窗口，写三遍 partition by user_id order by login_at 太啰嗦。

讲解 window w as (partition by user_id order by login_at) 定义一次，函数处写 over w 复用。改窗口定义只改一处，长的分析 SQL 里非常值得。
试试
```
select user_id, login_at,
       row_number()     over w as rn,
       lag(login_at)    over w as 上一条,
       sum(1)           over w as 累计条数
from user_logins
window w as (partition by user_id order by login_at)
limit 10;
```
易错 WINDOW 子句只在同一个 SELECT 里生效，CTE / 子查询里得各自定义。

练 · 60 min

跑 seed.sql「W4」段导入 user_logins（30 万行）
一条 SQL 同时输出每条登录记录和该用户的登录总次数
用 GROUP BY 实现「每用户登录次数」，对比两者返回行数
试着在 WHERE 里用窗口函数，记下报错；再用子查询包一层解决
用 WINDOW 子句定义一次窗口，被三个函数复用

过关用一句话讲清窗口函数和 GROUP BY 的区别，并解释为什么 WHERE 里用不了。

专注视图 ->

D 23 排行榜：排名类窗口函数高频 40 / 65 / 15

剧情增长要「每个类目销量 TOP3 商品」和「消费额第二高的用户」--排名需求扎堆来了。

学 · 40 min

row_number / rank / dense_rank 在并列时的三种行为
场景排行榜出现并列：两个用户消费额一样并列第一，第三个人该排第几？三个函数三种答案。

讲解 row_number：1,2,3,4--并列也硬编不重复的号，像发工牌；rank：1,1,3--并列同名次、跳号；dense_rank：1,1,2--并列同名次、不跳号。要「第几名」的语义用 rank（奥运并列就是它）；要去重取行用 row_number；要「总共有几档」用 dense_rank。
试试
```
select user_id, sum(total_amount) as 消费额,
       row_number() over (order by sum(total_amount) desc) as rn,
       rank()       over (order by sum(total_amount) desc) as rk,
       dense_rank() over (order by sum(total_amount) desc) as drk
from orders
group by user_id
order by 消费额 desc
limit 10;   -- 找一组并列的行，对比三列
```
易错「消费额第二高的用户」经典题：用 row_number 会把并列第一的第二人错排成第二；正确答案是 rank（并列第一的两个之后，下一个 rank = 3，可能无人是 2）。
ntile(n) 分桶、percent_rank / cume_dist
场景把用户按消费额均分四档做分层运营；「打败了百分之多少的人」。

讲解 ntile(4) 按排序把行切成 4 个桶（余数给前面的桶），分层运营神器；percent_rank = (rank-1)/(总行-1)，区间 [0,1]，「排在前百分之几」；cume_dist 是累计比例，「我和比我强的共占多少」。
试试
```
select user_id, 消费额,
       ntile(4) over (order by 消费额 desc) as 档位,
       round(percent_rank() over (order by 消费额 desc)::numeric, 3) as 前百分之几
from (
  select user_id, sum(total_amount) as 消费额 from orders group by 1
) t;
```
易错 ntile 的桶大小不严格均分（行数除不尽时靠前的桶多一个）；percent_rank 在并列时给相同值。
用 row_number 做「每组取一条」的去重模板
场景登录日志有重复：同一天同一用户多条记录，分析前要先去重。

讲解模板三件套：row_number() over (partition by 业务键 order by 时间 desc)，外面 where rn = 1。业务键是什么（用户+日期？用户+日期+IP？）由需求定，但骨架永远不变。distinct 只能整行去重，这里要「部分列相同只留一条」。
试试
```
-- 每个用户每天只留最后一条登录记录
select user_id, login_at
from (
  select user_id, login_at,
         row_number() over (partition by user_id, login_at::date
                            order by login_at desc) as rn
  from user_logins
) t
where rn = 1;
```
易错「留哪一条」由 order by 决定--写忘了 order by，留哪条随缘。
Top-N per group 的标准写法
场景「每个一级类目销量 TOP3 商品」--D20 用 LATERAL 写过，今天是标准解法。

讲解两步走：派生表里 row_number() over (partition by 组 order by 度量 desc) as rn，外层 where rn <= N。通用、不用管组数、N 想取几取几。和 LATERAL 的分工：排序列有索引且组多时 LATERAL 可能快，通用性上这个模板完胜。
试试
```
select *
from (
  select p.name, c.name as 类目,
         sum(i.qty * i.unit_price) as 销售额,
         row_number() over (partition by c.name order by sum(i.qty * i.unit_price) desc) as rn
  from order_items i
  join products p on p.id = i.product_id
  join categories c on c.id = p.category_id
  group by p.id, p.name, c.name
) t
where rn <= 3;
```
易错 rn 的过滤必须在子查询外层（D22 讲过的「包一层」）；用 rank 时并列会让 TOP3 实际出 4 行--口径要说清。

练 · 65 min

同一查询里同时输出三种排名，找一组并列数据观察差异
查每个一级类目销售额 TOP3 的商品
用 row_number 对重复登录记录去重，每个用户每天只留一条
用 ntile(4) 把用户按累计消费分成四档并统计每档人数
查「消费额第二高」的用户（经典题，注意并列情况怎么处理）

过关不查资料手写出「每组 TOP N」模板；能说清三种排名函数在并列时分别输出什么。

专注视图 ->

D 24 环比与用户间隔：LAG / LEAD 40 / 65 / 15

剧情「上个月比这个月少了多少？」「用户两单之间隔了几天？」增长分析师嘴里的环比、同比、下单间隔，全都建立在「拿上一行」这个动作上。

学 · 40 min

lag(col, offset, default) / lead 的三个参数
场景「月度 GMV 的环比」--每行要拿到「上一行的值」。

讲解 lag(列, 偏移量, 默认值) 在窗口排序后取「往上第 offset 行」的值，offset 默认 1、默认值默认 NULL；lead 往下取。排好序的表格里「上一行 / 下一行」这个动作，SQL 里就靠它俩。
试试
```
select 月份, gmv,
       lag(gmv) over (order by 月份) as 上月gmv,
       round(100.0 * (gmv - lag(gmv) over (order by 月份))
             / nullif(lag(gmv) over (order by 月份), 0), 1) as 环比pct
from (
  select date_trunc('month', created_at) as 月份,
         sum(total_amount) as gmv
  from orders group by 1
) t
order by 月份;
```
易错第一行 lag 拿到 NULL：算环比做除法前先 nullif / coalesce 处理，否则第一行的增长率是 NULL（还好不是报错，但要知道为什么）。
first_value / last_value / nth_value
场景给每笔订单带上「该用户的首单金额」，算和首单的差额。

讲解窗口内取指定位置的值：first_value(x) 第一行、nth_value(x, n) 第 n 行、last_value(x) 最后一行。first_value 最常用：把「该组的第一条」贴到每一行上。
试试
```
select user_id, created_at, total_amount,
       first_value(total_amount) over w as 首单金额,
       total_amount - first_value(total_amount) over w as 与首单差额
from orders
window w as (partition by user_id order by created_at);
```
易错 last_value 有个大坑--见今天的第 4 条，它常常「返回的不是你以为的最后一行」。
相邻记录差值的通用套路
场景用户两单之间隔了几天、两笔支付之间隔了多久--全是同一个模式。

讲解模板：当前值 - lag(值) over (partition by 谁 order by 何时)。时间是 timestamp，相减直接得 interval。注意 partition by 别漏--不分区的话，拿到的是「上一个任何人的行」，数字全错但看起来特别像对的。
试试
```
select user_id, created_at,
       created_at - lag(created_at) over (partition by user_id order by created_at) as 距上一单
from orders;
```
易错「看起来像对的」是这类错误最阴的地方：数值都是真实间隔，只是隔错了对象。多用户数据一定先 partition。
为什么 last_value 常常返回「当前行」--引出明天的框架
场景你想取「该用户最后一单的金额」，结果每一行返回的都是它自己。复现一下这个怪现象。

讲解罪魁是默认窗口框架：有 ORDER BY 时，默认框架是「从组头到当前行为止」（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。last_value 在这个范围里取最后一行--范围正好到当前行，所以永远是当前行。这不是 bug，是框架语义。怎么改框架，明天一整天讲。
试试
```
-- 复现：last_value 每行都等于当前行
select user_id, created_at, total_amount,
       last_value(total_amount) over (partition by user_id order by created_at) as 最后一单
from orders;
```
易错应急修法有两个：把框架显式撑到组尾（明天学），或者反过来用 first_value + order by created_at desc 倒排取第一行。

练 · 65 min

算月度 GMV 的环比增长率
算每个用户相邻两次下单的间隔天数
用 lag(x, 12) 算同比（数据不够 12 个月就造小表验证）
用 first_value 给每行带上该用户的首单金额，计算与首单的差额
用 last_value 取「该用户最后一单金额」，复现返回当前行的现象

过关能解释 last_value 为什么要改窗口框架才正确。

专注视图 ->

D 25 移动平均与累计曲线：窗口框架高频 45 / 60 / 15

剧情增长要「7 日滑动 GMV」「累计 GMV 曲线」。你发现同样的 sum() OVER ()，写不写 ORDER BY 结果完全不同。

学 · 45 min

默认框架：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
场景同一个 sum() over (...)，写不写 order by 结果天差地别--先搞清「框架」是什么。

讲解窗口函数的完整语法是 over (partition by ... order by ... 框架)。框架（frame）回答：每一行的计算范围到哪里为止。有 ORDER BY 时默认 = 从组头到当前行（所以 sum 变成累计和）；没 ORDER BY 时默认 = 整组（所以 sum 是组内总和）。
试试
```
select 月份, gmv,
       sum(gmv) over (order by 月份) as 累计,      -- 到当前行为止
       sum(gmv) over ()                 as 总和    -- 整组（全表）
from (select date_trunc('month', created_at) as 月份, sum(total_amount) as gmv
      from orders group by 1) t;
```
易错「不小心多写了 order by，sum 变累计」是本周最容易犯的错--写聚合窗口前先想清楚要不要框架。
ROWS（按行数）vs RANGE（按值）vs GROUPS
场景算 7 日移动平均写了 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW--这个 ROWS 是什么，能换成 RANGE 吗？

讲解 ROWS 按物理行数划范围（往上看 6 行）；RANGE 按排序值划范围（排序值和当前行相同/之前的都算，并列的行一起进来）；GROUPS 按「同值行组」计数。排序列没有重复值时 ROWS 和 RANGE 结果相同；一旦有并列（同一天多行），两者结果就分叉。
试试
```
-- 造一组带重复时间戳的数据，对比两种框架
select d, v,
       sum(v) over (order by d rows   between 1 preceding and current row) as 前一行,
       sum(v) over (order by d range  between interval '1 day' preceding and current row) as 按值
from (values (timestamp '2026-01-01 10:00', 10), (timestamp '2026-01-01 12:00', 20),
             (timestamp '2026-01-02 09:00', 30), (timestamp '2026-01-03 08:00', 40)) t(d, v);
```
易错排序列有重复值时 RANGE 会把「同值的所有行」拉进范围，数字悄悄变大--又一个报表口径事故源。
UNBOUNDED PRECEDING / N PRECEDING / CURRENT ROW / FOLLOWING
场景「7 日移动平均」「近 30 日累计」--把框架边界这几个词玩熟。

讲解框架边界四个词：unbounded preceding 组头、N preceding 往上 N 行、current row 当前行、N following / unbounded following 往下 N 行 / 组尾。between 两头都含。7 日移动平均 = rows between 6 preceding and current row（含当前共 7 行）。
试试
```
select 日期, gmv,
       round(avg(gmv) over (order by 日期
             rows between 6 preceding and current row), 0) as 七日均值,
       sum(gmv)  over (order by 日期) as 累计
from (select created_at::date as 日期, sum(total_amount) as gmv
      from orders group by 1) t;
```
易错写 6 preceding 不是 7 preceding--between 是闭区间，含当前行一共 7 行。
有 ORDER BY 和没有 ORDER BY 时默认框架不同
场景昨天 D24 第 5 题的坑，今天正式收掉。

讲解汇总：无 ORDER BY -> 整组；有 ORDER BY -> 组头到当前行（RANGE 语义）。所以 last_value 想取「真正的最后一行」，必须把框架撑满：rows between unbounded preceding and unbounded following。
试试
```
select user_id, created_at, total_amount,
       last_value(total_amount) over (
         partition by user_id order by created_at
         rows between unbounded preceding and unbounded following   -- 撑到组尾
       ) as 最后一单金额
from orders;
```
易错另一条路：first_value + order by 倒排（desc），取「排序后的第一行」= 最后一行--不用记长框架，语义也更直白。

练 · 60 min

算 7 日移动平均 GMV（ROWS BETWEEN 6 PRECEDING AND CURRENT ROW）
算每日累计 GMV
造一组有重复排序值的数据，对比 ROWS 与 RANGE 的结果差异
去掉 ORDER BY 再跑一次累计求和，解释结果为什么变了
用正确的框架修正 D24 第 5 题的 last_value

过关说出默认框架是什么、在什么数据下会给出反直觉的结果。

专注视图 ->

D 26 「80% 的 GMV 是谁贡献的」：占比与百分位 35 / 70 / 15

剧情老板问了个致命问题：「我们 80% 的 GMV 是多少头部用户贡献的？」--组内占比与百分位专场。

学 · 35 min

sum / avg / count / max 作为窗口函数使用
场景明细行旁边要同时出现「组内总和、组内均值」--普通聚合做不到同框。

讲解任何聚合函数加 over 都是窗口版：sum() over (partition by ...) 在每行旁边贴上组内总和。这比「先 group by 算汇总，再 join 回明细」少一步、快一截、还更可读。
试试
```
select id, user_id, total_amount,
       sum(total_amount) over (partition by user_id) as 该用户总额,
       avg(total_amount) over (partition by user_id) as 该用户均值
from orders;
```
易错窗口版聚合和普通聚合不是二选一：要明细带汇总用窗口，要纯汇总报表用 group by。

组内占比的通用写法：x / sum(x) OVER (PARTITION BY g)

场景「每个商品销售额占其类目的比例」--分母是组内总和。

讲解占比 = 分子 / sum(分子) over (partition by 组)，一行搞定，不用子查询。要同时算「占类目%」和「占全站%」就嵌两个不同分区的 sum：partition by 类目和空括号（全表）。分母分区是什么，占比就是什么口径。

试试

select p.name as 商品, s.销售额,
       round(100.0 * s.销售额 / sum(s.销售额) over (partition by s.类目), 1) as 占类目pct,
       round(100.0 * s.销售额 / sum(s.销售额) over (),                  1) as 占全站pct
from (
  select p.id, p.name, c.name as 类目, sum(i.qty * i.unit_price) as 销售额
  from order_items i
  join products p on p.id = i.product_id
  join categories c on c.id = p.category_id
  group by p.id, p.name, c.name
) s;

易错占比列求和应 = 100% 是自检手段；round 后可能是 99.9 / 100.1，属于舍入误差，交付时说一句即可。

明细行同时带组均值、与均值的差
场景哪些订单「明显高出该用户的平均水平」--离群排查。

讲解 x - avg(x) over (partition by 组)：每行直接标出离组均值多远。加上第 1 条的组均值列，一张表看懂「这个用户的正常水位在哪、这单偏了多少」。
试试
```
select id, user_id, total_amount,
       avg(total_amount) over (partition by user_id) as 用户均值,
       total_amount - avg(total_amount) over (partition by user_id) as 偏离均值
from orders;
```
易错 avg 跳过 NULL 行（D4 的分母口径问题在窗口版同样存在）--列里有 NULL 时先想清楚分母。

percentile_cont 算分组中位数

场景「每个类目的价格中位数」--中位数没有窗口函数版，语法也和别的聚合长得不一样。

讲解 percentile_cont(0.5) within group (order by 列) 算任意分位数（0.5 = 中位数），配合 group by 出分组结果。它是分组聚合不是窗口函数，PG 也不支持给它加 over（ordered-set 聚合没有窗口版，直接报错）--想要「明细行带组内中位数」，把分组结果写成 CTE 再 join 回明细。

试试

-- 每个一级类目的价格中位数（分组聚合版）
select c.name as 类目,
       percentile_cont(0.5) within group (order by p.price) as 价格中位数
from products p
join categories c on c.id = p.category_id
group by c.name;

-- 明细带组内中位数：分组结果当 CTE 再 join 回去（没有窗口版可走捷径）
with m as (
  select category_id,
         percentile_cont(0.5) within group (order by price) as 中位数
  from products
  group by 1
)
select p.name, p.price, m.中位数
from products p join m on m.category_id = p.category_id;

易错 PG 没有 median() 函数；within group 这个子句别漏--漏了语法就错。给 ordered-set 聚合加 over 也是语法错。

练 · 70 min

每个商品销售额占其所属一级类目的比例
每个一级类目销售额占全站的比例（同一条 SQL 里两个占比都要有）
每个用户消费额的百分位排名
每个一级类目的价格中位数
每笔订单金额与该用户平均客单价的差额

过关一条 SQL 输出「商品 | 销售额 | 占类目% | 占全站%」四列，两个占比列各自求和自洽。

专注视图 ->

D 27 增长团队的经典题库：12 道实战高频 15 / 90 / 15

剧情增长甩来他们的经典题库：连续登录、次日留存、漏斗、头部贡献……这 12 道题也是互联网面试的常驻嘉宾。

学 · 15 min

「差值分组法」（gaps and islands）的核心思路：连续序号 − 行号 = 常量
场景「连续登录 7 天的用户」是这 12 题里最绕的：日期本身不连续，SQL 怎么「识别连续」？

讲解套路两步：① 对每个用户的登录日期去重后编号：日期本身的序号（login_at::date - 起始日 得整数）和行号 row_number()；② 两者相减：只要日期连续，差值恒定；一断档，差值跳变。按「用户 + 差值」分组，每组就是一段连续区间，count >= 7 即答案。这个手法叫 gaps and islands（找岛），连续 N 天、连续上涨、连续签到全是它。
试试
```
with d as (
  select distinct user_id, login_at::date as d from user_logins   -- 先去重！
),
g as (
  select user_id, d,
         d - (row_number() over (partition by user_id order by d))::int as grp  -- 连续段标识
  from d
)
select user_id, min(d) as 起始, max(d) as 结束, count(*) as 天数
from g
group by user_id, grp
having count(*) >= 7;   -- 连续登录 7 天以上的用户及区间
```
易错日期必须先 distinct：同一天多条记录会把 row_number 顶歪，差值失去「连续段标识」的作用。

练 · 90 min · 这 12 题要背下来

查连续登录 7 天及以上的用户（差值分组法）
把连续的登录日期区间合并成「起止区间」
算次日留存率（注册次日是否登录）
算 7 日留存率
每个一级类目销量 TOP3 商品
销售额排名，并列名次要正确
算每个用户的消费额累计占比，找出贡献 80% GMV 的头部用户
下单 -> 支付的时间漏斗转化率
找出 GMV 连续 3 天上涨的日期
每个用户的第 2 笔订单
算每个一级类目的销售额中位数
同比环比一起输出的月度报表

过关12 题全部跑通；第 1、3、5 题能闭卷重写。

专注视图 ->

D 28 入职满月大测测评 0 / 90 / 30

剧情入职满月（第 28 天）。一次 90 分钟闭卷大测，检验前四周的全部家当。

规则

闭卷、限时 90 分钟、不许查文档
正确 < 7 题 -> 用第 5 周的复盘时段补 W3–W4，不要硬推进度

10 题构成 · 120 min

3 道窗口函数题（含 1 道连续区间）
2 道递归 / LATERAL 题
5 道多表聚合报表题
交卷后逐题写错因，更新 mistakes.md
重绘一张前四周的知识地图

过关10 题正确 ≥ 7 题。

专注视图 ->

增长团队入场：窗口函数

出题端点设置