Recursive CTE: org chart, BOM, danh mục cây — SQL and Data Analysis cho BA

Mở đầu — vì sao bài này quan trọng

Ở bài trước, bạn đã làm quen với CTE (Common Table Expression) như một cách viết query dễ đọc hơn, tách logic thành từng khối có tên. Nhưng CTE thường mới chỉ dừng ở mức "đặt tên cho một query con". Bài này mở ra một sức mạnh hoàn toàn khác: Recursive CTE — CTE tự gọi lại chính nó để đi xuống (hoặc đi lên) một cấu trúc phân cấp mà bạn không biết trước độ sâu.

Đây chính xác là loại bài toán mà BA (Business Analyst) gặp liên tục nhưng hay né tránh, vì nó "trông khó". Hãy thử nghĩ về những câu hỏi sau:

"Anh Khang quản lý tổng cộng bao nhiêu người, tính cả cấp dưới của cấp dưới?"
"Sản phẩm 'Combo quà Tết' này được lắp ráp từ những linh kiện gốc nào, và cần bao nhiêu cái mỗi loại?"
"Danh mục 'Điện tử' trên Tiki có tổng cộng bao nhiêu danh mục con cháu, đi sâu bao nhiêu tầng cũng phải đếm hết?"

Tất cả những câu hỏi này có chung một đặc điểm: dữ liệu nằm trong một bảng tự tham chiếu (self-referencing), và bạn phải đi xuyên qua nhiều tầng không cố định. Một nhân viên có sếp, sếp đó lại có sếp, cứ thế lên đến CEO. Nếu bạn dùng JOIN thông thường, bạn phải biết trước có bao nhiêu tầng để viết bấy nhiêu lần JOIN — điều bất khả thi khi cây có thể sâu 3 tầng hôm nay và 7 tầng tháng sau.

Recursive CTE giải quyết đúng vấn đề này một cách thanh lịch. Nắm được nó, bạn sẽ tự tin trả lời cả một lớp câu hỏi mà trước đây phải xuất dữ liệu ra Excel rồi ngồi nối tay. Đây là kỹ năng phân biệt một BA "biết SQL" với một BA "dùng SQL như công cụ thật sự".

Khái niệm cốt lõi

Dữ liệu phân cấp và bảng tự tham chiếu

Dữ liệu phân cấp (hierarchical data) là dữ liệu mà mỗi dòng có thể "trỏ" đến một dòng khác trong cùng bảng làm cha của nó. Ví dụ bảng employees:

id	name	manager_id
1	Khang (CEO)	NULL
2	Lan (VP)	1
3	Hùng (Director)	2
4	Mai (Manager)	3
5	Tú (Staff)	4

Cột manager_id trỏ về id của người sếp trực tiếp. CEO không có sếp nên manager_id là NULL. Cấu trúc này gọi là adjacency list — mỗi dòng chỉ biết "cha trực tiếp" của mình, không biết toàn bộ chuỗi tổ tiên.

Tại sao JOIN thường không đủ

Nếu muốn lấy chuỗi quản lý của Tú, bạn có thể viết:

SELECT e1.name AS staff, e2.name AS manager, e3.name AS director
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id
JOIN employees e3 ON e2.manager_id = e3.id;

Nhưng đoạn này cứng nhắc: nó chỉ đi lên đúng 3 tầng. Nếu công ty có 6 tầng, bạn phải thêm JOIN. Nếu phòng A có 4 tầng còn phòng B có 7 tầng, một query không thể vừa với cả hai. Đây là lúc đệ quy vào cuộc.

Cấu trúc của một Recursive CTE

Recursive CTE luôn có ba phần. Hãy thuộc nằm lòng khung này:

WITH RECURSIVE cte_name AS (
    -- 1. ANCHOR (điểm neo): dòng khởi đầu, chạy MỘT lần
    SELECT ...
    FROM ...
    WHERE ...
    UNION ALL    -- 2. RECURSIVE MEMBER: tự JOIN với chính cte_name
    SELECT ...
    FROM table t
    JOIN cte_name c ON t.parent = c.id
)
SELECT * FROM cte_name;  -- 3. truy vấn cuối cùng

Cơ chế hoạt động theo từng bước (iteration):

