共同資料表運算式（CTE）

BigQuery 入門

Matthew Forrest

Field CTO

什麼是 CTE？

-- 使用 WITH 開始定義 CTE
WITH our_cte AS (
  SELECT column1, column2
  FROM table
)


/* CTE 以括號包住，之後可在主查詢中
當作資料表使用 */
SELECT *
FROM our_cte

子查詢 vs. CTE

子查詢

SELECT *
FROM (
  SELECT order_id, status
  FROM ecommerce.ecomm_order_status
) AS subquery

CTE

WITH cte_name AS (
  SELECT order_id, status
  FROM ecommerce.ecomm_order_status
)
SELECT *
FROM cte_name

撰寫 CTE

-- 所有 CTE 都以 WITH 開頭
WITH cte_name AS


-- CTE 需置於括號中
(SELECT col_1, col_2
 FROM tablename WHERE col_1 > 10
)


-- 新的 CTE「cte_name」只在此查詢範圍內可用

SELECT * FROM cte_name LIMIT 10

使用多個 CTE

WITH orders AS (
  SELECT order_id, status
  FROM ecommerce.ecomm_order_status
),

-- 其後的 CTE 不重複寫 WITH，並以逗號分隔
order_details AS (
SELECT orders.*, details.order_items
FROM ecommerce.ecomm_orders details 
```

用 CTE 篩選資料

WITH filtered_data AS (
  SELECT *
  FROM ecommerce.ecomm_orders
  -- 在 CTE 中先篩選資料
  WHERE order_status = 'delivered'
)
SELECT order_id, order_status
FROM filtered_data

| order_id | order_status |
|----------|--------------|
| 1        | delivered    |
| 2        | delivered    |

用 CTE 最佳化查詢

WITH precomputed_data AS (
  SELECT order_id, SUM(payment_value) AS total
  FROM ecommerce.ecomm_payments
  GROUP BY order_id
)
SELECT *
FROM precomputed_data
WHERE total > 1000

| order_id | total   |
|----------|---------|
| 1        | 50000   |
| 2        | 8000    |

用 CTE 串接資料（JOIN）

WITH cte1 AS (
  SELECT order_id, status
  FROM ecommerce.ecomm_order_status
),
cte2 AS (
  SELECT order_id, order_items
  FROM ecommerce.ecomm_orders
)
SELECT *
FROM cte1
JOIN cte2 USING (order_id)

一起來練習吧！

BigQuery 入門