SQL nâng cao 7 — CTE & Truy vấn đệ quy

Khi một câu SQL báo cáo phình lên tới hàng chục dòng với ba tầng subquery lồng nhau, vấn đề không còn là máy có chạy nổi không mà là người sau có đọc nổi không. Ba tháng sau, chính bạn mở lại câu truy vấn đó và mất mười lăm phút chỉ để nhớ subquery giữa đang làm gì. CTE — Common Table Expression ra đời để chữa đúng căn bệnh này: nó cho phép đặt tên cho từng bước tính toán trung gian, biến một khối SQL rối rắm thành một chuỗi bước tuần tự đọc từ trên xuống như đọc văn xuôi.

Nhưng CTE còn một sức mạnh mà subquery và view không có: khả năng đệ quy (WITH RECURSIVE). Đây là công cụ SQL chuẩn duy nhất để duyệt cấu trúc phân cấp không giới hạn độ sâu — cây tổ chức, cây danh mục sản phẩm, bill-of-materials — và để sinh dữ liệu như một chuỗi ngày liên tục làm trục thời gian. Bài này đi từ CTE thường, qua cơ chế materialization ảnh hưởng hiệu năng, tới đệ quy và những cạm bẫy chết người của nó. Đây là mảnh ghép nối tiếp nền tảng ở SQL nâng cao 1 — Window Functions và sẽ được mổ xẻ về mặt hiệu năng ở SQL nâng cao 8 — Optimization & EXPLAIN.

1. CTE thường — đặt tên cho từng bước

Cú pháp gốc rất đơn giản: mệnh đề WITH đặt trước SELECT chính, định nghĩa một hay nhiều "bảng tạm có tên" chỉ tồn tại trong phạm vi câu truy vấn đó.

-- ▶ Chạy được
WITH so_du_khach AS (
  SELECT c.id, c.full_name, SUM(a.balance) AS tong_so_du
  FROM customers c
  JOIN accounts a ON a.customer_id = c.id
  GROUP BY c.id, c.full_name
)
SELECT full_name, tong_so_du
FROM so_du_khach
WHERE tong_so_du > 100000000
ORDER BY tong_so_du DESC;

CTE so_du_khach tính tổng số dư từng khách, còn SELECT chính chỉ việc lọc và sắp xếp trên kết quả đã có tên rõ ràng. So với việc nhét toàn bộ phép GROUP BY vào một subquery trong FROM, cách này tách bạch "tính gì" khỏi "dùng thế nào".

1.1 Nhiều CTE nối tiếp — pipeline SQL

Sức mạnh thật sự lộ ra khi ta xâu chuỗi nhiều CTE, mỗi CTE tham chiếu CTE đứng trước nó. Cấu trúc này biến câu SQL thành một pipeline các bước, giống một chuỗi biến đổi dữ liệu.

-- ▶ Chạy được
WITH so_du_khach AS (
  SELECT c.id, c.city, SUM(a.balance) AS tong_so_du
  FROM customers c
  JOIN accounts a ON a.customer_id = c.id
  GROUP BY c.id, c.city
),
xep_hang AS (
  SELECT
    city,
    tong_so_du,
    RANK() OVER (PARTITION BY city ORDER BY tong_so_du DESC) AS hang
  FROM so_du_khach
)
SELECT city, tong_so_du, hang
FROM xep_hang
WHERE hang <= 3
ORDER BY city, hang;

Ở đây: bước 1 (so_du_khach) gộp số dư theo khách; bước 2 (xep_hang) xếp hạng trong từng thành phố bằng window function; SELECT cuối chỉ lấy top 3 mỗi thành phố. Ba tầng logic được viết phẳng, đọc tuần tự — thay vì lồng ba lớp ( SELECT ... ( SELECT ... ) ) ngược từ trong ra ngoài. Với người bảo trì, đây là khác biệt giữa mười phút và một phút.

1.2 CTE vs subquery vs view

Ba công cụ này dễ nhầm. Bảng dưới phân định rõ:

Nguyên tắc chọn: subquery cho phép tính một lần dùng một lần đơn giản; CTE khi câu truy vấn phức tạp cần chia bước, hoặc cần đệ quy, hoặc cần tham chiếu một tập trung gian nhiều lần; view khi logic đó cần dùng lại ở nhiều câu truy vấn khác nhau và đáng được lưu thành object có kiểm soát quyền.

2. Materialization — mặt hiệu năng của CTE

