Biểu thức bảng chung (CTE)

Xử lý dữ liệu trong SQL

Mona Khalil

Data Scientist, Greenhouse Software

Khi thêm tiểu truy vấn...

Độ phức tạp tăng rất nhanh!
- Khó theo dõi thông tin

Giải pháp: Common Table Expressions!

Biểu thức bảng chung (CTE)

Biểu thức bảng chung (CTE)

Bảng được khai báo trước truy vấn chính
Có tên và được tham chiếu sau trong mệnh đề FROM

Thiết lập CTE

WITH cte AS (
    SELECT col1, col2
    FROM table)

SELECT
    AVG(col1) AS avg_col
FROM cte;

Dùng tiểu truy vấn trong 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       |

Đặt ở đầu truy vấn

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

Đặt ở đầu truy vấn

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

Hiển thị 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       |

Hiển thị tất cả CTE

WITH s1 AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10),
s2 AS (                              -- Tiểu truy vấn mới
  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         -- Cột mới
FROM country AS c
INNER JOIN s1
ON c.id = s1.country_id
INNER JOIN s2                        -- Join mới
ON c.id = s2.country_id
GROUP BY country;

Vì sao dùng CTE?

Thực thi một lần
- Sau đó lưu trong bộ nhớ
- Cải thiện hiệu năng truy vấn
Tổ chức truy vấn tốt hơn
Tham chiếu các CTE khác
Tự tham chiếu (SELF JOIN)

Ayo berlatih!

Xử lý dữ liệu trong SQL