Hierarchical queries: org tree, category tree nâng cao

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

Trong công việc của một Business Analyst (BA), bạn sẽ gặp đi gặp lại một loại dữ liệu rất đặc biệt: dữ liệu phân cấp (hierarchical data). Đó là sơ đồ tổ chức công ty với CEO ở trên cùng, rồi tới các giám đốc khối, trưởng phòng, nhân viên ở dưới. Đó là cây danh mục sản phẩm trên Tiki hay Shopee: "Điện tử" → "Điện thoại & Phụ kiện" → "Điện thoại" → "iPhone". Đó là chuỗi bình luận lồng nhau (comment threads), là cây tài khoản kế toán, là bảng định mức nguyên vật liệu (BOM — Bill of Materials).

Điểm chung của tất cả những thứ này là: một dòng dữ liệu lại "trỏ" về một dòng dữ liệu khác trong cùng một bảng. Một phòng ban có parent_id trỏ về phòng ban cấp trên. Một danh mục con có parent_id trỏ về danh mục cha. Đây không phải quan hệ giữa hai bảng khác nhau (mà bạn đã học ở phần JOIN), mà là quan hệ của bảng với chính nó, và quan trọng hơn — chiều sâu của nó không cố định. Bạn không biết trước cây sẽ sâu bao nhiêu cấp.

Đây chính là chỗ làm khó rất nhiều BA. Bạn không thể viết một câu JOIN bình thường để "đi" hết một cây sâu N cấp khi N thay đổi. Nếu bạn từng phải copy-paste 5-6 lần LEFT JOIN để lần theo sơ đồ tổ chức rồi vẫn thiếu cấp, thì bài này dành cho bạn. Chúng ta sẽ học cách truy vấn cây phân cấp một cách gọn gàng, đúng đắn và áp dụng được ngay vào báo cáo thực tế.

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

Mô hình adjacency list — cách lưu cây phổ biến nhất

Cách lưu trữ cây phổ biến nhất trong cơ sở dữ liệu là adjacency list (danh sách kề). Mỗi dòng chỉ cần biết "cha trực tiếp của tôi là ai" thông qua một cột như parent_id. Ví dụ một bảng phòng ban:

CREATE TABLE departments (
    dept_id      INT PRIMARY KEY,
    dept_name    VARCHAR(100),
    parent_id    INT  -- NULL nếu là gốc (CEO/công ty)
);

Node gốc (CEO, hoặc danh mục gốc) có parent_id = NULL. Mọi node khác trỏ về cha của nó. Mô hình này rất dễ ghi/sửa, nhưng để đọc cả cây thì cần kỹ thuật đặc biệt.

Recursive CTE — công cụ chủ lực

Để đi hết một cây có độ sâu bất kỳ, công cụ chuẩn của SQL hiện đại là Recursive CTE (Common Table Expression đệ quy). Bạn đã làm quen với cú pháp này ở Bài 17; ở bài 47 chúng ta tập trung vào việc dùng nó để giải các bài toán cây nâng cao mà BA thực sự gặp.

Cấu trúc tổng quát:

WITH RECURSIVE tree AS (
    -- 1. Anchor: điểm bắt đầu (node gốc hoặc một node bất kỳ)
    SELECT dept_id, dept_name, parent_id, 1 AS level
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL    -- 2. Recursive: nối thêm các con của những node đã có
    SELECT d.dept_id, d.dept_name, d.parent_id, t.level + 1
    FROM departments d
    JOIN tree t ON d.parent_id = t.dept_id
)
SELECT * FROM tree;

Hãy hiểu nó như sau: phần anchor chọn ra "tầng đầu tiên". Phần recursive lặp đi lặp lại, mỗi vòng lấy con trực tiếp của những node vừa tìm được ở vòng trước, cho tới khi không còn con nào nữa thì dừng. Cột level ta tự cộng thêm để biết mỗi node nằm ở cấp mấy.

Ba thứ BA hay cần lấy từ cây

