DAY 20 W3 · 需求变绕了：子查询、CTE 与章法

每个类目销量前三：LATERAL

运营要做「每个类目热销 TOP3」看板。你上次用整表聚合再过滤的写法，数据一大就慢；这次学个新武器。

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

学 · 40 min

  1. 01LATERAL 让右侧子查询能引用左侧的列

    场景「每个用户最近 3 笔订单」：子查询里得写「这个用户的」--普通子查询够不着外层。

    join lateral (子查询)：右侧子查询里可以直接用左边表的列。执行直觉：对左表每一行，把它的值代进右查询跑一遍，结果并上来。它是「能返回一整组行的相关子查询」。

    select u.name, t.*
from users u
join lateral (
  select id, total_amount, created_at
  from orders o
  where o.user_id = u.id            -- 引用了左边的 u.id
  order by created_at desc
  limit 3
) t on true
order by u.name, t.created_at desc;

    易错LATERAL 只能出现在 FROM / JOIN 的右侧；子查询里引用的别名必须在它左边出现过。

  2. 02LEFT JOIN LATERAL (...) ON true 的固定写法

    场景有的用户没下过单--inner join lateral 会把他们整个吞掉。

    lateral 子查询没有传统意义的连接条件，语法上用 on true 占位；改成 left join lateral (...) on true，右查询空结果也保留左行（右侧补 NULL）。「每个 X 及其最新 Y，没有也要」= 这句话。

    select u.name, t.id, t.total_amount
from users u
left join lateral (
  select id, total_amount from orders o
  where o.user_id = u.id
  order by created_at desc limit 1
) t on true;

    易错少了 on true 是语法错；该用 left 的场景写成 inner，没下过单的用户静默消失。

  3. 03Top-N per group 的三种解法对比

    场景同一个需求三条路：整表 row_number、LATERAL、聚合后过滤。选哪条？

    ① row_number() over (partition by 组) + 外层过滤：通用、组数无所谓；② LATERAL：每个组只扫自己要的那几行（前提：排序列上有索引），组多且每组取少量时常胜；③ 老式聚合拼接：别用了。D23 会正式展开 ①，今天先用 ② 的身体记住这个对比。

    -- LATERAL 版：每个一级类目销量前 3 的商品
select c.name as 类目, t.name as 商品, t.销量
from categories c
join lateral (
  select p.name, sum(i.qty) as 销量
  from products p
  join order_items i on i.product_id = p.id
  where p.category_id = c.id
  group by p.id, p.name
  order by 销量 desc
  limit 3
) t on true
order by c.name, t.销量 desc;

    易错LATERAL 快的前提是「组内排序键有索引支撑」。没有索引时它退化成每组一次排序，可能更慢--所以 D20 练习要做耗时对比表。

  4. 04LATERAL 与相关子查询的关系

    场景越看越像 D16 的相关子查询--本来就是一家。

    本质都是「对外层每行执行一次的子查询」。区别在位置和产出：相关子查询放在 WHERE / SELECT 里，返回标量或布尔；LATERAL 放在 FROM 里，返回一组行、成为数据源。心法：要一个数，用相关子查询；要几行几列，用 LATERAL。

    -- 要一个数（每用户订单数）
select u.name, (select count(*) from orders o where o.user_id = u.id) as n
from users u;
-- 要几行（每用户最近 3 单）-- 上面 LATERAL 的例子

    易错SELECT 列表里的子查询必须标量；想让它返回多行多列，硬写只会报错，换 LATERAL。

练 · 65 min

  1. 用 LATERAL 查每个用户最近 3 笔订单
    参考答案

    LATERAL 让右侧子查询引用左侧的列：对每个用户把 u.id 代进右查询跑一遍。想保留没下过单的用户，把 join lateral 换成 left join lateral ... on true，右侧补 NULL。

    select u.name, t.id as 订单id, t.total_amount, t.created_at
from users u
join lateral (
  select id, total_amount, created_at
  from orders o
  where o.user_id = u.id            -- 子查询里直接用左边的 u.id
  order by created_at desc
  limit 3
) t on true
order by u.name, t.created_at desc;
  2. 用 LATERAL 查每个一级类目销量前 3 的商品
    参考答案

    别忘了商品挂在二级：LATERAL 里要经二级跳到一级（c2.parent_id = c.id）。外层没显式筛顶级也没错--二级类目匹配不到任何商品，LATERAL 返回空集被 inner join 自动丢弃；显式加 where c.parent_id is null 会更清楚。类目树更多层时，换成 D18 的递归先算子孙集合。

    select c.name as 一级类目, t.name as 商品, t.销量
from categories c
join lateral (
  select p.name, sum(i.qty) as 销量
  from products p
  join order_items i on i.product_id = p.id
  join categories c2 on c2.id = p.category_id
  where c2.parent_id = c.id         -- 该一级类目下面所有二级的商品
  group by p.id, p.name
  order by 销量 desc
  limit 3
) t on true
order by c.name, t.销量 desc;
  3. 用 ROW_NUMBER 把第 2 题再写一遍（预习下周窗口函数语法）
    参考答案

    整表聚合一遍、窗口函数编号、外层过滤三段式，下周的主角今天先混个脸熟。它不依赖索引，通用性最好；和 LATERAL 的性能分水岭见第 5 题。

    select 一级类目, 商品, 销量
from (
  select c1.name as 一级类目,
         p.name as 商品,
         sum(i.qty) as 销量,
         row_number() over (partition by c1.name
                             order by sum(i.qty) 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 c1.name, p.name
) t
where rn <= 3;
  4. 用 DISTINCT ON 写 N=1 的版本
    参考答案

    distinct on (列) = 每组保留排序后的第一行，所以只能做 N=1。两个约束：order by 必须以 distinct on 的列开头，后面的排序键决定每组留哪一行。PG 方言，别的库没有。

    select distinct on (c1.name)
       c1.name as 一级类目, p.name as 商品, sum(i.qty) as 销量
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 c1.name, p.name
order by c1.name, sum(i.qty) desc;
  5. 三种写法都跑 EXPLAIN ANALYZE，记录耗时做成对比表
    参考答案

    方法：把第 2 / 3 / 4 题各跑 explain analyze 三次，记录 Execution Time 做成「写法 | 计划要点 | 耗时」三列的表。预期：12 万明细、几十个组，三种都在几十毫秒量级、难分胜负；LATERAL 的优势要等「组多、每组只取少量、组内排序键有索引」才显现（每组只扫自己要的那几行）。结论先记方法，第 6 周百万行时回来重跑对比。

过关标准 能说出 LATERAL 在「分组多、每组取少量」场景下为什么可能更快。