SQL nâng cao 6 — Pivot, Crosstab & Conditional Aggregation

Mọi báo cáo quản trị ngân hàng cuối cùng đều hội tụ về một hình dạng: ma trận. Cơ cấu số dư theo thành phố × loại tiền, số giao dịch theo chi nhánh × loại nghiệp vụ, dư nợ theo nhóm nợ × kỳ hạn. Dữ liệu gốc trong database lại nằm ở dạng "dài" (long) — mỗi bản ghi một dòng — còn báo cáo cần dạng "rộng" (wide) — mỗi giá trị thành một cột. Nhịp cầu giữa hai hình dạng đó chính là pivot, và kỹ thuật cốt lõi để làm pivot trong SQL chuẩn là conditional aggregation (tổng hợp có điều kiện).

Bài này đi từ nền tảng FILTER/CASE WHEN bên trong hàm tổng, lên tới pivot đầy đủ nhiều cột, chiều ngược lại (unpivot), và cách ghép thêm subtotal/tổng bằng GROUPING SETS. Đây là mảnh ghép báo cáo, nối tiếp nền tảng ở SQL nâng cao 1 — Window Functions và mảng chuỗi thời gian ở SQL nâng cao 5 — Funnel & time-series.

1. Conditional aggregation — trái tim của pivot

Ý tưởng gốc rất đơn giản: một hàm tổng hợp (SUM, COUNT, AVG...) không nhất thiết phải gộp tất cả các dòng trong nhóm. Ta có thể bảo nó chỉ gộp những dòng thỏa một điều kiện. Có hai cách viết, cùng ý nghĩa nhưng khác cú pháp.

1.1 Mệnh đề FILTER (chuẩn SQL, ưu tiên)

FILTER (WHERE <điều kiện>) là cú pháp SQL chuẩn (SQL:2003), được PostgreSQL hỗ trợ đầy đủ. Nó gắn trực tiếp sau hàm tổng và giới hạn tập dòng mà hàm đó nhìn thấy:

-- ▶ Chạy được
SELECT
  COUNT(*)                                   AS tong_tk,
  COUNT(*) FILTER (WHERE currency = 'VND')   AS so_tk_vnd,
  COUNT(*) FILTER (WHERE currency = 'USD')   AS so_tk_usd,
  SUM(balance) FILTER (WHERE currency = 'VND') AS du_vnd,
  SUM(balance) FILTER (WHERE currency = 'USD') AS du_usd
FROM accounts;

Mỗi cột là một "lát cắt" khác nhau của cùng tập dữ liệu, tính trong một lần quét bảng. COUNT(*) không có FILTER đếm toàn bộ; các cột có FILTER chỉ đếm/cộng phần thỏa điều kiện. Đây chính là pivot ở dạng thu nhỏ: giá trị của cột currency đã "biến" thành các cột kết quả.

1.2 CASE WHEN bên trong aggregate (tương thích rộng)

Trước khi FILTER phổ biến, người ta đạt cùng kết quả bằng CASE WHEN lồng trong hàm tổng. Mẹo mấu chốt: SUM bỏ qua NULL, và COUNT(<biểu thức>) chỉ đếm giá trị không NULL — nên nhánh ELSE để trống (mặc định trả NULL) sẽ loại dòng đó khỏi phép tổng:

-- ▶ Chạy được
SELECT
  SUM(CASE WHEN currency = 'VND' THEN balance END) AS du_vnd,
  SUM(CASE WHEN currency = 'USD' THEN balance END) AS du_usd,
  COUNT(CASE WHEN currency = 'VND' THEN 1 END)     AS so_tk_vnd
FROM accounts;

Hai cách cho kết quả y hệt. Khác biệt:

Lời khuyên: trên PostgreSQL luôn ưu tiên FILTER — ngắn, đúng chuẩn, và biểu đạt COUNT(*) có điều kiện gọn hơn. Chỉ lùi về CASE WHEN khi cần chạy chung câu lệnh trên nhiều dialect khác nhau.

2. Pivot đầy đủ: hàng → cột