Khi làm việc với cây, có ba yêu cầu lặp đi lặp lại mà bạn nên thuộc lòng:

Đường dẫn (path): chuỗi tổ tiên từ gốc xuống node, ví dụ Điện tử > Điện thoại > iPhone. Dùng để hiển thị breadcrumb hoặc xác định node thuộc nhánh nào.
Tất cả con cháu (descendants): từ một node, lấy hết mọi node nằm dưới nó. Ví dụ: "tất cả nhân viên thuộc khối Kinh doanh, dù cách bao nhiêu cấp".
Tổng hợp roll-up: cộng dồn số liệu của con cháu lên cha. Ví dụ: doanh số của một danh mục cha = tổng doanh số tất cả danh mục con cháu của nó.

Mỗi yêu cầu này đều dựa trên cùng một recursive CTE, chỉ khác cách bạn chọn anchor và xử lý kết quả.

Tình huống thực tế

Ví dụ 1: Sơ đồ tổ chức tại Tập đoàn FPT

Giả sử bạn là BA ở khối nhân sự của một tập đoàn công nghệ kiểu FPT, có bảng employees với emp_id, full_name, title, và manager_id (sếp trực tiếp). Sếp lớn nhất — Tổng Giám đốc — có manager_id = NULL.

Câu hỏi của giám đốc nhân sự: "Cho tôi xem toàn bộ cây tổ chức của bà Lan (Giám đốc khối Phần mềm, emp_id = 42), kèm số cấp và đường dẫn báo cáo."

WITH RECURSIVE org AS (
    SELECT emp_id, full_name, title, manager_id,
           1 AS level,
           CAST(full_name AS CHAR(1000)) AS path
    FROM employees
    WHERE emp_id = 42          -- bắt đầu từ bà Lan
    UNION ALL    SELECT e.emp_id, e.full_name, e.title, e.manager_id,
           o.level + 1,
           CONCAT(o.path, ' > ', e.full_name)
    FROM employees e
    JOIN org o ON e.manager_id = o.emp_id
)
SELECT level, path, title
FROM org
ORDER BY path;

Kết quả trả về toàn bộ nhân sự dưới quyền bà Lan, mỗi người kèm đường dẫn báo cáo như Lan > Hùng > Mai, sắp xếp theo thứ tự cây. Bài học rút ra: chọn anchor là một node bất kỳ (không phải gốc), bạn lập tức có "cây con" của riêng node đó — cực hữu ích khi sếp chỉ quan tâm một khối, không phải cả tập đoàn.

Ví dụ 2: Roll-up doanh số theo cây danh mục trên sàn TMĐT

Một sàn thương mại điện tử kiểu Tiki có bảng categories (cat_id, cat_name, parent_id) sâu 4-5 cấp, và bảng order_items ghi doanh số gắn với danh mục lá (cấp thấp nhất). Trưởng phòng ngành hàng hỏi: "Doanh số tháng 5 của ngành hàng 'Nhà cửa & Đời sống' là bao nhiêu, tính cả mọi danh mục con cháu?"

Vấn đề: doanh số chỉ gắn với danh mục lá, nhưng câu hỏi hỏi ở một danh mục cha tận trên cao. Ta cần map mỗi danh mục lá về danh mục gốc của nó:

WITH RECURSIVE cat_tree AS (
    -- anchor: chính các danh mục cấp 1 (ngành hàng lớn)
    SELECT cat_id AS root_id, cat_name AS root_name, cat_id
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL    SELECT ct.root_id, ct.root_name, c.cat_id
    FROM categories c
    JOIN cat_tree ct ON c.parent_id = ct.cat_id
)
SELECT ct.root_name,
       SUM(oi.amount) AS revenue
FROM cat_tree ct
JOIN order_items oi ON oi.cat_id = ct.cat_id
WHERE oi.order_month = '2026-05'
GROUP BY ct.root_name
ORDER BY revenue DESC;

