Giới thiệu về Window Functions

Window function cho phép bạn thực hiện các phép tính trên một tập hợp các dòng có liên quan đến dòng hiện tại, mà không gộp các dòng đó thành một kết quả duy nhất như GROUP BY. Nhờ vậy, bạn vẫn giữ được chi tiết ở cấp dòng trong khi vẫn có được các giá trị tổng hợp, xếp hạng và so sánh ở ngay cùng dòng. Các use case phổ biến gồm xếp hạng các sản phẩm bán chạy nhất theo khu vực, tính tổng lũy tiến cho báo cáo tài chính và tính trung bình động theo thời gian. Cú pháp cốt lõi kết hợp một aggregate function với mệnh đề OVER để định nghĩa cách "cửa sổ" các dòng được phân chia.

Cú pháp cơ bản và các hàm cốt lõi

Một lời gọi window function trông giống một aggregate thông thường nhưng có thêm mệnh đề OVER. Bên trong OVER, bạn định nghĩa PARTITION BY (cách nhóm các dòng thành cửa sổ), ORDER BY (cách sắp xếp trong mỗi cửa sổ) và tùy chọn một frame clause như ROWS BETWEEN. Các window function hay dùng nhất gồm ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG và COUNT — mỗi hàm hoạt động như một aggregate function hoặc navigation function tùy ngữ cảnh.

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

ROW_NUMBER gán một số thứ tự duy nhất trong mỗi partition, bắt đầu lại từ 1 khi partition thay đổi. RANK và DENSE_RANK hoạt động tương tự nhưng gán cùng số cho các giá trị bằng nhau và khác nhau ở cách xử lý khoảng trống sau các giá trị bằng nhau.

Xếp hạng và truy vấn Top-N

Xếp hạng là một trong những ứng dụng thực tế nhất. Giả sử bạn muốn lấy 3 nhân viên có mức lương cao nhất trong mỗi phòng ban. Bạn tính một cột xếp hạng rồi lọc theo cột đó, có thể dùng subquery hoặc CTE để rõ ràng hơn. Pattern này hiệu quả hơn nhiều so với correlated subquery và đọc rất tự nhiên trong code ứng dụng.

WITH ranked AS (
    SELECT
        employee_id,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT *
FROM ranked
WHERE rk <= 3
ORDER BY department, rk;

Lưu ý rằng PARTITION BY department sẽ khởi động lại phép xếp hạng cho mỗi phòng ban, nên bạn luôn lấy đúng 3 người cao nhất trên mỗi nhóm ngay cả khi có mức lương trùng nhau.

Tổng tích lũy và khung trượt

Tổng tích lũy và trung bình trượt đòi hỏi mệnh đề frame bên trong OVER. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW cho bạn tổng tích lũy tăng dần từ đầu partition đến hàng hiện tại. Chuyển sang khung cố định như ROWS BETWEEN 6 PRECEDING AND CURRENT ROW tạo ra trung bình trượt 7 hàng, rất phổ biến trong phân tích chuỗi thời gian.

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY order_date;

Cột đầu tiên tăng đơn điệu, trong khi trung bình trượt làm phẳng các đột biến hàng ngày để hiện lên xu hướng nền. Định nghĩa frame có thể dùng ROWS (đếm vật lý) hoặc RANGE (logic, dựa trên giá trị), nhưng ROWS nhanh hơn và dễ dự đoán hơn cho hầu hết nhu cầu báo cáo.

Lỗi thường gặp và mẹo hiệu năng

Một sai lầm phổ biến là quên rằng window functions chạy sau WHERE, HAVING và GROUP BY — vì vậy chúng không thể được lọc trực tiếp trong cùng truy vấn mà không có CTE hoặc subquery. Một cạm bẫy khác là trộn window functions với SELECT DISTINCT hoặc tổng hợp tập kết quả theo cách khiến bộ lập kế hoạch bối rối. Để có hiệu năng tốt, hãy luôn index các cột dùng trong PARTITION BY và ORDER BY, và ưu tiên ROWS thay vì RANGE khi bạn không cần frame dựa trên giá trị. Tránh đặt window functions trong mệnh đề WHERE bằng cách bọc truy vấn trong một CTE trước.

Tóm tắt

Window functions trong PostgreSQL là công cụ phù hợp mỗi khi bạn cần chi tiết theo hàng cùng với các phép tính đã nhóm. Mệnh đề OVER với PARTITION BY và ORDER BY cho bạn toàn quyền kiểm soát cách window được định nghĩa, và mệnh đề frame mở rộng khả năng kiểm soát đó cho các phép tính trượt. Thành thạo ranking, tổng tích lũy và trung bình trượt bao phủ khoảng 80% truy vấn phân tích thực tế mà bạn sẽ viết với vai trò backend developer.