Pivot thật sự là khi ta gộp FILTER/CASE với GROUP BY. GROUP BY xác định trục hàng (mỗi giá trị nhóm là một dòng kết quả), còn các cột FILTER xác định trục cột. Sơ đồ dưới minh hoạ cơ chế "quét một lần, rẽ nhiều cột":

Ví dụ kinh điển: mỗi thành phố một hàng, mỗi loại tiền một cột, ô là tổng số dư.

-- ▶ Chạy được
SELECT
  c.city,
  SUM(a.balance) FILTER (WHERE a.currency = 'VND') AS du_vnd,
  SUM(a.balance) FILTER (WHERE a.currency = 'USD') AS du_usd,
  SUM(a.balance) FILTER (WHERE a.currency = 'EUR') AS du_eur,
  SUM(a.balance)                                   AS tong_du
FROM customers c
JOIN accounts a ON a.customer_id = c.id
GROUP BY c.city
ORDER BY tong_du DESC;

Cột tong_du không có FILTER nên đóng vai trò tổng hàng (row total) — cộng ngang mọi loại tiền. Muốn thêm loại tiền chỉ cần thêm một cột FILTER; cấu trúc câu không đổi.

Cũng dạng đó nhưng đổi hàm và trục: đếm giao dịch theo kind thành cột, mỗi tài khoản (hoặc mỗi khách) một hàng.

-- ▶ Chạy được
SELECT
  c.city,
  COUNT(*) FILTER (WHERE t.kind = 'deposit')    AS so_gui,
  COUNT(*) FILTER (WHERE t.kind = 'withdrawal') AS so_rut,
  COUNT(*) FILTER (WHERE t.kind = 'transfer')   AS so_chuyen,
  COUNT(*)                                       AS tong_gd
FROM customers c
JOIN accounts a     ON a.customer_id = c.id
JOIN transactions t ON t.account_id = a.id
GROUP BY c.city
ORDER BY tong_gd DESC;

2.1 Điểm mấu chốt: danh sách cột là tĩnh

Hạn chế lớn nhất của pivot bằng SQL: bạn phải biết trước các giá trị sẽ thành cột ('VND', 'USD', 'deposit'...) và gõ tay từng cột. SQL không có cú pháp "tạo cột động theo dữ liệu" trong một câu SELECT đơn. Nếu tập giá trị thay đổi (thêm loại tiền mới), câu lệnh phải sửa. Đây là bản chất của mọi giải pháp pivot trong SQL, kể cả crosstab. Khi cần cột động thật sự, người ta sinh câu SQL từ tầng ứng dụng (đọc DISTINCT currency rồi ghép chuỗi câu lệnh) — nằm ngoài phạm vi một câu SELECT.

3. crosstab của tablefunc — tuỳ chọn, không bắt buộc

PostgreSQL có extension tablefunc cung cấp hàm crosstab() để pivot. Cách dùng đại khái:

-- Minh hoạ (KHÔNG chạy trên sandbox: cần extension tablefunc)
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  $$ SELECT c.city, a.currency, SUM(a.balance)
     FROM customers c JOIN accounts a ON a.customer_id = c.id
     GROUP BY c.city, a.currency ORDER BY 1,2 $$,
  $$ VALUES ('VND'),('USD'),('EUR') $$
) AS ct(city text, vnd numeric, usd numeric, eur numeric);

crosstab nhận hai truy vấn: một cho dữ liệu nguồn (row_name, category, value), một liệt kê danh mục cột, rồi trả về bảng rộng. Ưu điểm: gọn khi rất nhiều cột. Nhược điểm nặng:

• Cần cài CREATE EXTENSION tablefunc — không phải môi trường nào cũng có (sandbox read-only của ta thì không).
• Vẫn phải khai báo tĩnh danh sách và kiểu cột ở mệnh đề AS ct(...); sai kiểu là lỗi runtime.
• Cú pháp khó đọc (hai chuỗi lồng nhau), khó ghép thêm tổng/subtotal.

