SQL 10 — Truy vấn nâng cao thực chiến

Đến bài này, bạn đã quen với JOIN, GROUP BY, subquery và window function cơ bản. Giờ là lúc bước lên một tầng: những công cụ mà người viết báo cáo phân tích, dashboard tài chính và pipeline dữ liệu dùng hằng ngày. Chúng không khó, nhưng phần lớn người học SQL không bao giờ chạm tới vì chúng ít xuất hiện trong các khoá nhập môn.

Bài này đi sâu vào sáu nhóm kỹ thuật: tổng hợp đa cấp (GROUPING SETS, ROLLUP, CUBE), recursive CTE, LATERAL join, mệnh đề FILTER, DISTINCT ON (đặc sản Postgres) và cách kết hợp window với aggregate. Mỗi phần đều có khối SQL chạy được ngay trong SQL Builder của sandbox.

Schema sandbox

Tất cả ví dụ dùng đúng các bảng/cột sau (không có cột nào khác):

departments(id, name)
employees(id, name, department_id, salary, hired_at)
customers(id, full_name, city, created_at)
accounts(id, customer_id, account_no, balance, currency)
transactions(id, account_id, amount, kind ['credit'|'debit'], created_at)

Lưu ý quan trọng: bảng accounts định danh bằng cột id và có account_no (không có account_id hay account_number). Bảng transactions tham chiếu tài khoản qua account_id, và khoá chính của nó là id (không phải transaction_id).

Tổng hợp đa cấp: GROUPING SETS, ROLLUP, CUBE

Vấn đề: nhiều mức tổng hợp trong một truy vấn

Giả sử bạn muốn báo cáo tổng số dư theo currency, và kèm dòng "tổng toàn bộ". Cách cũ là viết hai truy vấn rồi UNION ALL. Cách hiện đại là dùng ROLLUP.

ROLLUP(a, b) sinh ra các mức tổng hợp theo thứ tự phân cấp: (a, b) → (a) → () (tổng cuối). CUBE(a, b) sinh mọi tổ hợp con: (a,b), (a), (b), (). Còn GROUPING SETS cho bạn liệt kê chính xác những nhóm mình cần.

ROLLUP tổng số dư theo currency

▶ Chạy được trong SQL Builder

SELECT
    currency,
    SUM(balance) AS tong_so_du,
    COUNT(*) AS so_tai_khoan
FROM accounts
GROUP BY ROLLUP (currency)
ORDER BY currency NULLS LAST;

Kết quả gồm một dòng cho mỗi currency, cộng thêm một dòng cuối có currency = NULL — đó chính là tổng toàn bộ. Vấn đề: làm sao phân biệt dòng tổng (NULL do ROLLUP) với một currency thực sự bị NULL? Câu trả lời là hàm GROUPING().

Dùng GROUPING() để gắn nhãn dòng tổng

▶ Chạy được trong SQL Builder

SELECT
    CASE WHEN GROUPING(currency) = 1 THEN 'TỔNG TẤT CẢ' ELSE currency END AS nhom,
    SUM(balance) AS tong_so_du,
    COUNT(*) AS so_tai_khoan
FROM accounts
GROUP BY ROLLUP (currency)
ORDER BY GROUPING(currency), currency;

GROUPING(currency) trả về 1 khi dòng đó là dòng tổng hợp (cột này bị "gộp"), và 0 khi là giá trị thực. Nhờ vậy ta thay nhãn NULL bằng chữ "TỔNG TẤT CẢ" cho dễ đọc.

GROUPING SETS: chọn đúng các nhóm cần

Giả sử bạn muốn tổng số dư theo currency, và theo customer_id, nhưng không muốn tổ hợp chéo của cả hai (vì sẽ quá nhiều dòng). GROUPING SETS làm việc này:

▶ Chạy được trong SQL Builder

SELECT
    currency,
    customer_id,
    SUM(balance) AS tong_so_du
