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

PostgreSQL Bài 17: Các mệnh đề tổng hợp nâng cao: GROUPING SETS, ROLLUP và CUBE

Khi xây dựng các dashboard phân tích kinh doanh (BI), báo cáo tài chính hoặc trích xuất số liệu đa chiều, chúng ta thường xuyên đối mặt với yêu cầu:

Thống kê doanh thu theo từng Chi nhánh.
Thống kê doanh thu theo từng Năm.
Thống kê doanh thu theo cặp (Chi nhánh, Năm).
Tính toán tổng kết chung toàn bộ (Grand Total).

Cách làm ngây thơ nhất là viết 4 câu lệnh SELECT ... GROUP BY độc lập rồi dùng UNION ALL nối lại. Cách này vừa làm code cồng kềnh, vừa ép cơ sở dữ liệu phải quét bảng (Table Scan) và tổng hợp dữ liệu 4 lần riêng biệt.

PostgreSQL cung cấp giải pháp tiêu chuẩn ANSI SQL tối ưu: GROUPING SETS, ROLLUP và CUBE. Các mệnh đề này cho phép tính toán báo cáo đa tầng chỉ trong một lần quét bảng duy nhất.

1. Dữ liệu thực hành mẫu

Tạo một bảng lưu trữ doanh số bán hàng theo khu vực, danh mục và năm:

SQL

CREATE SCHEMA IF NOT EXISTS reporting_lab;

CREATE TABLE reporting_lab.sales_data (
    id INT PRIMARY KEY,
    region TEXT NOT NULL,
    category TEXT NOT NULL,
    year INT NOT NULL,
    revenue NUMERIC(12, 2) NOT NULL
);

INSERT INTO reporting_lab.sales_data VALUES
(1, 'North', 'Electronics', 2025, 10000),
(2, 'North', 'Electronics', 2026, 15000),
(3, 'North', 'Furniture',   2025,  5000),
(4, 'North', 'Furniture',   2026,  7000),
(5, 'South', 'Electronics', 2025, 20000),
(6, 'South', 'Electronics', 2026, 25000),
(7, 'South', 'Furniture',   2025,  8000),
(8, 'South', 'Furniture',   2026, 12000);

2. Vấn đề của cách tiếp cận `UNION ALL` cổ điển

Nếu muốn lấy tổng doanh thu theo (Region, Year), theo Region, theo Year và Grand Total:

SQL

-- CÁCH TIẾP CẬN CŨ (Chậm, cồng kềnh):
SELECT region, year::TEXT, SUM(revenue) FROM reporting_lab.sales_data GROUP BY region, year
UNION ALL
SELECT region, 'ALL YEARS', SUM(revenue) FROM reporting_lab.sales_data GROUP BY region
UNION ALL
SELECT 'ALL REGIONS', year::TEXT, SUM(revenue) FROM reporting_lab.sales_data GROUP BY year
UNION ALL
SELECT 'ALL REGIONS', 'ALL YEARS', SUM(revenue) FROM reporting_lab.sales_data;

Bẫy hiệu năng: Nếu bảng sales_data có 50 triệu dòng, câu truy vấn trên sẽ ép CPU đọc và băm/sắp xếp dữ liệu 4 lần. Toàn bộ tài nguyên I/O bị lãng phí chỉ để thực hiện các phép gom nhóm lặp lại.

3. `GROUPING SETS`: Tùy biến tập hợp nhóm theo ý muốn

Mệnh đề GROUPING SETS cho phép bạn khai báo chính xác danh sách các tổ hợp nhóm cần tính toán bên trong một câu lệnh GROUP BY duy nhất.

Cú pháp:

SQL

SELECT column1, column2, AGG_FUNC(...)
FROM table_name
GROUP BY GROUPING SETS (
    (column1, column2), -- Gom nhóm theo cả 2 cột
    (column1),          -- Gom nhóm theo column1
    (column2),          -- Gom nhóm theo column2
    ()                  -- Grand Total (không gom nhóm nào, tính trên toàn bộ bảng)
);

