课程 6.4: 公共表表达式 (CTEs)

公共表表达式，或称CTEs，是SQL中最强大且未被充分利用的特性之一。它们允许您定义可以在更大查询中引用的临时命名结果集。在本课程中，我们将探讨CTEs如何使您的SQL代码更具可读性、可维护性和更易于调试。

什么是CTEs？

公共表表达式 (CTE) 是在查询开始时使用 WITH 子句定义的临时结果集。可以将其视为一个命名的子查询，可以在同一查询中多次使用。

CTEs的主要优点：

可读性：命名结果集使查询更易于理解
可重用性：多次引用同一CTE而无需重新定义
模块化：将复杂查询分解为逻辑上可管理的部分
可维护性：逻辑的更改只需在一个地方进行
调试：在组合之前独立测试每个CTE

基本CTE语法

CTE的一般语法为：

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

组成部分：

WITH：引入CTE的关键字
cte_name：您为临时结果集指定的名称
AS：引入查询定义的关键字
(SELECT ...)：定义CTE的查询
主查询可以通过名称引用CTE

您的第一个CTE

让我们从一个简单的示例开始，计算客户支出：

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;

这个CTE：

定义了一个名为 customer_spending 的结果集
计算每个客户的支出指标
在主查询中引用此CTE以筛选高支出客户

这里的好处是清晰——意图显而易见：我们正在处理客户支出数据。

CTEs与子查询

让我们比较一下使用传统子查询方法的相同逻辑：

使用子查询：

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：

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;

关键区别：

CTE在顶部定义，使查询结构立即清晰
CTE有一个有意义的名称（customer_spending），而不仅仅是一个匿名子查询
主查询的意图在深入数据转换之前是可见的
如果您需要多次引用此结果集，只需使用CTE定义一次

在一个查询中使用多个CTEs

您可以在单个查询中定义多个CTEs，每个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
),
customer_details AS (
    SELECT
        hs.customer_id,
        hs.total_spent,
        c.first_name,
        c.last_name,
        c.email
    FROM
        high_spenders hs
    JOIN
        customer c ON hs.customer_id = c.customer_id
)
SELECT
    customer_id,
    CONCAT(first_name, ' ', last_name) AS customer_name,
    email,
    total_spent
FROM
    customer_details
ORDER BY
    total_spent DESC;

在这个查询中：

customer_spending 计算每个客户的总支出
high_spenders 筛选出总支出 > 150 的客户
customer_details 将高支出客户与客户信息连接
主查询选择并格式化最终结果

这种结构使逻辑流程清晰且易于跟随。

CTE的可重用性

CTEs的一个强大方面是可以多次引用自己：

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', payment_date) AS month,
        SUM(amount) AS monthly_total
    FROM
        payment
    GROUP BY
        DATE_TRUNC('month', payment_date)
)
SELECT
    m1.month AS current_month,
    m1.monthly_total AS current_sales,
    m2.monthly_total AS previous_month_sales,
    ROUND(((m1.monthly_total - m2.monthly_total) / m2.monthly_total * 100), 2) AS percent_change
FROM
    monthly_sales m1
LEFT JOIN
    monthly_sales m2 ON m1.month = m2.month + INTERVAL '1 month'
WHERE
    m1.month IS NOT NULL
ORDER BY
    m1.month;

在这里，我们引用了 monthly_sales 两次——一次作为 m1，一次作为 m2。如果我们不使用CTE，这将需要两个单独的子查询。

CTE与窗口函数

CTEs与窗口函数完美结合：

WITH ranked_rentals AS (
    SELECT
        customer_id,
        rental_date,
        return_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY rental_date DESC
        ) AS rental_rank
    FROM
        rental
),
most_recent_rental AS (
    SELECT
        customer_id,
        rental_date,
        return_date
    FROM
        ranked_rentals
    WHERE
        rental_rank = 1
)
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    mrr.rental_date AS last_rental_date,
    DATEDIFF(CURDATE(), mrr.rental_date) AS days_since_rental
FROM
    customer c
LEFT JOIN
    most_recent_rental mrr ON c.customer_id = mrr.customer_id
ORDER BY
    days_since_rental DESC
LIMIT 20;

这个查询：

使用 ROW_NUMBER() 确定每个客户最近的租赁
筛选出每个客户的最近租赁
与客户表连接以显示客户名称并计算自租赁以来的天数

模块化结构使其易于理解和修改。

实际示例：队列分析

CTEs非常适合复杂的分析查询，如队列分析：

WITH customer_first_rental AS (
    SELECT
        customer_id,
        MIN(rental_date) AS first_rental_date,
        DATE_TRUNC('month', MIN(rental_date)) AS cohort_month
    FROM
        rental
    GROUP BY
        customer_id
),
customer_rental_history AS (
    SELECT
        cfr.customer_id,
        cfr.cohort_month,
        DATE_TRUNC('month', r.rental_date) AS rental_month,
        COUNT(*) AS rentals_in_month
    FROM
        customer_first_rental cfr
    JOIN
        rental r ON cfr.customer_id = r.customer_id
    GROUP BY
        cfr.customer_id,
        cfr.cohort_month,
        DATE_TRUNC('month', r.rental_date)
)
SELECT
    cohort_month,
    rental_month,
    COUNT(DISTINCT customer_id) AS customers,
    SUM(rentals_in_month) AS total_rentals
FROM
    customer_rental_history
GROUP BY
    cohort_month,
    rental_month
ORDER BY
    cohort_month,
    rental_month;

这个复杂的分析通过CTEs变得可管理：

第一个CTE识别每个客户的队列（第一次租赁月份）
第二个CTE构建所有租赁的历史记录，并包含队列信息
最终查询汇总以显示队列随时间的表现

优势总结

方面	CTE	子查询
可读性	通过命名结果集高度可读	可能变得难以阅读（嵌套结构）
可重用性	易于多次引用	每次使用必须重新定义
调试	可以独立测试每个CTE	难以隔离特定逻辑
组织	逻辑的自上而下结构	线性但有时杂乱
性能	相同或更好（依赖优化器）	深度嵌套时可能效率较低

关键要点

CTEs 是使用 WITH 子句定义的临时命名结果集
可读性：命名CTE使查询自我文档化
多个CTE：将CTE串联在一起，每个CTE基于前一个构建
可重用性：多次引用同一CTE而无需重新定义
无性能惩罚：CTEs不会创建中间存储；它们是查询优化工具
适用于所有情况：CTEs可以包括连接、聚合、窗口函数等
模块化：将复杂查询分解为更易于理解和维护的逻辑部分

CTEs将复杂查询从难以理解的嵌套结构转变为清晰、可读、易于维护的代码。它们是任何数据分析师工具包中的重要工具。

在下一课中，我们将探讨递归CTEs——处理层次数据的强大特性。

练习

课程

练习场

测试

课程 6.4: 公共表表达式 (CTEs)

什么是CTEs？

基本CTE语法

您的第一个CTE

CTEs与子查询

在一个查询中使用多个CTEs

CTE的可重用性

CTE与窗口函数

实际示例：队列分析

优势总结

关键要点

尝试解决以下任务，以巩固您在本课中学到的内容。

登录

注册

课程 6.4: 公共表表达式 (CTEs)

什么是CTEs？

基本CTE语法

您的第一个CTE

CTEs与子查询

在一个查询中使用多个CTEs

CTE的可重用性

CTE与窗口函数

实际示例：队列分析

优势总结

关键要点

尝试解决以下任务，以巩固您在本课中学到的内容。