FROM accounts
GROUP BY GROUPING SETS ((currency), (customer_id), ())
ORDER BY currency NULLS LAST, customer_id NULLS LAST;

Truy vấn này trả về: các dòng gộp theo currency (lúc đó customer_id là NULL), các dòng gộp theo customer_id (lúc đó currency là NULL), và một dòng tổng cuối (). Đây chính là điều ROLLUP/CUBE làm bên trong — chúng chỉ là cú pháp tắt cho các GROUPING SETS thông dụng.

CUBE: mọi tổ hợp

CUBE(a, b) tương đương GROUPING SETS ((a,b),(a),(b),()). Dùng khi bạn cần một bảng tổng hợp đa chiều đầy đủ (kiểu pivot table).

▶ Chạy được trong SQL Builder

SELECT
    currency,
    customer_id,
    SUM(balance) AS tong_so_du,
    GROUPING(currency) AS g_cur,
    GROUPING(customer_id) AS g_cust
FROM accounts
GROUP BY CUBE (currency, customer_id)
ORDER BY g_cur, g_cust, currency NULLS LAST, customer_id NULLS LAST;

Hai cột g_cur, g_cust giúp bạn nhìn rõ mỗi dòng thuộc mức tổng hợp nào. Khi cả hai bằng 1, đó là tổng toàn cục.

Khi nào dùng gì? Dùng ROLLUP khi các chiều có quan hệ phân cấp tự nhiên (năm → quý → tháng, quốc gia → tỉnh → thành phố). Dùng CUBE khi cần phân tích chéo mọi chiều. Dùng GROUPING SETS khi muốn kiểm soát chính xác từng nhóm — tiết kiệm tài nguyên hơn CUBE khi bạn không cần hết tổ hợp.

Mệnh đề FILTER: aggregate có điều kiện

Trước đây để đếm có điều kiện trong một aggregate, ta hay viết SUM(CASE WHEN ... THEN 1 ELSE 0 END). SQL chuẩn (Postgres hỗ trợ tốt) có cú pháp gọn hơn: COUNT(*) FILTER (WHERE điều_kiện).

FILTER đếm credit/debit theo tài khoản

▶ Chạy được trong SQL Builder

SELECT
    account_id,
    COUNT(*) AS tong_giao_dich,
    COUNT(*) FILTER (WHERE kind = 'credit') AS so_credit,
    COUNT(*) FILTER (WHERE kind = 'debit') AS so_debit,
    SUM(amount) FILTER (WHERE kind = 'credit') AS tien_vao,
    SUM(amount) FILTER (WHERE kind = 'debit') AS tien_ra
FROM transactions
GROUP BY account_id
ORDER BY account_id;

Một lần quét bảng transactions, ta tính được năm chỉ số cho mỗi tài khoản. So với CASE WHEN, cú pháp FILTER đọc rõ ràng hơn và ý định thể hiện ngay trên bề mặt: "đếm các dòng thoả điều kiện này".

Pivot mini bằng FILTER

FILTER cũng là cách gọn để "xoay" dữ liệu thành cột. Ví dụ tính dòng tiền ròng theo tài khoản (tiền vào trừ tiền ra):

▶ Chạy được trong SQL Builder

SELECT
    account_id,
    COALESCE(SUM(amount) FILTER (WHERE kind = 'credit'), 0)
      - COALESCE(SUM(amount) FILTER (WHERE kind = 'debit'), 0) AS dong_tien_rong
FROM transactions
GROUP BY account_id
ORDER BY dong_tien_rong DESC;

COALESCE ở đây quan trọng: nếu một tài khoản chỉ có giao dịch debit, SUM(...) FILTER (WHERE kind = 'credit') sẽ trả NULL, và NULL - X = NULL. Bọc COALESCE(..., 0) để biến NULL thành 0.

Recursive CTE

WITH RECURSIVE cho phép một CTE tự tham chiếu đến chính nó. Cấu trúc luôn gồm hai phần nối bằng UNION ALL:

1. Anchor (mỏ neo): truy vấn khởi tạo, chạy một lần.
2. Recursive term: truy vấn tham chiếu lại tên CTE, lặp cho đến khi không sinh thêm dòng mới.

Sinh dãy số

Ví dụ kinh điển nhất — sinh dãy 1..10:

▶ Chạy được trong SQL Builder

WITH RECURSIVE day_so AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM day_so
    WHERE n < 10
)
SELECT n
FROM day_so;

Anchor cho n = 1. Mỗi vòng lặp lấy giá trị n hiện tại cộng 1, dừng khi n >= 10. Đây là nền tảng để sinh lịch (dải ngày), tạo dữ liệu test, hay lấp đầy các khoảng trống trong báo cáo.

Sinh dãy và tính tích luỹ

Có thể dùng dãy sinh ra làm khung rồi tính toán thêm. Ví dụ tính giai thừa từng bước bằng cách mang theo tích luỹ:

▶ Chạy được trong SQL Builder

WITH RECURSIVE giai_thua AS (
    SELECT 1 AS n, 1::bigint AS gia_tri
    UNION ALL
    SELECT n + 1, gia_tri * (n + 1)
    FROM giai_thua
    WHERE n < 10
)
SELECT n, gia_tri AS n_giai_thua
FROM giai_thua;

Cột gia_tri mang trạng thái qua từng vòng — một dạng "biến tích luỹ" mà SQL thông thường không làm được.

Duyệt phân cấp (minh hoạ, không chạy trong sandbox)

Khi có bảng tự tham chiếu kiểu cây (mỗi nhân viên có manager_id trỏ về một nhân viên khác), recursive CTE duyệt từ gốc xuống lá. Sandbox không có cột manager_id, nên đây chỉ là minh hoạ:

-- (minh hoạ, không chạy trong sandbox)
WITH RECURSIVE cap_duoi AS (
    SELECT id, name, manager_id, 1 AS cap
    FROM employees
    WHERE manager_id IS NULL          -- gốc: người không có cấp trên
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.cap + 1
    FROM employees e
    JOIN cap_duoi c ON e.manager_id = c.id
)
SELECT id, name, cap
FROM cap_duoi
ORDER BY cap, id;

Cột cap cho biết độ sâu trong cây. Cùng kỹ thuật áp dụng cho cây danh mục sản phẩm, sơ đồ tổ chức, hay phân rã nguyên vật liệu (BOM).

Cảnh báo: recursive CTE có thể lặp vô hạn nếu dữ liệu có chu trình (A là cấp trên của B, B lại là cấp trên của A). Luôn có điều kiện dừng rõ ràng, và với cây thực tế nên mang theo một mảng "đường đi" để phát hiện chu trình.

LATERAL join và subquery tương quan

Subquery tương quan: ôn lại

Subquery tương quan là subquery tham chiếu cột của truy vấn ngoài. Ví dụ: với mỗi tài khoản, lấy số dư so với số dư trung bình của cùng loại tiền tệ.

▶ Chạy được trong SQL Builder

SELECT
    a.id,
    a.account_no,
    a.currency,
    a.balance,
    (SELECT AVG(a2.balance)
     FROM accounts a2
     WHERE a2.currency = a.currency) AS tb_cung_currency
FROM accounts a
ORDER BY a.currency, a.balance DESC;

Subquery chạy "một cách logic" cho mỗi dòng của a, tham chiếu a.currency từ bên ngoài.

LATERAL: subquery nhìn thấy cột của bảng bên trái

LATERAL cho phép một subquery trong FROM tham chiếu các cột của bảng đứng trước nó. Điều này mở khoá kiểu truy vấn "top-N theo nhóm" rất gọn. Ví dụ: với mỗi tài khoản, lấy 3 giao dịch gần nhất.

▶ Chạy được trong SQL Builder

SELECT
    a.id AS account_id,
    a.account_no,
    t.id AS transaction_id,
    t.amount,
    t.kind,
    t.created_at
