SQL 查询

如何在 SQL 中使用通用表表达式 (CTE)

A tiger developer coding on his computing (and probably using a common table expression).

通用表表达式 (CTE) 是我在编写复杂 SQL 查询时最常用的工具。可以将它们视为子查询或临时表，但它们拥有自己的超能力。

在本文中，我们将深入探讨 CTE 是什么以及它们如何简化您的查询，递归 CTE 如何将不可能的 PostgreSQL 查询变为可能，以及如何使用 CTE 创建优化栅栏以提高查询性能。

最后，在文章末尾，我们将讨论 Timescale 的超函数以及它们如何像 CTE 一样改进和简化我们的查询。

什么是通用表表达式？

通用表表达式是辅助查询，它们为主要查询提供临时结果集。换句话说，可以将 CTE 视为仅在查询期间存在的临时表。首先，让我们分解 SQL 中 CTE 的结构

WITH [cte name] AS (
   [auxiliary query]
)
[primary query]

分解

WITH 子句：开始通用表表达式，位于主要查询之前。（您可能还会遇到将 CTE 称为“With 语句”的人。）
CTE 名称：定义 CTE 的名称，可以在主要查询中引用——名称在查询中必须唯一。
AS：As 关键字开始 CTE 定义。
辅助查询：这定义了 CTE 并“填充临时表”。
主要查询：主要查询是我们的主要 SQL 查询——我们可以通过名称引用 CTE 并像使用常规表一样使用它。

通用表表达式不限于一个辅助语句。我们可以用逗号分隔辅助语句，以提供任意数量的 CTE。

WITH [cte name] AS (
   [query] -- auxiliary one statement
),
[cte 2 name] AS (
   [query] -- auxiliary two statement
),
[cte 3 name] AS (
   [query] -- auxiliary three statement
)
[primary query] -- primary statement

现在我们知道了 CTE 的结构，让我们深入研究一个例子，以巩固我们对 CTE 的心理模型，并了解它们为什么有用！

使用 CTE

在本例中，我们管理一个员工管理系统。数据库包含两个表：一个 departments 表和一个 employees 表。数据库模式如下所示

CREATE TABLE departments (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(255) NOT NULL,
    department_id INTEGER REFERENCES departments(id),
    salary        DECIMAL(10, 2),
    hire_date     DATE
);

我们的经理给我们布置了一项任务，确定运营每个部门的成本。使用 CTE，我们可以将任务分解为两个步骤。

首先，我们需要对每个部门的每个员工的薪资进行汇总。执行此操作的 SQL 代码如下所示

SELECT 
  department_id, 
  SUM(salary) AS total_salary 
FROM 
  employees 
GROUP BY 
  department_id

其次，我们需要按成本最高的部门列出和排序部门。查询将是

SELECT 
  d.name, 
  [department budgets].cost 
FROM 
  [department budgets]
ORDER BY 
  [department budgets].cost DESC;

由于我们还没有了解每个部门的成本，因此现在需要编写占位符。我们可以使用 CTE 将创建的两个查询合并成一个查询。最终报告将如下所示

WITH department_budgets AS (
  SELECT 
    department_id, 
    SUM(salary) AS cost 
  FROM 
    employees 
  GROUP BY 
    department_id
) 
SELECT 
  d.name, 
  db.cost 
FROM 
  department_budgets db 
  JOIN departments d ON d.id = db.department_id 
ORDER BY 
  db.cost DESC;

合并查询后，我们可以用 CTE 的名称替换占位符。

当然，可以在不使用 CTE 的情况下编写 SQL 语句。该查询将更混乱。此外，GROUP BY 子句是许多人感到沮丧和困惑的常见来源——尤其是在复杂查询中。CTE 使我们能够整理思路，逐步解决问题。最终结果是一个易于阅读的查询。我们不仅可以使用 CTE 来查询数据，而且它们也是修改数据的强大工具。

使用 CTE 修改数据

我们可以在通用表表达式的辅助或主要查询中使用 INSERT、UPDATE 和 DELETE 语句。与 RETURNING 子句配对，它返回正在修改的数据，CTE 提供了一种很好的方法来复制或记录对另一个表的更改。例如，假设我们有一个表来跟踪已离开公司的员工。

CREATE TABLE deactivated_employees (
  id                 SERIAL PRIMARY KEY,
  name               VARCHAR(255) NOT NULL,
  department_id      INTEGER REFERENCES departments(id),
  deactivation_date  DATE
);