Đây là điểm nhiều người bỏ qua nhưng lại quyết định CTE nhanh hay chậm. Câu hỏi cốt lõi: khi PostgreSQL gặp một CTE, nó tính trước ra một bảng tạm rồi mới dùng (materialize), hay nhét định nghĩa CTE vào câu chính để tối ưu chung (inline)?

Lịch sử PostgreSQL chia làm hai mốc:

• Trước phiên bản 12: mọi CTE đều bị materialize bắt buộc — luôn tính ra bảng tạm. Đây là "optimization fence" (hàng rào tối ưu): planner không thể đẩy điều kiện WHERE từ câu ngoài vào trong CTE. Nếu CTE trả 10 triệu dòng mà câu ngoài chỉ cần 100 dòng, bạn vẫn phải vật chất hóa cả 10 triệu — rất chậm.
• Từ phiên bản 12 trở đi: PostgreSQL inline mặc định các CTE không đệ quy, được tham chiếu đúng một lần và không có tác dụng phụ. Planner khi đó có thể đẩy predicate xuống, dùng index như thể CTE là một subquery bình thường.

Bạn kiểm soát hành vi này bằng từ khóa tường minh:

-- ▶ Chạy được
WITH gd_lon AS NOT MATERIALIZED (
  SELECT account_id, amount, created_at
  FROM transactions
  WHERE kind = 'debit'
)
SELECT account_id, SUM(amount) AS tong_rut
FROM gd_lon
WHERE created_at >= DATE '2024-01-01'
GROUP BY account_id;

• NOT MATERIALIZED — ép inline, cho phép planner gộp CTE vào câu ngoài (điều kiện created_at sẽ được đẩy xuống lọc sớm).
• MATERIALIZED — ép tính trước ra bảng tạm một lần. Hữu ích khi CTE được tham chiếu nhiều lần và tính lại tốn kém (ví dụ một phép join nặng dùng lại 3 lần), hoặc khi CTE chứa hàm side-effect bạn muốn chạy đúng một lần.

Quy tắc thực hành: mặc định để PostgreSQL tự quyết; chỉ can thiệp khi EXPLAIN cho thấy planner chọn sai. CTE tham chiếu nhiều lần và nặng → cân nhắc MATERIALIZED. CTE chỉ dùng một lần nhưng bạn thấy nó không đẩy được filter → thử NOT MATERIALIZED. Chủ đề này được đào sâu ở SQL nâng cao 8 — Optimization & EXPLAIN.

3. WITH RECURSIVE — cỗ máy đệ quy

Đệ quy là khả năng một CTE tự tham chiếu chính nó. Đây là công cụ duy nhất trong SQL chuẩn để xử lý cấu trúc có độ sâu không biết trước.

3.1 Giải phẫu cú pháp

Một WITH RECURSIVE luôn gồm ba phần, nối bởi UNION hoặc UNION ALL:

WITH RECURSIVE ten_cte AS (
    <ANCHOR>          -- truy vấn khởi tạo, chạy 1 lần
  UNION ALL
    <RECURSIVE TERM>  -- tham chiếu ten_cte, lặp cho tới khi không sinh dòng mới
)
SELECT ... FROM ten_cte;

• Anchor (mỏ neo): truy vấn nền, không tự tham chiếu, tạo ra tập dòng ban đầu.
• Recursive term (số hạng đệ quy): tham chiếu chính CTE, nhận đầu ra của vòng lặp trước làm đầu vào, sinh ra tập dòng tiếp theo.
• Điều kiện dừng: ẩn trong recursive term — khi nó không sinh thêm dòng nào mới nữa, vòng lặp kết thúc.

3.2 Sinh chuỗi số — đệ quy tối giản

Ví dụ kinh điển nhất: sinh dãy số 1..10 mà không cần bảng nào.

-- ▶ Chạy được
WITH RECURSIVE dem AS (
  SELECT 1 AS n                    -- anchor
  UNION ALL
  SELECT n + 1 FROM dem WHERE n < 10   -- recursive term + điều kiện dừng
)
SELECT n FROM dem ORDER BY n;

Đọc từng bước: anchor cho n = 1. Vòng 1: recursive term lấy n = 1, sinh n = 2 (vì 1 < 10). Vòng 2 sinh 3... Tới khi n = 10, điều kiện n < 10 là false, không sinh dòng mới → dừng. Chú ý WHERE n < 10 chính là điều kiện dừng: thiếu nó, câu này sẽ chạy vô hạn.

