CTE, CTE đệ quy, VIEW và bảng tạm
Chia truy vấn phức tạp thành các bước đọc được bằng CTE, duyệt cây/đồ thị bằng CTE đệ quy, đóng gói logic bằng VIEW và materialized view.
Truy vấn thực tế dài hàng chục dòng. Người viết SQL giỏi không viết một khối khổng lồ mà chia bước có tên — như hàm trong lập trình — để đọc, kiểm thử và gỡ lỗi từng phần. CTE làm điều đó; CTE đệ quy mở ra lớp bài toán mà JOIN không làm được (cây danh mục, sơ đồ tổ chức, đồ thị giới thiệu) và VIEW cho phép tái sử dụng logic nghiệp vụ ở nhiều nơi.
🎯 Mục tiêu học tập
- Viết truy vấn nhiều bước bằng CTE và gỡ lỗi từng bước
- Viết CTE đệ quy cho cây/đồ thị, kiểm soát độ sâu, chống vòng lặp với CYCLE
- Dùng data-modifying CTE (UPDATE/DELETE ... RETURNING trong WITH)
- Phân biệt VIEW, MATERIALIZED VIEW, bảng tạm và subquery; biết khi nào dùng cái nào
CTE (WITH): truy vấn có tên
CTE (Common Table Expression) định nghĩa một "bảng tạm có tên" chỉ sống trong một câu lệnh. Lợi ích: đọc từ trên xuống như các bước xử lý, tái sử dụng nhiều lần trong cùng câu, và kiểm tra từng bước riêng.
-- Bài toán: khách VIP = chi tiêu >= 300 triệu. Hiển thị tên, tổng chi, và tỉ trọng so với tổng doanh thu
WITH chi_tieu AS (
SELECT o.customer_id, sum(p.amount) AS tong_chi
FROM payments p JOIN orders o ON o.id = p.order_id
GROUP BY o.customer_id
),
tong AS (
SELECT sum(tong_chi) AS tong_doanh_thu FROM chi_tieu
)
SELECT c.full_name,
ct.tong_chi,
round(100.0 * ct.tong_chi / t.tong_doanh_thu, 2) AS ti_trong_pct
FROM chi_tieu ct
JOIN customers c ON c.id = ct.customer_id
CROSS JOIN tong t
WHERE ct.tong_chi >= 300000000
ORDER BY ct.tong_chi DESC; full_name | tong_chi | ti_trong_pct
---------------+--------------+--------------
Đặng Thu Hà | 388803000.00 | 3.66
Lê Văn An | 362434000.00 | 3.41
Lê Khánh Linh | 357144600.00 | 3.36
Nguyễn Thu Hà | 335643100.00 | 3.16
(4 rows)Khi kết quả sai, đừng đoán: chạy riêng SELECT * FROM chi_tieu bằng cách thay phần SELECT cuối. Mỗi CTE là một "điểm dừng" kiểm tra — đối chiếu số dòng, tổng, giá trị mẫu. Đây là lý do chuyên gia thích CTE hơn subquery lồng 5 tầng.
Từ PostgreSQL 12, CTE không đệ quy, không có tác dụng phụ và chỉ được dùng một lần sẽ được inline (coi như subquery) nên không chậm hơn. Nếu CTE được tham chiếu nhiều lần, PostgreSQL mặc định materialize (tính một lần, lưu lại).
Bạn có thể ép bằng WITH x AS MATERIALIZED (...) hoặc NOT MATERIALIZED. (Ở PostgreSQL ≤ 11 và nhiều DBMS khác, CTE là "hàng rào tối ưu" — kiến thức cũ này còn lưu truyền nhiều.)
t1, t2, t3, tmp, x không nói lên gì. Đặt tên theo nghĩa nghiệp vụ và grain: doanh_thu_theo_khach, don_hop_le. Mỗi CTE nên làm một việc. Nếu một CTE phức tạp, ghi chú một dòng grain ở đầu.
CTE đệ quy: cây và đồ thị
Cấu trúc: phần neo (anchor — điểm bắt đầu) UNION ALL phần đệ quy (tham chiếu chính CTE, sinh thêm dòng) — lặp đến khi không sinh dòng mới.
-- Cây danh mục: mỗi danh mục kèm đường dẫn đầy đủ và độ sâu
WITH RECURSIVE cay AS (
SELECT id, name, parent_id, 1 AS cap, name::text AS duong_dan
FROM categories WHERE parent_id IS NULL -- neo: danh mục gốc
UNION ALL
SELECT c.id, c.name, c.parent_id, cay.cap + 1, cay.duong_dan || ' > ' || c.name
FROM categories c JOIN cay ON c.parent_id = cay.id -- đệ quy: con của dòng đã có
)
SELECT cap, duong_dan FROM cay ORDER BY duong_dan; cap | duong_dan
-----+----------------------
1 | Điện tử
2 | Điện tử > Điện thoại
2 | Điện tử > Laptop
2 | Điện tử > Phụ kiện
1 | Gia dụng
2 | Gia dụng > Nhà bếp
1 | Thời trang
2 | Thời trang > Áo
2 | Thời trang > Giày
(9 rows)-- Sơ đồ tổ chức: cấp bậc và toàn bộ cấp dưới của mỗi quản lý
WITH RECURSIVE org AS (
SELECT id, full_name, manager_id, 0 AS cap, ARRAY[id] AS chuoi_quan_ly
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.full_name, e.manager_id, org.cap + 1, org.chuoi_quan_ly || e.id
FROM employees e JOIN org ON e.manager_id = org.id
)
SELECT repeat(' ', cap) || full_name AS so_do, cap
FROM org ORDER BY chuoi_quan_ly; so_do | cap
-------------------------+-----
Trần Quốc Bảo | 0
Lê Thu Hương | 1
Nguyễn Hải Yến | 2
Đặng Ngọc Mai | 3
Bùi Anh Tuấn | 3
Vũ Gia Khang | 2
Phạm Đình Hiếu | 3
Phạm Minh Đức | 1
Hoàng Thị Lan | 2
Trần Bích Ngọc | 3
Lê Văn Phong | 2
Nguyễn Thảo Vy | 1
(12 rows)-- Đếm tổng số cấp dưới (trực tiếp + gián tiếp) của từng người
WITH RECURSIVE duoi AS (
SELECT id AS goc, id AS nv FROM employees
UNION ALL
SELECT d.goc, e.id FROM duoi d JOIN employees e ON e.manager_id = d.nv
)
SELECT e.full_name, count(*) - 1 AS so_cap_duoi
FROM duoi d JOIN employees e ON e.id = d.goc
GROUP BY e.id, e.full_name
ORDER BY so_cap_duoi DESC, e.id LIMIT 4; full_name | so_cap_duoi
----------------+-------------
Trần Quốc Bảo | 11
Lê Thu Hương | 5
Phạm Minh Đức | 3
Nguyễn Hải Yến | 2
(4 rows)-- Chuỗi giới thiệu khách (đồ thị): khách 24 do ai giới thiệu, người đó do ai... đến tận gốc
WITH RECURSIVE chuoi AS (
SELECT id, referred_by, 1 AS buoc FROM customers WHERE id = 24
UNION ALL
SELECT c.id, c.referred_by, chuoi.buoc + 1
FROM customers c JOIN chuoi ON c.id = chuoi.referred_by
)
SELECT * FROM chuoi; id | referred_by | buoc
----+-------------+------
24 | 5 | 1
5 | NULL | 2
(2 rows)
Chống vòng lặp vô hạn
Nếu dữ liệu có vòng (A→B→C→A), CTE đệ quy chạy mãi. Có hai lớp bảo vệ: giới hạn độ sâu bằng điều kiện (WHERE cap < 20) và, từ PostgreSQL 14, mệnh đề CYCLE tự phát hiện vòng.
WITH RECURSIVE g(src, dst) AS (VALUES (1,2), (2,3), (3,1), (3,4)), -- đồ thị có vòng 1→2→3→1
walk AS (
SELECT src, dst FROM g WHERE src = 1
UNION ALL
SELECT g.src, g.dst FROM g JOIN walk w ON g.src = w.dst
) CYCLE src SET la_vong USING duong_di
SELECT src, dst, la_vong FROM walk; src | dst | la_vong
-----+-----+---------
1 | 2 | f
2 | 3 | f
3 | 4 | f
3 | 1 | f
1 | 2 | t
(5 rows)Thiếu phần chặn (JOIN sai điều kiện, dữ liệu có vòng, quên giới hạn độ sâu) làm truy vấn chạy đến hết bộ nhớ/đĩa tạm. Luôn có ít nhất một: điều kiện kết thúc tự nhiên (hết con), giới hạn độ sâu, hoặc CYCLE.
Đặt statement_timeout khi thử nghiệm trên DB thật.
UNION ALL, không phải UNIONCả hai đều hợp lệ trong CTE đệ quy; UNION khử trùng giúp tự dừng ở đồ thị có vòng nhưng chậm và không cho phép giữ đường đi. Ưu tiên UNION ALL + CYCLE.
Data-modifying CTE: UPDATE/DELETE ... RETURNING trong WITH
PostgreSQL cho phép INSERT/UPDATE/DELETE ... RETURNING bên trong CTE rồi dùng kết quả cho câu lệnh sau — thực hiện nhiều thao tác nguyên tử trong một câu lệnh. Mẫu kinh điển: "di chuyển dòng sang bảng lưu trữ".
-- Tăng giá 10% cho Áo (danh mục 7) và báo cáo lại trước/sau trong cùng một câu lệnh
WITH cu AS (
SELECT id, price AS gia_cu FROM products WHERE category_id = 7
), moi AS (
UPDATE products p SET price = round(price * 1.10, -3)
FROM cu WHERE p.id = cu.id
RETURNING p.id, p.name, p.price AS gia_moi
)
SELECT moi.name, cu.gia_cu, moi.gia_moi
FROM moi JOIN cu USING (id)
ORDER BY moi.id; name | gia_cu | gia_moi
------------------+-----------+-----------
Áo thun basic | 199000.00 | 219000.00
Áo sơ mi công sở | 399000.00 | 439000.00
Áo khoác gió | 690000.00 | 759000.00
(3 rows)-- Di chuyển: xoá event cũ và chép sang bảng archive trong 1 câu lệnh (atomic)
CREATE TEMP TABLE events_archive (LIKE events);
WITH moved AS (
DELETE FROM events WHERE occurred_at < '2024-03-02' RETURNING *
)
INSERT INTO events_archive SELECT * FROM moved;
SELECT (SELECT count(*) FROM events_archive) AS da_luu_tru,
(SELECT count(*) FROM events) AS con_lai;CREATE TABLE
INSERT 0 40
da_luu_tru | con_lai
------------+---------
40 | 2960
(1 row)Ví dụ UPDATE/DELETE trên đây được bọc BEGIN ... ROLLBACK để không đổi dữ liệu mẫu. Khi tự chạy trên máy bạn, hãy làm tương tự (hoặc chạy thật rồi nạp lại shopviet.sql). Quy tắc nghề: luôn chạy SELECT với cùng điều kiện WHERE trước khi UPDATE/DELETE.
VIEW, MATERIALIZED VIEW, bảng tạm
| Đối tượng | Lưu dữ liệu? | Luôn mới? | Dùng khi |
|---|---|---|---|
| CTE / subquery | Không (trong 1 câu lệnh) | Có | Chia bước trong một truy vấn |
VIEW | Không — chỉ là truy vấn có tên | Có (chạy lại mỗi lần) | Tái sử dụng logic nghiệp vụ, che cột nhạy cảm, tương thích ngược khi đổi schema |
MATERIALIZED VIEW | Có (kết quả được lưu) | Chỉ sau REFRESH | Báo cáo nặng, dashboard; có thể tạo index |
TEMP TABLE | Có, trong phiên | Tuỳ bạn | Kết quả trung gian lớn dùng nhiều lần, ETL, cần index/ANALYZE |
CREATE VIEW v_doanh_thu_don AS
SELECT o.id AS order_id, o.customer_id, o.status, o.ordered_at,
sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) AS tien_hang,
count(*) AS so_dong
FROM orders o JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;
-- Dùng như một bảng
SELECT status, count(*) AS so_don, round(avg(tien_hang)) AS gia_tri_tb
FROM v_doanh_thu_don GROUP BY status ORDER BY so_don DESC;CREATE VIEW
status | so_don | gia_tri_tb
-----------+--------+------------
delivered | 240 | 30515873
cancelled | 40 | 14819800
pending | 40 | 39724250
paid | 40 | 45186190
shipped | 40 | 37610550
(5 rows)CREATE MATERIALIZED VIEW mv_doanh_thu_thang AS
SELECT date_trunc('month', o.ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS thang,
round(sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100)) AS doanh_thu
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY 1;
CREATE UNIQUE INDEX ON mv_doanh_thu_thang (thang); -- cần để REFRESH ... CONCURRENTLY
SELECT * FROM mv_doanh_thu_thang ORDER BY thang LIMIT 3;
-- Làm mới dữ liệu (không chặn người đọc nếu có unique index):
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_doanh_thu_thang;SELECT 11
CREATE INDEX
thang | doanh_thu
------------+------------
2024-01-01 | 30429000
2024-02-01 | 1008038600
2024-03-01 | 644785000
(3 rows)
REFRESH MATERIALIZED VIEWView A dùng view B dùng view C... Planner vẫn "mở" tất cả ra nhưng người đọc không còn biết thực sự đang chạy gì, và một thay đổi ở tầng dưới làm hỏng cả dây chuyền (không thể DROP nếu còn phụ thuộc). Giữ tối đa 1–2 tầng.
Ngoài ra SELECT * trong định nghĩa view bị "đóng băng" danh sách cột tại thời điểm tạo.
Dashboard hiển thị số liệu hôm qua mà không ai biết vì REFRESH không được lên lịch (cron / pg_cron / orchestrator). Hãy ghi rõ độ tươi dữ liệu trên báo cáo và giám sát việc refresh.
🏋️ Bài tập thực hành
Dùng CTE để tìm top 3 sản phẩm có doanh thu cao nhất (đã trừ giảm giá, bỏ đơn huỷ) và hiển thị tỉ lệ đóng góp phần trăm trên tổng doanh thu.
Xem lời giải & giải thích
WITH dt AS (
SELECT oi.product_id, sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) AS doanh_thu
FROM order_items oi JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY oi.product_id
)
SELECT p.name, round(dt.doanh_thu) AS doanh_thu,
round(100.0 * dt.doanh_thu / sum(dt.doanh_thu) OVER (), 1) AS ti_trong_pct
FROM dt JOIN products p ON p.id = dt.product_id
ORDER BY dt.doanh_thu DESC LIMIT 3; name | doanh_thu | ti_trong_pct
-----------------------+------------+--------------
Laptop Zenbook 16 Pro | 4125180000 | 33.7
Laptop Zenbook 14 | 3591000000 | 29.4
Điện thoại Aurora X1 | 1551006000 | 12.7
(3 rows)sum(...) OVER () là window function (module 8) tính tổng toàn bộ — ở đây thay thế một CTE 'tổng' riêng. Cả hai cách đều đúng.
Dùng CTE đệ quy để lấy mọi danh mục con (mọi cấp) của 'Điện tử' rồi đếm số sản phẩm đang bán trong nhánh đó.
Xem lời giải & giải thích
WITH RECURSIVE nhanh AS (
SELECT id FROM categories WHERE name = 'Điện tử'
UNION ALL
SELECT c.id FROM categories c JOIN nhanh n ON c.parent_id = n.id
)
SELECT count(*) AS so_sp_dang_ban
FROM products p
WHERE p.category_id IN (SELECT id FROM nhanh) AND p.is_active; so_sp_dang_ban
----------------
9
(1 row)Không cần biết trước nhánh có bao nhiêu cấp — đó là sức mạnh của đệ quy. JOIN thường chỉ đi được số cấp cố định.
Với nhân viên id = 11, liệt kê chuỗi quản lý từ người đó lên đến người đứng đầu (kèm thứ tự bước).
Xem lời giải & giải thích
WITH RECURSIVE len AS (
SELECT id, full_name, manager_id, 0 AS buoc FROM employees WHERE id = 11
UNION ALL
SELECT e.id, e.full_name, e.manager_id, len.buoc + 1
FROM employees e JOIN len ON e.id = len.manager_id
)
SELECT buoc, full_name FROM len ORDER BY buoc; buoc | full_name
------+----------------
0 | Phạm Đình Hiếu
1 | Vũ Gia Khang
2 | Lê Thu Hương
3 | Trần Quốc Bảo
(4 rows)Đi ngược lên: phần đệ quy nối theo e.id = len.manager_id (cha), ngược với duyệt xuống ở ví dụ sơ đồ tổ chức.
📚 Tài nguyên học tập
-
PostgreSQL — WITH QueriesCTE, đệ quy, SEARCH/CYCLE, data-modifying CTEDocs
-
PostgreSQL — Materialized ViewsTạo, làm mới và giới hạnDocs
✅ Tự đánh giá hoàn thành
- Viết được truy vấn 3–4 CTE đặt tên theo nghĩa nghiệp vụ và kiểm tra được từng bước
- Viết CTE đệ quy duyệt cây xuống và đi ngược lên, có điều kiện dừng/CYCLE
- Dùng UPDATE/DELETE ... RETURNING trong WITH khi cần thao tác nguyên tử nhiều bước
- Chọn đúng giữa VIEW, MATERIALIZED VIEW, TEMP TABLE và biết cách làm mới