定价
登录 开始使用
文档 学习
主页
课程
Metabase 基础
提升您的数据技能
速查表

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

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

使用 Metabase 学习 SQL

免费下载 Metabase，或注册 Metabase Cloud 免费试用

一个 **公共表表达式 (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

您可以使用**变量侧边栏**查看变量 {{#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.

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

下一篇：在 SQL 中处理日期

使用 SQL 按时间段对结果进行分组，比较周环比总计，并查找两个日期之间的持续时间。

下一篇文章
© . All rights reserved.