把绕需求写成人话：CTE 与五步拆解法

运营的原话：「近 3 个月，每月新客的 GMV 和老客的 GMV 分开算。」你决定以后拿到需求先拆步骤再动手，用 CTE 把每一步写成能读的段落。

学 40 min

练 65 min

盘 15 min

共 120 分钟

完成这一天

学 · 40 min

01WITH 基本语法与多级 CTE
场景派生表套派生表，三层括号已经读不懂了--需要给每个中间结果起名字。
with a as (...), b as (...引用 a...), c as (...引用 b...) select ...：后面的能用前面的，自顶向下一条流水线。给每段起业务名（清洗 / 聚合 / 排名），SQL 从一坨嵌套变成分段的作文--CTE 的核心价值是给思路命名。
```
with 聚合 as (
  select user_id, sum(total_amount) as gmv from orders group by 1
),
带名字 as (
  select u.name, a.gmv from 聚合 a join users u on u.id = a.user_id
)
select * from 带名字 order by gmv desc limit 10;
```
易错CTE 名别和真实表名撞（撞了会遮蔽原表，查错极难发现）；只能引用「写在它前面」的 CTE。
02PG 12 起 CTE 默认可内联，之前版本是优化栅栏
场景老资料说「CTE 能防止条件推入、能提速」--按版本说话。
PG 11 及以前：CTE 一律物化（算一次存临时表），并且是优化栅栏--外层的 where 推不进去。PG 12 起：只被引用一次的 CTE 默认内联（像视图一样展开进主查询，栅栏没了）；被引用多次的仍然物化。
```
select version();   -- 先知道自己站在哪个版本上讨论
-- PG 12+：这个 CTE 会被内联，user_id 条件能推入
with t as (select * from orders where status = 2)
select * from t where user_id = '...';
```
易错面试聊 CTE 必带版本号。不加版本说「CTE 是优化栅栏」，遇到懂 PG 的面试官直接扣分。
03MATERIALIZED / NOT MATERIALIZED 的显式控制
场景一个昂贵的 CTE 要被引用两次--到底算一遍还是两遍？
with t as materialized (...) 强制算一次存结果（引用 N 次也只算一次）；as not materialized 强制内联（每次引用展开一次）。默认规则记不住没关系--在意就显式写，语义摆在自己手里。
```
with t as materialized (   -- 昂贵计算只跑一次
  select user_id, count(*) as n from orders group by 1
)
select (select count(*) from t where n = 1) as 只下一单,
       (select count(*) from t where n >= 2) as 复购;
```
易错物化 = 写临时结果（可能落盘），有真实代价；不是免费缓存。「引用多次的昂贵 CTE」才值得物化。
04澄清误区：CTE 本身不是性能优化手段
场景网上文章标题《用 CTE 让查询快 10 倍》--你想知道它到底优化了什么。
CTE 解决的是可读性和拆解，不是速度。内联的 CTE 和一坨子查询在优化器眼里是同一个东西；物化倒是省了重复计算，但那是「你把逻辑组织对了」的副产品。快是因为索引、是因为少算了东西，从来不是因为 with 这个关键字。
易错为了「性能」把查询套 10 层 CTE，既不快也不可读--方向就错了。

05复杂需求五步法：定粒度 -> 找主表 -> 逐步补维度 -> 定过滤位置 -> 最后聚合排序

场景运营原话：「近 3 个月，每月新客的 GMV 和老客的 GMV 分开算。」--先在注释里拆，再写 SQL。

① 定粒度：一行 = 月份 × 新老客；② 找主表：事实在 orders；③ 补维度：「新客」要判定（首单是否落在当月）--先算每个用户的首单月份；④ 定过滤：近 3 个月在哪一步过滤；⑤ 聚合排序最后写。五步写在注释里，SQL 一段对应一步，写完的需求自己都能复查。

-- ① 粒度：月 × 新老客  ② 主表：orders
with 首单 as (                       -- ③ 维度：首单月份
  select user_id, min(created_at)::date as 首单日 from orders group by 1
)
select date_trunc('month', o.created_at) as 月份,
       case when date_trunc('month', f.首单日) = date_trunc('month', o.created_at)
            then '新客' else '老客' end as 客群,
       sum(o.total_amount) as gmv    -- ⑤ 最后聚合
from orders o
join 首单 f on f.user_id = o.user_id
where o.created_at >= date_trunc('month', current_date) - interval '3 months'  -- ④ 过滤
group by 1, 2
order by 1, 2;