Anchor chạy một lần, tạo ra "tập kết quả ban đầu". Ví dụ: lấy CEO.
Recursive member lấy kết quả của bước trước, JOIN với bảng gốc để tìm "lớp con tiếp theo". Ví dụ: tìm những người có sếp là CEO.
Kết quả mới này lại trở thành đầu vào cho lần lặp tiếp theo, tìm lớp cháu.
Quá trình dừng khi recursive member không trả về dòng nào nữa (không còn con để tìm).
UNION ALL gom tất cả các lớp lại thành một bảng phẳng.

Từ khoá RECURSIVE là bắt buộc trong PostgreSQL và MySQL 8+. Một lưu ý quan trọng: bạn phải dùng UNION ALL chứ không phải UNION trong hầu hết trường hợp, vì UNION loại trùng sẽ làm chậm và đôi khi sai logic khi đếm số lượng.

Đi xuống vs đi lên

Hướng đệ quy phụ thuộc vào điều kiện JOIN:

Đi xuống (top-down): anchor là gốc (CEO, danh mục gốc), recursive member tìm con. JOIN kiểu child.parent_id = cte.id.
Đi lên (bottom-up): anchor là một node bất kỳ, recursive member tìm cha. JOIN kiểu parent.id = cte.parent_id.

Cùng một bảng, chỉ đảo điều kiện JOIN là bạn đổi hướng đi. Đây là điểm nhiều người mới nhầm.

Tình huống thực tế

Ví dụ 1 — Org chart tại một startup fintech Việt Nam

Giả sử bạn là BA tại một ví điện tử kiểu MoMo, công ty 400 người. Giám đốc khối Vận hành hỏi bạn: "Cho anh xem toàn bộ cấp dưới của chị Lan (VP Operations), kèm theo mỗi người đang ở tầng thứ mấy tính từ chị Lan." Đây là bài toán đi xuống có đo độ sâu.

WITH RECURSIVE org AS (
    -- ANCHOR: bắt đầu từ chị Lan
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE name = 'Lan'
    UNION ALL    -- RECURSIVE: tìm cấp dưới trực tiếp của mỗi người đã có trong org
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
)
SELECT level, name FROM org ORDER BY level, name;

Cột level được khởi tạo bằng 1 ở anchor, và mỗi lần đệ quy thì +1. Nhờ vậy bạn biết Hùng ở tầng 2, Mai ở tầng 3, Tú ở tầng 4 dưới chị Lan. Khi sếp khối hỏi tiếp "chị Lan quản lý bao nhiêu người?", bạn chỉ cần SELECT COUNT(*) - 1 FROM org (trừ 1 vì bản thân chị Lan cũng nằm trong kết quả).

Bài học rút ra: chỉ với một cột level cộng dồn, bạn vừa trả lời được "ai dưới ai" vừa trả lời "sâu bao nhiêu tầng" — hai câu hỏi mà phòng nhân sự thường phải dò tay trên file Excel.

Ví dụ 2 — Bill of Materials (BOM) tại nhà máy lắp ráp

Một công ty sản xuất đồ điện gia dụng ở Bình Dương bán "Nồi cơm điện cao cấp". Sản phẩm này gồm nhiều cụm linh kiện, và mỗi cụm lại gồm linh kiện nhỏ hơn — đúng cấu trúc cây nhiều tầng. Bảng bom lưu:

parent_part	child_part	quantity
Nồi cơm	Thân nồi	1
Nồi cơm	Bo mạch	1
Thân nồi	Lòng nồi	1
Thân nồi	Ốc vít	8
Bo mạch	Tụ điện	3

Phòng mua hàng hỏi: "Để sản xuất 500 nồi cơm, tổng cộng cần bao nhiêu linh kiện gốc mỗi loại?" Đây là bài toán "nổ tung BOM" (BOM explosion), kinh điển của ngành sản xuất.

WITH RECURSIVE bom_explosion AS (
    -- ANCHOR: các linh kiện trực tiếp của Nồi cơm, nhân với 500 nồi
    SELECT child_part, quantity * 500 AS total_qty
    FROM bom
    WHERE parent_part = 'Nồi cơm'
    UNION ALL    -- RECURSIVE: với mỗi linh kiện, tìm linh kiện con và nhân dồn số lượng
    SELECT b.child_part, be.total_qty * b.quantity
    FROM bom b
    JOIN bom_explosion be ON b.parent_part = be.child_part
)
SELECT child_part, SUM(total_qty) AS need
FROM bom_explosion
GROUP BY child_part
ORDER BY need DESC;

