SQL nâng cao 2 — Frame cửa sổ: running total & moving average

Ở bài SQL nâng cao 1 — Window functions ta đã nắm bộ khung func() OVER (PARTITION BY ... ORDER BY ...) và các hàm xếp hạng. Nhưng có một phần mà nhiều người dùng window function suốt nhiều năm vẫn hiểu mơ hồ: window frame — cái "khung" quyết định chính xác những dòng nào được đưa vào phép tính cho từng dòng hiện tại.

Frame là thứ tách biệt một SUM(...) OVER (...) trả về tổng luỹ kế với một SUM(...) OVER (...) trả về tổng toàn phân vùng. Cùng một hàm, cùng một cột, chỉ khác cái frame — kết quả khác hẳn nhau. Hiểu sai frame là nguyên nhân số một khiến "moving average" chạy ra số sai mà không ai để ý, vì query vẫn chạy, vẫn ra số, chỉ là số sai.

Bài này mổ xẻ frame đến tận cùng, rồi ráp vào ba bài toán ngân hàng cốt lõi: số dư luỹ kế (running total), trung bình trượt (moving average) và tỉ trọng đóng góp luỹ kế.

1. Frame là gì?

Với mỗi dòng khi tính window function, PostgreSQL xác định một tập con các dòng trong phân vùng gọi là frame (khung). Hàm tổng hợp (SUM, AVG, COUNT, MIN, MAX...) chỉ chạy trên các dòng nằm trong frame của dòng hiện tại.

Cú pháp đầy đủ của một mệnh đề OVER:

func() OVER (
  PARTITION BY ...        -- chia dữ liệu thành các nhóm độc lập
  ORDER BY ...            -- sắp thứ tự trong mỗi nhóm
  <frame_unit> BETWEEN <start> AND <end>
)

<frame_unit> là một trong ba: ROWS, RANGE, GROUPS. Còn <start>/<end> chọn từ các mốc:

Một vài frame quen thuộc:

• ROWS UNBOUNDED PRECEDING — từ đầu phân vùng đến dòng hiện tại (viết tắt của ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Đây là running total.
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — cửa sổ trượt 3 dòng gần nhất. Đây là nền của moving average.
• ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING — từ dòng hiện tại đến cuối, ví dụ để tính "còn lại bao nhiêu".

Sơ đồ: với dòng hiện tại r4, mỗi loại frame "quét" một dải khác nhau. Running total quét r1→r4; moving 3 kỳ quét r2→r4; frame nhìn về sau quét r4→r7.

2. Cạm bẫy #1: frame mặc định khi có ORDER BY

Đây là điều gây nhầm nhiều nhất. Khi bạn viết OVER (ORDER BY ...) mà không ghi mệnh đề frame, PostgreSQL tự thêm frame mặc định:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Chú ý: mặc định là RANGE, không phải ROWS. Còn khi bạn viết OVER () không có ORDER BY, frame mặc định là toàn bộ phân vùng (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).

Hệ quả:

• SUM(x) OVER (ORDER BY d) → tổng luỹ kế (đúng như mong đợi, thường vô hại).
• SUM(x) OVER () → tổng toàn bộ (grand total). Nhiều người tưởng thêm ORDER BY vào chỉ để "cho đẹp" nhưng nó âm thầm biến grand total thành running total.

Quy tắc sống còn: khi làm moving average hoặc rolling sum, LUÔN ghi frame ROWS BETWEEN ... một cách tường minh. Đừng bao giờ dựa vào mặc định, vì mặc định là RANGE ... CURRENT ROW — không phải cửa sổ trượt bạn muốn, và với giá trị trùng ở khoá ORDER BY nó còn cho kết quả khác ROWS (xem mục 3).

3. ROWS vs RANGE vs GROUPS

Ba đơn vị frame khác nhau ở cách hiểu "PRECEDING/FOLLOWING/CURRENT ROW":

• ROWS — đếm theo số dòng vật lý. 2 PRECEDING = đúng 2 dòng phía trên, bất kể giá trị. Trực giác, dễ đoán.
• RANGE — tính theo giá trị của khoá ORDER BY. CURRENT ROW trong RANGE bao gồm tất cả các dòng có cùng giá trị khoá với dòng hiện tại (peers). N PRECEDING nghĩa là các dòng có khoá nằm trong khoảng [giá trị_hiện_tại − N, giá trị_hiện_tại].
• GROUPS — đếm theo số nhóm peer (các cụm dòng cùng giá trị khoá). 1 PRECEDING = nhóm peer liền trước.

Khác biệt lộ ra khi khoá ORDER BY có giá trị trùng. Xét running total SUM(amount) OVER (ORDER BY d ...) với hai dòng cùng ngày d:

• Với ROWS ... CURRENT ROW: dòng thứ nhất trong ngày chỉ cộng đến chính nó; dòng thứ hai cộng thêm cả hai. Hai dòng cùng ngày ra hai giá trị khác nhau.
• Với RANGE ... CURRENT ROW (mặc định): cả hai dòng cùng ngày là peers, nên cả hai đều lấy tổng luỹ kế đến hết ngày đó → hai giá trị bằng nhau.

Điều này quan trọng khi bạn tính số dư luỹ kế theo ngày nhưng dữ liệu có nhiều giao dịch/ngày: dùng RANGE sẽ ép mọi giao dịch trong cùng ngày về cùng một mức luỹ kế cuối ngày; dùng ROWS cho luỹ kế chi tiết từng giao dịch. Chọn cái nào là tuỳ nghiệp vụ — nhưng phải chọn có ý thức.

Lưu ý kỹ thuật: N PRECEDING/N FOLLOWING với RANGE chỉ hợp lệ khi ORDER BY trên một cột kiểu số hoặc thời gian (để cộng/trừ được offset). Với thời gian, offset là INTERVAL, ví dụ RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW — rất tiện cho "7 ngày gần nhất tính theo thời gian thực" thay vì "7 dòng gần nhất".

4. Running total — số dư luỹ kế

Bài toán kinh điển: với mỗi tài khoản, tính số dư luỹ kế cộng dồn theo thời gian giao dịch. Coi amount dương là ghi có, âm là ghi nợ (tuỳ quy ước; ở đây ta minh hoạ cộng dồn thẳng amount).

-- ▶ Chạy được
SELECT
  account_id,
  created_at,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY created_at
    ROWS UNBOUNDED PRECEDING
  ) AS running_balance