易错不拆就写的人，一半概率写到一半发现粒度错了全部推翻重来。五步法是慢就是快。

练 · 65 min

把 D13 里最复杂的一题用 CTE 重写，对比可读性

参考答案

挑法：找当时嵌套两层以上、中间结果没有名字的那题。改法：把每层子查询拎出来，按「它算的是什么」起业务名变成 CTE，逻辑一行不改。预期现象：读 SQL 从「从外往里、数括号」变成「从上往下读流水线」；检验标准是隔一天再读，CTE 版能一眼说出每段在干嘛，原版要重新数括号。

写一个三级 CTE：清洗 -> 聚合 -> 排名

参考答案

每个 CTE 只干一件事、名字用业务动词，后面的引用前面的，自顶向下一条流水线。CTE 的核心价值是给思路命名，不是提速。

with 清洗 as (                            -- ① 剔除脏数据：负金额、空状态
  select *
  from orders
  where total_amount > 0 and status is not null
),
聚合 as (                              -- ② 每用户算指标
  select user_id, count(*) as 单数, sum(total_amount) as gmv
  from 清洗
  group by 1
),
排名 as (                              -- ③ 按消费额排名
  select *, row_number() over (order by gmv desc) as 排名
  from 聚合
)
select * from 排名 where 排名 <= 10;

同一查询加与不加 MATERIALIZED，对比执行计划差异

参考答案

看计划的差异点：内联版里 orders 的扫描节点直接挂着 user_id 过滤（扫的行数少）；物化版多一个 CTE Scan 节点，先算出 3 万多行已支付订单再过滤。uuid 是灌数脚本的确定性映射，编号 42 对应这个值。

-- 默认（PG 12+，只引用一次会内联）：user_id 条件被推进底层扫描
explain analyze
with t as (select * from orders where status = 2)
select count(*) from t
where user_id = '00000000-0000-0000-0000-000000000042';

-- 强制物化：先算出整个 CTE，再 CTE Scan 过滤
explain analyze
with t as materialized (select * from orders where status = 2)
select count(*) from t
where user_id = '00000000-0000-0000-0000-000000000042';

「近 3 个月每月新客 GMV 与老客 GMV」：先写五步拆解，再写 SQL

参考答案

最容易错的是③和④的配合：首单必须用全历史算，「近 3 个月」只过滤订单本身。如果先过滤再算首单，四个月前就下过单的用户会被误判成新客--五步法里「过滤放在哪一步」要想清楚再动手。

-- ① 粒度：月 × 新老客  ② 主表：orders（事实在订单上）
with 首单 as (                                -- ③ 维度：每个用户的首单时间（用全历史算）
  select user_id, min(created_at) as 首单时间
  from orders
  group by 1
)
select date_trunc('month', o.created_at) as 月份,
       case when date_trunc('month', f.首单时间) = date_trunc('month', o.created_at)
            then '新客' else '老客' end as 客群,
       sum(o.total_amount) as gmv             -- ⑤ 最后聚合
from orders o
join 首单 f on f.user_id = o.user_id
where o.created_at >= date_trunc('month', current_date) - interval '3 months'  -- ④ 过滤
group by 1, 2
order by 1, 2;

写一个被引用两次的 CTE，观察是否被计算两次

参考答案

对比两版 explain：默认版只有一个 GroupAggregate，CTE 结果被两个子查询共用；not materialized 版分组节点出现两份。结论：被引用多次的昂贵 CTE，默认的物化是在帮你省钱。

-- t 被引用两次：PG 会物化，分组只算一次
with t as (
  select user_id, count(*) as n from orders group by 1
)
select (select count(*) from t where n = 1)  as 只下一单,
       (select count(*) from t where n >= 2) as 复购用户数;

-- 强制不物化：同样的分组算两遍
with t as not materialized (
  select user_id, count(*) as n from orders group by 1
)
select (select count(*) from t where n = 1)  as 只下一单,
       (select count(*) from t where n >= 2) as 复购用户数;

过关标准 能说清 PG 里 CTE 什么时候会被物化、物化意味着什么代价。

学 · 40 min

01WITH 基本语法与多级 CTE

02PG 12 起 CTE 默认可内联，之前版本是优化栅栏

03MATERIALIZED / NOT MATERIALIZED 的显式控制

04澄清误区：CTE 本身不是性能优化手段

05复杂需求五步法：定粒度 -> 找主表 -> 逐步补维度 -> 定过滤位置 -> 最后聚合排序

练 · 65 min

出题端点设置