SQL CTE：公用表表达式与 WITH 子句

第 6.4 课 · 阅读时间：约 8 分钟 · 练习题 ↓

SQL CTE（公用表表达式）是用 WITH 子句定义的命名临时结果集，之后可以在主查询中像表一样使用。在本课中，你将学习 CTE 的语法、CTE 与子查询的比较、如何串联多个 CTE，以及如何构建仍然易于阅读的多步骤分析。

在前面的课程中，你在 FROM 和 WHERE 中编写过子查询。它们能够正常工作，但随着逻辑变复杂，嵌套的子查询会变得难以阅读：你必须从最内层的查询开始，一层层向外看。CTE 让你可以为每个步骤命名，并按照你思考的顺序从上到下写出这些步骤。

SQL CTE：嵌套子查询要从内向外阅读，而 WITH 子句从上到下阅读；CTE 链以及被引用两次的 CTE

SQL 中的 CTE 是什么？

公用表表达式是在语句开头定义的、带有名称的查询结果。它只在该语句执行期间存在：数据库中不会保存任何东西，其他查询也看不到它。

你可以把 CTE 看作一个带名称的子查询。它的主要优点是：

可读性：每个步骤都有一个有意义的名称。
复用：主查询可以多次引用同一个 CTE。
模块化：复杂的查询被拆分成若干小的逻辑步骤。
调试：每个 CTE 都可以单独运行和检查。

基本语法

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

WITH 引出 CTE。
cte_name 是主查询将要使用的名称。
括号中的查询定义 CTE 的行。
随后的主查询像使用普通表一样使用 cte_name。

你的第一个 CTE

找出累计消费超过 100 美元的客户：

WITH customer_spending AS (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
)
SELECT customer_id, total_spent, payment_count, avg_payment
FROM customer_spending
WHERE total_spent > 100
ORDER BY total_spent DESC;

结果：付款总额超过 100 美元的客户，以及他们的付款笔数和平均付款金额，消费最多的排在最前面。

CTE 计算每位客户的消费，主查询只负责过滤和排序。读者在看到计算方式之前，就能从名称 customer_spending 知道这些中间数据是什么。

CTE 与子查询

下面是用 FROM 中的子查询写的同一个查询：

SELECT customer_id, total_spent, payment_count, avg_payment
FROM (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
) AS spending_data
WHERE total_spent > 100
ORDER BY total_spent DESC;

两个查询返回相同的行。区别在于它们的阅读方式：

方面	CTE	`FROM` 中的子查询
阅读顺序	从上到下，一步一步	从内向外
命名	每个步骤都有有意义的名称	别名往往只是随手起的
复用	一次定义，多次引用	每次使用都要重复子查询
调试	每个 CTE 都能单独运行	很难单独拆出某一层嵌套

对于单个简短的步骤，子查询完全够用。当逻辑包含两个或更多步骤，或者同一个结果需要用两次时，CTE 通常更清晰。

一个查询中的多个 CTE

你可以定义多个 CTE，用逗号分隔。每个 CTE 都可以使用在它之前定义的 CTE：