3.3 Sinh chuỗi ngày — trục thời gian

Ứng dụng thực tế đắt giá nhất của kỹ thuật trên trong ngân hàng: sinh một trục thời gian liên tục để báo cáo không bị "thủng" ngày không có giao dịch. Đây là câu sinh 12 mốc đầu tháng của năm 2024:

-- ▶ Chạy được
WITH RECURSIVE thang AS (
  SELECT DATE '2024-01-01' AS ky
  UNION ALL
  SELECT (ky + INTERVAL '1 month')::date
  FROM thang
  WHERE ky < DATE '2024-12-01'
)
SELECT ky FROM thang ORDER BY ky;

Mỗi vòng lặp cộng thêm một tháng cho tới hết tháng 12. Kết quả là 12 dòng — một trục thời gian đầy đủ, sẵn sàng LEFT JOIN với dữ liệu giao dịch thật để những tháng không phát sinh vẫn hiện ra với giá trị 0.

Ghi chú: Trong PostgreSQL, cách chuẩn và gọn hơn để sinh chuỗi là hàm generate_series(start, stop, step). Ví dụ SELECT generate_series(DATE '2024-01-01', DATE '2024-12-01', INTERVAL '1 month'). WITH RECURSIVE vẫn đáng học vì nó là SQL chuẩn di động (chạy được cả trên các DB không có generate_series) và vì logic đệ quy của nó là nền tảng cho phần cây phân cấp bên dưới.

3.4 Lũy kế theo bước — mang trạng thái qua vòng lặp

Recursive term có thể mang theo trạng thái tích lũy, không chỉ đếm. Ví dụ tính lũy kế 1 + 2 + ... + n:

-- ▶ Chạy được
WITH RECURSIVE luy_ke AS (
  SELECT 1 AS n, 1 AS tong
  UNION ALL
  SELECT n + 1, tong + (n + 1)
  FROM luy_ke
  WHERE n < 8
)
SELECT n, tong FROM luy_ke ORDER BY n;

Cột tong chuyển từ vòng này sang vòng sau, cộng dồn dần: 1, 3, 6, 10, 15, 21, 28, 36. Đây là ý tưởng cốt lõi để đệ quy tính lãi kép theo kỳ, số dư cuộn (rolling balance), hay khấu hao theo bước.

4. Duyệt cây phân cấp

Đây là lý do lớn nhất người ta cần đệ quy. Một bảng "tự tham chiếu" (self-referencing) — mỗi dòng có một cột trỏ tới dòng cha — mô tả một cây có độ sâu tùy ý. WITH RECURSIVE là cách duy nhất để duyệt trọn cây bằng SQL chuẩn.

Giả sử ta có bảng nhan_vien(id, ho_ten, quan_ly_id), trong đó quan_ly_id trỏ tới id của cấp trên (giám đốc có quan_ly_id = NULL). Câu sau liệt kê toàn bộ nhân viên kèm cấp bậc trong cây:

-- Minh hoạ (KHÔNG chạy trên sandbox: cần bảng tự tham chiếu quan_ly_id)
WITH RECURSIVE cay_to_chuc AS (
  -- anchor: những người đứng đầu (không có cấp trên)
  SELECT id, ho_ten, quan_ly_id, 1 AS cap, ho_ten::text AS duong_dan
  FROM nhan_vien
  WHERE quan_ly_id IS NULL
  UNION ALL
  -- recursive: nối cấp dưới vào từng người đã tìm được
  SELECT nv.id, nv.ho_ten, nv.quan_ly_id,
         c.cap + 1,
         c.duong_dan || ' > ' || nv.ho_ten
  FROM nhan_vien nv
  JOIN cay_to_chuc c ON nv.quan_ly_id = c.id
)
SELECT cap, duong_dan FROM cay_to_chuc ORDER BY duong_dan;

Anchor lấy gốc cây (giám đốc). Mỗi vòng đệ quy join bảng gốc với tập kết quả vòng trước theo quan hệ cha-con, tăng cap (độ sâu) và nối duong_dan (đường đi từ gốc). Kết quả cho ta cả cấp bậc lẫn "breadcrumb" của từng người.

Lưu ý sandbox: các bảng của sandbox NCB (employees, departments) không có cột tự tham chiếu quan hệ cha-con, nên ví dụ cây tổ chức ở trên là minh hoạ, không đánh dấu chạy được. Cùng khuôn mẫu này áp dụng cho bill-of-materials (cây linh kiện: sản phẩm → cụm → chi tiết) và cây danh mục sản phẩm — chỉ khác tên bảng.