FROM transactions
ORDER BY account_id, created_at;

ROWS UNBOUNDED PRECEDING = ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: mỗi dòng lấy tổng của chính nó và tất cả giao dịch trước đó trong cùng tài khoản. PARTITION BY account_id đảm bảo luỹ kế reset lại từ 0 khi sang tài khoản khác.

Muốn số dư luỹ kế theo ngày (gộp nhiều giao dịch cùng ngày về một điểm) thì thêm khoá phụ hoặc dùng RANGE. Đây là ví dụ dùng ROWS để lấy luỹ kế chi tiết cùng số thứ tự giao dịch:

-- ▶ Chạy được
SELECT
  account_id,
  created_at,
  amount,
  ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY created_at) AS seq,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_balance
FROM transactions
ORDER BY account_id, created_at;

Chỉ tính luỹ kế cho tài khoản cụ thể để dễ đọc kết quả:

-- ▶ Chạy được
SELECT
  t.created_at,
  t.kind,
  t.amount,
  SUM(t.amount) OVER (
    ORDER BY t.created_at
    ROWS UNBOUNDED PRECEDING
  ) AS running_balance
FROM transactions t
JOIN accounts a ON a.id = t.account_id
WHERE a.account_no = '000001'
ORDER BY t.created_at;

5. Moving average — trung bình trượt N kỳ

Trung bình trượt làm mượt nhiễu để nhìn ra xu hướng. Trung bình trượt 3 kỳ dùng frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW (2 dòng trước + dòng hiện tại = 3 dòng).

Nhớ quy tắc sandbox: AVG(...) trả double precision, muốn ROUND(x, 2) phải ép ::numeric.

-- ▶ Chạy được
SELECT
  account_id,
  created_at,
  amount,
  ROUND(
    AVG(amount) OVER (
      PARTITION BY account_id
      ORDER BY created_at
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )::numeric, 2
  ) AS moving_avg_3
FROM transactions
ORDER BY account_id, created_at;

Ở 2 dòng đầu mỗi tài khoản, frame chưa đủ 3 dòng nên trung bình được lấy trên 1 rồi 2 dòng — đó là hành vi đúng của cửa sổ trượt (không phải NULL). Nếu muốn chỉ hiển thị khi đã đủ 3 kỳ, lọc thêm bằng COUNT(*) OVER (... same frame ...) = 3.

