「注册了但没买过」的用户：集合运算

增长侧的同事问：有多少用户注册了却一单没下？你发现这本质是个集合问题。

学 35 min

练 70 min

盘 15 min

共 120 分钟

完成这一天

学 · 35 min

01UNION vs UNION ALL：去重的代价
场景老板要「上海和北京的用户名单合成一张表」。
UNION 把两个查询的结果上下叠起来并去重（内部要做排序或哈希）；UNION ALL 只叠不去重。去重有真实代价：数据量大时 UNION 明显更慢。上海和北京的用户本来就不重叠，用 UNION 等于白白多付一次去重。
```
select name, city from users where city = '上海'
union all
select name, city from users where city = '北京';

-- 换成 union 再跑一遍，对比 EXPLAIN 里多出来的 Unique/Sort 节点
```
易错确认两段结果不可能重叠，就写 ALL；用它做「去重」是顺便的副作用，不是设计目标。

02INTERSECT / EXCEPT：集合的交与差

场景「注册了但没下过单」「既买过商品 1 又买过商品 2」--听着就是集合的交与差。

INTERSECT 取两个查询都有的行；EXCEPT 取前者减去后者。配套铁律：两边列数、列序、对应类型必须一致（列名可以不同）。EXCEPT 天生去重：减完每行只留一份。

-- 注册了但从未下单（和 D10 的反连接对照着看）
select id from users
except
select user_id from orders;

-- 既买过「商品1」也买过「商品2」（明细表没有 user_id，经 orders 带出；商品 id 是 uuid，先按名字查出 id）
select o.user_id
from orders o join order_items i on i.order_id = o.id
where i.product_id = (select id from products where name = '商品1')
intersect
select o.user_id
from orders o join order_items i on i.order_id = o.id
where i.product_id = (select id from products where name = '商品2');

易错集合运算的世界里没有「重复行」：两边都先去重再运算，和 JOIN 的世界规则不同。

03PG 特性 DISTINCT ON：每组取一条
场景「每个用户最新的一单」：按用户分组、取组内 created_at 最大的那行。用 GROUP BY 写很别扭。
distinct on (user_id) 是 PG 特产：结果里每个 user_id 只保留一行，而保留哪一行由 ORDER BY 决定。固定三件套：SELECT DISTINCT ON (列)、ORDER BY 的第一组列与它一致、再排取行依据。
```
select distinct on (user_id)
       user_id, id, created_at, total_amount
from orders
order by user_id, created_at desc;   -- 每个用户时间最大的一单
```
易错ORDER BY 打头的列必须和 DISTINCT ON 的列一致，否则报错。它和窗口函数 row_number() 的分工在 W3 会展开。

练 · 70 min

同一查询用 UNION 和 UNION ALL 各跑一次，对比 EXPLAIN 里多出来的节点
参考答案

UNION ALL 的计划只有一个 Append；UNION 在 Append 之上多出 HashAggregate（或 Sort + Unique）去重节点--去重是真实开销。两段结果天然互斥（一个用户只登记一个城市），确认不重叠就写 ALL。
```
explain
select name, city from users where city = '上海'
union all
select name, city from users where city = '北京';

explain
select name, city from users where city = '上海'
union
select name, city from users where city = '北京';
```
用 EXCEPT 查「注册了但从未下过单」的用户
参考答案

EXCEPT = 集合减法：左边是全部用户 id，右边是下过单的 user_id，差集就是答案。两边列数、列序、对应类型必须一致；EXCEPT 天生去重，结果每行只一份。
```
select id from users
except
select user_id from orders;

-- 只要个数的话包一层
select count(*) as 从未下单用户数
from (
  select id from users
  except
  select user_id from orders
) t;
```

用 INTERSECT 查「既买过商品 1 也买过商品 2」的用户

参考答案

注意明细表没有 user_id，用户信息要到 orders 里找；两侧各是「买过某商品的用户集合」，INTERSECT 取交集。两侧本来就各自去重，intersect 外面不用再套 distinct。

-- 明细表没有 user_id，得经 orders 换算回用户；商品 id 是 uuid，先按名字查出 id
select o.user_id
from orders o join order_items i on i.order_id = o.id
where i.product_id = (select id from products where name = '商品1')
intersect
select o.user_id
from orders o join order_items i on i.order_id = o.id
where i.product_id = (select id from products where name = '商品2');

用 DISTINCT ON (user_id) 取每个用户最新一单
参考答案

三件套：DISTINCT ON (user_id)、ORDER BY 打头列与它一致、第二键 created_at desc 决定每组留哪一行（最新的）。打头列不一致 PG 直接报错，这是它在替你把关。
```
select distinct on (user_id)
       user_id, id, created_at, total_amount
from orders
order by user_id, created_at desc;
```
用 LEFT JOIN 改写第 2 题，对比两种写法的执行计划
参考答案

结果相同，计划不同：反连接是 Hash Left Join + IS NULL 过滤，不用去重，数据量大时通常更快；EXCEPT 要对两边先去重再做集合减法。顺带记住 NOT IN 的坑：子查询里出现 NULL 会一行都不返回，本库 user_id 恰好非空，但别赌。
```
-- EXCEPT 版（第 2 题）
select id from users
except
select user_id from orders;

-- LEFT JOIN 反连接版
select u.id
from users u
left join orders o on o.user_id = u.id
where o.id is null;
```

过关标准 说出 UNION 相比 UNION ALL 多做了什么操作，因此何时该用哪个。

学 · 35 min

01UNION vs UNION ALL：去重的代价

02INTERSECT / EXCEPT：集合的交与差

03PG 特性 DISTINCT ON：每组取一条

练 · 70 min

出题端点设置