使用通用表格表達式 (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 |
|... |...    |

看起來很簡單：我們有一個子查詢 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 基礎知識

提升您的資料技能

速查表