Rolling sum 7 giao dịch gần nhất (cùng ý tưởng, đổi hàm và độ rộng frame):

-- ▶ Chạy được
SELECT
  account_id,
  created_at,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_sum_7,
  COUNT(*) OVER (
    PARTITION BY account_id
    ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS n_in_window
FROM transactions
ORDER BY account_id, created_at;

Kết hợp giá trị hiện tại với đường trung bình trượt để so sánh "giao dịch này lớn/nhỏ hơn mức trung bình gần đây bao nhiêu" — nền tảng phát hiện bất thường thô sơ:

-- ▶ Chạy được
SELECT
  account_id,
  created_at,
  amount,
  ROUND(
    AVG(amount) OVER (
      PARTITION BY account_id ORDER BY created_at
      ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
    )::numeric, 2
  ) AS avg_prev_5,
  amount - ROUND(
    AVG(amount) OVER (
      PARTITION BY account_id ORDER BY created_at
      ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
    )::numeric, 2
  ) AS chenh_lech_vs_trung_binh
FROM transactions
ORDER BY account_id, created_at;

Chú ý frame BETWEEN 5 PRECEDING AND 1 PRECEDING: nó loại dòng hiện tại khỏi cửa sổ, nên trung bình phản ánh "quá khứ gần" mà không bị chính giao dịch đang xét kéo lệch — đúng thứ ta cần khi so sánh dòng hiện tại với nền.

6. Tỉ trọng đóng góp luỹ kế

Bài toán Pareto: xếp khách theo số dư giảm dần, tính % đóng góp luỹ kế để trả lời "bao nhiêu khách chiếm 80% tổng số dư". Công thức: running_total / grand_total. Ở đây grand_total là SUM(...) OVER () (không frame → toàn bộ), còn running_total là SUM(...) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING).

-- ▶ Chạy được
SELECT
  customer_id,
  balance,
  ROUND(
    100.0 * SUM(balance) OVER (
      ORDER BY balance DESC
      ROWS UNBOUNDED PRECEDING
    ) / SUM(balance) OVER ()
  , 2) AS pct_luy_ke
FROM accounts
ORDER BY balance DESC;

Ghép tên khách và gộp số dư theo từng khách trước khi xếp hạng:

-- ▶ Chạy được
WITH per_customer AS (
  SELECT c.id, c.full_name, SUM(a.balance) AS tong_du
  FROM customers c
  JOIN accounts a ON a.customer_id = c.id
  GROUP BY c.id, c.full_name
)
SELECT
  full_name,
  tong_du,
  ROUND(
    100.0 * SUM(tong_du) OVER (ORDER BY tong_du DESC ROWS UNBOUNDED PRECEDING)
    / SUM(tong_du) OVER ()
  , 2) AS pct_luy_ke
FROM per_customer
ORDER BY tong_du DESC;

Cột pct_luy_ke tăng dần từ khách giàu nhất tới 100%. Tìm ngưỡng 80% là ra "nhóm khách VIP" cần chăm sóc riêng.

7. Hiệu năng frame

Vài lưu ý thực chiến để frame không thành nút cổ chai:

• ROWS thường rẻ hơn RANGE. PostgreSQL tối ưu tốt frame ROWS ... CURRENT ROW bằng cách cộng dồn tăng dần. RANGE với offset phải dò biên peer nên tốn hơn; với giá trị trùng, RANGE ... CURRENT ROW phải quét hết cụm peer.
• Sort là chi phí lớn nhất. Window function cần dữ liệu đã sắp theo PARTITION BY, ORDER BY. Một index khớp thứ tự này (ví dụ (account_id, created_at)) giúp Postgres bỏ bước Sort. Xem EXPLAIN để biết có WindowAgg phía trên Sort hay Index Scan — chi tiết ở SQL nâng cao 8 — EXPLAIN & tối ưu và Tối ưu truy vấn.
• Tái dùng WINDOW. Nhiều cột chung một khung thì khai báo WINDOW w AS (...) rồi OVER w để tính một lần.

Use case thực tế

Bối cảnh NCB. Đội giám sát vận hành cần một bảng theo dõi hằng ngày cho các tài khoản thanh toán: (1) số dư luỹ kế theo dòng tiền để đối chiếu sổ, và (2) một tín hiệu xu hướng giao dịch để cảnh báo sớm khi hành vi của tài khoản đổi khác — ví dụ dòng tiền vào/ra tăng vọt so với nền gần đây (dấu hiệu cần rà soát AML hoặc tài khoản bị lợi dụng).