当员工离开公司时，我们希望从 employees 表中删除该员工，并将他们添加到 deactivated_employees 表中。这可以通过在 CTE 中删除该行并 RETURNING 我们从该行中需要的信息来完成。然后，让主要查询 INSERT 删除的数据到 deactivated_employees，方法是引用 CTE。CTE 将如下所示

WITH deactivated AS (
  DELETE FROM 
    employees 
  WHERE 
    id = 1 
  RETURNING 
    id, 
    name, 
    department_id
) 
INSERT INTO deactivated_employees 
  SELECT 
    id, 
    name, 
    department_id, 
    now() 
  FROM 
    deactivated;

将数据修改语句与读取语句结合使用，可以打开许多用例。但这仅仅是开始。接下来，我们将了解递归查询以及如何使用 CTE 使几乎不可能的查询变得简单。

递归查询

递归 CTE 非常强大，尤其是在处理分层或树状结构数据时，例如组织结构图、文件系统或包含子类别的类别。它们使我们能够查询与父子关系相关的数据，迭代遍历层次结构级别，直到满足特定条件。只有通过递归通用表表达式，PostgreSQL 等关系数据库才能转变为图形数据库。

WITH 可以使用可选修饰符 RECURSIVE 将 CTE 变成递归循环。SQL 结构如下所示

WITH RECURSIVE [cte name] AS (
  [base case]
  UNION ALL
  [recursive term]
)
[primary query]

递归 CTE 由基本情况和递归项组成。基本情况定义递归查询的起点（例如，图中的第一个节点或层次结构中的父节点）。递归项描述了将在确定某个端点之前运行的“循环”。

这个递归项将引用 CTE 本身来创建循环。基本情况和递归项使用 UNION ALL（或有时如果要消除重复，则仅使用 UNION，但要谨慎，因为在性能方面它可能更昂贵）来创建完整的递归 CTE。

使用递归 CTE

回到我们之前的员工管理系统，employees 表有一个新的 manager_id 列，该列引用另一个员工。

CREATE TABLE employees (
  id            SERIAL PRIMARY KEY,
  name          VARCHAR(255) NOT NULL,
  department_id INTEGER REFERENCES departments(id),
  salary        DECIMAL(10, 2),
  hire_date     DATE,
  manager_id    INTEGER REFERENCES employees(id)
);

我们的任务是从给定的员工开始生成公司组织结构图。与上一个示例一样，将任务分解为小步骤将有助于确定递归 CTE 查询。第一步是开发基本情况，这将是获取起始员工。

SELECT id, name, manager_id
  FROM employees
  WHERE id = [employee id];

接下来，我们需要确定递归项或如何遍历员工层次结构。为此，我们可以考虑 employees 表对自身执行 JOIN，并使用 current_employee.manager_id = manager.id。

SELECT manager.id, manager.name, manager.manager_id
  FROM employees manager
  JOIN [current employee] employee ON employee.manager_id = manager.id

我们还不能引用当前迭代中的（递归）循环，因此我将放置一个占位符 [current employee]。递归项将继续执行，直到它们到达递归项不返回任何行的路径。

为了防止递归查询进入无限循环，这一点很重要！在递归项中，当收集员工层次结构时，递归查询将在 manager_id 为空时停止执行（例如，组织结构图顶部的 CEO）。

在编写了基本情况和递归项后，让我们将它们组合成一个递归 CTE。我们的递归 CTE 将有一个名称，我们可以在递归项中引用它，这样我们就可以用 CTE 的名称替换 [当前员工] 占位符。收集员工层次结构的完整递归查询如下所示

WITH RECURSIVE employee_hierarchy AS (
  -- Base case: select the starting employee
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 1 -- employee id

  UNION ALL

  -- Recursive term: The recursion will stop when `manager_id` = NULL
  SELECT manager.id, manager.name, manager.manager_id
  FROM employees manager
  JOIN employee_hierarchy employee ON employee.manager_id = manager.id
)
SELECT * FROM employee_hierarchy;

列列表

CTE 将返回其 SELECT 语句中指定的所有列（在 employee_hierarchy 中，它是 id, name, 和 manager_id）。但是，我们也可以选择显式地定义 CTE 返回的列及其名称，称为列列表。可以通过在 WITH 表达式中手动指定 CTE 名称后的每一列来更改列的名称

WITH RECURSIVE employee_hierarchy(employee_id, name, manager_id) AS (
  SELECT id, name, manager_id
  ... query
)

我们还可以将列添加到列列表中以提供生成的数据。例如，如果我们想按员工在组织结构图中的级别对 employee_hierarchy 进行排序，我们可以将 level 列添加到列列表中。基本情况将从 level 设置为 1 开始。然后，递归项的每次迭代都会将 level 增加 1。这将看起来像

