Trino SQL Bài học 38: Kỹ thuật xoay trục dữ liệu (Pivot và Unpivot) trong Trino

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

• Phân biệt cấu trúc dữ liệu dạng Dài (Long Format) và dạng Rộng (Wide Format).

• Hiểu lý do tại sao Trino không hỗ trợ từ khóa PIVOT / UNPIVOT nguyên bản và cách giải quyết chuẩn ANSI SQL.

• Làm chủ kỹ thuật Tổng hợp có điều kiện (Conditional Aggregation) để biến Dòng thành Cột (Pivot).

• Vận dụng kỹ thuật ghép mảng CROSS JOIN UNNEST siêu tốc để biến Cột thành Dòng (Unpivot).

1. Dạng Dài (Long) vs Dạng Rộng (Wide)

Trước khi xoay trục, bạn cần hiểu mục đích của hai định dạng dữ liệu này:

• Long Format (Dạng dài): Là định dạng chuẩn mực của Cơ sở dữ liệu (Normalized). Số lượng cột rất ít, số lượng dòng rất nhiều. Máy tính rất thích đọc dạng này (để GROUP BY, JOIN), nhưng con người đọc rất khó nhìn.

• Wide Format (Dạng rộng): Là định dạng báo cáo (Cross-tab / Pivot Table). Số lượng dòng ít, số lượng cột trải dài sang ngang (Ví dụ: Doanh thu chia thành 12 cột cho 12 tháng). Con người và các sếp rất thích đọc dạng này.

Nhiệm vụ của Data Engineer: Thường xuyên phải chuyển đổi dữ liệu từ Long sang Wide để phục vụ Dashboard (Pivot), và đôi khi phải làm ngược lại từ Wide về Long để đưa dữ liệu rác vào chuẩn CSDL (Unpivot).

2. PIVOT: Biến Dòng thành Cột (Row to Column)

Sự thật về Trino: Khác với SQL Server hay Snowflake, Trino KHÔNG CÓ từ khóa PIVOT. Trino tuân thủ chặt chẽ chuẩn ANSI SQL, do đó để xoay trục, bạn sẽ dùng kỹ thuật Tổng hợp có điều kiện (Conditional Aggregation) mà chúng ta đã nhắc sơ qua ở Bài 16.

Kỹ thuật này sử dụng SUM hoặc COUNT kết hợp với mệnh đề rẽ nhánh CASE WHEN.

Bài toán: Bạn có bảng orders (dạng Long) chứa doanh thu của các khách hàng với nhiều trạng thái khác nhau (O, F, P). Sếp muốn xem một báo cáo tổng quan: Mỗi khách hàng là 1 dòng, và có 3 cột tương ứng thể hiện doanh thu cho 3 trạng thái.

SQL

-- Dữ liệu gốc (Long Format): custkey | orderstatus | totalprice
-- Mục tiêu (Wide Format):    custkey | open_rev | finished_rev | pending_rev

SELECT 
    custkey,
    -- Nếu trạng thái là 'O' thì lấy tiền để cộng, ngược lại cộng 0 (Bỏ qua)
    SUM(CASE WHEN orderstatus = 'O' THEN totalprice ELSE 0 END) AS open_rev,
    SUM(CASE WHEN orderstatus = 'F' THEN totalprice ELSE 0 END) AS finished_rev,
    SUM(CASE WHEN orderstatus = 'P' THEN totalprice ELSE 0 END) AS pending_rev
FROM tpch.tiny.orders
GROUP BY custkey
ORDER BY custkey;

Tại sao cách này tối ưu? Trino Engine chỉ cần quét bảng orders đúng một vòng duy nhất (Single Pass). Khi đọc đến mỗi dòng, nó sẽ dùng CASE WHEN để quyết định xem số tiền này sẽ được "thả" vào cái xô (bucket) nào trong 3 xô open_rev, finished_rev, pending_rev. Hiệu năng cao hơn rất nhiều so với việc dùng nhiều lệnh JOIN.

3. UNPIVOT: Biến Cột thành Dòng (Column to Row)

Tương tự, Trino cũng KHÔNG CÓ từ khóa UNPIVOT.

Bài toán: Bộ phận Marketing gửi cho bạn một file Excel dữ liệu ngân sách theo dạng Wide Format (mỗi quý là một cột). Bạn cần đưa nó vào CSDL thành dạng Long Format (Cột Quarter, Cột Ngân sách).

Cách 1: Dùng UNION ALL (Cổ điển, Chậm)

Cách truyền thống là viết 4 câu SELECT cho 4 quý rồi dán chúng lại với nhau. Nhược điểm: Trino phải đọc bảng dữ liệu gốc tới 4 lần, cực kỳ tốn I/O nếu bảng lớn.

Cách 2: Dùng CROSS JOIN UNNEST (Tuyệt kỹ Trino - Nhanh như chớp)

Nhớ lại Bài 31, UNNEST có khả năng bẻ gãy mảng thành các dòng. Đỉnh cao của thủ thuật Unpivot trong Trino là gom các cột lại thành Mảng (ARRAY), sau đó dùng UNNEST để bung nó ra. Trino chỉ đọc bảng gốc đúng 1 lần!

SQL

WITH wide_budget AS (
    -- Giả lập dữ liệu Wide Format
    SELECT 'MKT-01' AS dept_id, 1000 AS q1_budget, 1500 AS q2_budget, 1200 AS q3_budget, 2000 AS q4_budget
)
SELECT 
    w.dept_id,
    u.quarter_name,
    u.budget_amount
FROM wide_budget AS w
-- Tuyệt kỹ Unpivot:
-- Mảng 1: Hard-code tên các cột thành chuỗi
-- Mảng 2: Truyền trực tiếp các cột dữ liệu vào
CROSS JOIN UNNEST(
    ARRAY['Q1', 'Q2', 'Q3', 'Q4'],
    ARRAY[w.q1_budget, w.q2_budget, w.q3_budget, w.q4_budget]
) AS u(quarter_name, budget_amount);

Kết quả trả về: Dữ liệu tự động bẻ thành 4 dòng cho phòng Marketing.

4. Góc nhìn Kỹ sư: Khi nào nên Pivot?

Là một Data Engineer, bạn cần thiết lập ranh giới rõ ràng với bộ phận BI (Business Intelligence - Những người tạo Dashboard) hoặc Data Analyst:

1. Trách nhiệm của Engine (Trino): Việc biến Cột thành Dòng (Unpivot) để chuẩn hóa dữ liệu, làm sạch, và nối bảng (JOIN) là trách nhiệm của Data Engineer viết trên Trino. Trino làm việc với Long Format rất nhanh.

2. Trách nhiệm của BI Tool (Tableau, PowerBI, Superset): Việc biến Dòng thành Cột (Pivot) để hiển thị báo cáo nên được nhường lại cho các công cụ BI. Trừ khi hệ thống BI của bạn quá yếu, bạn mới phải dùng SQL CASE WHEN để Pivot sẵn data dưới tầng Data Warehouse. Lý do là Pivot bằng SQL sẽ làm "cứng hóa" (hard-code) các cột, mỗi khi công ty có thêm một phân khúc mới, bạn lại phải sửa code SQL để thêm dòng SUM(CASE WHEN...). Nếu đẩy lên BI Tool, nó sẽ tự động nhận diện và vẽ ra biểu đồ động linh hoạt hơn.