Đã đăng vào khoảng 11 giờ trước 8 phút đọc

PostgreSQL Bài 44: Common Table Expressions (CTE) và Truy vấn đệ quy (Recursive Queries)

Trong quá trình phát triển báo cáo, phân tích dữ liệu hoặc xử lý các logic kinh doanh phức tạp, bạn sẽ thường xuyên gặp phải những câu truy vấn SQL dài hàng trăm dòng, lồng ghép chằng chịt (Nested Subqueries). Kiểu viết này không chỉ khiến người đọc (và chính bạn vài tháng sau) "tẩu hỏa nhập ma", mà còn rất khó để debug.

Để giải quyết bài toán đọc hiểu và cấu trúc code SQL, chuẩn ANSI SQL:1999 đã giới thiệu CTE (Common Table Expressions) thông qua mệnh đề WITH. Đặc biệt hơn, với biến thể WITH RECURSIVE, SQL phá vỡ giới hạn của dữ liệu dạng bảng phẳng để tự do duyệt qua các cấu trúc dữ liệu dạng cây (Trees) và đồ thị (Graphs) chỉ trong một câu lệnh duy nhất.

1. Bản chất và Cú pháp của CTE cơ bản

CTE thực chất là một bảng kết quả tạm thời được đặt tên, chỉ tồn tại trong phạm vi của một câu lệnh truy vấn duy nhất.

1.1. So sánh Subquery (Truy vấn con) và CTE

Cách viết Subquery truyền thống (Inside-Out):

Bạn phải đọc từ trong ra ngoài. Càng lồng nhiều lớp, não bộ càng khó theo dõi ngữ cảnh.

SQL

SELECT c.name, stat.total_spent
FROM customers c
JOIN (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY customer_id
) stat ON c.id = stat.customer_id
WHERE stat.total_spent > 10000;

Cách viết bằng CTE (Top-Down):

Khối logic được đưa lên đầu, đặt tên rõ ràng, giúp luồng đọc diễn ra tuần tự từ trên xuống dưới.

SQL

WITH customer_stats AS (
    -- Định nghĩa khối dữ liệu trung gian
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY customer_id
)
-- Truy vấn chính sử dụng CTE như một bảng bình thường
SELECT c.name, cs.total_spent
FROM customers c
JOIN customer_stats cs ON c.id = cs.customer_id
WHERE cs.total_spent > 10000;

Bạn có thể xâu chuỗi nhiều CTE bằng dấu phẩy:

SQL

WITH step_1 AS (...),
     step_2 AS (SELECT * FROM step_1 WHERE ...),
     step_3 AS (...)
SELECT * FROM step_3;

2. Kỹ thuật độc quyền của PostgreSQL: Data-Modifying CTEs

Ở đa số các hệ quản trị khác, CTE chỉ dùng với lệnh SELECT. Tuy nhiên, PostgreSQL cho phép bạn bọc các lệnh INSERT, UPDATE, DELETE bên trong CTE, kết hợp với mệnh đề RETURNING để tạo ra các luồng luân chuyển dữ liệu (Data Pipeline) cực kỳ thanh lịch chỉ trong 1 transaction.

Bài toán thực tế: Người dùng bấm nút "Thanh toán", bạn cần lấy toàn bộ dữ liệu trong bảng cart_items (Giỏ hàng), chuyển nó sang bảng order_items (Chi tiết đơn hàng), và xóa chúng khỏi giỏ hàng.

Nếu dùng code Backend thông thường, bạn phải chạy lệnh SELECT, giữ data trên RAM của backend, chạy lệnh INSERT, rồi chạy lệnh DELETE. Với PostgreSQL, bạn gộp tất cả vào 1 câu SQL:

SQL

WITH moved_items AS (
    -- Bước 1: Xóa sản phẩm khỏi giỏ hàng và TRẢ VỀ dữ liệu vừa xóa
    DELETE FROM cart_items 
    WHERE session_id = 'abc-123'
    RETURNING product_id, quantity, price
)
-- Bước 2: Lấy dữ liệu trả về đó chèn thẳng vào bảng order_items
INSERT INTO order_items (order_id, product_id, quantity, price)
SELECT 1050, product_id, quantity, price 
FROM moved_items;

