「花得比平均多的用户」：子查询三个位置

老板：「找出消费高于平均水平的那批人，我给他们发券。」平均值本身就要一条查询来算--你第一次需要查询里套查询。

学 35 min

练 70 min

盘 15 min

共 120 分钟

完成这一天

学 · 35 min

01标量子查询（SELECT 里）：必须只返回一行一列
场景「消费高于平均水平」--平均水平这个数，得先用一条查询算出来。
返回一行一列的子查询叫标量子查询，可以放在任何「单个值」能放的位置：SELECT 列表里、比较运算符右边。它先算一次，结果当一个常量用。
```
select user_id,
       sum(total_amount) as 消费额,
       sum(total_amount) - (select avg(total_amount) from orders) as 高于平均多少
from orders
group by user_id;
```
易错子查询返回两行就报错 more than one row returned--而且这是运行时才炸：测试数据少时潜伏，上线数据多了才炸。
02派生表（FROM 里）：必须起别名
场景「每个用户的订单数 + 用户名」--订单数要先聚合，users 的名字在另一张表，两步走。
from (子查询) t：先把子查询算成一张临时表，再对这张表连接、过滤。PG 强制要求给这个临时表起别名。它是「先聚合再连接」套路的标准载体--凡是你想说「先把 X 算出来，再和 Y 拼」，就是派生表。
```
select u.name, t.单数
from (
  select user_id, count(*) as 单数
  from orders
  group by user_id
) t
join users u on u.id = t.user_id;
```
易错忘写别名是语法错；别名和真实表撞名，连接时列的归属会看不清。
03条件子查询（WHERE 里）：单值与多值
场景「有过已支付记录的订单」--过滤条件本身是一张查询的结果。
用哪个运算符，取决于子查询吐出什么形状：一行一列配 = <>；一列多行配 IN / NOT IN；多列的极少（配行构造器）。写之前先问自己：这个子查询大概返回几行几列？
```
select *
from orders
where user_id in (select user_id from orders where status = 2);
-- 单值版：= (select avg(total_amount) from orders)
```
易错ANY / ALL 也接多值一列（如 > ANY 表示「大于其中任意一个」），知道存在即可，可读性差、少用。
04相关子查询：什么时候会被执行 N 次
场景同一条 SQL，小表上秒出，大数据量上突然慢几十倍--先怀疑它。
判断标准：子查询里引用了外层的列（如 o.user_id）就是相关子查询。逻辑上外层每行都要带着自己的值进子查询算一遍，N 行 = N 次。非相关子查询只算一次、结果当常量。明天 D17 整天跟它打交道。
```
-- 相关：每个用户和"他自己的"平均客单价比
select id, total_amount
from orders o
where total_amount >= (select avg(total_amount)
                       from orders o2
                       where o2.user_id = o.user_id);
```
易错EXPLAIN 里看到 SubPlan 出现在大行数节点之下，就是「每行执行一次」的现场，先估一下总行数 × 子查询成本。

练 · 70 min

用标量子查询输出「每个用户消费额 − 全站平均消费额」

参考答案

口径先说清：「全站平均消费额」的分母是用户数（先按用户聚合再 avg），不是订单数。标量子查询是非相关的，整条 SQL 里只算一次、结果当常量用。

select user_id,
       sum(total_amount) as 消费额,
       round(sum(total_amount)
             - (select avg(消费额)
                from (select user_id, sum(total_amount) as 消费额
                      from orders group by 1) x), 2) as 与全站平均的差额
from orders
group by 1;

用派生表先按用户聚合订单数，再连接 users 输出
参考答案

派生表必须起别名（PG 强制），它是「先聚合再连接」套路的标准载体：先把订单数算成一张临时表，再去 users 拿名字。
```
select u.name, t.单数
from (
  select user_id, count(*) as 单数
  from orders
  group by user_id
) t
join users u on u.id = t.user_id;
```
用 WHERE id IN (子查询) 查有过已支付记录的订单
参考答案

子查询返回一列多行，所以配 IN。写之前先问「子查询吐出什么形状」：一行一列配 =，一列多行配 IN / NOT IN。
```
select *
from orders
where user_id in (select user_id from orders where status = 2);
```
写一个返回多行的标量子查询，记录报错信息
参考答案

已取消的订单远不止一单，标量位置的子查询返回多行直接报错。注意这是运行时才炸：测试数据少时可能恰好一行、潜伏到上线。改成 in (...) 就是合法写法。
```
select id, total_amount
from orders
where total_amount = (select total_amount from orders where status = 3);
-- ERROR: more than one row returned by a subquery used as an expression
```

同一需求分别用派生表和 CTE 写，对比可读性