Kết luận thực chiến: ưu tiên SUM(...) FILTER. Nó luôn chạy trên PostgreSQL thuần không cần extension, dễ đọc, dễ thêm cột tổng, và ghép thoải mái với GROUPING SETS. crosstab chỉ đáng cân nhắc khi số cột rất lớn và bạn chấp nhận phụ thuộc extension.

4. UNPIVOT: cột → hàng

Chiều ngược lại — biến nhiều cột thành các dòng dạng (nhãn, giá trị) — gọi là unpivot. Tình huống: bảng có sẵn nhiều cột đo (ví dụ mỗi loại tiền một cột) và bạn muốn "gập" chúng về dạng dài để dễ nhóm, lọc, vẽ biểu đồ. PostgreSQL không có từ khoá UNPIVOT riêng; có hai cách chuẩn.

4.1 UNION ALL

Cách trực tiếp nhất: mỗi cột nguồn thành một nhánh SELECT, gắn nhãn hằng, rồi UNION ALL:

-- ▶ Chạy được
SELECT city, 'VND' AS currency, du FROM (
  SELECT c.city, SUM(a.balance) FILTER (WHERE a.currency = 'VND') AS du
  FROM customers c JOIN accounts a ON a.customer_id = c.id
  GROUP BY c.city
) v1
UNION ALL
SELECT city, 'USD', du FROM (
  SELECT c.city, SUM(a.balance) FILTER (WHERE a.currency = 'USD') AS du
  FROM customers c JOIN accounts a ON a.customer_id = c.id
  GROUP BY c.city
) v2
ORDER BY city, currency;

Dễ hiểu nhưng lặp code; nhiều cột thì dài.

4.2 LATERAL + VALUES (gọn hơn nhiều)

Cách "Postgres-native" đẹp hơn: mỗi dòng nguồn được "nở" ra nhiều dòng nhờ CROSS JOIN LATERAL (VALUES ...). VALUES liệt kê từng cặp (nhãn, giá trị lấy từ cột), LATERAL cho phép biểu thức trong VALUES tham chiếu cột của dòng bên ngoài:

-- ▶ Chạy được
SELECT
  a.account_no,
  x.chi_tieu,
  x.gia_tri
FROM accounts a
CROSS JOIN LATERAL (
  VALUES
    ('balance', a.balance),
    ('currency_code_len', length(a.currency)::numeric)
) AS x(chi_tieu, gia_tri)
ORDER BY a.account_no
LIMIT 20;

Mỗi tài khoản sinh ra 2 dòng (một cho số dư, một cho độ dài mã tiền tệ — ví dụ minh hoạ cách nở cột). Thêm "cột-nguồn" chỉ là thêm một dòng trong VALUES, ngắn hơn hẳn UNION ALL. Lưu ý ép ::numeric để mọi giá trị trong VALUES cùng kiểu — nếu trộn kiểu khác nhau sẽ lỗi.

5. Subtotal & tổng: GROUPING SETS, ROLLUP, CUBE

Pivot cho ta các ô; báo cáo còn cần dòng/cột tổng và tổng nhóm. Thay vì UNION ALL nhiều truy vấn GROUP BY khác cấp, SQL chuẩn có ba cú pháp mở rộng cho GROUP BY, đều được PostgreSQL hỗ trợ:

• GROUPING SETS — liệt kê tường minh các tổ hợp nhóm muốn tính. Ví dụ GROUPING SETS ((city), ()) tính vừa theo city, vừa tổng toàn cục.
• ROLLUP(a, b) — sinh phân cấp: (a,b), (a), và (). Dùng cho subtotal có thứ bậc (theo thành phố, rồi tổng chung).
• CUBE(a, b) — sinh mọi tổ hợp con: (a,b), (a), (b), (). Dùng khi cần cắt lát theo mọi chiều.

Quan hệ giữa chúng:

Ví dụ ROLLUP theo city để có từng thành phố + một dòng tổng cuối:

-- ▶ Chạy được
SELECT
  c.city,
  COUNT(*)        AS so_tk,
  SUM(a.balance)  AS tong_du
FROM customers c
JOIN accounts a ON a.customer_id = c.id
GROUP BY ROLLUP (c.city)
ORDER BY GROUPING(c.city), tong_du DESC;