FROM accounts a
JOIN LATERAL (
    SELECT id, amount, kind, created_at
    FROM transactions tx
    WHERE tx.account_id = a.id
    ORDER BY tx.created_at DESC
    LIMIT 3
) t ON true
ORDER BY a.id, t.created_at DESC;

Mấu chốt: subquery bên trong tham chiếu a.id — điều mà subquery thường trong FROM không làm được. ON true chỉ là cú pháp bắt buộc của JOIN (ta không cần điều kiện nối vì LATERAL đã liên kết qua tham chiếu cột). Dùng LEFT JOIN LATERAL ... ON true nếu muốn giữ cả các tài khoản chưa có giao dịch.

Khi nào dùng LATERAL? Khi cần chạy một subquery có LIMIT/ORDER BY riêng cho từng dòng của bảng ngoài (top-N theo nhóm), hoặc khi gọi một hàm trả về tập (set-returning function) với tham số lấy từ bảng ngoài. Nếu chỉ cần một giá trị vô hướng thì subquery tương quan ở phần SELECT thường gọn hơn.

DISTINCT ON (đặc sản Postgres)

DISTINCT ON (cột) giữ lại dòng đầu tiên cho mỗi giá trị của cột đó, theo thứ tự ORDER BY. Đây là cách ngắn nhất để lấy "bản ghi mới nhất theo nhóm" trong Postgres.

Giao dịch mới nhất của mỗi tài khoản

▶ Chạy được trong SQL Builder

SELECT DISTINCT ON (account_id)
    account_id,
    id AS transaction_id,
    amount,
    kind,
    created_at
FROM transactions
ORDER BY account_id, created_at DESC;

Quy tắc bắt buộc: các cột trong DISTINCT ON (...) phải đứng đầu trong ORDER BY. Phần ORDER BY còn lại (created_at DESC) quyết định dòng nào được chọn trong mỗi nhóm — ở đây là giao dịch mới nhất.

So với cách dùng window function (ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY created_at DESC) rồi lọc = 1), DISTINCT ON ngắn hơn và thường nhanh hơn cho đúng nhu cầu "lấy một dòng đại diện mỗi nhóm". Nhược điểm: nó chỉ có ở Postgres, không chuẩn SQL.

Kết hợp window function với aggregate

Window function và GROUP BY không loại trừ nhau — bạn có thể tính aggregate ở mức nhóm rồi xếp hạng các nhóm đó, hoặc tính tỷ trọng từng dòng so với tổng nhóm.

Tỷ trọng số dư từng tài khoản trong loại tiền tệ

▶ Chạy được trong SQL Builder

SELECT
    id,
    account_no,
    currency,
    balance,
    SUM(balance) OVER (PARTITION BY currency) AS tong_currency,
    ROUND(
        100.0 * balance / NULLIF(SUM(balance) OVER (PARTITION BY currency), 0),
        2
    ) AS ty_trong_pct
FROM accounts
ORDER BY currency, ty_trong_pct DESC;

SUM(balance) OVER (PARTITION BY currency) tính tổng theo nhóm mà không làm gộp dòng — mỗi dòng tài khoản vẫn giữ nguyên, kèm theo tổng nhóm của nó. Nhờ vậy ta tính được tỷ trọng phần trăm ngay trên cùng một dòng. NULLIF(..., 0) tránh lỗi chia cho 0.

Xếp hạng nhóm sau khi đã GROUP BY

Có thể bọc một truy vấn GROUP BY trong CTE rồi áp window lên kết quả tổng hợp:

▶ Chạy được trong SQL Builder

WITH tong_theo_currency AS (
    SELECT currency, SUM(balance) AS tong_so_du
    FROM accounts
    GROUP BY currency
)
SELECT
    currency,
    tong_so_du,
    RANK() OVER (ORDER BY tong_so_du DESC) AS hang
FROM tong_theo_currency
ORDER BY hang;

