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

Trino SQL Bài học 29: Window Functions: Định nghĩa khung tính toán (Window Frames - ROWS, RANGE)

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

Hiểu được cơ chế "Cửa sổ trượt" (Sliding Window) bên trong một phân vùng (Partition).
Phân biệt sự khác biệt cốt lõi giữa giới hạn vật lý (ROWS) và giới hạn logic (RANGE).
Nắm vững các từ khóa định biên: UNBOUNDED PRECEDING, CURRENT ROW, FOLLOWING.
Ứng dụng thiết kế biểu đồ Trung bình động (Moving Average) và phân tích xu hướng ngắn hạn.

1. Khái niệm Cửa sổ trượt (Sliding Window)

Ở Bài 26, chúng ta đã dùng PARTITION BY để chia dữ liệu thành các Vách ngăn lớn, và dùng ORDER BY để tạo dòng chảy cộng dồn bên trong vách ngăn đó.

Tuy nhiên, nếu bạn muốn tính "Trung bình doanh thu của 3 ngày gần nhất" (Ngày hôm qua, Ngày hôm nay, Ngày ngày mai) thay vì cộng dồn từ lúc khai trương công ty đến nay thì sao?

Bạn cần một cái "khung nhỏ" trượt dọc theo dòng chảy dữ liệu. Khung này chỉ bao quanh 3 dòng xung quanh con trỏ hiện tại. Đó chính là Window Frame (Khung tính toán).

2. Cú pháp định nghĩa Window Frame

Mệnh đề khai báo khung được đặt ở cuối cùng bên trong OVER(), ngay sau ORDER BY.

Cú pháp tổng quát:

SQL

OVER (
    [PARTITION BY ...] 
    ORDER BY ...
    [ROWS hoặc RANGE] BETWEEN [Điểm_Bắt_Đầu] AND [Điểm_Kết_Thúc]
)

Các từ khóa xác định Điểm bắt đầu / Điểm kết thúc:

UNBOUNDED PRECEDING: Vô cực về phía trước (Từ dòng đầu tiên của Partition).
[N] PRECEDING: Lùi về trước N dòng (hoặc đơn vị).
CURRENT ROW: Dòng hiện tại.
[N] FOLLOWING: Tiến về sau N dòng (hoặc đơn vị).
UNBOUNDED FOLLOWING: Vô cực về phía sau (Tới dòng cuối cùng của Partition).

Ví dụ: Cấu hình một khung trượt lấy 1 dòng trước, dòng hiện tại, và 1 dòng sau.

SQL

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

3. Cạm bẫy "Tàng hình" của Default Frame

Điều quan trọng nhất mà mọi Kỹ sư Dữ liệu phải biết: Nếu bạn viết ORDER BY trong hàm cửa sổ mà không khai báo Window Frame, Trino sẽ âm thầm gán một khung mặc định (Default Frame).

Khung mặc định đó là:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

(Từ dòng đầu tiên đến dòng hiện tại).

Đây chính là lý do tại sao ở Bài 26, hàm SUM(totalprice) OVER (ORDER BY orderdate) lại tạo ra tính năng Cộng dồn (Running Total). Nó cộng từ đầu đến dòng hiện tại, rồi trượt sang dòng tiếp theo và lại cộng từ đầu đến dòng đó.

4. ROWS vs. RANGE: Sự khác biệt sống còn

Trino cung cấp hai đơn vị đo lường cho khung trượt: ROWS (Dòng vật lý) và RANGE (Dải logic).

A. ROWS (Đếm số dòng vật lý)

ROWS không quan tâm giá trị dữ liệu là gì, nó chỉ đếm đúng số lượng dòng bên trên và bên dưới.

Nếu bạn nói ROWS BETWEEN 1 PRECEDING AND CURRENT ROW, nó sẽ lấy chính xác dòng ngay trên nó.

B. RANGE (Gộp các giá trị bằng nhau)

RANGE làm việc dựa trên Giá trị của cột được ORDER BY.

Nếu có nhiều dòng có giá trị ORDER BY giống hệt nhau (Hòa / Ties), RANGE sẽ gộp tất cả các dòng đó lại và coi như chúng đang đứng cùng một vị trí.

Hệ quả: Nếu bạn có 5 đơn hàng đặt trong cùng 1 ngày, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW sẽ cộng gộp cả 5 đơn hàng đó lại trước khi hiển thị tổng, thay vì cộng dồn từng cái một. Muốn cộng dồn từng đơn, bạn phải ép nó dùng ROWS.

SQL

-- So sánh sự khác biệt khi có đơn hàng trùng ngày (orderdate)
SELECT 
    orderkey, 
    orderdate, 
    totalprice,
    SUM(totalprice) OVER (
        ORDER BY orderdate ASC 
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_by_range, -- Nhảy vọt tổng theo từng cụm ngày
    
    SUM(totalprice) OVER (
        ORDER BY orderdate ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_by_rows   -- Cộng dồn chi tiết từng dòng một
FROM tpch.tiny.orders
WHERE orderdate BETWEEN DATE '1995-01-01' AND DATE '1995-01-05';

5. Ứng dụng thực tế: Tính Đường Trung Bình Động (Moving Average)

Trong phân tích chứng khoán hoặc doanh số bán lẻ, dữ liệu thường biến động rất mạnh theo ngày. Người ta dùng Đường trung bình động (MA - Moving Average) để làm mượt biểu đồ, giúp nhìn rõ xu hướng.

Yêu cầu: Tính trung bình doanh thu của "7 đơn hàng gần nhất" (Gồm 6 đơn liền trước và đơn hiện tại).

SQL

SELECT 
    orderkey, 
    orderdate, 
    totalprice,
    AVG(totalprice) OVER (
        ORDER BY orderdate ASC, orderkey ASC
        -- Định nghĩa khung trượt gồm 6 dòng trước và dòng hiện tại
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7_orders
FROM tpch.tiny.orders
WHERE orderdate >= DATE '1995-01-01'
LIMIT 20;

(Lưu ý thuật toán: Ở dòng thứ nhất, khung trượt chỉ có 1 dòng, nên AVG là chính nó. Ở dòng thứ hai, khung trượt có 2 dòng. Bắt đầu từ dòng thứ 7 trở đi, khung trượt mới đạt đủ kích thước chuẩn 7 dòng. Việc thêm orderkey ASC vào ORDER BY giúp Trino sắp xếp các đơn hàng trùng ngày một cách dứt khoát).*

Các Câu Lệnh Trong MS SQL Server