WITH customer_spending AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM payment
    GROUP BY customer_id
),
high_spenders AS (
    SELECT customer_id, total_spent
    FROM customer_spending
    WHERE total_spent > 150
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       c.email,
       hs.total_spent
FROM high_spenders AS hs
JOIN customer AS c ON c.customer_id = hs.customer_id
ORDER BY hs.total_spent DESC;

结果：消费超过 150 美元的客户的姓名和电子邮件，消费最多的排在最前面。

这个查询读起来就像一个简短的计划：

customer_spending 汇总每位客户的付款。
high_spenders 只保留消费超过 150 美元的客户。
主查询从 customer 中补充姓名和电子邮件。

注意，WITH 只写一次，放在第一个 CTE 之前。

多次引用同一个 CTE

一个 CTE 可以在主查询中出现多次，例如把一个结果与它自身连接。下面把每个月的收入与上个月进行比较：

WITH monthly_sales AS (
    SELECT CAST(DATE_FORMAT(payment_date, '%Y-%m-01') AS DATE) AS month_start,
           SUM(amount) AS monthly_total
    FROM payment
    GROUP BY month_start
)
SELECT cur.month_start,
       cur.monthly_total AS current_sales,
       prev.monthly_total AS previous_sales,
       ROUND((cur.monthly_total - prev.monthly_total) / prev.monthly_total * 100, 2) AS percent_change
FROM monthly_sales AS cur
LEFT JOIN monthly_sales AS prev
       ON prev.month_start = cur.month_start - INTERVAL 1 MONTH
ORDER BY cur.month_start;

结果：每个月的收入、上个月的收入以及变化百分比。当上个月没有付款时（2005 年 5 月是第一个月，2006 年 2 月之前数据有中断），比较列为 NULL。

monthly_sales 只定义一次，却被使用了两次：分别作为 cur 和 prev。如果用子查询，你就得把同样的聚合写两遍，还要保持两份副本一致。

实践示例：每位客户最近一次租赁

一个常见的任务是找出每位客户最近的一次事件。使用 CTE，它可以拆成两个清晰的步骤：先找出最近的日期，再补充细节。

WITH last_rental AS (
    SELECT customer_id, MAX(rental_date) AS last_rental_date
    FROM rental
    GROUP BY customer_id
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       lr.last_rental_date
FROM customer AS c
LEFT JOIN last_rental AS lr ON lr.customer_id = c.customer_id
ORDER BY lr.last_rental_date
LIMIT 20;

结果：最长时间没有租过任何东西的 20 位客户，以及他们最近一次租赁的日期。

在第 7 模块中，你将看到当需要整行数据而不仅仅是日期时，窗口函数如何解决“每组最新一行”这类问题。

实践示例：同期群分析

CTE 特别适合多步骤分析。在同期群（cohort）分析中，客户按首次租赁的月份分组，然后按月跟踪他们的活动：

WITH customer_cohort AS (
    SELECT customer_id,
           CAST(DATE_FORMAT(MIN(rental_date), '%Y-%m-01') AS DATE) AS cohort_month
    FROM rental
    GROUP BY customer_id
),
monthly_activity AS (
    SELECT cc.cohort_month,
           CAST(DATE_FORMAT(r.rental_date, '%Y-%m-01') AS DATE) AS rental_month,
           r.customer_id
    FROM customer_cohort AS cc
    JOIN rental AS r ON r.customer_id = cc.customer_id
)
SELECT cohort_month,
       rental_month,
       COUNT(DISTINCT customer_id) AS active_customers,
       COUNT(*) AS rentals
FROM monthly_activity
GROUP BY cohort_month, rental_month
ORDER BY cohort_month, rental_month;

结果：对于每个同期群（首次租赁的月份），其客户在之后每个月中有多少人租过电影，以及他们租了多少次。

customer_cohort 把每位客户归入其首次租赁的月份。
monthly_activity 为每笔租赁附加所属的同期群。
主查询按同期群和月份统计活跃客户数和租赁次数。

CTE 与性能

CTE 首先是一种组织查询的方式。在大多数现代数据库中，简单的 CTE 与等价的子查询性能相同，因为优化器会把它内联到主查询中。

不同数据库之间存在差异：

PostgreSQL 在 12 版之前总是物化 CTE（把它计算一次并存入临时结果）。从 12 版开始，只被引用一次的 CTE 会被内联；AS MATERIALIZED 和 AS NOT MATERIALIZED 允许你自行选择。
MySQL（8.0+）和 MariaDB（10.2+）既可以把 CTE 合并到主查询中，也可以将其物化。被引用多次的 CTE 通常只计算一次。

如果带 CTE 的查询很慢，请像对待其他查询一样，用 EXPLAIN 检查它的执行计划。

要点：

CTE 是用 WITH 定义的命名临时结果集，只在一条语句中可用。
CTE 让你可以从上到下编写多步骤逻辑，并为每个步骤起一个有意义的名称。
多个 CTE 用逗号分隔，每个 CTE 都可以使用在它之前定义的 CTE。
一个 CTE 可以在主查询中被多次引用，无需重复同一个子查询。
单个简短步骤用子查询即可；多步骤逻辑通常用 CTE 更易读。
性能通常与子查询相同，但不同数据库的物化规则不同。

常见问题

CTE 和子查询有什么区别？

两者都在一个查询内部定义中间结果。CTE 带名称地写在主查询之前，可以被多次引用，并且从上到下阅读。子查询直接写在使用它的地方，每次使用都必须重复编写。

CTE 比子查询快吗？

通常两者都不更快：大多数优化器对简单的 CTE 和等价的子查询采用相同的处理方式。差异出现在数据库物化 CTE 的时候：当结果被重复使用时这会有帮助，而当主查询对它进行大量过滤时则可能有害。请用 EXPLAIN 检查执行计划。

CTE 和临时表一样吗？

不一样。CTE 只在一条语句执行期间存在，不会被保存。临时表需要显式创建，会一直存在到会话结束，可以被多个查询使用，并且可以建立索引。

哪些数据库支持 WITH 子句？

PostgreSQL、SQL Server、Oracle、SQLite（3.8.3+）、MySQL（8.0+）和 MariaDB（10.2+）都支持 CTE。