参考答案

两层结果完全一致，优化器眼里也是同一个东西；差别在读法。这题只有一层，差异不大--嵌到三层时 CTE 从上往下读的优势会碾压式显现，D19 整天都在写它。

-- 派生表版：从外往里读，数括号
select u.name, t.单数
from (select user_id, count(*) as 单数 from orders group by 1) t
join users u on u.id = t.user_id;

-- CTE 版：从上往下读，先定义后使用
with 每用户单数 as (
  select user_id, count(*) as 单数 from orders group by 1
)
select u.name, t.单数
from 每用户单数 t
join users u on u.id = t.user_id;

月报续集：商品 GMV 的 TOP10 及其占总 GMV 的比例（D13 缺的工具今天到货）
参考答案

D13 当时卡住的就是这个分母。为什么不能直接写 sum / sum？因为 GROUP BY 之后每组的 sum 只看得见本组，全站总额不在这个分组的视野里，必须另起一条查询--标量子查询返回一行一列、整条 SQL 只算一次，正好摆在除号右边当常量。LIMIT 发生在最后，不影响分母。自洽校验：去掉 LIMIT，全部商品的占比之和 = 100%。（D26 你会用 sum(...) over () 把它写成一行。）
```
select p.name as 商品,
       sum(i.qty * i.unit_price) as gmv,
       round(100.0 * sum(i.qty * i.unit_price)
             / (select sum(qty * unit_price) from order_items), 1) as 占比
from order_items i
join products p on p.id = i.product_id
group by p.id, p.name
order by gmv desc
limit 10;
```
月报续集：复购用户数（下单 ≥ 2 次的用户）及复购率
参考答案

D13 的另一道欠账，缺的就是 from (子查询) t 这一步。「先聚合、再对聚合结果统计」是派生表最典型的场景：里层把每人压成一行（粒度 = 用户），外层对这批行再数一次。三要素翻译：粒度 = 用户、过滤 = 下过单的用户、度量 = 单数 ≥ 2 的占比。注意分母是「下过单的用户」而不是全部注册用户--两个口径差一截，月报里必须写明用的哪个。
```
select count(*) filter (where 单数 >= 2) as 复购用户数,
       count(*) as 下单用户数,
       round(100.0 * count(*) filter (where 单数 >= 2) / count(*), 1) as 复购率
from (
  select user_id, count(*) as 单数
  from orders
  group by user_id
) t;
```

过关标准 能说出标量子查询在什么情况下会被执行 N 次（相关子查询）。

今日复盘

D+1 默写 D15 类目上线：树形结构与自连接

跑 seed.sql「W3」段：建 categories 并回填 products.category_id

参考答案

答案不是抄 SQL，是把 §C 段的三条语句找出来按顺序跑掉：第 2 条插二级依赖第 1 条的父类目，第 3 条回填依赖前两条，顺序不能乱。

-- seed.sql 的 §C 段共三条：插 30 个一级、插 45 个二级、回填 products.category_id
-- 在 psql 里逐条执行，灌完核对行数
select count(*) from categories;                       -- 75 = 30 + 45
select count(*) from products where category_id is not null;  -- 500：全部回填

D+3 重做 D13 月报日：八张业务报表

GMV 三个口径对账：全部订单 / 只算已支付 / 已支付且剔除负金额
参考答案

月报第一件事是把口径钉死：同一个「GMV」，三种算法三个数--全部订单（含未支付、已取消）、只算已支付、再把 D01 埋的负金额压成 0。三列并排摆出来，差额来自哪一眼就看见。用 LEFT JOIN 是因为 city 可能为空，别让 INNER 把订单吃掉。选哪个口径都算对，错的是不把选择写进报表备注。
```
select coalesce(u.city, '未知') as 城市,
       sum(o.total_amount) as 全部口径,
       sum(o.total_amount) filter (where o.status = 2) as 已支付口径,
       sum(greatest(o.total_amount, 0)) filter (where o.status = 2) as 剔负口径
from orders o
left join users u on u.id = o.user_id
group by 1
order by 已支付口径 desc nulls last;
```
D+7 口头解释 D09 商品和明细也到了：三表链与笛卡尔积事故

orders -> order_items -> products 的连接链

易错点

连接顺序按链条写、JOIN 一行一个，别把四张表挤在 FROM 里用逗号连接--老式逗号语法没有 ON，漏条件就是笛卡尔积且不报错。

学 · 35 min

01标量子查询（SELECT 里）：必须只返回一行一列

02派生表（FROM 里）：必须起别名

03条件子查询（WHERE 里）：单值与多值

04相关子查询：什么时候会被执行 N 次

练 · 70 min

今日复盘