Trino SQL Bài học 19: Phân tích đa chiều với GROUPING SETS

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

• Hiểu được bài toán hiệu năng khi tạo các báo cáo tổng hợp (Grand Total, Subtotal) bằng phương pháp truyền thống.

• Nắm vững cú pháp GROUPING SETS để tính toán nhiều cấp độ gom nhóm chỉ trong một lần quét dữ liệu.

• Kết hợp COALESCE để "làm đẹp" các giá trị NULL sinh ra từ quá trình phân tích đa chiều, chuẩn bị dữ liệu hoàn hảo cho các công cụ BI.

1. Nỗi đau của Báo cáo tổng hợp (UNION ALL)

Khi làm việc với các công cụ Business Intelligence (BI) hoặc tạo báo cáo tài chính, yêu cầu kinh điển nhất là một bảng dữ liệu phải chứa cả chi tiết và tổng cộng.

Giả sử Giám đốc yêu cầu bạn xuất một báo cáo doanh thu gồm 3 phần:

1. Doanh thu theo từng Phân khúc khách hàng (mktsegment).

2. Doanh thu theo từng Trạng thái đơn hàng (orderstatus).

3. Tổng doanh thu toàn công ty (Grand Total).

Nếu chỉ dùng kiến thức ở Bài 17, bạn sẽ phải viết 3 câu lệnh GROUP BY riêng biệt và ghép chúng lại bằng UNION ALL:

SQL

-- Cách viết truyền thống (Anti-pattern trong Big Data)
SELECT mktsegment, NULL AS orderstatus, SUM(totalprice) FROM table GROUP BY mktsegment
UNION ALL
SELECT NULL, orderstatus, SUM(totalprice) FROM table GROUP BY orderstatus
UNION ALL
SELECT NULL, NULL, SUM(totalprice) FROM table;

Vấn đề chết người: Trino sẽ phải quét (scan) bảng dữ liệu 3 lần. Nếu bảng có 10 tỷ dòng, hệ thống phải đọc 30 tỷ dòng, gây lãng phí tài nguyên I/O và CPU khổng lồ, làm chậm hệ thống của toàn công ty.

2. Cứu cánh mang tên GROUPING SETS

GROUPING SETS là một tiện ích mở rộng của mệnh đề GROUP BY chuẩn ANSI SQL. Nó cho phép bạn định nghĩa nhiều "tập hợp gom nhóm" khác nhau ngay trong một câu truy vấn duy nhất.

Trino Engine đủ thông minh để nhận diện cú pháp này, nó sẽ chỉ quét bảng dữ liệu một lần duy nhất, lưu vào bộ nhớ (RAM) và tính toán đồng thời cho tất cả các tập hợp bạn yêu cầu.

Cú pháp cơ bản:

SQL

GROUP BY GROUPING SETS (
    (cột_A, cột_B), -- Tập hợp 1: Gom nhóm theo cả 2 chiều
    (cột_A),        -- Tập hợp 2: Chỉ gom nhóm theo chiều A
    (cột_B),        -- Tập hợp 3: Chỉ gom nhóm theo chiều B
    ()              -- Tập hợp 4: Không gom nhóm (Tương đương Grand Total)
)

3. Thực hành phân tích đa chiều

Chúng ta sẽ giải quyết bài toán của Giám đốc ở mục 1 bằng một truy vấn tối ưu và thanh lịch hơn rất nhiều:

SQL

SELECT 
    c.mktsegment, 
    o.orderstatus, 
    SUM(o.totalprice) AS revenue
FROM tpch.tiny.orders AS o
JOIN tpch.tiny.customer AS c ON o.custkey = c.custkey
GROUP BY GROUPING SETS (
    (c.mktsegment),    -- Subtotal theo Phân khúc
    (o.orderstatus),   -- Subtotal theo Trạng thái
    ()                 -- Grand Total
);

Phân tích kết quả trả về:

• Đối với các dòng là kết quả của tập hợp (c.mktsegment), cột orderstatus sẽ tự động mang giá trị NULL.

• Ngược lại, đối với các dòng của tập hợp (o.orderstatus), cột mktsegment sẽ bị NULL.

• Đối với dòng Grand Total (), cả hai cột mktsegment và orderstatus đều là NULL.

4. "Làm đẹp" báo cáo với COALESCE

Các giá trị NULL sinh ra từ GROUPING SETS mang ý nghĩa logic là "Tất cả" (All), nhưng hiển thị chữ NULL lên Dashboard sẽ khiến người dùng cuối bối rối.

Đây là lúc kiến thức ở Bài 10 (COALESCE) phát huy tác dụng rực rỡ nhất: Đón lõng các giá trị NULL do GROUPING SETS tạo ra và dán nhãn lại cho chúng.

SQL

SELECT 
    COALESCE(c.mktsegment, 'Tất cả phân khúc') AS segment_level, 
    COALESCE(o.orderstatus, 'Tất cả trạng thái') AS status_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 GROUPING SETS (
    (c.mktsegment),
    (o.orderstatus),
    ()
)
ORDER BY total_revenue DESC;

Bằng câu truy vấn này, bạn đã cung cấp cho công cụ BI (như Metabase hay Superset) một bảng dữ liệu sạch sẽ, có đầy đủ các cấp độ tổng quan và chi tiết, mà hệ thống Trino dưới nền chỉ tốn đúng 1 nhịp đọc I/O.

Sự linh hoạt của GROUPING SETS là không thể phủ nhận, nhưng đôi khi việc phải liệt kê thủ công từng tổ hợp có thể gây mỏi tay nếu bạn có tới 4, 5 chiều phân tích. Trino cung cấp hai lối tắt (shortcut) mạnh mẽ hơn dựa trên nền tảng của GROUPING SETS để tự động hóa việc này.