Coding Interview Prep · 课时

CTE、子查询与临时表

Q: 「CTE、子查询与临时表」这节课中我会学到什么？

比较物化、复用和优化器行为方面的权衡 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

比较物化、复用和优化器行为方面的权衡

CTE、子查询与临时表 是 CoddyKit 上的免费 Coding Interview Prep 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。

组织逻辑的三种方式

当查询需要中间结果时，您有三种常用工具可选：子查询、CTE和临时表。面试官会要求您比较它们，因为您的选择能体现您是否理解物化和优化器的行为。

本课将构建一个可在压力下复述的决策框架。

子查询

子查询是嵌套在另一个查询中的内联查询，通常位于 FROM、WHERE 或 SELECT 中。它属于同一条语句，优化器会将其视为一个整体。

不需要名称（派生表需要别名）。
优化器可以自由地将其合并到外层查询中。
深度嵌套时会变得冗长且难以阅读。

SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

CTE

CTE 是 WITH 代码块中的命名子查询，作用域限于一条语句。它比深度嵌套的子查询更易读，并且可以被多次引用。

它有名称，因此意图得到了记录。
可以在同一条语句中被引用多次。
作用域仍然只限于一条语句，之后便会消失。

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

临时表

临时表是真实存在的物理表，其生命周期持续整个会话（或事务）。您可以用一条语句填充它，然后在后续彼此独立的语句中查询它。

在会话中的多条语句之间持续存在。
可以建立索引并收集统计信息。
需要付出磁盘 I/O 成本，并且需要显式清理。

CREATE TEMP TABLE spend AS
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

SELECT * FROM spend WHERE total > 1000;

物化：核心区别

面试官重点考察的概念是物化：中间结果是否会被实际写入某个位置。

子查询和 CTE 通常不会被物化；优化器经常会将它们内联。
临时表总是会被物化到存储中。
某些数据库允许您通过提示强制或阻止 CTE 物化。

优化器屏障与旧版 PostgreSQL 陷阱

过去，PostgreSQL 会将每个 CTE 都视为优化屏障，对其进行物化并阻止谓词下推。自 PostgreSQL 12 起，只被引用一次的简单非递归 CTE 默认会被内联；您可以使用 MATERIALIZED 和 NOT MATERIALIZED 提示来覆盖这一行为。

提到这个细节是资深水平的有力体现。

WITH spend AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
)
SELECT * FROM spend WHERE total > 1000;

同一条语句内复用

如果您在一条语句中多次引用同一个中间结果，使用 CTE 可能比重复编写子查询更清晰。但请注意：内联的 CTE 可能会在每次引用时被重新计算。

当重新计算的成本很高时，强制物化（或使用临时表）可以避免重复执行相同的工作。

跨语句复用

CTE 和子查询只在一条语句的生命周期内存在。如果您需要在多条独立查询中使用同一个结果，临时表才是合适的工具。

典型情况是多步骤 ETL 或报表：您先构建一次暂存数据集，然后针对它运行多次分析。为临时表建立索引可以加速之后的每一条查询。

索引和统计信息

只有临时表可以携带索引和最新的统计信息。对于需要多次连接的超大中间结果，这一点可能具有决定性作用。

CTE/子查询：优化器根据基础表进行估算。
临时表：您可以对其执行 ANALYZE，并添加针对后续连接进行优化的索引。

因此，对于规模庞大且频繁复用的结果，尽管需要额外步骤，临时表仍可能在性能上胜出。

决策框架

面试中的简洁回答：

子查询：一次性使用、嵌套较浅，且可读性没有问题。
CTE：用于提升可读性，或在一条语句中被引用数次。
临时表：需要跨语句复用、数据量非常大，或者需要索引和统计信息。

为清晰起见，默认使用 CTE；只有在物化或跨语句复用确实有帮助时，才选择临时表。

如何阐述取舍

避免使用“CTE 总是更慢”这样的绝对说法。可以改为：CTE 和子查询通常会被内联，因此它们主要解决可读性问题；临时表会被物化，当我需要在多个语句之间复用大型结果或需要索引时，使用临时表才值得。

承认这种行为取决于数据库引擎（在 PostgreSQL 中还取决于版本），可以体现真正的深入理解。

快速检查

请选择临时表明显更合适的场景。

回顾：CTE、子查询与临时表

选择的关键在于物化和作用域。

子查询和 CTE：通常会被内联，作用域限于一条语句，选择它们是为了可读性。
CTE 增加了命名能力，并支持在同一条语句中复用。
临时表：总是会被物化，可跨语句持续存在，并且可以建立索引。
PostgreSQL 12+ 会内联简单 CTE；使用 MATERIALIZED 提示可以控制这一行为。

接下来：将一条纠缠的嵌套查询重构为清晰的 CTE。

常见问题解答

「CTE、子查询与临时表」课时是免费的吗？

是的 — 「CTE、子查询与临时表」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。

「CTE、子查询与临时表」这节课中我会学到什么？

比较物化、复用和优化器行为方面的权衡你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Coding Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「CTE、子查询与临时表」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Coding Interview Prep 课中编写并运行代码吗？

能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Coding Interview Prep