Lợi ích: Code ngắn gọn, đảm bảo tính nguyên tử (Atomicity), và triệt tiêu hoàn toàn độ trễ mạng (Network Latency) giữa các vòng gọi backend-database.

3. Bí mật Hiệu năng: Materialized vs Not Materialized (Từ PG 12+)

Đây là câu hỏi phỏng vấn kinh điển dành cho Senior Database Engineer.

Trước PostgreSQL 12, CTE được coi là một "Hàng rào tối ưu hóa" (Optimization Fence). Engine sẽ luôn luôn tính toán và lưu tạm kết quả CTE vào bộ nhớ rồi mới cho truy vấn chính đọc (Materialized). Điều này vô tình làm chậm các câu query nếu kết quả CTE quá lớn mà truy vấn chính lại chỉ cần 1 dòng (LIMIT 1).

Từ PostgreSQL 12 trở đi, hành vi mặc định đã thay đổi:

Nếu CTE chỉ được tham chiếu 1 lần trong truy vấn chính, Planner sẽ tự động "tháo dỡ" CTE và gộp nó chung vào câu lệnh chính (Inline / Not Materialized) để đẩy các điều kiện WHERE xuống sâu hơn nhằm tận dụng Index.
Nếu CTE được tham chiếu từ 2 lần trở lên, nó mới được Materialized để tránh việc phải tính toán lại nhiều lần.

Quyền kiểm soát trong tay bạn:

Bạn có thể ép buộc Query Planner theo ý muốn bằng từ khóa MATERIALIZED hoặc NOT MATERIALIZED:

SQL

WITH heavy_calculation AS MATERIALIZED (
    SELECT * FROM billion_row_table WHERE ...
)
SELECT * FROM heavy_calculation;

4. `WITH RECURSIVE`: Giải quyết bài toán Dữ liệu Phân cấp (Cây)

Trong cơ sở dữ liệu quan hệ, dữ liệu thường được lưu theo cấu trúc cha - con bằng tham chiếu parent_id.

Ví dụ, bảng categories (Danh mục sản phẩm):

Thiết bị điện tử (id=1, parent_id=NULL)
- Điện thoại (id=2, parent_id=1)
  - iPhone (id=3, parent_id=2)
    - iPhone 15 Pro (id=4, parent_id=3)

Nỗi đau của SQL truyền thống:

Nếu người dùng đang đứng ở danh mục "Thiết bị điện tử", làm sao để query ra TOÀN BỘ sản phẩm nằm trong "iPhone 15 Pro" và các nhánh con sâu hơn nữa?

Bạn không biết cây có độ sâu bao nhiêu tầng, nên không thể dùng lệnh JOIN (nếu có 10 tầng phải viết 10 cái JOIN).

Giải pháp: Mệnh đề WITH RECURSIVE.

4.1. Cơ chế hoạt động của Mệnh đề Đệ quy

Một cấu trúc WITH RECURSIVE bắt buộc phải có 3 thành phần liên kết với nhau qua UNION ALL:

SQL

WITH RECURSIVE cte_name AS (
    -- 1. BASE QUERY (Mỏ neo / Điều kiện cơ sở)
    -- Lệnh này CHẠY 1 LẦN DUY NHẤT để khởi tạo tập dữ liệu ban đầu
    SELECT ... 
    
    UNION ALL
    
    -- 2. RECURSIVE QUERY (Truy vấn đệ quy)
    -- Lệnh này sẽ tham chiếu lại chính cte_name. 
    -- Nó chạy lặp đi lặp lại cho đến khi không tìm thấy thêm dòng nào mới.
    SELECT ... FROM table JOIN cte_name ON ...
)
-- 3. MAIN QUERY
SELECT * FROM cte_name;

5. Thực hành: Xây dựng Cây Danh mục Sản phẩm

Chuẩn bị dữ liệu mẫu:

SQL

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INT REFERENCES categories(id)
);

INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Smartphones', 1),
(4, 'Laptops', 2),
(5, 'MacBooks', 4),
(6, 'Clothing', NULL),
(7, 'Menswear', 6);

5.1. Bài toán 1: Lấy toàn bộ cây con (Sub-tree) từ một gốc (Top-Down)

Bạn muốn tìm toàn bộ danh mục con, cháu, chắt... của "Electronics" (id = 1):

SQL