Thực hành với bảng doanh số:

SQL

SELECT 
    region,
    year,
    SUM(revenue) AS total_revenue
FROM reporting_lab.sales_data
GROUP BY GROUPING SETS (
    (region, year), -- Mức 1: Chi tiết từng vùng theo năm
    (region),       -- Mức 2: Tổng theo vùng
    ()              -- Mức 3: Grand Total toàn quốc
)
ORDER BY region NULLS LAST, year NULLS LAST;

Kết quả trả về:

Plaintext

 region | year | total_revenue 
--------+------+---------------
 North  | 2025 |      15000.00
 North  | 2026 |      22000.00
 North  | NULL |      37000.00  <-- Subtotal của miền Bắc
 South  | 2025 |      28000.00
 South  | 2026 |      37000.00
 South  | NULL |      65000.00  <-- Subtotal của miền Nam
 NULL   | NULL |     102000.00  <-- Grand Total toàn quốc

Khi một cột không tham gia vào phép tính toán của một nhóm cụ thể (ví dụ dòng Subtotal của North), PostgreSQL sẽ tự động điền giá trị NULL vào cột đó.

4. `ROLLUP`: Tính toán Subtotal và Grand Total phân tầng

Trong thực tế, các chiều dữ liệu thường có quan hệ phân cấp logic (Hierarchy), ví dụ:

$\text{Năm} \longrightarrow \text{Quý} \longrightarrow \text{Tháng}$

hoặc

$\text{Quốc gia} \longrightarrow \text{Tỉnh/Thành} \longrightarrow \text{Quận/Huyện}$

Mệnh đề ROLLUP là một dạng viết tắt mạnh mẽ của GROUPING SETS, tự động sinh ra các nhóm tổng hợp từ chi tiết nhất đến tổng quan nhất theo thứ tự từ phải sang trái.

$\text{ROLLUP}(A, B, C) \equiv \text{GROUPING SETS}((A, B, C), (A, B), (A), ( ))$

ROLLUP (region, category)
 │
 ├── 1. (region, category) ──► Doanh thu chi tiết từng nhóm
 ├── 2. (region)           ──► Subtotal theo từng region
 └── 3. ()                 ──► Grand Total toàn bộ

Thực hành:

SQL

SELECT 
    region,
    category,
    SUM(revenue) AS total_revenue