Điểm tinh tế ở đây là phép nhân dồn số lượng: mỗi tầng đi xuống, total_qty được nhân với số lượng linh kiện con. Cần 1 thân nồi cho mỗi nồi, mỗi thân nồi cần 8 ốc vít, vậy 500 nồi cần 500 × 1 × 8 = 4000 ốc vít. Đệ quy lo việc nhân dây chuyền này tự động.

Bài học rút ra: Recursive CTE không chỉ để "liệt kê cây" — nó có thể mang theo và tính toán giá trị tích luỹ qua từng tầng (số lượng, chi phí, trọng số). Đây là điều phân biệt một báo cáo sản xuất chính xác với một bảng tính tay đầy rủi ro sai sót.

Ví dụ 3 — Cây danh mục sản phẩm trên sàn TMĐT kiểu Tiki

Sàn thương mại điện tử có cây danh mục rất sâu: Điện tử → Điện thoại & Phụ kiện → Điện thoại smartphone → iPhone... Đội Marketing hỏi: "Cho tôi tất cả danh mục con nằm dưới 'Điện tử', kèm đường dẫn đầy đủ để hiển thị breadcrumb." Bảng categories(id, name, parent_id).

WITH RECURSIVE cat_tree AS (
    SELECT id, name, parent_id,
           CAST(name AS CHAR(500)) AS path
    FROM categories
    WHERE name = 'Điện tử'
    UNION ALL    SELECT c.id, c.name, c.parent_id,
           CONCAT(ct.path, ' > ', c.name)
    FROM categories c
    JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT id, path FROM cat_tree ORDER BY path;

Cột path tích luỹ tên qua từng tầng bằng CONCAT, cho ra chuỗi như Điện tử > Điện thoại & Phụ kiện > iPhone. Đây chính là breadcrumb mà front-end cần.

Bài học rút ra: ngoài việc đếm và tính số, Recursive CTE còn dựng được đường dẫn dạng chuỗi xuyên suốt cây — cực hữu ích cho báo cáo phân loại, export danh mục, hay làm dữ liệu cho dashboard.

Hướng dẫn từng bước

Khi gặp một bài toán phân cấp mới, hãy làm theo trình tự này:

Xác định bảng tự tham chiếu và cặp cột cha–con. Tìm cột nào trỏ về khoá chính của chính bảng đó (manager_id → id, parent_id → id, parent_part → child_part). Không có cặp này thì không có cây.

Quyết định hướng đi. Bạn cần đi xuống (tìm con cháu) hay đi lên (tìm tổ tiên)? Điều này quyết định anchor là gì và JOIN viết ra sao.

Viết ANCHOR trước, chạy thử riêng. Anchor chỉ là một SELECT thường, lọc ra điểm xuất phát. Chạy nó độc lập để chắc chắn bạn lấy đúng node gốc trước khi thêm đệ quy.

Thêm các cột "mang theo" cần thiết. Nếu cần đo tầng, thêm 1 AS level. Nếu cần đường dẫn, thêm cột path. Nếu cần nhân số lượng, thêm cột số. Anchor phải khai báo đủ các cột này, vì recursive member bắt buộc có cùng số cột và kiểu dữ liệu.

Viết RECURSIVE MEMBER. JOIN bảng gốc với chính CTE. Nhớ cập nhật các cột mang theo: level + 1, CONCAT(path, ...), qty * child_qty.

Ghép bằng UNION ALL. Gần như luôn là UNION ALL, không phải UNION.

Viết truy vấn cuối. SELECT từ CTE, có thể WHERE, GROUP BY, ORDER BY như bảng thường. Đây là nơi bạn tổng hợp ra con số cuối cùng cho sếp.

Đặt giới hạn an toàn khi test. Trong lúc thử nghiệm, thêm điều kiện WHERE level < 20 ở recursive member để tránh vòng lặp vô tận nếu dữ liệu có lỗi.

Lỗi thường gặp & mẹo

Quên từ khoá RECURSIVE. Trong PostgreSQL và MySQL, bạn phải viết WITH RECURSIVE. Thiếu nó, query báo lỗi hoặc hiểu sai. (SQL Server thì không cần từ khoá này nhưng cơ chế tương tự.)

Vòng lặp vô tận do dữ liệu bị "cycle". Nếu vì lỗi nhập liệu mà A là cha của B, B lại là cha của A, đệ quy sẽ chạy mãi không dừng. MySQL có biến cte_max_recursion_depth (mặc định 1000) để cắt; PostgreSQL hỗ trợ cú pháp ... CYCLE col SET is_cycle USING path để phát hiện. Mẹo an toàn: luôn mang theo cột path hoặc level và thêm điều kiện chặn độ sâu khi nghi ngờ dữ liệu bẩn.

Dùng UNION thay vì UNION ALL. UNION loại bỏ dòng trùng sau mỗi bước, vừa chậm vừa có thể làm sai kết quả khi bạn cần đếm hay nhân số lượng (như ví dụ BOM, hai nhánh cùng cần "ốc vít" là chuyện bình thường, không được gộp mất).

Anchor và recursive member lệch số cột hoặc lệch kiểu. Hai phần của UNION ALL phải khớp số cột và kiểu dữ liệu theo từng vị trí. Lỗi hay gặp nhất là cột path: ở anchor bạn để name (kiểu ngắn), nhưng sau nhiều lần CONCAT chuỗi dài ra và bị tràn. Mẹo: ở anchor hãy CAST(name AS CHAR(500)) hoặc CAST(... AS VARCHAR(1000)) để khai báo độ rộng đủ ngay từ đầu.

Nhầm hướng JOIN. Muốn đi xuống mà lại viết điều kiện như đi lên sẽ ra kết quả rỗng hoặc sai. Khi bí, hãy đọc to: "tôi đang có node X trong CTE, tôi muốn tìm dòng nào trong bảng gốc có cha là X" — câu đó dịch thẳng thành gốc.parent_id = cte.id.

Mẹo về hiệu năng: cây càng sâu và rộng, đệ quy càng nặng. Đảm bảo cột dùng để JOIN (parent_id, manager_id) có index. BA thường không tự tạo index, nhưng nên biết để đề nghị đội DBA — chủ đề này bạn sẽ học kỹ ở các bài về Indexes và performance.

Bài tập thực hành

Dùng bảng employees(id, name, manager_id) và categories(id, name, parent_id) ở trên để luyện:

Đi lên: Viết Recursive CTE in ra chuỗi quản lý từ "Tú" lên đến CEO (Tú → Mai → Hùng → Lan → Khang). Gợi ý: anchor là Tú, recursive member tìm cha.

Đếm theo tầng: Với mỗi nhân viên, tính họ cách CEO bao nhiêu tầng (CEO = tầng 0). Sắp xếp theo tầng tăng dần.

Đếm cấp dưới: Viết query trả về mỗi quản lý quản tổng cộng bao nhiêu người (kể cả gián tiếp). Gợi ý: chạy đệ quy đi xuống cho từng người, rồi GROUP BY và COUNT.

Cây danh mục có độ sâu: Liệt kê tất cả danh mục con của "Điện tử" kèm cột depth cho biết mỗi danh mục sâu mấy tầng so với "Điện tử".

Thử thách: Thêm vào bảng categories một dòng cố tình tạo vòng lặp (A là cha của B và B là cha của A), rồi viết query có cơ chế chặn để không bị treo. Quan sát cách cột level giúp bạn cắt vòng.

Tóm tắt

Recursive CTE là công cụ để xử lý dữ liệu phân cấp nhiều tầng mà bạn không biết trước độ sâu — org chart, BOM, cây danh mục, và mọi cấu trúc cha–con tự tham chiếu. Cấu trúc của nó luôn gồm ba phần: anchor (điểm neo chạy một lần), recursive member (tự JOIN với CTE để tìm lớp tiếp theo), nối nhau bằng UNION ALL, và một truy vấn cuối để tổng hợp.

Sức mạnh thật sự nằm ở những cột "mang theo": cột level để đo tầng, cột path để dựng breadcrumb, cột số để nhân dồn số lượng qua từng tầng. Chỉ cần thay đổi hướng JOIN là bạn đi lên hoặc đi xuống cây tuỳ ý.

Hãy nhớ các cạm bẫy: luôn dùng WITH RECURSIVE, ưu tiên UNION ALL, khớp số cột và kiểu giữa hai phần, khai báo cột chuỗi đủ rộng, và đề phòng vòng lặp vô tận bằng cách mang theo level/path. Khi nắm chắc khung tư duy này, cả một lớp câu hỏi "đi xuyên cây" mà trước đây phải làm tay trong Excel sẽ được bạn trả lời gọn gàng chỉ trong một query SQL.