WITH RECURSIVE category_tree AS (
    -- 1. Base Query: Lấy node gốc (Electronics)
    SELECT id, name, parent_id, 1 AS depth
    FROM categories
    WHERE id = 1
    
    UNION ALL
    
    -- 2. Recursive Query: Tìm các node con có parent_id trỏ về các node ở bước trước
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth;

Quá trình Engine thực thi:

Vòng 1 (Base): Lấy ra (1, 'Electronics').
Vòng 2 (Recursive): Tìm bảng categories xem ai có parent_id = 1. Lấy ra (2, 'Computers') và (3, 'Smartphones').
Vòng 3 (Recursive): Tìm ai có parent_id là 2 hoặc 3. Lấy ra (4, 'Laptops').
Vòng 4 (Recursive): Tìm ai có parent_id = 4. Lấy ra (5, 'MacBooks').
Vòng 5 (Recursive): Tìm ai có parent_id = 5. Ra rỗng $\rightarrow$ Dừng đệ quy!

5.2. Bài toán 2: Tạo đường dẫn Breadcrumbs (Bottom-Up)

Khi người dùng đang xem "MacBooks" (id = 5), giao diện cần hiển thị đường dẫn ngược lên gốc: Electronics > Computers > Laptops > MacBooks.

SQL

WITH RECURSIVE breadcrumbs AS (
    -- Base: Bắt đầu từ node hiện tại
    SELECT id, name, parent_id, name::TEXT AS path
    FROM categories
    WHERE id = 5
    
    UNION ALL
    
    -- Recursive: Đi ngược lên cha
    SELECT c.id, c.name, c.parent_id, (c.name || ' > ' || b.path)
    FROM categories c
    JOIN breadcrumbs b ON b.parent_id = c.id
)
-- Chọn dòng cuối cùng trên cùng (gốc)
SELECT path FROM breadcrumbs WHERE parent_id IS NULL;

Kết quả: 'Electronics > Computers > Laptops > MacBooks'

6. Cạm bẫy vòng lặp vô hạn (Infinite Loop)

Dữ liệu cây là dạng đồ thị có hướng (Directed Graph). Nếu dữ liệu vô tình bị tạo vòng lặp (Node A là cha Node B, Node B lại là cha Node A), truy vấn RECURSIVE sẽ lặp vô tận và ăn hết 100% CPU.

Cách phòng vệ: Giới hạn độ sâu (Depth limit)

Luôn luôn kèm theo một bộ đếm depth và giới hạn cứng để tự ngắt vòng lặp:

SQL

WITH RECURSIVE safe_tree AS (
    SELECT id, parent_id, 1 AS depth FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.parent_id, st.depth + 1
    FROM categories c
    JOIN safe_tree st ON c.parent_id = st.id
    WHERE st.depth < 20 -- TỰ ĐỘNG NGẮT NẾU SÂU QUÁ 20 TẦNG
)
SELECT * FROM safe_tree;

7. Tổng kết & Bài tiếp theo

CTE (WITH) không chỉ giúp tổ chức code SQL theo khối logic dễ đọc (Top-down) mà còn là công cụ mạnh mẽ để luân chuyển dữ liệu với INSERT/UPDATE/DELETE RETURNING.
Nắm vững cơ chế Materialization (từ PG 12+) để tránh việc Planner tối ưu sai ý đồ.
WITH RECURSIVE là công cụ chuyên dụng và duy nhất trong SQL thuần để giải quyết các bài toán về cấu trúc cây, thư mục đa cấp, sơ đồ tổ chức nhân sự hay đồ thị liên kết.
Luôn kiểm soát độ sâu khi đệ quy để phòng chống vòng lặp vô tận.

Bài 45 xem tiếp: Window Functions: Phép màu của các hàm Cửa sổ (OVER, PARTITION BY) — khép lại các kỹ thuật truy vấn dữ liệu nâng cao, chúng ta sẽ học cách giải quyết bài toán: "Lấy ra 3 sản phẩm bán chạy nhất CỦA TỪNG danh mục". So với GROUP BY làm gộp mất chi tiết các dòng dữ liệu, Window Functions cho phép bạn tính toán các giá trị thống kê (Xếp hạng, Tính lũy kế, So sánh dòng hiện tại với dòng trước đó) trong khi vẫn giữ nguyên trạng thái độc lập của từng dòng!

PostgreSQL