Mẹo ở đây là CTE giữ luôn root_id/root_name xuyên suốt quá trình đệ quy, nên mỗi danh mục lá đều "nhớ" mình thuộc ngành hàng gốc nào. Sau đó chỉ cần JOIN với doanh số và GROUP BY theo gốc. Bài học rút ra: khi cần roll-up số liệu lên cây, hãy "kéo" thông tin tổ tiên (root) theo suốt quá trình đệ quy thay vì cố gắng đi ngược lên sau.

Ví dụ 3: Bình luận lồng nhau trên ứng dụng nội dung

Một startup nội dung kiểu báo VnExpress hay diễn đàn có bảng comments (comment_id, post_id, parent_comment_id, content, created_at). Bình luận gốc có parent_comment_id = NULL, trả lời thì trỏ về bình luận cha. Đội sản phẩm muốn lấy toàn bộ cây bình luận của một bài viết, hiển thị thụt lề theo độ sâu và đúng thứ tự.

WITH RECURSIVE thread AS (
    SELECT comment_id, parent_comment_id, content, created_at,
           0 AS depth,
           CAST(LPAD(comment_id, 10, '0') AS CHAR(500)) AS sort_key
    FROM comments
    WHERE post_id = 1001 AND parent_comment_id IS NULL
    UNION ALL    SELECT c.comment_id, c.parent_comment_id, c.content, c.created_at,
           t.depth + 1,
           CONCAT(t.sort_key, '.', LPAD(c.comment_id, 10, '0'))
    FROM comments c
    JOIN thread t ON c.parent_comment_id = t.comment_id
)
SELECT depth, content
FROM thread
ORDER BY sort_key;

Cột sort_key ghép id của tổ tiên lại tạo thành một "khóa sắp xếp" để con luôn nằm ngay dưới cha đúng vị trí — đây là kỹ thuật materialized path tạm thời trong lúc truy vấn. Bài học rút ra: với cây cần hiển thị đúng thứ tự lồng nhau, một sort_key dạng path là cách gọn nhất; còn cột depth dùng để thụt lề (indent) khi render giao diện.

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

Khi đứng trước một bài toán cây, hãy đi theo trình tự sau:

Xác định mô hình lưu trữ. Tìm cột self-reference: thường tên là parent_id, manager_id, parent_comment_id. Hỏi rõ: node gốc đánh dấu bằng NULL hay bằng cách trỏ về chính nó (parent_id = id)? Cách thứ hai sẽ gây vòng lặp vô hạn nếu bạn không lọc.

Quyết định bạn muốn đi xuống hay đi lên. Lấy con cháu thì anchor là node cần xét, điều kiện đệ quy nối con.parent_id = cha.id. Lấy tổ tiên (đường dẫn lên gốc) thì làm ngược lại: nối cha.id = con.parent_id.

Chọn anchor. Cả cây thì anchor là WHERE parent_id IS NULL. Một nhánh cụ thể thì anchor là WHERE id = <node>.

Thêm các cột tiện ích trong anchor và giữ chúng qua mỗi vòng: level/depth (cộng 1 mỗi vòng), path (nối tên), root_id (giữ nguyên gốc để roll-up).

Viết phần recursive bằng cách JOIN bảng gốc với chính CTE đang xây.

Xử lý kết quả ở câu SELECT ngoài cùng: lọc theo level nếu chỉ cần vài cấp, ORDER BY path/sort_key để giữ thứ tự cây, hoặc JOIN với bảng số liệu rồi GROUP BY để roll-up.

Luôn đặt chốt an toàn. Thêm điều kiện WHERE level < 50 trong phần recursive nếu nghi ngờ dữ liệu có vòng lặp, để tránh truy vấn chạy mãi không dừng.

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

Quên RECURSIVE. Ở PostgreSQL bắt buộc viết WITH RECURSIVE, nếu thiếu sẽ báo lỗi cú pháp. MySQL (8.0+) cũng yêu cầu RECURSIVE. Đây là lỗi gõ sai phổ biến nhất.

