高级 SQL (Chinese Edition)

第 2 章 高级 SQL 技术

本作品已使用人工智能进行翻译。欢迎您提供反馈和意见：[e-mail]

在第 1 章中，我们探讨了 SQL 的演变历程，以及它在传统数据库和 Cloud 数据平台上的广泛应用。我们看到，SQL 已远远超越了简单的SELECT语句，发展成为一个具备丰富分析能力的生态系统。第 2 章在此基础上，深入探讨了高级 SQL 查询技术以及实用的性能优化策略。 我们将探讨若干 SQL 特性，例如递归公共表表达式（CTE）和窗口函数，这些特性使 SQL 能够进行复杂的数据操作。随后，我们将探索重构和优化查询以提升性能的技术，包括 PostgreSQL 中的索引策略、理解查询规划器如何做出执行决策、结果缓存，以及在本地和云环境中利用物化视图。 全书将对比 PostgreSQL（代表了许多传统关系型数据库管理系统）与 Snowflake 和 BigQuery 等现代云数据仓库的实现方法，重点介绍中级和高级 SQL 从业者可运用的最佳实践和实际应用场景。

递归查询、窗口函数和CTE

高级 SQL 允许您通过使用 CTE（包括递归 CTE）和窗口函数在查询中表达复杂逻辑。这些功能使您能够处理分层或序列化数据，并直接在 SQL 中执行分析——这些能力是早期 SQL 方言所缺乏或难以实现的。在本节中，我们将探讨非递归和递归 CTE 以及窗口函数的语法及其自然用法。 我们将探讨典型的用例，例如分层数据遍历（组织结构图、图路径）、生成序列以及计算累积聚合。全篇将提供针对 PostgreSQL（作为传统关系型数据库管理系统）以及 Snowflake 和 BigQuery 等现代云数据仓库的示例，以说明这些概念适用于不同的 SQL 实现。

常见表表达式与子查询因子化

CTE（常见表表达式）由 SQL:1999 标准引入（ ），它提供了一种在查询开头定义临时结果集（即子查询）并为其命名的方法，随后可在查询中引用该结果集。CTE 通过在主语句之前使用WITH子句来定义。CTE 允许将复杂逻辑分解为命名组件，而非在行内嵌套子查询，从而提高了查询的可读性和结构清晰度。

假设我们希望将一个复杂查询拆分为多个部分。通过使用 CTE，我们可以像示例 2-1 所示那样实现这一目标。

示例 2-1. 使用 CTE 将查询模块化

-- Using a CTE to modularize a query
WITH RecentOrders AS
(
  SELECT customer_id,
         amount
  FROM orders
  WHERE order_date >= DATE '2025-01-01'
)

SELECT r.customer_id,
       SUM(r.amount) AS total_2025_spending
FROM RecentOrders AS r
JOIN customers AS c ON c.id = r.customer_id
GROUP BY r.customer_id;

在此示例中，名为RecentOrdersCTE 被定义为查询 2025 年及之后的所有订单。在主查询中，我们将RecentOrders视为一张表进行引用，将其与customers表进行连接并进行聚合。这种方法使查询更易于理解：筛选逻辑被封装在 CTE 中，而主查询显然是对该子查询结果进行操作。

CTE 有助于将 SQL 查询模块化，特别是对于包含多个步骤的复杂查询。与其编写一个庞大的查询，不如分步构建，并为每个子查询（以双连字符开头）赋予自解释性名称。大多数主流 ...