使用公用表表达式 (CTE) 简化复杂查询

CTE 是命名的结果集，有助于保持代码的组织性。它们允许您在同一查询中重用结果，并执行多级聚合。

公用表表达式 (CTE) 是 SQL 查询中命名的结果集。CTE 有助于保持代码的组织性，并允许您对数据执行多级聚合，例如查找一组计数的平均值。我们将通过一些示例来展示 CTE 的工作原理以及您为什么要使用它们，使用 Metabase 附带的示例数据库，以便您可以跟随操作。

CTE 的优势

CTE 使代码更具可读性。 可读性使查询更易于调试。
CTE 可以在整个查询中多次引用结果。 通过存储子查询的结果，您可以在更大的查询中重用它们。
CTE 可以帮助您执行多级聚合。 使用 CTE 存储聚合结果，然后可以在主查询中汇总这些结果。

CTE 语法

CTE 的语法使用 WITH 关键字和变量名来创建一种临时表，您可以在查询的其他部分引用该表。

WITH cte_name(column1, column2, etc.) AS (SELECT ...)

AS 关键字在这里有点不寻常。通常 AS 用于指定别名，例如 consumables_orders AS orders，其中 orders 是 AS 右侧的别名。对于 CTE，变量 cte_name 位于 AS 关键字之前（在左侧），后跟子查询。请注意，列列表 (column1, column2, etc) 是可选的，前提是 SELECT 语句中的每列都有唯一的名称。

CTE 示例

让我们来看一个简单的例子。我们想查看总额大于平均订单总额的所有订单列表。

SELECT
  id,
  total
FROM
  orders
WHERE
-- filter for orders with above-average totals
  total > (
    SELECT
      AVG(total)
    FROM
      orders
  )

此查询给出：

|ID  |TOTAL  |
|----|-------|
|2   |117.03 |
|4   |115.22 |
|5   |134.91 |
|... |...    |

看起来很简单：我们有一个子查询，SELECT AVG(total) FROM orders，嵌套在 WHERE 子句中，用于计算平均订单总额。但是，如果获取平均值更复杂怎么办？例如，假设您需要过滤掉测试订单，或排除应用程序启动之前的订单

SELECT
  id,
  total
FROM
  orders
WHERE
  total > (
    -- calculate average order total
    SELECT
      AVG(total)
    FROM
      orders
    WHERE
      -- exclude test orders
      product_id > 10
      AND -- exclude orders before launch
      created_at > '2016-04-21'
      AND -- exclude test accounts
      user_id > 10
  )
ORDER BY
  total DESC

现在查询开始失去可读性。我们可以使用 WITH 语句将子查询重写为公用表表达式，以封装该子查询的结果

-- CTE to calculate average order total
-- with the name for the CTE (avg_order) and column (total)
WITH avg_order(total) AS (

-- CTE query
  SELECT
    AVG(total)
  FROM
    orders
  WHERE
    -- exclude test orders
    product_id > 10
    AND -- exclude orders before launch
    created_at > '2016-04-21'
    AND -- exclude test accounts
    user_id > 10
)

-- our main query:
-- orders with above-average totals
SELECT
  o.id,
  o.total
FROM
  orders AS o
  -- join the CTE: avg_order
  LEFT JOIN avg_order AS a
WHERE
  -- total is above average
  o.total > a.total
ORDER BY
  o.total DESC

CTE 打包了查找平均值的逻辑，并将该逻辑与核心查询分离：查找总额高于平均水平的订单 ID。请注意，此 CTE 的结果不会保存在任何地方；每次运行查询时都会执行其子查询。

将此查询存储为 CTE 也使查询更易于修改。假设我们还想知道哪些订单具有

高于平均水平的总额，
低于平均水平的订购商品数量。

我们可以轻松地扩展查询，如下所示

-- CTE to calculate average order total and quantity
WITH avg_order(total, quantity) AS (
  SELECT
    AVG(total),
    AVG(quantity)
  FROM
    orders
  WHERE
    -- exclude test orders
    product_id > 10
    AND -- exclude orders before launch
    created_at > '2016-04-21'
    AND -- exclude test accounts
    user_id > 10
)

-- orders with above-average totals
SELECT
  o.id,
  o.total,
  o.quantity
