Tính tổng tích lũy với hàm cửa sổ: SUM(), AVG()

Giả sử nhé: bạn đang theo dõi doanh thu công ty, doanh số shop online hoặc đơn giản là phân tích chi tiêu của bản thân trong năm. Bạn không chỉ muốn xem doanh thu hay chi phí từng tháng, mà còn muốn biết chúng tích lũy như thế nào qua từng tháng.

Những hàm tổng hợp thông thường (GROUP BY) không giúp được gì ở đây — chúng gom nhóm dữ liệu và trả về một dòng cho mỗi nhóm thôi. Nhưng nếu bạn muốn vừa xem từng tháng và vừa tính tổng tích lũy thì sao? Lúc này SUM() kết hợp hàm cửa sổ mới là chân ái.

Cơ bản về dùng hàm cửa sổ để tính tổng tích lũy

Hàm cửa sổ cho phép thực hiện các phép tổng hợp trên khung cửa sổ. Nhờ vậy, bạn có thể cộng giá trị trên từng dòng mà không làm mất các dòng khác. Không còn phải hy sinh vì GROUP BY nữa!

Cú pháp SUM() với hàm cửa sổ

Đây là mẫu cơ bản để tính tổng tích lũy:

SELECT
    column_name,
    SUM(column_name) OVER (PARTITION BY partition_column ORDER BY order_column) AS cumulative_sum
FROM 
    table_name;

Ở đây:

• SUM(column_name) — cộng các giá trị.
• OVER() — xác định cửa sổ để tính toán.
• PARTITION BY — chia dữ liệu thành nhóm (không bắt buộc).
• ORDER BY — xác định thứ tự dòng trong cửa sổ.

Ví dụ: tổng tích lũy doanh thu theo tháng

Giả sử bạn có bảng doanh thu như sau:

Bạn muốn xem doanh thu từng tháng và tổng tích lũy. Thử viết truy vấn SQL nhé:

SELECT
    tháng,
    doanh_thu,
    SUM(doanh_thu) OVER (ORDER BY tháng) AS tong_tich_luy
FROM 
    doanh_thu;

Kết quả:

Chuyện gì đang xảy ra:

1. ORDER BY tháng trong OVER() bảo PostgreSQL phải xét các dòng theo thứ tự thời gian.
2. Mỗi dòng sẽ tính tổng cộng dồn từ các dòng trước (và dòng hiện tại).

Hãy để ý kỹ nhé. Dòng đầu tiên SUM() chỉ cộng dòng đầu, dòng thứ hai cộng hai dòng, dòng thứ ba cộng ba dòng. Vì vậy thứ tự tháng rất quan trọng!

Ví dụ: tổng tích lũy doanh thu theo vùng

Nếu bạn có bảng doanh số theo vùng, một phần có thể như sau:

Bây giờ bạn muốn tính tổng tích lũy riêng cho từng vùng:

SELECT
    vung,
    tháng,
    doanh_thu,
    SUM(doanh_thu) OVER (PARTITION BY vung ORDER BY tháng) AS tong_tich_luy
FROM 
    ban_hang;

Kết quả sẽ như sau:

Bây giờ mỗi vùng được phân tích riêng (PARTITION BY vung), nhưng trong từng vùng thì dòng vẫn sắp xếp theo thời gian (ORDER BY tháng).

Trung bình trượt (AVG())

Ok, tổng tích lũy thì ngon rồi, nhưng nếu bạn muốn phân tích xu hướng, ví dụ trong 3 tháng gần nhất thì sao? Lúc này trung bình trượt là chuẩn bài.

Ví dụ: trung bình trượt doanh thu

Ta lại dùng bảng doanh_thu, dữ liệu như sau:

Truy vấn tính trung bình trượt 3 tháng:

SELECT
    tháng,
    doanh_thu,
    AVG(doanh_thu) OVER (
        ORDER BY tháng 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS trung_binh_truot
FROM 
    doanh_thu;

Kết quả:

Giải thích:

1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW bảo PostgreSQL xét dòng hiện tại và hai dòng trước để tính trung bình.
2. Kết quả là mỗi tháng bạn sẽ thấy trung bình doanh thu của 3 tháng gần nhất.

Tức là mỗi dòng sẽ lấy cửa sổ 3 dòng: dòng hiện tại và hai dòng trước. Sau đó tính trung bình. Quá tiện luôn.

Cách ORDER BY hoạt động và ảnh hưởng của nó

Hàm cửa sổ phụ thuộc vào thứ tự dòng. Nếu thứ tự sai (hoặc không có), kết quả sẽ rất bất ngờ.

Ví dụ: lỗi khi thiếu ORDER BY

Nếu bạn bỏ ORDER BY khỏi OVER(), thay vì tổng tích lũy bạn sẽ nhận tổng tất cả doanh thu cho từng dòng:

SELECT
    tháng,
    doanh_thu,
    SUM(doanh_thu) OVER () AS tong_tich_luy_sai
FROM 
    doanh_thu;

Kết quả:

Các dòng không được sắp xếp, và thay vì tổng tích lũy, hàm chỉ cộng tất cả các dòng cho từng dòng luôn.

Case thực tế sử dụng

Phân tích doanh thu:

• Tổng tích lũy giúp theo dõi doanh số hoặc doanh thu tăng trưởng thế nào.
• Trung bình trượt giúp nhìn thấy xu hướng "thật" mà không bị nhiễu.

Mô hình tài chính:

Ngân hàng và công ty tài chính dùng hàm cửa sổ để phân tích trả góp, tăng trưởng nợ và các chỉ số khác.

Phân tích chuỗi thời gian:

Dữ liệu thời gian như số người dùng online, lượt xem trang, doanh thu v.v. rất hợp để phân tích với SUM() và AVG().