Vòng lặp vô hạn vì dữ liệu bẩn. Nếu trong dữ liệu có node A trỏ về B mà B lại trỏ về A (lỗi nhập liệu), recursive CTE sẽ chạy không dừng và đụng giới hạn của hệ quản trị (MySQL mặc định cte_max_recursion_depth = 1000). Luôn thêm cột level và chặn level < N để bảo vệ.

Nhầm chiều đệ quy. Muốn lấy con cháu mà lại viết điều kiện như đi tìm tổ tiên — kết quả ra rỗng hoặc sai. Hãy đọc to điều kiện JOIN: "con của tôi là dòng có parent_id bằng id của tôi" để chắc chắn không lộn chiều.

Đếm trùng khi roll-up. Nếu một danh mục lá vô tình xuất hiện hai lần trong cây (do dữ liệu lỗi), SUM sẽ cộng đôi. Mẹo: kiểm tra số node trả về của CTE bằng COUNT(DISTINCT cat_id) so với tổng số danh mục thực tế trước khi tin vào con số tổng hợp.

Hiệu năng trên cây lớn. Recursive CTE quét bảng nhiều lần. Hãy đảm bảo có index trên cột parent_id/manager_id để mỗi vòng JOIN nhanh. Với cây cực lớn và truy vấn lặp lại nhiều, cân nhắc đề xuất đội kỹ thuật lưu sẵn cột path (materialized path, dạng /1/42/108/) — khi đó lấy con cháu chỉ cần WHERE path LIKE '/1/42/%' mà không cần đệ quy.

Mẹo trình bày cho người không chuyên: khi xuất báo cáo cây cho sếp, dùng CONCAT(REPEAT('— ', level - 1), name) để tạo thụt lề bằng dấu gạch, giúp người đọc thấy ngay cấu trúc phân cấp mà không cần nhìn cột level.

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

Dùng bảng departments(dept_id, dept_name, parent_id) và employees(emp_id, full_name, dept_id, salary):

Liệt kê cây phòng ban từ gốc, mỗi phòng kèm level và đường dẫn dạng Công ty > Khối KD > Phòng Telesales. Sắp xếp đúng thứ tự cây.

Đếm nhân sự con cháu: với mỗi phòng ban cấp 1, tính tổng số nhân viên thuộc phòng đó và mọi phòng con cháu của nó.

Quỹ lương roll-up: tính tổng salary của toàn bộ nhân viên trong nhánh do phòng ban dept_id = 5 quản lý (gồm cả các phòng con cháu).

Tìm đường lên gốc: cho dept_id = 88, viết recursive CTE đi ngược lên để liệt kê toàn bộ phòng ban tổ tiên từ nó tới gốc.

Phát hiện vòng lặp: viết truy vấn (kèm chốt level < 50) để kiểm tra xem có phòng ban nào tạo thành vòng lặp trong dữ liệu hay không.

Hãy thử trên cả MySQL và PostgreSQL để cảm nhận khác biệt nhỏ về cú pháp CAST/CONCAT.

Tóm tắt

Dữ liệu phân cấp xuất hiện ở khắp nơi trong công việc BA: sơ đồ tổ chức, cây danh mục, bình luận lồng nhau, cây tài khoản. Chìa khóa để truy vấn chúng là mô hình adjacency list (parent_id) kết hợp với Recursive CTE. Ghi nhớ ba mẫu việc cốt lõi: lấy đường dẫn (path), lấy toàn bộ con cháu (descendants), và roll-up số liệu lên cây. Mỗi mẫu chỉ khác nhau ở cách chọn anchor và những cột tiện ích bạn mang theo qua mỗi vòng đệ quy — đặc biệt là level, path và root_id. Đừng quên WITH RECURSIVE, đặt chốt an toàn chống vòng lặp, đảm bảo index trên cột cha, và cân nhắc materialized path khi hiệu năng quan trọng. Nắm vững những kỹ thuật này, bạn sẽ trả lời được những câu hỏi cây mà trước đây phải né tránh hoặc làm thủ công trong Excel.