WITH子句（CTE）不只是让SQL好看，还有这些门道

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

WITH
step1 AS (
  SELECT user_id, MIN(login_date) AS first_login
  FROM user_login
  GROUP BY user_id
),
step2 AS (
  SELECT first_login, COUNT(1) AS new_users
  FROM step1
  GROUP BY first_login
)
SELECT * FROM step2 ORDER BY first_login;

SELECT * FROM (
  SELECT
    channel,
    avg_cost,
    ROW_NUMBER() OVER (ORDER BY avg_cost DESC) AS rn
  FROM (
    SELECT
      channel,
      ROUND(AVG(cost), 2) AS avg_cost
    FROM (
      SELECT * FROM ad_daily_cost
      WHERE dt >= DATE_SUB(current_date(), 7)
    ) filtered
    GROUP BY channel
  ) aggregated
) ranked
WHERE rn <= 5

WITH filtered AS (
  -- 第一步：过滤最近7天
  SELECT * FROM ad_daily_cost
  WHERE dt >= DATE_SUB(current_date(), 7)
),
aggregated AS (
  -- 第二步：按渠道算日均
  SELECT channel, ROUND(AVG(cost), 2) AS avg_cost
  FROM filtered
  GROUP BY channel
),
ranked AS (
  -- 第三步：排名
  SELECT *, ROW_NUMBER() OVER (ORDER BY avg_cost DESC) AS rn
  FROM aggregated
)
-- 第四步：取Top 5
SELECT * FROM ranked WHERE rn <= 5

-- 这两种写法在Hive/Spark中的执行计划完全相同
WITH filtered AS (SELECT * FROM big_table WHERE dt = '2025-01-01')
SELECT * FROM filtered;
 
SELECT * FROM (SELECT * FROM big_table WHERE dt = '2025-01-01') filtered;

WITH user_stats AS (
  -- 这个子查询涉及大量计算
  SELECT user_id, SUM(cost) AS total_cost
  FROM user_order
  GROUP BY user_id
)
-- 被引用了两次
SELECT a.user_id, a.total_cost, b.total_cost AS other_cost
FROM user_stats a
JOIN user_stats b ON a.user_id != b.user_id  -- 极端例子

在Hive/Spark中，user_stats这个CTE会被执行两次——因为它只是语法展开，不会缓存结果。如果CTE内部计算量很大，这就是双倍的开销。

解法：手动物化

-- 用临时表/临时视图物化CTE的结果
CREATE TEMPORARY VIEW user_stats AS
SELECT user_id, SUM(cost) AS total_cost
FROM user_order
GROUP BY user_id;
 
-- Spark中还可以用cache
CACHE TABLE user_stats;
 
SELECT ... FROM user_stats a JOIN user_stats b ...

不过大多数场景下CTE只被引用一次，不用担心这个问题。

PostgreSQL的特殊处理

值得一提的是，PostgreSQL 12之前的版本对CTE的处理和Hive/Spark不同——PostgreSQL会把CTE的结果物化（相当于自动建临时表），优化器不会把CTE内联到主查询中。

这导致两个后果：

1. CTE被引用多次时不会重复计算（好处）

2. 优化器无法对CTE内部做谓词下推等优化（坏处）

-- 在PostgreSQL 11中，WHERE条件不会被下推到CTE内部
WITH all_orders AS (
  SELECT * FROM user_order  -- 全表扫描
)
SELECT * FROM all_orders WHERE dt = '2025-01-01';
-- dt的过滤发生在CTE物化之后，全表已经被读出来了

PostgreSQL 12引入了MATERIALIZED / NOT MATERIALIZED关键字来控制：

WITH all_orders AS NOT MATERIALIZED (
  SELECT * FROM user_order
)
SELECT * FROM all_orders WHERE dt = '2025-01-01';
-- NOT MATERIALIZED允许优化器内联CTE，谓词可以下推

面试的时候如果对方用的是PostgreSQL，提到这个版本差异是加分项。

CTE的实战用法

用法一：分步调试

开发复杂SQL时，CTE可以逐步构建、逐步验证：

-- 开发时，先跑step1看看结果对不对
WITH step1 AS (...)
SELECT * FROM step1 LIMIT 10;
 
-- step1确认没问题，加上step2
WITH step1 AS (...),
     step2 AS (SELECT ... FROM step1 ...)
SELECT * FROM step2 LIMIT 10;
 
-- 逐步加，直到完整SQL

我写复杂的广告归因SQL都是这么开发的——先把数据清洗步骤写成CTE，确认每一步的中间结果正确，再往下接。比一口气写完再debug效率高太多了。

用法二：重复引用简化

虽然CTE不会自动缓存，但它可以避免你在SQL里复制粘贴同样的子查询：

WITH base_metrics AS (
  SELECT
    dt,
    channel,
    SUM(cost) AS cost,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks
  FROM ad_daily_cost
  WHERE dt BETWEEN '2025-01-01' AND '2025-01-31'
  GROUP BY dt, channel
)
SELECT
  a.dt,
  a.channel,
  a.cost,
  -- 日环比
  a.cost - b.cost AS dod_diff,
  -- 渠道占比
  a.cost * 100.0 / c.daily_total AS channel_pct
FROM base_metrics a
LEFT JOIN base_metrics b
  ON a.channel = b.channel
  AND a.dt = DATE_ADD(b.dt, 1)
CROSS JOIN (
  SELECT dt, SUM(cost) AS daily_total
  FROM base_metrics
  GROUP BY dt
) c
WHERE a.dt = c.dt

用法三：递归CTE

CTE还支持递归，可以处理层级关系数据（比如组织架构、分类树）：

-- 查找某个员工的所有上级
WITH RECURSIVE management_chain AS (
  -- 起点：目标员工
  SELECT emp_id, emp_name, manager_id, 1 AS level
  FROM employee
  WHERE emp_id = 'E100'
 
  UNION ALL
 
  -- 递归：找上级的上级
  SELECT e.emp_id, e.emp_name, e.manager_id, mc.level + 1
  FROM employee e
  JOIN management_chain mc ON e.emp_id = mc.manager_id
)
SELECT * FROM management_chain ORDER BY level;

递归CTE在面试中偶尔会考到，但Hive不支持递归CTE，Spark SQL从3.x开始有限支持。PostgreSQL和MySQL 8.0+完整支持。

CTE vs 临时表 vs 视图

特性	CTE	临时表	视图
------	-----	--------	------
作用范围	单条SQL内	当前Session	永久（除非DROP）
是否物化	通常不（PG特殊）	是	否
被多次引用	可能重复计算	只计算一次	每次引用都重新计算
适用场景	SQL内的逻辑拆分	中间结果需要复用	公共逻辑封装

选择原则：

只在一条SQL内用一次：CTE

同一条SQL内用多次且计算量大：临时表

多条SQL、多个任务都要用：视图