WITH RECURSIVE employee_hierarchy(id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1 -- level starting at one
  FROM employees
  WHERE id = 1

  UNION ALL

  SELECT manager.id, manager.name, manager.manager_id, level+1 -- increment level
  FROM employees manager
  JOIN employee_hierarchy employee ON employee.manager_id = manager.id
)
SELECT * FROM employee_hierarchy ORDER BY level DESC; -- order by level

优化

到目前为止，我们已经探讨了使用通用表表达式将查询分解成简单的步骤以及使用递归 CTE 从图形或树状数据结构中查询数据。我们将要检查的最后一个用例是如何 CTE 可以帮助我们优化 SQL 查询。在在 PostgreSQL 上将 DISTINCT 查询性能提高 8,000 倍中，我们讨论了递归 CTE 如何改进 DISTINCT 查询。这是 CTE 创造性地优化查询的一个例子，如果您自己运行 PostgreSQL，它会非常有用。但是，TimescaleDB 支持跳过索引扫描，而无需依赖 CTE。

在 PostgreSQL 12 之前，CTE 默认情况下会物化。换句话说，通用表表达式只计算一次，然后缓存（类似于物化视图）。对 CTE 的任何其他引用都将引用缓存的结果。CTE 的物化提供了出色的选项，用于优化在主查询中多次调用的昂贵查询。

从 PostgreSQL 12 开始，没有副作用的查询不是递归的，并且只被引用一次，可以被查询规划器内联。这意味着 CTE 被变成了一个子查询。

例如，一个看起来像这样的查询

WITH my_cte AS (
  SELECT * FROM my_table
)
SELECT * FROM other_table ot JOIN my_cte m ON ot.id = m.other_id;

它可以被内联以像这样工作

SELECT * FROM other_table ot 
JOIN (SELECT * FROM my_table) m ON ot.id = m.other_id;

但是，我们可以告诉查询规划器我们希望它如何工作。可以通过在 WITH 表达式中的 AS 之后指定 MATERIALIZED 或 NOT MATERIALIZED 来启用或禁用物化。

WITH my_cte AS MATERIALIZED (
  SELECT * FROM my_table
)
SELECT * FROM other_table ot JOIN my_cte m ON ot.id = m.other_id;

CTE 可以成为优化查询的工具。但是对于每项优化，始终要 ANALYZE 查询，然后再显式设置 NOT MATERIALIZED 或 MATERIALIZED，以确保您不会意外地使查询运行速度变慢。

超函数

就像通用表表达式将复杂的 SQL 查询简化为更易于管理的单元一样，Timescale 的超函数使我们能够将冗长的 SQL 语句压缩成更直接、简洁的查询。

例如，如果我们想计算股票的时间加权平均价格，考虑到每个价格有效的持续时间，查询可能看起来有点复杂

SELECT 
  date_trunc('day', time) AS day,
  SUM(price * duration) / SUM(duration) AS time_weighted_average_price
FROM (
  SELECT 
    time,
    price,
    lead(time) OVER (ORDER BY time) - time AS duration
  FROM 
    stock_prices
) sub
GROUP BY 
  day
ORDER BY 
  day;

此查询使用 lead() 窗口函数获取下一个时间戳并从中减去当前时间戳，从而计算每个价格有效的持续时间。然后，它通过将每个价格乘以其持续时间，将这些乘积加起来，然后除以总持续时间来计算时间加权平均价格。

Timescale 提供了 time_weight() 超函数，结合 average() 访问器，使计算时间加权平均变得简单

SELECT 
  time_bucket('1 day', time) AS one_day_bucket,
  average(time_weight('Linear', time, price)) AS time_weighted_average_price
FROM 
  stock_prices
GROUP BY 
  one_day_bucket
ORDER BY 
  one_day_bucket;

在此查询中，time_bucket() 将数据分组到每日间隔。time_weight('Linear', time, price) 函数计算线性时间加权平均值，其中时间是时间戳，价格是要随时间平均的值。average() 访问器从时间加权计算中提取平均值。

此方法抽象了复杂的窗口函数和算术运算，大大简化了查询并提高了可读性，就像 CTE 将复杂的查询逻辑简化为更易于管理的部分一样。

结论

通用表表达式是编写查询时功能强大的工具。本文表明，CTE 使我们能够将复杂的查询分解成可管理的块，通过递归查询遍历图形状结构，并通过优化栅栏来优化查询。Timescale 的超函数通过简化复杂查询和加速时间序列分析，为 CTE 提供了一些辅助功能。

您可以创建一个免费的 Timescale 帐户并使用通用表表达式和超函数。