4.1 Bill-of-materials — khai triển cây linh kiện

Bài toán BOM (bill-of-materials): một sản phẩm gồm nhiều cụm, mỗi cụm gồm nhiều chi tiết, đệ quy nhiều tầng. Bảng bom(cha_id, con_id, so_luong) mô tả quan hệ. Đệ quy khai triển toàn bộ chi tiết cần cho một sản phẩm, nhân dồn số lượng qua từng tầng:

-- Minh hoạ (KHÔNG chạy trên sandbox: cần bảng bom cha-con)
WITH RECURSIVE khai_trien AS (
  SELECT con_id, so_luong
  FROM bom
  WHERE cha_id = 100          -- sản phẩm gốc
  UNION ALL
  SELECT b.con_id, k.so_luong * b.so_luong
  FROM bom b
  JOIN khai_trien k ON b.cha_id = k.con_id
)
SELECT con_id, SUM(so_luong) AS tong_can
FROM khai_trien
GROUP BY con_id;

Trong ngân hàng, cùng khuôn mẫu này áp cho cây phân bổ chi phí (chi phí trung tâm → phòng ban → sản phẩm), cây sở hữu doanh nghiệp (công ty mẹ → công ty con nhiều tầng, phục vụ soi UBO trong AML), hay cây tài khoản kế toán.

5. Cạm bẫy đệ quy

Đệ quy mạnh nhưng dễ gây tai họa. Bốn cạm bẫy phổ biến:

1. Thiếu điều kiện dừng → vòng lặp vô hạn. Nếu recursive term không có WHERE giới hạn, hoặc dữ liệu cây có chu trình (A là cha B, B lại là cha A do lỗi dữ liệu), câu truy vấn chạy mãi. Cách phòng: luôn có điều kiện dừng rõ ràng; với cây nghi có chu trình, mang theo một mảng đường đi và loại vòng lặp, hoặc dùng mệnh đề CYCLE (PostgreSQL 14+).

2. UNION vs UNION ALL. UNION ALL giữ mọi dòng — nhanh, nhưng không tự chặn chu trình. UNION loại trùng — an toàn hơn chút với chu trình đơn giản (dòng lặp lại bị loại nên vòng lặp có thể tự dừng), nhưng chậm hơn vì phải khử trùng mỗi vòng. Mặc định dùng UNION ALL; chỉ đổi sang UNION khi thật sự cần khử trùng.

3. Giới hạn độ sâu. Nên chủ động chặn độ sâu bằng một cột cap và WHERE cap < N trong recursive term. Đây vừa là van an toàn chống chạy lố, vừa cho kết quả có ý nghĩa (ví dụ "chỉ soi 5 tầng công ty con").

4. Hiệu năng. Mỗi vòng đệ quy là một lần join. Cây rộng và sâu → chi phí tăng nhanh. Hãy đảm bảo cột join (khóa cha-con) có index, và giới hạn tập anchor càng nhỏ càng tốt. Luôn kiểm tra bằng EXPLAIN ANALYZE.

Use case thực tế

Bối cảnh: Phòng MIS của NCB cần bảng báo cáo số dư huy động theo tháng của năm 2024, phân theo thành phố, với yêu cầu: (1) đủ 12 tháng kể cả tháng không phát sinh, (2) SQL dễ bảo trì vì hội đồng kiểm toán sẽ đọc lại. Đây là bài toán ghép hai kỹ thuật của bài: WITH RECURSIVE sinh trục thời gian đủ kỳ, và nhiều CTE nối tiếp tách báo cáo thành tầng rõ ràng.

Trước tiên, sinh trục 12 tháng (chạy được, độc lập):

-- ▶ Chạy được
WITH RECURSIVE truc_thang AS (
  SELECT DATE '2024-01-01' AS thang_dau
  UNION ALL
  SELECT (thang_dau + INTERVAL '1 month')::date
  FROM truc_thang
  WHERE thang_dau < DATE '2024-12-01'
)
SELECT thang_dau FROM truc_thang ORDER BY thang_dau;

Tiếp theo, báo cáo nhiều tầng bằng CTE nối tiếp — gộp giao dịch nạp tiền theo tài khoản, quy về khách và thành phố, rồi tổng hợp theo tháng:

-- ▶ Chạy được
WITH gd_nap AS (
  SELECT t.account_id,
         date_trunc('month', t.created_at)::date AS thang,
         t.amount
  FROM transactions t
  WHERE t.kind = 'credit'
    AND t.created_at >= TIMESTAMP '2024-01-01'
    AND t.created_at <  TIMESTAMP '2025-01-01'
),
gan_thanh_pho AS (
  SELECT g.thang, c.city, g.amount
  FROM gd_nap g
  JOIN accounts a  ON a.id = g.account_id
  JOIN customers c ON c.id = a.customer_id
)
SELECT
  thang,
  city,
  COUNT(*)                       AS so_gd,
  ROUND(SUM(amount)::numeric, 2) AS tong_nap
FROM gan_thanh_pho
GROUP BY thang, city
ORDER BY thang, tong_nap DESC;

Diễn giải cho hội đồng kiểm toán: câu trên đọc từ trên xuống như ba câu tiếng Việt — "lấy các giao dịch nạp năm 2024" → "gắn mỗi giao dịch với thành phố của khách" → "tổng hợp theo tháng và thành phố". Không một subquery lồng nào phải đọc ngược. Bước tách CTE gd_nap cũng khoanh vùng điều kiện thời gian một chỗ duy nhất, dễ soát.

Trong pipeline thực tế, phòng MIS LEFT JOIN trục truc_thang (12 tháng) với kết quả tổng hợp trên, để tháng không phát sinh vẫn hiện tong_nap = 0 — biểu đồ đường không bị gãy, và con số khớp tuyệt đối với sổ cái. Kết quả: một câu SQL kiểm toán viên đọc hiểu trong một lượt, thay cho khối truy vấn ba tầng subquery mà tháng trước không ai dám sửa. Về mặt chỉ số quản trị, xem thêm BI 4 — Metrics & KPI.

Ghi nhớ

• CTE (WITH) đặt tên cho từng bước tính, biến truy vấn lồng sâu thành pipeline đọc tuần tự từ trên xuống — cứu cánh cho khả năng bảo trì.
• Nhiều CTE nối tiếp nhau: CTE sau tham chiếu CTE trước, mỗi bước một tầng logic rõ ràng.
• CTE vs subquery vs view: subquery cho việc dùng một lần; CTE khi cần chia bước / tái dùng trong câu / đệ quy; view khi logic dùng lại ở nhiều truy vấn.
• Materialization: PostgreSQL ≥ 12 inline mặc định CTE không đệ quy dùng một lần. Ép bằng MATERIALIZED (tính trước, tốt khi tham chiếu nhiều lần) hoặc NOT MATERIALIZED (inline, cho phép đẩy filter).
• WITH RECURSIVE = anchor (chạy 1 lần) + recursive term (tự tham chiếu, lặp) nối bởi UNION [ALL]; dừng khi recursive term không sinh dòng mới.
• Dùng đệ quy để sinh chuỗi (số, ngày/tháng làm trục thời gian) và duyệt cây phân cấp (tổ chức, BOM, sở hữu doanh nghiệp). Với sinh chuỗi trong PostgreSQL, generate_series gọn hơn.
• Cạm bẫy: luôn có điều kiện dừng (tránh vòng lặp vô hạn); UNION ALL nhanh nhưng không chặn chu trình; chặn độ sâu bằng cột cap; index khóa cha-con và kiểm tra bằng EXPLAIN ANALYZE.
• Xem tiếp phần tối ưu và đọc kế hoạch thực thi ở SQL nâng cao 8 — Optimization & EXPLAIN.

Nguồn tham khảo

• PostgreSQL Documentation — WITH Queries (Common Table Expressions) — anchor/recursive term, MATERIALIZED/NOT MATERIALIZED, mệnh đề CYCLE và SEARCH.
• PostgreSQL Documentation — generate_series (Set Returning Functions) — cách sinh chuỗi số/ngày gọn hơn WITH RECURSIVE.
• PostgreSQL Documentation — EXPLAIN — đọc kế hoạch thực thi để kiểm tra hành vi materialization và join của CTE đệ quy.
• ISO/IEC 9075 (SQL standard) — chuẩn định nghĩa cú pháp WITH và WITH RECURSIVE cho truy vấn đệ quy.
• PostgreSQL Documentation — Date/Time Functions and Operators (date_trunc, INTERVAL) — các phép toán ngày tháng dùng trong sinh trục thời gian.