FROM
  orders AS o -- join the CTE avg_order
  LEFT JOIN avg_order AS a
WHERE
  -- above-average total
  o.total > a.total
  -- below-average quantity
  AND o.quantity < a.quantity
ORDER BY
  o.total DESC,
  o.quantity ASC

我们还可以选择并仅运行 CTE 中的子查询。

Highlighting part of a query in a CTE and running the selection to see its results.

如上图所示，您也可以将 CTE 的子查询另存为代码段，但最好将子查询另存为问题。决定代码段和已保存问题之间选择的经验法则是，如果代码块可以自行返回结果，您可能需要考虑将其另存为问题（请参阅 SQL 代码段 vs 已保存问题 vs 视图）。

SQL 代码段的更好用例是捕获过滤客户订单逻辑的 WHERE 子句。

Inserting a snippet, Customer orders, that filters out test orders and accounts.

带有已保存问题的 CTE

您可以使用 WITH 语句引用已保存的问题

WITH avg_order(total, quantity) AS {{#2}}

-- orders with above-average totals
SELECT
  o.id,
  o.total,
  o.quantity
FROM
  orders AS o -- join the CTE avg_order
  LEFT JOIN avg_order AS a
WHERE
  -- above-average totals
  o.total > a.total
  -- below-average quantity
  AND o.quantity < a.quantity
ORDER BY
  o.total DESC,
  o.quantity ASC

您可以使用“变量”侧边栏查看变量 {{#2}} 引用的问题。在本例中，2 是问题的 ID。

View referenced questions in the Variables sidebar.

通过将该子查询另存为独立问题，多个问题将能够引用其结果。如果您需要添加额外的 WHERE 子句以从计算中排除更多测试订单，则引用该计算的每个问题都将从更新中受益。这种好处的另一方面是，如果您最终更改已保存的问题以返回不同的列，则会破坏依赖其结果的查询。

用于多级聚合的 CTE

您可以使用 CTE 执行多级或多阶段聚合。也就是说，您可以对聚合执行聚合，例如取计数的平均值。

示例：每个产品类别每周的平均订单数是多少？

要回答本节标题中的问题，我们需要

查找每个产品类别每周的订单计数。
查找每个类别的平均计数。

您可以使用 CTE 查找计数，然后使用主查询计算平均值。

-- CTE to find orders per week by product category
WITH orders_per_week(
  order_week, order_count, category
) AS (
  SELECT
    DATE_TRUNC('week', o.created_at) as order_week,
    COUNT(*) as order_count,
    category
  FROM
    orders AS o
    left join products AS p ON o.product_id = p.id
  GROUP BY
    order_week,
    p.category
)

-- Main query to calculate average order count per week
SELECT
    category AS "Category",
    AVG(order_count) AS "Average orders per week"
FROM
  orders_per_week
GROUP BY
  category

产生：

|Category |Average orders per week|
|---------|-----------------------|
|Doohickey|19                     |
|Gizmo    |23                     |
|Widget   |25                     |
|Gadget   |24                     |

查询构建器中的多级聚合

为了提供对此查询中发生的事情的鸟瞰图，以下是上述查询在 Metabase 查询构建器中的外观

Using the query builder to find the average weekly count of orders by product category. Note the two summarization steps in green.

您可以清楚地看到聚合的两个阶段（两个“汇总”部分）。正如这所示，即使您正在编写 SQL 查询，查询构建器 也可以成为探索数据并帮助您规划方法的好工具。

在单个查询中使用多个 CTE

您可以在同一查询中使用多个 CTE。您只需用逗号分隔它们的名称和子查询，就像这样

-- first CTE
WITH avg_order(total) AS (
  SELECT
    AVG(total)
  FROM
    orders
),
-- second CTE (note the preceding comma)
avg_product(rating) AS (
  SELECT
    AVG(rating)
  FROM
    products
)

阅读

您可以在我们关于在 SQL 中处理日期的文章中查看更多实际使用的 CTE，包括一个使用 CTE 连接自身的示例。

下一步：使用连接组合表格

如何使用连接组合来自两个表的信息。

下一篇文章

商业智能

嵌入式分析

文档

学习

课程

Metabase 基础

提升您的数据技能

速查表