公用表表达式（CTE）

SQL 中的数据处理

Mona Khalil

Data Scientist, Greenhouse Software

添加子查询时……

查询复杂度迅速上升！
- 信息难以跟踪

方案：公用表表达式（CTE）！

公用表表达式（CTE）

公用表表达式（CTE）

在主查询前"声明"的表
在 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 (                              -- 新子查询
  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         -- 新列
FROM country AS c
INNER JOIN s1
ON c.id = s1.country_id
INNER JOIN s2                        -- 新连接
ON c.id = s2.country_id
GROUP BY country;

为何使用 CTE？

执行一次
- 之后保存在内存中
- 提升查询性能
改善查询组织
可引用其他 CTE
可自引用（SELF JOIN）

Ayo berlatih!

SQL 中的数据处理