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

两个数字来自不同的表、不同的时间列，一条 SQL 聚不出来--必须先各自聚成「月份表」再对齐，这正是 CTE 存在的理由：给两段中间结果起名字，主查询只负责拼。为什么是 FULL JOIN：用户注册横跨两年、订单只有近 90 天，两边的月份集合互相都有对方没有的，LEFT 会丢掉只有订单的月、INNER 两头都丢。对齐后 coalesce 兜两处：月份本身（哪边有取哪边）和计数（没有就是 0）。

with new_u as (                          -- 注册侧：按月数新增用户
  select date_trunc('month', created_at) as 月份, count(*) as 新增用户数
  from users
  group by 1
),
act as (                                 -- 下单侧：按月数活跃用户（去重）
  select date_trunc('month', created_at) as 月份,
         count(distinct user_id) as 当月下单用户数
  from orders
  group by 1
)
select coalesce(n.月份, a.月份) as 月份,
       coalesce(n.新增用户数, 0) as 新增用户数,
       coalesce(a.当月下单用户数, 0) as 当月下单用户数
from new_u n
full join act a on a.月份 = n.月份
order by 1;

每个 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;

看计划的差异点：内联版里 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 个月」只过滤订单本身。如果先过滤再算首单，四个月前就下过单的用户会被误判成新客--五步法里「过滤放在哪一步」要想清楚再动手。

-- ① 粒度：月 × 新老客  ② 主表：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;

对比两版 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 复购用户数;