Trino SQL Bài học 26: Khái niệm Window Functions và mệnh đề OVER()

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

• Phá vỡ giới hạn của GROUP BY (sự nén dòng dữ liệu).

• Nắm vững khái niệm "Cửa sổ dữ liệu" (Window) và cách nó hoạt động ngầm.

• Làm chủ cú pháp cốt lõi OVER() với các thành phần PARTITION BY và ORDER BY.

• Ứng dụng để tính toán các chỉ số kinh doanh kinh điển như Doanh thu cộng dồn (Running Total) hay Tỷ trọng (Percentage of Total).

1. Sự hạn chế của GROUP BY và Lối thoát Window Functions

Khi làm báo cáo, bạn thường xuyên gặp phải một yêu cầu "oái oăm": Giám đốc muốn xem danh sách chi tiết từng đơn hàng, nhưng bên cạnh mỗi đơn hàng, phải hiển thị thêm tổng doanh thu của toàn bộ tháng đó để xem đơn hàng này chiếm bao nhiêu phần trăm.

Nếu bạn dùng GROUP BY (Bài 17), dữ liệu chi tiết của từng đơn hàng sẽ bị "nén" lại thành một dòng duy nhất cho mỗi tháng. Bạn mất đi chi tiết. Nếu bạn không dùng GROUP BY, bạn không thể tính được tổng doanh thu tháng. Trước đây, để làm được việc này, bạn phải viết 2 câu query rồi JOIN chúng lại với nhau rất cồng kềnh.

Window Functions (Hàm cửa sổ) ra đời để giải quyết chính xác bài toán này. Nó cho phép bạn thực hiện các phép tính tổng hợp (như SUM, AVG, COUNT) trên một tập hợp dòng dữ liệu (gọi là "Cửa sổ"), nhưng TUYỆT ĐỐI KHÔNG NÉN các dòng đó lại. Số dòng dữ liệu đầu vào bao nhiêu, số dòng trả về bấy nhiêu.

2. Cú pháp lõi: Mệnh đề OVER()

Để biến một hàm tổng hợp bình thường thành một Window Function, bạn chỉ cần đặt thêm mệnh đề OVER() ngay phía sau nó.

SQL

-- Tính tổng doanh thu toàn công ty và gắn vào bên cạnh từng đơn hàng chi tiết
SELECT 
    orderkey, 
    totalprice,
    SUM(totalprice) OVER () AS grand_total -- Hàm cửa sổ trống
FROM tpch.tiny.orders
LIMIT 5;

Khi mệnh đề OVER () để trống, Trino sẽ coi "Cửa sổ" chính là toàn bộ dữ liệu của bảng. Kết quả trả về sẽ có 5 dòng chi tiết, và cột grand_total của cả 5 dòng đều mang chung một con số khổng lồ (tổng của cả công ty).

3. Chia nhỏ cửa sổ với PARTITION BY

Thực tế, hiếm khi chúng ta mở một cửa sổ lớn bằng cả cái bảng. Chúng ta muốn chia nhỏ dữ liệu ra thành các khung cửa sổ riêng biệt. PARTITION BY hoạt động hệt như GROUP BY, nhưng nó không làm mất dòng.

Ví dụ: Tính tổng doanh thu theo từng trạng thái đơn hàng (orderstatus), hiển thị cạnh từng mã đơn.

SQL

SELECT 
    orderkey, 
    orderstatus, 
    totalprice,
    -- Cửa sổ giờ đây được giới hạn trong phạm vi của từng orderstatus
    SUM(totalprice) OVER (PARTITION BY orderstatus) AS status_total
FROM tpch.tiny.orders
ORDER BY orderstatus;

Cách Trino xử lý: Khi đọc đến các đơn hàng có trạng thái 'O', cửa sổ sẽ chỉ bao gồm các đơn hàng 'O', và cột status_total sẽ là tổng của 'O'. Khi quét đến trạng thái 'F', khung cửa sổ trượt sang tập dữ liệu 'F', và tính tổng mới cho 'F'.

Ứng dụng - Tính tỷ trọng (Percentage of Total):

SQL

SELECT 
    orderkey, 
    orderstatus, 
    totalprice,
    totalprice / SUM(totalprice) OVER (PARTITION BY orderstatus) AS pct_of_status
FROM tpch.tiny.orders;

4. Tạo dòng chảy dữ liệu với ORDER BY

Bên trong mệnh đề OVER(), ngoài PARTITION BY, bạn có thể chỉ định thêm ORDER BY.

Cảnh báo: ORDER BY bên trong OVER() hoàn toàn khác biệt với ORDER BY ở cuối câu truy vấn. ORDER BY ở cuối dùng để sắp xếp kết quả hiển thị ra màn hình. Còn ORDER BY bên trong OVER() dùng để định hình dòng chảy tính toán bên trong khung cửa sổ.

Khi bạn kết hợp hàm SUM với ORDER BY bên trong một Window Function, nó sẽ thay đổi bản chất từ "Tổng cố định" thành "Tổng cộng dồn" (Running Total).

Ví dụ: Tính doanh thu cộng dồn qua từng ngày (Lũy kế).

SQL

SELECT 
    orderkey, 
    orderdate, 
    totalprice,
    -- Tính tổng cộng dồn: Cộng dần totalprice theo thứ tự ngày tăng dần
    SUM(totalprice) OVER (ORDER BY orderdate ASC) AS running_total
FROM tpch.tiny.orders
WHERE orderdate BETWEEN DATE '1995-01-01' AND DATE '1995-01-31';

Kết quả ở dòng 1 sẽ là doanh thu ngày 1. Dòng 2 sẽ là (ngày 1 + ngày 2). Dòng 3 sẽ là (ngày 1 + ngày 2 + ngày 3).

5. Kết hợp toàn diện: PARTITION BY và ORDER BY

Bạn có thể dùng cả hai trong cùng một mệnh đề OVER() để tạo ra các báo cáo lũy kế cực kỳ mạnh mẽ.

Ví dụ: Tính doanh thu cộng dồn qua từng ngày, nhưng reset (làm mới) lại từ đầu đối với mỗi trạng thái đơn hàng khác nhau.

SQL

SELECT 
    orderstatus,
    orderkey, 
    orderdate, 
    totalprice,
    SUM(totalprice) OVER (
        PARTITION BY orderstatus   -- Tạo vách ngăn giữa các trạng thái
        ORDER BY orderdate ASC     -- Cộng dồn theo ngày trong từng vách ngăn đó
    ) AS status_running_total
FROM tpch.tiny.orders;

6. Góc nhìn Kỹ sư: Thứ tự thực thi của Window Functions

Window Functions là những hàm xử lý được kích hoạt GẦN NHƯ CUỐI CÙNG trong quy trình SQL. Cụ thể, nó chạy SAU khi WHERE, GROUP BY và HAVING đã hoàn tất, và chỉ đứng TRƯỚC mệnh đề ORDER BY toàn cục.

Hệ quả nghiêm trọng: Bạn KHÔNG THỂ đặt Window Functions vào mệnh đề WHERE để lọc. Ví dụ: Bạn không thể viết WHERE SUM(totalprice) OVER () > 1000. Trino sẽ báo lỗi ngay lập tức. Để lọc dựa trên kết quả của Window Functions, bạn bắt buộc phải bọc nó vào một truy vấn con (Subquery) hoặc CTE (Bài 38).