Cách làm. Với mỗi tài khoản, ta tính đồng thời: giá trị giao dịch hiện tại, trung bình trượt 5 giao dịch trước đó (loại dòng hiện tại), và tỉ lệ hiện tại/nền. Tài khoản có amount vượt nhiều lần đường trung bình gần đây sẽ nổi lên đầu danh sách rà soát.

-- ▶ Chạy được
SELECT
  t.account_id,
  t.created_at,
  t.amount,
  ROUND(
    AVG(t.amount) OVER (
      PARTITION BY t.account_id ORDER BY t.created_at
      ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
    )::numeric, 2
  ) AS nen_5_gd_truoc,
  ROUND(
    SUM(t.amount) OVER (
      PARTITION BY t.account_id ORDER BY t.created_at
      ROWS UNBOUNDED PRECEDING
    ), 2
  ) AS so_du_luy_ke
FROM transactions t
ORDER BY t.account_id, t.created_at;

Diễn giải. Cột so_du_luy_ke cho đội đối soát thấy đường số dư dựng lại từ chuỗi giao dịch — lệch với sổ cái là có vấn đề ghi nhận. Cột nen_5_gd_truoc là "mức bình thường gần đây" của tài khoản; đặt cạnh amount hiện tại, phân tích viên chỉ cần lọc amount > k * nen_5_gd_truoc (ví dụ k = 5) để rút ra các giao dịch đột biến. Vì frame dừng ở 1 PRECEDING, giao dịch đột biến hôm nay không tự làm phồng cái nền nó bị so sánh — tránh việc bất thường tự che giấu chính nó. Đường trung bình trượt này chính là bước tiền xử lý cho các phân tích chuỗi thời gian và phễu ở SQL nâng cao 5 — Funnel & time-series, và là một chỉ số vận hành theo tinh thần BI — Metrics & KPI.

Trên dữ liệu thật, đội thường vật chất hoá bảng này mỗi đêm (materialized view) với index (account_id, created_at) để WindowAgg không phải sort lại, giữ thời gian chạy ở mức phút ngay cả với hàng chục triệu giao dịch.

Ghi nhớ

• Frame = tập dòng thực sự đưa vào hàm tổng hợp. Cùng SUM mà khác frame là khác kết quả: running total vs grand total.
• Có ORDER BY mà không ghi frame → mặc định RANGE UNBOUNDED PRECEDING → CURRENT ROW, không phải ROWS. Đây là bẫy làm sai moving average — luôn ghi ROWS BETWEEN ... tường minh.
• ROWS đếm theo dòng vật lý; RANGE gộp mọi peer cùng giá trị khoá; GROUPS đếm theo nhóm peer. Khác biệt lộ ra khi khoá ORDER BY có giá trị trùng.
• Running total = SUM(x) OVER (PARTITION BY ... ORDER BY ... ROWS UNBOUNDED PRECEDING).
• Moving average N kỳ = AVG(x) OVER (... ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW); dùng ... AND 1 PRECEDING để loại dòng hiện tại khỏi nền so sánh.
• Tỉ trọng luỹ kế = running total (OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING)) chia grand total (OVER ()).
• Trên sandbox Postgres, ROUND(AVG/STDDEV..., 2) phải ép ::numeric trước.
• Hiệu năng: ưu tiên ROWS, tạo index khớp (PARTITION BY, ORDER BY) để tránh Sort, tái dùng mệnh đề WINDOW.
• Nền tảng chung ở SQL nâng cao 1 — Window functions; ứng dụng chuỗi thời gian ở SQL nâng cao 5.

Nguồn tham khảo

• PostgreSQL Documentation — Window Function Calls (mệnh đề frame: ROWS/RANGE/GROUPS BETWEEN, frame mặc định, khái niệm peer)
• PostgreSQL Documentation — 3.5. Window Functions (tutorial)
• PostgreSQL Documentation — SELECT (mệnh đề WINDOW, cú pháp window_definition)
• PostgreSQL Documentation — 9.22. Window Functions (danh sách hàm window)
• ISO/IEC 9075:2016 (SQL:2016), Part 2: Foundation — định nghĩa chuẩn window frame (ROWS/RANGE/GROUPS, frame exclusion)