Trino SQL Bài học 20: Tổng hợp dữ liệu nâng cao với CUBE và ROLLUP

Mục tiêu bài học:

• Hiểu bản chất CUBE và ROLLUP thực chất là các "phím tắt" (syntactic sugar) để tự động sinh ra các GROUPING SETS.

• Ứng dụng ROLLUP cho các báo cáo dữ liệu có tính phân cấp (Hierarchical Data).

• Ứng dụng CUBE cho các bài toán phân tích chéo đa chiều (Cross-Tabular Reports).

• Sử dụng hàm GROUPING() để phân biệt NULL của hệ thống và NULL của dữ liệu thực.

• Nhận diện và phòng tránh cạm bẫy "bùng nổ tổ hợp" làm sập bộ nhớ cụm Trino.

1. ROLLUP: Khai phá dữ liệu theo cấp bậc (Hierarchical)

Ở Bài 19, bạn đã biết cách dùng GROUPING SETS để tạo Grand Total và Subtotal. Tuy nhiên, khi dữ liệu của bạn có tính thứ bậc tự nhiên (ví dụ: Năm > Tháng > Ngày, hoặc Châu lục > Quốc gia > Tỉnh thành), việc gõ thủ công từng tập hợp rất mất thời gian.

ROLLUP sinh ra để tự động hóa việc này. Nó tạo ra các tập hợp bằng cách loại bỏ dần các cột từ phải sang trái.

Công thức quy đổi:

GROUP BY ROLLUP (A, B, C) sẽ tự động dịch dưới nền (engine) thành:

SQL

GROUPING SETS (
    (A, B, C), -- Chi tiết nhất
    (A, B),    -- Bỏ C
    (A),       -- Bỏ B, C
    ()         -- Bỏ tất cả (Grand Total)
)

Ví dụ thực tế: Xem báo cáo doanh thu theo thứ bậc Thời gian (Năm > Tháng).

SQL

SELECT 
    EXTRACT(YEAR FROM orderdate) AS order_year,
    EXTRACT(MONTH FROM orderdate) AS order_month,
    SUM(totalprice) AS revenue
FROM tpch.tiny.orders
WHERE orderdate >= DATE '1995-01-01'
GROUP BY ROLLUP (
    EXTRACT(YEAR FROM orderdate), 
    EXTRACT(MONTH FROM orderdate)
)
ORDER BY order_year, order_month;

Kết quả trả về sẽ có các dòng chi tiết từng tháng, theo sau là dòng Subtotal của cả Năm đó (cột tháng bị NULL), và cuối cùng là một dòng Grand Total cho toàn bộ các năm (cả năm và tháng đều NULL).

2. CUBE: Phân tích chéo toàn diện (Cross-Dimensional)

Nếu ROLLUP chỉ bóc tách từ phải sang trái theo một đường thẳng cấp bậc, thì CUBE (Khối lập phương) sẽ tạo ra tất cả các tổ hợp (combinations) có thể có của các cột được truyền vào.

Nó thường được dùng để đổ dữ liệu vào các Pivot Table, nơi người dùng BI có thể kéo thả bất kỳ chiều nào (dimension) để xem dữ liệu.

Công thức quy đổi:

GROUP BY CUBE (A, B, C) sẽ tự động dịch thành $2^3 = 8$ tập hợp:

SQL

GROUPING SETS (
    (A, B, C), 
    (A, B), (A, C), (B, C), 
    (A), (B), (C), 
    ()
)

Ví dụ thực tế: So sánh chéo giữa Độ ưu tiên đơn hàng và Phân khúc thị trường.

SQL

SELECT 
    c.mktsegment, 
    o.orderpriority, 
    SUM(o.totalprice) AS total_revenue
FROM tpch.tiny.orders AS o
JOIN tpch.tiny.customer AS c ON o.custkey = c.custkey
GROUP BY CUBE (c.mktsegment, o.orderpriority);

Chỉ với một dòng CUBE ngắn gọn, Trino đã quét bảng 1 lần và trả về cho bạn báo cáo gồm:

• Doanh thu chi tiết cho từng cặp (Phân khúc + Độ ưu tiên).

• Tổng doanh thu riêng cho từng Phân khúc (bất chấp độ ưu tiên).

• Tổng doanh thu riêng cho từng Độ ưu tiên (bất chấp phân khúc).

• Tổng doanh thu toàn cục (Grand Total).

3. Hàm GROUPING(): Nhận diện "NULL xịn" và "NULL pha-ke"

Ở Bài 19, chúng ta đã dùng COALESCE để đổi các giá trị NULL thành chữ "Tất cả". Tuy nhiên, sẽ có một lỗ hổng logic xảy ra: Nếu bản thân dữ liệu gốc của bạn đã bị NULL từ trước thì sao?

Làm thế nào để phân biệt một ô là NULL do người dùng chưa nhập dữ liệu, hay NULL do ROLLUP/CUBE sinh ra đại diện cho hàm ý "Tất cả"? Trino cung cấp hàm GROUPING(cột) để kiểm chứng điều này.

• GROUPING(cột) = 0: Cột này đang chứa dữ liệu thật (kể cả NULL gốc của dữ liệu).

• GROUPING(cột) = 1: Cột này bị NULL do thuộc dòng Subtotal/Grand Total của ROLLUP/CUBE.

Ứng dụng "làm sạch" tuyệt đối:

SQL

SELECT 
    CASE WHEN GROUPING(c.mktsegment) = 1 THEN 'Tất cả phân khúc' 
         ELSE COALESCE(c.mktsegment, 'Không xác định') 
    END AS segment_level,

    SUM(o.totalprice) AS total_revenue
FROM tpch.tiny.orders AS o
JOIN tpch.tiny.customer AS c ON o.custkey = c.custkey
GROUP BY ROLLUP (c.mktsegment);

4. Góc nhìn Kỹ sư: Cạm bẫy "Bùng nổ tổ hợp" (Combinatorial Explosion)

Khi bạn đẩy SQL xuống một hệ thống phân tán, sự tiện lợi thường đi kèm với cái giá rất đắt về tài nguyên (Memory và CPU).

Trong khi ROLLUP (A, B, C, D, E) chỉ sinh ra $5 + 1 = 6$ tổ hợp, thì CUBE (A, B, C, D, E) sẽ sinh ra $2^5 = 32$ tổ hợp.

Nghĩa là engine của Trino phải mở 32 bộ đệm bộ nhớ (memory buffers) trong RAM của các Worker để cộng dồn số liệu song song. Nếu bạn vô tình ném 10 cột vào CUBE ($2^{10} = 1024$ tổ hợp), truy vấn của bạn sẽ ngay lập tức gây ra hiện tượng Query Exceeded Memory Limit, có thể làm treo (OOM) hàng loạt Worker Node và đánh sập cụm Trino của toàn công ty.

Best Practice:

• Dùng CUBE cho tối đa 3-4 chiều phân tích (Dimensions) có số lượng giá trị duy nhất (Cardinality) thấp.

• Luôn sử dụng WHERE để giới hạn tập dữ liệu trước khi đưa vào CUBE (ví dụ: chỉ phân tích dữ liệu 1 tuần gần nhất thay vì toàn thời gian).