共同資料表運算式（CTE）

SQL 資料操作

Mona Khalil

Data Scientist, Greenhouse Software

加入子查詢時…

查詢複雜度成長很快！
- 資訊很難一一掌握

解方：共同資料表運算式（CTE）！

共同資料表運算式（CTE）

共同資料表運算式（CTEs）

在主查詢前先宣告的資料表
在 FROM 子句中以名稱引用

建立 CTE

WITH cte AS (
    SELECT col1, col2
    FROM table)

SELECT
    AVG(col1) AS avg_col
FROM cte;

把 `FROM` 裡的子查詢取出

SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10) AS s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

放到最前面

(
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

放到最前面

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

把 CTE 顯示出來

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)
SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

把所有 CTE 都顯示出來

WITH s1 AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10),
s2 AS (                              -- New subquery
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) <= 1
)
SELECT
  c.name AS country,
  COUNT(s1.id) AS high_scores,
  COUNT(s2.id) AS low_scores         -- New column
FROM country AS c
INNER JOIN s1
ON c.id = s1.country_id
INNER JOIN s2                        -- New join
ON c.id = s2.country_id
GROUP BY country;

為什麼用 CTE？

只執行一次
- 然後將 CTE 暫存在記憶體
- 可提升查詢效能
讓查詢結構更清楚
可引用其他 CTE
也可引用自身（SELF JOIN）

一起來練習吧！

SQL 資料操作