FROM reporting_lab.sales_data
GROUP BY ROLLUP (region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

Kết quả:

Plaintext

 region |  category   | total_revenue 
--------+-------------+---------------
 North  | Electronics |      25000.00
 North  | Furniture   |      12000.00
 North  | NULL        |      37000.00  <-- Subtotal North
 South  | Electronics |      45000.00
 South  | Furniture   |      20000.00
 South  | NULL        |      65000.00  <-- Subtotal South
 NULL   | NULL        |     102000.00  <-- Grand Total

Lưu ý thứ tự tham số: ROLLUP (region, category) sẽ tính subtotal cho region. Nếu bạn đảo ngược lại thành ROLLUP (category, region), hệ thống sẽ tính subtotal theo category.

5. `CUBE`: Ma trận tổng hợp đa chiều toàn diện (Cross-Tabulation)

Nếu ROLLUP đi theo một hướng phân tầng cố định, thì CUBE sẽ sinh ra toàn bộ tất cả các tổ hợp có thể có ( $2^N$ tổ hợp, với $N$ là số cột truyền vào).

$\text{CUBE}(A, B) \equiv \text{GROUPING SETS}((A, B), (A), (B), ( ))$

$\text{CUBE}(A, B, C) \equiv 8\text{ tổ hợp grouping sets}$

Thực hành:

SQL

SELECT 
    region,
    category,
    SUM(revenue) AS total_revenue
FROM reporting_lab.sales_data
GROUP BY CUBE (region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

Kết quả:

Plaintext

 region |  category   | total_revenue 
--------+-------------+---------------
 North  | Electronics |      25000.00
 North  | Furniture   |      12000.00
 North  | NULL        |      37000.00  <-- Tổng theo North
 South  | Electronics |      45000.00
 South  | Furniture   |      20000.00
 South  | NULL        |      65000.00  <-- Tổng theo South
 NULL   | Electronics |      70000.00  <-- Tổng toàn quốc ngành Electronics (Chỉ CUBE mới có!)
 NULL   | Furniture   |      32000.00  <-- Tổng toàn quốc ngành Furniture   (Chỉ CUBE mới có!)
 NULL   | NULL        |     102000.00  <-- Grand Total

CUBE là vũ khí tối thượng để tạo dữ liệu cho các báo cáo dạng bảng chéo (Pivot Table / Cross-Tab), nơi bạn vừa muốn xem tổng hàng ngang, vừa muốn xem tổng cột dọc.

6. Phân biệt `NULL` do gom nhóm với `NULL` dữ liệu thực tế bằng hàm `GROUPING()`

Một vấn đề kỹ thuật phát sinh: Nếu trong database của bạn, cột region vốn dĩ đã có một dòng mang giá trị NULL (chưa xác định khu vực), thì làm sao bạn phân biệt được giá trị NULL đó với giá trị NULL do PostgreSQL tự sinh ra ở dòng Subtotal?

PostgreSQL cung cấp hàm GROUPING(column_name, ...):

Trả về 0: Nếu cột đó tham gia vào phép gom nhóm của dòng hiện tại (tức là giá trị cụ thể).
Trả về 1: Nếu cột đó bị loại trừ khỏi phép gom nhóm (tức là dòng Subtotal / Grand Total được tổng hợp lại).

Ứng dụng: Chuẩn hóa nhãn báo cáo với `CASE WHEN`

SQL

SELECT 
    CASE 
        WHEN GROUPING(region) = 1 THEN '== TOÀN BỘ KHU VỰC =='
        ELSE COALESCE(region, 'Chưa xác định')
    END AS region_label,
    
    CASE 
        WHEN GROUPING(category) = 1 THEN '== TẤT CẢ NGÀNH HÀNG =='
        ELSE category
    END AS category_label,
    
    SUM(revenue) AS total_revenue,
    GROUPING(region, category) AS grouping_bitmask -- Trả về bitmask nhị phân biểu thị mức độ tổng hợp
FROM reporting_lab.sales_data
GROUP BY ROLLUP (region, category);

Kết quả chuẩn hóa cho ứng dụng Frontend:

Plaintext

      region_label      |     category_label     | total_revenue | grouping_bitmask 
------------------------+------------------------+---------------+------------------
 North                  | Electronics            |      25000.00 |                0
 North                  | Furniture              |      12000.00 |                0
 North                  | == TẤT CẢ NGÀNH HÀNG ==|      37000.00 |                1
 South                  | Electronics            |      45000.00 |                0
 South                  | Furniture              |      20000.00 |                0
 South                  | == TẤT CẢ NGÀNH HÀNG ==|      65000.00 |                1
 == TOÀN BỘ KHU VỰC ==  | == TẤT CẢ NGÀNH HÀNG ==|     102000.00 |                3

Bitmask 0 (00 nhị phân): Cả region và category đều tham gia gom nhóm.
Bitmask 1 (01 nhị phân): category bị gộp (Subtotal của region).
Bitmask 3 (11 nhị phân): Cả 2 cột đều bị gộp (Grand Total).

7. Cơ chế thực thi của Planner: `MixedAggregate`

Khi bạn chạy một câu lệnh ROLLUP hoặc CUBE, Query Planner của PostgreSQL sẽ sử dụng toán tử thực thi chuyên biệt: Aggregate (với strategy là Mixed hoặc Hashed):

SQL

EXPLAIN
SELECT region, category, SUM(revenue)
FROM reporting_lab.sales_data
GROUP BY CUBE (region, category);

Plaintext

QUERY PLAN
-------------------------------------------------------------------------------
 HashAggregate  (cost=1.16..1.40 rows=8 width=72)
   Hash Key: region, category
   Hash Key: region
   Hash Key: category
   Hash Key: ()
   ->  Seq Scan on sales_data  (cost=0.00..1.08 rows=8 width=44)

Engine chỉ quét bảng sales_data đúng 1 lần duy nhất (Seq Scan).
Sau đó, dữ liệu được đưa vào các bucket băm khác nhau cho từng tổ hợp Hash Key trực tiếp trên bộ nhớ RAM. Hiệu năng vượt trội hơn gấp nhiều lần so với giải pháp ghép nối UNION ALL.

8. Bảng đối chiếu nhanh 3 mệnh đề

Mệnh đề	Số lượng tập hợp nhóm sinh ra	Khi nào nên sử dụng?
`GROUPING SETS`	Do người dùng chỉ định chính xác	Chỉ cần một vài tổ hợp cụ thể, tránh dư thừa dữ liệu không cần thiết.
`ROLLUP`	$N + 1$ nhóm	Khi dữ liệu có tính phân cấp từ lớn đến nhỏ (Năm > Tháng > Ngày; Quốc gia > Vùng > Tỉnh).
`CUBE`	$2^N$ nhóm	Khi cần ma trận tổng hợp chéo tất cả các chiều độc lập (Pivot Table). Cần thận trọng khi $N > 5$ vì số dòng kết quả bùng nổ theo cấp số nhân.

9. Tóm tắt & Bài tiếp theo

GROUPING SETS, ROLLUP và CUBE thay thế hoàn toàn nhu cầu viết UNION ALL thủ công cho các báo cáo tổng hợp.
ROLLUP phục vụ cho dữ liệu phân tầng; CUBE phục vụ cho ma trận trực giao đa chiều.
Dùng hàm GROUPING() để bắt các dòng Subtotal/Grand Total và gắn nhãn giao diện rõ ràng.
PostgreSQL tối ưu hóa các phép toán này bằng Mixed/Hash Aggregate trong 1 lần quét bảng duy nhất.

Bài 18 xem tiếp: Tìm kiếm văn bản toàn diện (Full-Text Search): tsvector, tsquery và GIN Index — chúng ta sẽ tìm hiểu công cụ tìm kiếm tích hợp sẵn của PostgreSQL: cách tách từ (Parsing), chuẩn hóa gốc từ (Stemming), từ điển dừng (Stop words), xếp hạng mức độ liên quan (ts_rank) và kỹ thuật đánh index GIN để thay thế Elasticsearch cho các bài toán vừa và nhỏ.

PostgreSQL

1. Dữ liệu thực hành mẫu

2. Vấn đề của cách tiếp cận UNION ALL cổ điển

3. GROUPING SETS: Tùy biến tập hợp nhóm theo ý muốn

Thực hành với bảng doanh số:

4. ROLLUP: Tính toán Subtotal và Grand Total phân tầng

Thực hành:

5. CUBE: Ma trận tổng hợp đa chiều toàn diện (Cross-Tabulation)

Thực hành:

6. Phân biệt NULL do gom nhóm với NULL dữ liệu thực tế bằng hàm GROUPING()

Ứng dụng: Chuẩn hóa nhãn báo cáo với CASE WHEN

7. Cơ chế thực thi của Planner: MixedAggregate

8. Bảng đối chiếu nhanh 3 mệnh đề

9. Tóm tắt & Bài tiếp theo

Mục lục

2. Vấn đề của cách tiếp cận `UNION ALL` cổ điển

3. `GROUPING SETS`: Tùy biến tập hợp nhóm theo ý muốn

4. `ROLLUP`: Tính toán Subtotal và Grand Total phân tầng

5. `CUBE`: Ma trận tổng hợp đa chiều toàn diện (Cross-Tabulation)

6. Phân biệt `NULL` do gom nhóm với `NULL` dữ liệu thực tế bằng hàm `GROUPING()`

Ứng dụng: Chuẩn hóa nhãn báo cáo với `CASE WHEN`

7. Cơ chế thực thi của Planner: `MixedAggregate`