Thiết lập window frame với ROWS và RANGE

Khi dùng window functions, sẽ có câu hỏi: "Có bao nhiêu dòng trong cửa sổ sẽ tham gia tính giá trị cho dòng hiện tại?" Câu trả lời phụ thuộc vào window frame.

Window frame — là phạm vi các dòng được dùng để tính kết quả cho window function. Phạm vi này được xây dựng dựa trên dòng hiện tại và các điều kiện bổ sung được chỉ định qua ROWS hoặc RANGE.

Ví dụ đơn giản: khi tính tổng tích lũy, bạn có thể chỉ định:

• Chỉ tính dòng hiện tại.
• Tính dòng hiện tại và tất cả các dòng phía trên.
• Tính dòng hiện tại và một số dòng cố định phía trên/dưới.

Chính ROWS và RANGE sẽ kiểm soát dòng nào sẽ vào window frame.

Sử dụng ROWS

ROWS xác định window frame ở mức vị trí vật lý của dòng. Nghĩa là nó đếm dòng từ trên xuống theo thứ tự, bất kể giá trị trong các dòng đó là gì.

Cú pháp

window_function OVER (
    ORDER BY cột
    ROWS BETWEEN bắt_đầu AND kết_thúc
)

Các biểu thức chính:

• CURRENT ROW — dòng hiện tại.
• số PRECEDING — số dòng phía trên dòng hiện tại.
• số FOLLOWING — số dòng phía dưới dòng hiện tại.
• UNBOUNDED PRECEDING — từ đầu cửa sổ.
• UNBOUNDED FOLLOWING — đến cuối cửa sổ.

Ví dụ: tổng tích lũy cho dòng hiện tại và 2 dòng trước đó

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

Giải thích:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW nghĩa là: lấy dòng hiện tại và hai dòng phía trên nó.
• Tổng tích lũy sẽ chỉ tính cho ba dòng này.

Kết quả:

Ví dụ: phân tích "cửa sổ trượt" với số dòng cố định

Bài toán: tính lương trung bình cho dòng hiện tại và hai dòng tiếp theo.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Kết quả:

Sử dụng RANGE

RANGE xây dựng window frame dựa trên giá trị, không phải vị trí dòng. Nghĩa là, dòng sẽ được đưa vào frame nếu giá trị trong cột ORDER BY nằm trong phạm vi chỉ định.

Cú pháp

window_function OVER (
    ORDER BY cột
    RANGE BETWEEN bắt_đầu AND kết_thúc
)

Ví dụ: tổng tích lũy theo phạm vi giá trị

Bài toán: tính tổng tích lũy cho các dòng mà lương khác dòng hiện tại không quá 2000.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

Giải thích:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING nghĩa là: lấy các dòng mà giá trị salary nằm trong khoảng ±2000 so với dòng hiện tại.

Kết quả:

So sánh ROWS và RANGE

• ROWS làm việc với dòng thực tế và số lượng dòng. Nó không phụ thuộc vào giá trị.
• RANGE làm việc với phạm vi logic của giá trị, được chỉ định cho cột trong ORDER BY.

Để so sánh, xem ví dụ sau. Giả sử có bảng sales với dữ liệu:

So sánh các truy vấn:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Kết quả:

Ở đây mỗi dòng được cộng vào tổng theo thứ tự xuất hiện thực tế.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Kết quả:

Ở đây dòng 1 và 2 được gộp lại vì amount = 100. RANGE tính cả giá trị lặp lại trong cột amount.

Ví dụ bài toán thực tế

1. Tính tăng trưởng doanh thu

Bài toán: tính sự thay đổi doanh thu so với dòng trước đó.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. So sánh dòng hiện tại với trung bình trong nhóm

Bài toán: với mỗi phòng ban, tính chênh lệch lương của nhân viên so với lương trung bình của phòng ban đó.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

Lỗi khi dùng ROWS và RANGE

Chỉ định sai thứ tự dòng (ORDER BY): Nếu không chỉ định thứ tự sắp xếp, PostgreSQL sẽ báo lỗi vì không xác định được dòng hiện tại.

Kết hợp ROWS và RANGE trong cùng một bài toán: Chọn cách tiếp cận phù hợp với dữ liệu của bạn. ROWS hợp cho bài toán với số dòng cố định, còn RANGE — cho phạm vi giá trị.

Bỏ qua giá trị lặp lại trong RANGE: Nhớ rằng RANGE tính cả các giá trị lặp lại, điều này có thể làm thay đổi kết quả đáng kể.