DAY 24 W4 · 增长团队入场：窗口函数

环比与用户间隔：LAG / LEAD

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

学 40 min

练 65 min

盘 15 min

共 120 分钟

完成这一天

学 · 40 min

01lag(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（还好不是报错，但要知道为什么）。
02first_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 条，它常常「返回的不是你以为的最后一行」。
03相邻记录差值的通用套路
场景用户两单之间隔了几天、两笔支付之间隔了多久--全是同一个模式。
模板：当前值 - 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。
04为什么 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 拿到 NULL，环比自然为空（不是报错）；nullif 防分母为 0。W4 阶段只有约 3 个月数据，正好观察首行。

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 月份;

算每个用户相邻两次下单的间隔天数

参考答案

timestamp 相减直接得 interval；::date 相减得天数。partition by user_id 千万别漏--不分区拿到的是「上一个任何人的订单」，数值全是真的、对象全错了。

select user_id, created_at,
       created_at - lag(created_at) over (partition by user_id order by created_at) as 距上一单
from orders
limit 10;

-- 只要天数的话：日期相减得整数
select user_id, created_at,
       created_at::date - (lag(created_at) over (partition by user_id order by created_at))::date as 间隔天数
from orders
limit 10;

用 lag(x, 12) 算同比（数据不够 12 个月就造小表验证）

参考答案

同比环比是同一个 lag，只是偏移量从 1 换成 12。前 12 行「去年同月」是 NULL，属预期。第 6 周 §F 时间快进出两年数据后，同一条 SQL 换回 orders 直接可用。

-- 本库 W4 阶段只有约 3 个月订单，同比先用 18 个月的小表验证写法
with 小表(月份, gmv) as (
  select date '2025-01-01' + (n || ' month')::interval, 100 + n * 7
  from generate_series(0, 17) n
)
select 月份::date, gmv,
       lag(gmv, 12) over (order by 月份) as 去年同月,
       round(100.0 * (gmv - lag(gmv, 12) over (order by 月份))
             / nullif(lag(gmv, 12) over (order by 月份), 0), 1) as 同比pct
from 小表
order by 月份;

用 first_value 给每行带上该用户的首单金额，计算与首单的差额

参考答案

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 取「该用户最后一单金额」，复现返回当前行的现象
参考答案

预期现象：每一行的「最后一单」都等于它自己的金额。有 ORDER BY 时默认框架只到当前行为止，last_value 在这个范围里取最后一行，当然取到自己。这不是 bug，是框架语义，明天 D25 修。
```
select user_id, created_at, total_amount,
       last_value(total_amount) over (partition by user_id order by created_at) as 最后一单
from orders
limit 20;
```

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

学 · 40 min

01lag(col, offset, default) / lead 的三个参数

02first_value / last_value / nth_value

03相邻记录差值的通用套路

04为什么 last_value 常常返回「当前行」--引出明天的框架

练 · 65 min

出题端点设置