DAY 23 W4 · 增长团队入场：窗口函数 高频

排行榜：排名类窗口函数

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

学 40 min
练 65 min
盘 15 min
共 120 分钟

学 · 40 min

  1. 01row_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）。

  2. 02ntile(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 在并列时给相同值。

  3. 03用 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，留哪条随缘。

  4. 04Top-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

  1. 同一查询里同时输出三种排名，找一组并列数据观察差异
    参考答案

    并列时：row_number 硬编不重复的号、rank 同名次后面跳号、dense_rank 同名次不跳号。「第几名」语义用 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 20;

-- 本库消费额基本不并列，用一个小表把并列时的三种行为看死
select v,
       row_number() over (order by v) as rn,
       rank()       over (order by v) as rk,
       dense_rank() over (order by v) as drk
from (values (100), (100), (200)) t(v);
-- rn: 1,2,3   rk: 1,1,3   drk: 1,1,2
  2. 查每个一级类目销售额 TOP3 的商品
    参考答案

    商品挂在二级类目上，按一级聚合必须 categories 自连接两层。rn 的过滤必须在子查询外层（D22 讲过的「包一层」）。

    select *
from (
  select p.name as 商品, c1.name as 一级类目,
         sum(i.qty * i.unit_price) as 销售额,
         row_number() over (partition by c1.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 c2 on c2.id = p.category_id      -- 商品挂在二级
  join categories c1 on c1.id = c2.parent_id       -- 自连接到一级
  group by p.id, p.name, c1.name
) t
where rn <= 3
order by 一级类目, rn;
  3. 用 row_number 对重复登录记录去重，每个用户每天只留一条
    参考答案

    模板三件套：partition by 业务键、order by 决定留哪一条（这里留每天最后一条）、外层 rn = 1。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;
  4. 用 ntile(4) 把用户按累计消费分成四档并统计每档人数
    参考答案

    档位 1 是消费最高的一档。1 万用户除以 4 除不尽时靠前的桶多一行--ntile 不严格均分；带上 min/max 能看清每档的边界在哪。

    select 档位, count(*) as 人数,
       min(消费额) as 该档最低消费额, max(消费额) as 该档最高消费额
from (
  select user_id, sum(total_amount) as 消费额,
         ntile(4) over (order by sum(total_amount) desc) as 档位
  from orders
  group by user_id
) t
group by 档位
order by 档位;
  5. 查「消费额第二高」的用户（经典题，注意并列情况怎么处理）
    参考答案

    必须用 rank 不用 row_number：两个并列第一之后名次直接跳到 3，rk = 2 可能是空集--「不存在严格第二」本身也是正确答案。本库金额来自随机数，基本没有大并列，通常能查出一行。

    select user_id, 消费额
from (
  select user_id, 消费额,
         rank() over (order by 消费额 desc) as rk
  from (select user_id, sum(total_amount) as 消费额 from orders group by 1) s
) t
where rk = 2;
过关标准 不查资料手写出「每组 TOP N」模板；能说清三种排名函数在并列时分别输出什么。