Tầng dưới (CTE) gộp theo currency; tầng trên xếp hạng các loại tiền tệ theo tổng số dư. Mẫu "aggregate trước, window sau" rất phổ biến khi xây bảng xếp hạng.

CASE nâng cao trong aggregate

CASE không chỉ dùng để gắn nhãn — nó còn phân loại rồi gộp trong cùng một truy vấn. Ví dụ phân tầng tài khoản theo số dư:

▶ Chạy được trong SQL Builder

SELECT
    CASE
        WHEN balance >= 1000000 THEN 'cao'
        WHEN balance >= 100000  THEN 'trung binh'
        ELSE 'thap'
    END AS tang_so_du,
    COUNT(*) AS so_tai_khoan,
    SUM(balance) AS tong_so_du
FROM accounts
GROUP BY
    CASE
        WHEN balance >= 1000000 THEN 'cao'
        WHEN balance >= 100000  THEN 'trung binh'
        ELSE 'thap'
    END
ORDER BY tong_so_du DESC;

Lưu ý phải lặp lại biểu thức CASE trong GROUP BY (không thể tham chiếu alias tang_so_du ở đó trong SQL chuẩn). Đây là cách "phân bin" dữ liệu liên tục thành các khoảng rời rạc rồi tổng hợp.

Một lưu ý về INTERSECT/EXCEPT

Hai phép toán tập hợp INTERSECT (giao) và EXCEPT (hiệu) rất hữu ích để so sánh hai tập kết quả — ví dụ "khách hàng có tài khoản USD nhưng không có tài khoản VND". Tuy nhiên SQL Builder của sandbox chặn chúng, nên ở đây chỉ minh hoạ:

-- (minh hoạ, không chạy trong sandbox)
SELECT customer_id FROM accounts WHERE currency = 'USD'
EXCEPT
SELECT customer_id FROM accounts WHERE currency = 'VND';

Trong sandbox, bạn có thể đạt cùng kết quả bằng NOT EXISTS hoặc LEFT JOIN ... WHERE ... IS NULL thay cho EXCEPT, và bằng EXISTS/INNER JOIN thay cho INTERSECT.

Tóm tắt

• ROLLUP / CUBE / GROUPING SETS sinh nhiều mức tổng hợp trong một truy vấn; dùng GROUPING() để phân biệt dòng tổng với giá trị NULL thật.
• FILTER thay cho SUM(CASE WHEN ...), giúp aggregate có điều kiện đọc rõ ràng và dễ làm pivot.
• WITH RECURSIVE gồm anchor + recursive term nối bằng UNION ALL, dùng để sinh dãy số hoặc duyệt cây phân cấp — luôn cần điều kiện dừng để tránh lặp vô hạn.
• LATERAL cho subquery trong FROM thấy cột của bảng bên trái, mở khoá mẫu top-N theo nhóm; subquery tương quan phù hợp khi chỉ cần một giá trị vô hướng.
• DISTINCT ON (Postgres) lấy nhanh "một dòng đại diện mỗi nhóm"; các cột trong DISTINCT ON phải đứng đầu ORDER BY.
• Window + aggregate kết hợp để tính tỷ trọng từng dòng so với tổng nhóm, hoặc xếp hạng các nhóm sau khi đã GROUP BY.

Tự kiểm tra

1. Sự khác nhau giữa ROLLUP(a, b) và CUBE(a, b) về số mức tổng hợp sinh ra là gì?
2. Trong truy vấn ROLLUP, làm sao bạn phân biệt dòng "tổng toàn cục" với một giá trị currency tình cờ bằng NULL?
3. Viết lại COUNT(*) FILTER (WHERE kind = 'credit') bằng cú pháp CASE WHEN. Hai cách có khác nhau về kết quả không?
4. Recursive CTE bắt buộc có hai phần nối bằng phép toán nào, và phần nào chạy đúng một lần?
5. Tại sao subquery thường trong FROM không thể tham chiếu cột của bảng bên trái, còn LATERAL thì có thể?
6. Khi dùng DISTINCT ON (account_id) để lấy giao dịch mới nhất, ORDER BY phải bắt đầu bằng cột nào và vì sao?

