CTE

1. CTE 概述

公用表表达式（Common Table Expression，CTE）是 SQL 中定义临时结果集的机制，使用 WITH 关键字定义，在后续查询中引用。

1.1 基本语法

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

1.2 CTE vs 子查询 vs 临时表

2. 基本 CTE

2.1 单个 CTE

WITH dept_stats AS (
    SELECT
        dept_id,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT
    d.dept_name,
    ds.emp_count,
    ds.avg_salary
FROM departments d
JOIN dept_stats ds ON d.id = ds.dept_id
WHERE ds.emp_count > 10
ORDER BY ds.avg_salary DESC;

2.2 多个 CTE

WITH
dept_stats AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
),
high_salary_depts AS (
    SELECT dept_id FROM dept_stats WHERE avg_salary > 50000
)
SELECT e.name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN high_salary_depts hsd ON e.dept_id = hsd.dept_id
WHERE e.salary > (SELECT avg_salary FROM dept_stats WHERE dept_id = e.dept_id);

2.3 CTE 多次引用

-- CTE 可以在同一查询中多次引用
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS total
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    m1.month,
    m1.total AS current_month,
    m2.total AS previous_month,
    m1.total - m2.total AS diff,
    ROUND((m1.total - m2.total) * 100.0 / NULLIF(m2.total, 0), 2) AS growth_rate
FROM monthly_sales m1
LEFT JOIN monthly_sales m2 ON m1.month = m2.month + INTERVAL '1 month';

3. CTE 的优势

3.1 可读性提升

-- 不使用 CTE：深层嵌套
SELECT * FROM (
    SELECT * FROM (
        SELECT dept_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY dept_id
    ) t1
    WHERE avg_salary > 50000
) t2
ORDER BY avg_salary;

-- 使用 CTE：扁平化
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
),
high_salary_depts AS (
    SELECT * FROM dept_avg WHERE avg_salary > 50000
)
SELECT * FROM high_salary_depts ORDER BY avg_salary;

3.2 逻辑分步

WITH
-- 步骤1：计算用户消费总额
user_spending AS (
    SELECT user_id, SUM(amount) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
),
-- 步骤2：划分消费等级
user_tiers AS (
    SELECT
        user_id,
        total_spent,
        CASE
            WHEN total_spent >= 10000 THEN 'platinum'
            WHEN total_spent >= 5000  THEN 'gold'
            WHEN total_spent >= 1000  THEN 'silver'
            ELSE 'bronze'
        END AS tier
    FROM user_spending
)
-- 步骤3：统计各等级人数
SELECT tier, COUNT(*) AS user_count, AVG(total_spent) AS avg_spent
FROM user_tiers
GROUP BY tier
ORDER BY avg_spent DESC;

4. CTE 的物化

4.1 默认行为

大多数数据库中 CTE 是内联展开的（不物化），即每次引用时重新执行 CTE 查询。

-- 以下 CTE 引用两次，可能执行两次
WITH expensive_query AS (
    SELECT * FROM large_table WHERE complex_condition
)
SELECT * FROM expensive_query WHERE flag = 'A'
UNION ALL
SELECT * FROM expensive_query WHERE flag = 'B';

4.2 PostgreSQL 物化提示

-- PostgreSQL 12+：控制 CTE 是否物化
-- MATERIALIZED：物化为临时表，只执行一次
WITH dept_stats AS MATERIALIZED (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM dept_stats WHERE avg_salary > 50000
UNION ALL
SELECT * FROM dept_stats WHERE avg_salary <= 50000;

-- NOT MATERIALIZED：内联展开，可能执行多次
WITH dept_stats AS NOT MATERIALIZED (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM dept_stats WHERE avg_salary > 50000;

4.3 何时使用物化

-- 适合物化：
-- 1. CTE 查询开销大且被多次引用
-- 2. CTE 结果集较小

-- 不适合物化：
-- 1. CTE 只被引用一次
-- 2. CTE 查询简单，内联展开后优化器可做更多优化
-- 3. CTE 结果集很大，物化占用大量内存

5. CTE 在 DML 中的使用

5.1 INSERT with CTE

WITH new_users AS (
    SELECT DISTINCT email FROM staging_data
)
INSERT INTO users (email, created_at)
SELECT email, NOW() FROM new_users
ON CONFLICT (email) DO NOTHING;

5.2 UPDATE with CTE

WITH high_value_users AS (
    SELECT user_id FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 10000
)
UPDATE users SET tier = 'vip'
WHERE id IN (SELECT user_id FROM high_value_users);

5.3 DELETE with CTE

WITH expired_sessions AS (
    SELECT id FROM sessions
    WHERE last_active < NOW() - INTERVAL '30 days'
)
DELETE FROM sessions
WHERE id IN (SELECT id FROM expired_sessions);

6. CTE 的限制

6.1 作用域限制

-- CTE 只能在定义它的查询中使用
WITH cte1 AS (SELECT 1 AS val)
SELECT * FROM cte1;
-- 以下不能引用 cte1
-- SELECT * FROM cte1;  -- 错误！

-- 多个 CTE 中，后面的可以引用前面的
WITH
cte1 AS (SELECT 1 AS val),
cte2 AS (SELECT val + 1 AS val2 FROM cte1)  -- 可以引用 cte1
SELECT * FROM cte2;

6.2 不能在 CTE 内部引用自身

-- 非 RECURSIVE CTE 不能引用自身
WITH cte1 AS (
    SELECT * FROM cte1  -- 错误！非递归 CTE 不能自引用
)
SELECT * FROM cte1;

-- 递归 CTE 使用 RECURSIVE 关键字
WITH RECURSIVE cte1 AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte1 WHERE n < 10
)
SELECT * FROM cte1;