Cửa sổ trượt

Bạn sẽ học được

• Khung cửa sổ (frame) là gì và cú pháp ROWS/RANGE BETWEEN.
• Trung bình động, tổng động — công cụ làm mượt chuỗi thời gian.
• Cách xử lý các kỳ đầu khi chưa đủ cửa sổ.
• Vì sao ROWS và RANGE cho kết quả khác nhau khi dữ liệu có khoảng trống.
• Ứng dụng: phát hiện xu hướng thay vì phản ứng với từng tháng.

Khái niệm cốt lõi

Mọi window function đều có một khung quyết định dòng nào được tính. Nếu không viết khung, PostgreSQL dùng mặc định: có ORDER BY thì khung là "từ đầu đến dòng hiện tại", không có ORDER BY thì là toàn bộ cửa sổ.

Khung trượt giới hạn phạm vi quanh dòng hiện tại:

AVG(doanh_thu) OVER (
  ORDER BY thang
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

Đọc là: "trung bình của dòng hiện tại và hai dòng ngay trước nó". Với dữ liệu tháng đều đặn, đó là trung bình động ba tháng.

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT thang, doanh_thu,
       ROUND(AVG(doanh_thu) OVER (ORDER BY thang
                                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS ma_3_thang
FROM monthly
ORDER BY thang;

Kết quả:

Ba dòng đầu cho thấy khung chưa đầy: tháng 1 chỉ có một dòng nên trung bình bằng chính nó; tháng 2 có hai dòng; từ tháng 3 mới đủ ba. PostgreSQL không báo lỗi — nó tính trên những gì có. Đây thường là hành vi mong muốn, nhưng phải biết để không diễn giải sai: ma_3_thang của tháng 1 không phải trung bình ba tháng.

Nếu chỉ muốn hiển thị khi đã đủ cửa sổ:

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
), smoothed AS (
  SELECT thang, doanh_thu,
         AVG(doanh_thu) OVER (ORDER BY thang ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma,
         COUNT(*) OVER (ORDER BY thang ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS so_ky
  FROM monthly
)
SELECT thang, doanh_thu, ROUND(ma, 0) AS ma_3_thang
FROM smoothed
WHERE so_ky = 3
ORDER BY thang;

Kết quả còn 9 dòng, bắt đầu từ tháng 3. Cách này hữu ích khi đưa số liệu vào báo cáo so sánh: mọi giá trị đều dựa trên cùng số kỳ, nên công bằng hơn.

Tổng động dùng cùng khung:

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT thang, doanh_thu,
       SUM(doanh_thu) OVER (ORDER BY thang
                            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS tong_3_thang
FROM monthly
ORDER BY thang;

Tháng 3 có tổng 10.560.000; tháng 4 là 12.310.000; tháng 11 là 4.690.000. Tổng động hữu ích khi doanh thu từng tháng nhỏ và nhiễu, còn tổng quý ổn định hơn.

ROWS khác RANGE ở đâu

ROWS đếm số dòng. RANGE đếm giá trị của cột ORDER BY. Với dữ liệu tháng đều đặn, hai cách cho kết quả giống nhau:

AVG(doanh_thu) OVER (
  ORDER BY thang
  RANGE BETWEEN INTERVAL '2 months' PRECEDING AND CURRENT ROW
)

Nhưng khi chuỗi có tháng bị thiếu, chúng tách nhau ngay:

• ROWS BETWEEN 2 PRECEDING luôn lấy đúng ba dòng, có thể là tháng 1, tháng 4 và tháng 7.
• RANGE BETWEEN INTERVAL '2 months' PRECEDING chỉ lấy các dòng trong khoảng thời gian hai tháng trước, nên tháng 4 chỉ thấy chính nó.

Muốn "ba tháng gần nhất theo lịch", RANGE đúng nghĩa hơn. Muốn "ba kỳ gần nhất bất kể cách nhau bao lâu", ROWS đúng nghĩa hơn. Không có lựa chọn nào mặc định đúng cho mọi báo cáo.

Với dữ liệu mẫu, cả 11 tháng liên tiếp nên hai khung cho cùng con số. Đừng để sự trùng hợp này đánh lừa: hãy thử xoá một tháng khỏi CTE và quan sát sự khác biệt.

Các khung hay dùng:

Ví dụ chạy được

Trung bình động bảy tháng — làm mượt mạnh hơn, phản ánh xu hướng dài hạn:

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT thang, doanh_thu,
       ROUND(AVG(doanh_thu) OVER (ORDER BY thang
                                  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 0) AS ma_7_thang
FROM monthly
ORDER BY thang;

Tháng 7 là điểm đầu tiên đủ bảy dòng: 3.215.714. Tháng 11 đạt 2.152.857. So sánh với ma_3_thang: đường bảy tháng phẳng hơn, ít phản ứng với cú tăng tháng 4 và cú sụt tháng 9. Đây là bản chất của làm mượt — đánh đổi độ nhạy lấy độ ổn định.

Phát hiện tháng bất thường bằng cách so với trung bình động:

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
), smoothed AS (
  SELECT thang, doanh_thu,
         AVG(doanh_thu) OVER (ORDER BY thang ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS ma_truoc
  FROM monthly
)
SELECT thang, doanh_thu, ROUND(ma_truoc, 0) AS trung_binh_3_thang_truoc,
       ROUND(100.0 * (doanh_thu - ma_truoc) / NULLIF(ma_truoc, 0), 1) AS lech_pct
FROM smoothed
ORDER BY thang;

Khung 3 PRECEDING AND 1 PRECEDING loại trừ dòng hiện tại — quan trọng, vì nếu tính cả dòng hiện tại thì mức bất thường bị pha loãng. Tháng 9 có doanh_thu 580.000 so với trung bình ba tháng trước (2.150.000; 2.050.000; 4.030.000 → 2.743.333), lệch khoảng −78,9%. Đây là cách đơn giản để gắn cờ bất thường mà không cần mô hình thống kê.

Tính tỷ trọng doanh thu trong cửa sổ trượt:

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
)
SELECT thang, doanh_thu,
       ROUND(100.0 * doanh_thu
             / SUM(doanh_thu) OVER (ORDER BY thang ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS pct_trong_3_thang
FROM monthly
ORDER BY thang;

Tháng 3 chiếm 34,0% doanh thu ba tháng gần nhất; tháng 4 chiếm 45,5%; tháng 9 chỉ 8,7%. Con số này cho biết tháng hiện tại đóng góp bao nhiêu vào cục diện gần đây, thay vì vào toàn bộ lịch sử.

Thực hành

1. Chạy câu trung bình động ba tháng và đối chiếu bảng 11 dòng.
2. Lọc so_ky = 3 và xác nhận còn 9 dòng, bắt đầu từ tháng 3.
3. Đổi ROWS thành RANGE BETWEEN INTERVAL '2 months' PRECEDING AND CURRENT ROW và giải thích vì sao kết quả giống nhau trên dữ liệu này.
4. Tính trung bình động bảy tháng và so sánh độ mượt với ba tháng.
5. Dùng khung 3 PRECEDING AND 1 PRECEDING để tìm tháng lệch nhiều nhất so với xu hướng trước đó.

Lỗi thường gặp

Lỗi thứ ba là lỗi âm thầm nguy hiểm nhất: dữ liệu thật thường thiếu tháng do lỗi nhập liệu hoặc do tháng không có giao dịch. Một báo cáo "trung bình ba tháng" dùng ROWS có thể thực chất đang tính trung bình của tháng 1, 4 và 7. Luôn kiểm tra chuỗi có đủ kỳ trước khi tin vào cửa sổ trượt.

Tóm tắt

• Khung xác định dòng nào tham gia tính toán; mặc định là luỹ kế khi có ORDER BY.
• ROWS BETWEEN n PRECEDING AND CURRENT ROW tạo trung bình/tổng động.
• Khung chưa đầy ở đầu chuỗi — kiểm tra COUNT(*) OVER nếu cần đủ kỳ.
• ROWS đếm dòng, RANGE đếm khoảng giá trị; khác biệt lộ ra khi thiếu kỳ.
• Loại dòng hiện tại khỏi khung khi muốn đo bất thường so với quá khứ.