价格
登录 开始使用
文档 学习
课程
Metabase 教程
提升您的数据技能
备忘单

使用公共表表达式（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 示例

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

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 |
|... |...    |

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

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 的子查询保存为代码片段，但最好将子查询保存为问题。片段和保存的问题之间的经验法则是，如果一段代码可以自行返回结果，您可能需要考虑将其保存为问题（参见片段 vs 保存的问题 vs 视图）。

片段的更好用例将是捕获过滤客户订单逻辑的 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

您可以使用 Variables sidebar（变量侧边栏）查看由变量 {{#2}} 引用的问题。在这种情况下，2 是问题的 ID。

View referenced questions in the Variables sidebar.

通过将该子查询保存为独立问题，多个问题将能够引用其结果。如果您需要添加额外的 WHERE 子句以从计算中排除更多测试订单，则引用该计算的每个问题都将受益于此更新。此优点也有弊端，即如果您最终更改了该保存问题以返回不同的列，则会破坏依赖其结果的查询。

用于多级聚合的 CTE

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

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

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

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

您可以使用 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.

您可以清楚地看到两个聚合阶段（两个 Summarize 部分）。这表明，即使您正在编写 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 自连接的示例。

这有帮助吗？

感谢您的反馈！
分析师每周技巧
获取可行的见解
关于 AI 和数据的资讯，直接发送到您的收件箱
© . This site is unofficial and not affiliated with Metabase, Inc.