Ở dòng tổng, c.city trả NULL và GROUPING(c.city) trả 1 (thay vì 0 ở dòng chi tiết) — nhờ đó ta phân biệt "tổng" với "thành phố tên NULL" và đẩy dòng tổng xuống cuối bằng ORDER BY GROUPING(c.city). Muốn hiển thị chữ "Tất cả" thay cho NULL, bọc COALESCE(c.city, 'TỔNG').

Ghép cả pivot lẫn subtotal trong một câu — vừa FILTER (trục cột) vừa ROLLUP (dòng tổng):

-- ▶ Chạy được
SELECT
  COALESCE(c.city, 'TỔNG CỘNG') AS thanh_pho,
  SUM(a.balance) FILTER (WHERE a.currency = 'VND') AS du_vnd,
  SUM(a.balance) FILTER (WHERE a.currency = 'USD') AS du_usd,
  SUM(a.balance)                                   AS tong_du
FROM customers c
JOIN accounts a ON a.customer_id = c.id
GROUP BY ROLLUP (c.city)
ORDER BY GROUPING(c.city), tong_du DESC;

Đây gần như là bảng báo cáo hoàn chỉnh: hàng = thành phố, cột = loại tiền, dòng cuối = tổng cộng.

6. Tỉ trọng % có điều kiện

Báo cáo cơ cấu thường cần tỉ trọng chứ không chỉ số tuyệt đối. Tính phần trăm mỗi thành phố đóng góp vào tổng số dư toàn hệ thống — dùng window function SUM() OVER () làm mẫu số, và ép ::numeric trước khi ROUND vì phép chia có thể cho ra kiểu số thực:

-- ▶ Chạy được
SELECT
  c.city,
  SUM(a.balance) AS tong_du,
  ROUND(
    100.0 * SUM(a.balance) / SUM(SUM(a.balance)) OVER ()::numeric,
    2
  ) AS ty_trong_pct
FROM customers c
JOIN accounts a ON a.customer_id = c.id
GROUP BY c.city
ORDER BY ty_trong_pct DESC;

SUM(SUM(a.balance)) OVER () là "tổng của các tổng" — sau khi GROUP BY city cho ra tổng mỗi thành phố, window OVER () cộng tất cả lại thành mẫu số chung. Nhân 100.0 (hằng số kiểu numeric) đảm bảo phép chia không bị cắt về số nguyên. Có thể kết hợp FILTER để tính tỉ trọng của riêng một loại tiền trong từng thành phố:

-- ▶ Chạy được
SELECT
  c.city,
  ROUND(
    100.0 * SUM(a.balance) FILTER (WHERE a.currency = 'VND')
          / NULLIF(SUM(a.balance), 0)::numeric,
    2
  ) AS pct_vnd_trong_thanh_pho
FROM customers c
JOIN accounts a ON a.customer_id = c.id
GROUP BY c.city
ORDER BY pct_vnd_trong_thanh_pho DESC NULLS LAST;

NULLIF(..., 0) chặn lỗi chia cho 0 (trả NULL thay vì báo lỗi). Đây là thói quen phòng thủ bắt buộc mọi khi mẫu số là một tổng có thể bằng 0.

Use case thực tế

Bối cảnh. Khối Nguồn vốn NCB cần bảng cơ cấu số dư huy động theo thành phố × loại tiền, kèm dòng tổng, để họp ALCO (Ủy ban Quản lý Tài sản Nợ - Có) hằng tháng. Yêu cầu: mỗi thành phố một hàng; các cột VND, USD, EUR; một cột tổng ngang; và một dòng "TỔNG CỘNG" ở cuối gộp toàn hệ thống. Trước đây bộ phận báo cáo export ba truy vấn rời rồi ghép tay trong Excel — chậm và dễ lệch số.

Giải pháp một câu SQL. Gộp SUM(...) FILTER (pivot loại tiền) với ROLLUP(city) (dòng tổng) trong đúng một truy vấn:

-- ▶ Chạy được
SELECT
  COALESCE(c.city, 'TỔNG CỘNG')                    AS thanh_pho,
  SUM(a.balance) FILTER (WHERE a.currency = 'VND') AS huy_dong_vnd,
  SUM(a.balance) FILTER (WHERE a.currency = 'USD') AS huy_dong_usd,
  SUM(a.balance) FILTER (WHERE a.currency = 'EUR') AS huy_dong_eur,
  SUM(a.balance)                                   AS tong_cong,
  ROUND(
    100.0 * SUM(a.balance) / SUM(SUM(a.balance)) OVER ()::numeric,
    2
  ) AS ty_trong_pct
FROM customers c
JOIN accounts a ON a.customer_id = c.id
GROUP BY ROLLUP (c.city)
ORDER BY GROUPING(c.city), tong_cong DESC;

Diễn giải kết quả.

• Mỗi dòng chi tiết (GROUPING = 0) là một thành phố với ba cột loại tiền, tổng ngang, và tỉ trọng đóng góp.
• Dòng cuối (GROUPING = 1) hiện TỔNG CỘNG với các cột là tổng dọc toàn hệ thống; ty_trong_pct của nó xấp xỉ 100%.
• ORDER BY GROUPING(c.city) đảm bảo dòng tổng luôn nằm cuối bất kể sắp xếp theo giá trị nào.
• Cần thêm chi nhánh/loại tiền: chỉ thêm một cột FILTER hoặc đổi ROLLUP(c.city) thành ROLLUP(region, c.city) để có thêm subtotal theo vùng — cấu trúc không đổi.

Kết quả vận hành. Một truy vấn thay cho ba lần export + ghép tay; số liệu nhất quán tuyệt đối vì cùng một lần quét; và bảng có thể cắm thẳng vào lớp báo cáo BI. Đây đúng tinh thần các chỉ số quản trị ở BI — Metrics & KPI: một định nghĩa, một nguồn tính, không lệch giữa các bản.

Ghi nhớ

• Conditional aggregation (SUM/COUNT FILTER (WHERE ...) hoặc CASE WHEN trong hàm tổng) là nền của mọi pivot trong SQL: quét một lần, rẽ ra nhiều cột.
• Trên PostgreSQL ưu tiên FILTER — chuẩn SQL, gọn, biểu đạt COUNT(*) có điều kiện tự nhiên. CASE WHEN chỉ cần khi phải chạy chung nhiều dialect.
• Pivot = GROUP BY (trục hàng) + FILTER (trục cột). Cột không có FILTER đóng vai trò tổng ngang.
• Danh sách cột pivot luôn tĩnh — phải biết trước và gõ tay. Cột động cần sinh SQL từ tầng ứng dụng.
• crosstab (extension tablefunc) là tuỳ chọn, cần cài extension và vẫn khai báo cột tĩnh; mặc định dùng FILTER vì luôn chạy được, dễ đọc, dễ ghép tổng.
• UNPIVOT (cột → hàng): UNION ALL (đơn giản, lặp) hoặc CROSS JOIN LATERAL (VALUES ...) (gọn, Postgres-native) — nhớ ép cùng kiểu trong VALUES.
• Subtotal/tổng: GROUPING SETS (thủ công), ROLLUP (phân cấp/subtotal), CUBE (mọi chiều). Dùng GROUPING(col) để phân biệt dòng tổng với NULL thật và để sắp xếp.
• Tính tỉ trọng %: mẫu số bằng SUM(SUM(x)) OVER (), luôn ép ::numeric trước ROUND và bọc NULLIF(...,0) chặn chia cho 0.

Nguồn tham khảo

• PostgreSQL Documentation — Aggregate Expressions (mệnh đề FILTER)
• PostgreSQL Documentation — GROUPING SETS, CUBE, and ROLLUP
• PostgreSQL Documentation — tablefunc (hàm crosstab())
• PostgreSQL Documentation — LATERAL Subqueries
• PostgreSQL Documentation — GROUPING() và hàm tổng hợp
• ISO/IEC 9075 — Information technology — Database languages — SQL (chuẩn định nghĩa FILTER, GROUPING SETS, ROLLUP, CUBE)