使用公用表表达式（CTEs）简化复杂查询

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

现在查询的可读性开始下降。我们可以将子查询重写为CTE，使用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片段与保存问题及视图的比较）。

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 基础

增长你的数据技能