Use case thực tế

Bối cảnh (minh hoạ): Khối Vận hành NCB cần một báo cáo huy động cuối ngày phục vụ họp giao ban. Yêu cầu gồm ba lát cắt trong một truy vấn: tổng số dư theo từng loại tiền (currency), tổng theo từng khách hàng lớn, và một dòng tổng toàn bộ — trước đây đội phải chạy ba truy vấn rồi ghép tay trên Excel, mất khoảng 20 phút mỗi ngày (ước lượng).

Với GROUP BY GROUPING SETS ((currency), (customer_id), ()), cả ba mức tổng hợp ra trong một lần quét bảng accounts. Hàm GROUPING(currency) giúp gắn nhãn "TỔNG TẤT CẢ" cho dòng tổng, tránh nhầm với số dư tài khoản có currency bị NULL.

Bước tiếp theo, đội kiểm soát rủi ro cần 3 giao dịch gần nhất của mỗi tài khoản để rà soát bất thường. Thay vì lặp truy vấn cho từng tài khoản, một JOIN LATERAL (... ORDER BY created_at DESC LIMIT 3) ON true trả về đúng top-3 theo nhóm trong một câu lệnh. Cuối cùng, để biết mỗi loại tiền chiếm bao nhiêu phần trăm tổng huy động, SUM(balance) OVER (PARTITION BY currency) cho tỷ trọng ngay trên từng dòng mà không làm gộp bản ghi. Kết quả: một truy vấn thay cho quy trình thủ công nhiều bước, số liệu nhất quán vì cùng đọc một ảnh chụp dữ liệu.

Ghi nhớ

• ROLLUP, CUBE, GROUPING SETS sinh nhiều mức tổng hợp trong một truy vấn; chúng chỉ là cú pháp tắt của GROUPING SETS. Dùng hàm GROUPING() để phân biệt dòng tổng (cột bị gộp) với giá trị NULL thật.
• Chọn ROLLUP cho chiều phân cấp, CUBE cho phân tích chéo đầy đủ, GROUPING SETS khi cần kiểm soát chính xác từng nhóm (tiết kiệm hơn CUBE).
• FILTER (WHERE ...) là cách chuẩn, dễ đọc thay cho SUM(CASE WHEN ...); nhớ bọc COALESCE(..., 0) khi làm phép trừ vì aggregate rỗng trả NULL.
• WITH RECURSIVE luôn gồm anchor (chạy một lần) + recursive term nối bằng UNION ALL; bắt buộc có điều kiện dừng, cây có chu trình cần mang theo "đường đi" để tránh lặp vô hạn.
• LATERAL cho subquery trong FROM thấy cột của bảng bên trái — mở khoá top-N theo nhóm; subquery tương quan hợp hơn khi chỉ cần một giá trị vô hướng.
• DISTINCT ON (cột) (đặc sản Postgres) lấy nhanh một dòng đại diện mỗi nhóm; các cột trong DISTINCT ON phải đứng đầu ORDER BY.
• Window function không loại trừ GROUP BY: kết hợp để tính tỷ trọng từng dòng so với tổng nhóm hoặc xếp hạng các nhóm theo mẫu "aggregate trước, window sau".

Nguồn tham khảo

• PostgreSQL Documentation — GROUPING SETS, CUBE, and ROLLUP
• PostgreSQL Documentation — WITH Queries (Common Table Expressions)
• PostgreSQL Documentation — LATERAL Subqueries
• PostgreSQL Documentation — Window Functions
• PostgreSQL Documentation — SELECT (DISTINCT ON, Aggregate FILTER)
• ISO/IEC 9075 (SQL standard) — Part 2: Foundation (SQL/Foundation), phần về GROUPING SETS, ROLLUP, CUBE và window functions

Đọc tiếp

SQL 11 — Hàm ngày tháng & chuỗi