Window patterns — RANK + LAG/LEAD + running total + moving average

Module 3 bài 6 đã giới thiệu window function: khái niệm cửa sổ, cú pháp OVER, và tại sao window không collapse row như GROUP BY. Bây giờ là lúc đi sâu — 4 pattern xuất hiện trong 80% bài toán analytics SQL thực chiến.

Mỗi pattern có function riêng và frame clause để kiểm soát "window size": ranking để tìm top N per group, LAG/LEAD để so sánh kỳ trước với kỳ này, running total để đếm dồn từ đầu năm, moving average để làm mịn fluctuation theo 7 ngày gần nhất. Biết 4 cái này bạn giải quyết phần lớn yêu cầu báo cáo trong TaskFlow mà không cần subquery phức tạp hay join nhiều tầng.

1. Analogy — 4 loại nhìn xung quanh

Tưởng tượng bạn là thí sinh trong kỳ thi cuối kỳ, ngồi trong phòng thi cùng 30 người khác:

• Ranking: Nhìn điểm số cả phòng rồi biết mình xếp thứ mấy. Hai người cùng điểm — giám khảo quyết định đồng hạng hay tách.
• LAG/LEAD: So bài thi này với bài thi kỳ trước của chính mình — tiến bộ hay thụt lùi bao nhiêu điểm?
• Running total: Ban tổ chức đếm dồn số thí sinh hoàn thành bài từ đầu giờ đến hiện tại — số tích lũy tăng dần theo thời gian.
• Moving average: Điểm trung bình 7 buổi kiểm tra gần nhất của một thí sinh — làm mịn biến động ngắn hạn.

2. Pattern 1 — Ranking: ROW_NUMBER vs RANK vs DENSE_RANK

Cho 4 score [100, 95, 95, 90] — hai score bằng nhau ở vị trí 2 và 3:

• ROW_NUMBER: luôn unique 1, 2, 3, 4 — không có tie, tie break tùy ý (thứ tự vật lý hoặc thêm ORDER BY phụ).
• RANK: tie cùng số, gap sau tie — 1, 2, 2, 4 (skip 3 vì hai người cùng hạng 2).
• DENSE_RANK: tie cùng số, không gap — 1, 2, 2, 3.

-- Top 3 user co nhieu task done nhat per project
SELECT
  project_id,
  user_id,
  done_count,
  ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY done_count DESC) AS rn,
  RANK()       OVER (PARTITION BY project_id ORDER BY done_count DESC) AS rk,
  DENSE_RANK() OVER (PARTITION BY project_id ORDER BY done_count DESC) AS drk
FROM (
  SELECT
    project_id,
    assignee_id AS user_id,
    COUNT(*) FILTER (WHERE status = 'done') AS done_count
  FROM tasks
  GROUP BY project_id, assignee_id
) sub;

Khi nào dùng cái nào:

3. Pattern 2 — LAG/LEAD: so sánh với row trước/sau

-- "Diff time-to-completion giua task lien tiep cua moi user"
SELECT
  assignee_id,
  id,
  EXTRACT(DAY FROM (updated_at - created_at))                          AS days_taken,
  LAG(EXTRACT(DAY FROM (updated_at - created_at)))
    OVER (PARTITION BY assignee_id ORDER BY created_at)                AS prev_days,
  EXTRACT(DAY FROM (updated_at - created_at))
  - LAG(EXTRACT(DAY FROM (updated_at - created_at)))
      OVER (PARTITION BY assignee_id ORDER BY created_at)             AS diff_vs_prev
FROM tasks
WHERE status = 'done';

• LAG(col, n, default) — giá trị của row cách n row trước trong window (mặc định n=1, default=NULL).
• LEAD(col, n, default) — giá trị của row cách n row sau.

Row đầu tiên của mỗi partition không có row trước — LAG trả NULL. Dùng COALESCE(LAG(...), 0) nếu muốn default.

Use case phổ biến:

• Time-between-events: khoảng cách giữa hai commit, hai lần login, hai task done.
• Period-over-period diff: revenue tháng này so tháng trước, active user tuần này so tuần trước.
• Detect transition: status trước là gì, status hiện tại là gì (LAG(status) — phát hiện chuyển in_progress sang done).

4. Pattern 3 — Running total: cumulative SUM với frame

-- "Cumulative task done per day (running total)"
SELECT
  date_trunc('day', updated_at) AS day,
  COUNT(*)                       AS daily_done,
  SUM(COUNT(*)) OVER (
    ORDER BY date_trunc('day', updated_at)
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )                              AS cumulative_done
FROM tasks
WHERE status = 'done'
GROUP BY date_trunc('day', updated_at)
ORDER BY day;

Sample output:

day        | daily_done | cumulative_done
-----------+------------+-----------------
2026-05-01 |          5 |               5
2026-05-02 |          3 |               8
2026-05-03 |          7 |              15
2026-05-04 |          2 |              17

SUM(...) OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = "tổng từ row đầu tiên đến row hiện tại theo ORDER BY". Mỗi row nhận tổng tích lũy tính đến ngày đó.

Lưu ý: SUM(COUNT(*)) là aggregate-over-aggregate — GROUP BY tính COUNT(*) per day trước, window function SUM cộng dồn giá trị đó theo ORDER BY ngày.

5. Pattern 4 — Moving average: frame N preceding rows

-- "7-day moving average task completion (smooth trend)"
SELECT
  day,
  daily_done,
  AVG(daily_done) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7d
FROM (
  SELECT
    date_trunc('day', updated_at) AS day,
    COUNT(*)                       AS daily_done
  FROM tasks
  WHERE status = 'done'
  GROUP BY 1
) sub;

Frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW = window 7 row gần nhất (6 row trước + current row). AVG tính trên tối đa 7 row — nếu chưa đủ 7 row (đầu dataset), PostgreSQL tính trên số row hiện có.

Moving average làm mịn fluctuation ngắn hạn — ngày có spike đột biến ít ảnh hưởng hơn trend 7 ngày. Dùng trong dashboard để hiển thị trend completion ổn định hơn daily bar chart.

6. Frame clause — ROWS vs RANGE vs GROUPS

3 frame mode trong PostgreSQL 11+:

-- ROWS: luon dung 6 row vat ly ke truoc, bat ke khoang cach ngay
SUM(x) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- RANGE: moi row trong 7 ngay truoc theo value time
SUM(x) OVER (ORDER BY date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW)

Phân biệt quan trọng khi data có gap (ngày không có row): ROWS BETWEEN 6 PRECEDING lấy đúng 6 row vật lý (có thể trải dài nhiều tuần nếu data thưa), RANGE BETWEEN INTERVAL '7 days' PRECEDING gắn vào giá trị thời gian thực — đúng semantic "7 ngày lịch" dù data có gap.

7. Pitfall — frame default thay đổi với ORDER BY

-- KHONG ORDER BY: frame default = BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-- -> tinh tong toan partition, moi row tra ve cung gia tri
SELECT id, SUM(x) OVER (PARTITION BY p) FROM t;

-- CO ORDER BY: frame default = BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- -> tinh running total, moi row tra ve tong tich luy den row do
SELECT id, SUM(x) OVER (PARTITION BY p ORDER BY id) FROM t;

8. Pattern 5 (bonus) — Gap-and-island: streak liên tục

Gap-and-island là kỹ thuật xác định chuỗi liên tiếp bị gián đoạn bởi gap. Pattern: day - ROW_NUMBER() * INTERVAL '1 day' ra cùng group_key nếu các ngày liên tục.

-- "Streak ngay co task done lien tiep cua moi user"
WITH daily AS (
  SELECT
    assignee_id,
    date_trunc('day', updated_at) AS day,
    COUNT(*)                       AS daily_done
  FROM tasks
  WHERE status = 'done'
  GROUP BY 1, 2
),
gap_island AS (
  SELECT
    assignee_id,
    day,
    daily_done,
    day - (ROW_NUMBER() OVER (PARTITION BY assignee_id ORDER BY day))::int
          * INTERVAL '1 day'                                           AS group_key
  FROM daily
)
SELECT
  assignee_id,
  MIN(day)   AS streak_start,
  MAX(day)   AS streak_end,
  COUNT(*)   AS streak_length
FROM gap_island
GROUP BY assignee_id, group_key
ORDER BY streak_length DESC
LIMIT 5;

Tại sao hoạt động: nếu day liên tục (2026-05-01, 02, 03) và ROW_NUMBER tăng tương ứng (1, 2, 3), hiệu day - rn * 1 day ra cùng hằng số (2026-04-30). Khi có gap (2026-05-05 sau 05-03, skip 05-04), ROW_NUMBER tiếp tục tăng nhưng day nhảy — group_key ra giá trị khác. Group by group_key → mỗi streak thành 1 nhóm.

9. Applied — TaskFlow analytics dashboard

-- "Top 3 closer per month + diff vs previous month"
WITH monthly_per_user AS (
  SELECT
    date_trunc('month', updated_at) AS month,
    assignee_id,
    COUNT(*)                         AS done_count
  FROM tasks
  WHERE status = 'done'
  GROUP BY 1, 2
),
ranked AS (
  SELECT
    month,
    assignee_id,
    done_count,
    DENSE_RANK() OVER (PARTITION BY month ORDER BY done_count DESC)    AS rk,
    LAG(done_count) OVER (PARTITION BY assignee_id ORDER BY month)     AS prev_month_done
  FROM monthly_per_user
)
SELECT
  month,
  assignee_id,
  done_count,
  rk,
  prev_month_done,
  done_count - COALESCE(prev_month_done, 0) AS month_over_month
FROM ranked
WHERE rk <= 3
ORDER BY month DESC, rk;

Query kết hợp DENSE_RANK (top 3 per month, không gap nếu tie) và LAG (diff so tháng trước của cùng user). Filter rk <= 3 ở outer query vì window result không thấy được ở WHERE trực tiếp.

10. Deep Dive — Window patterns

11. Tóm tắt

• ROW_NUMBER trả unique rank (không tie); RANK tie → cùng số + gap sau; DENSE_RANK tie → cùng số + không gap.
• LAG(col, n, default) lấy giá trị n row trước trong window — dùng cho period-over-period diff, time-between-events, status transition.
• Running total: SUM(...) OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — tổng tích lũy từ row đầu đến row hiện tại.
• Moving average: AVG(...) OVER (ORDER BY ... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — trung bình 7 row gần nhất.
• Frame mode ROWS đếm row vật lý, RANGE gắn theo value của ORDER BY column — khác nhau khi data có gap.
• Frame default thay đổi tuỳ có ORDER BY hay không — luôn khai báo explicit frame clause để tránh bug silent.
• Gap-and-island: day - ROW_NUMBER() * INTERVAL '1 day' ra cùng group_key cho chuỗi ngày liên tiếp — group by group_key để tìm streak.
• Xem thêm Module 3 bài 8 của khoá này (mini-challenge kết hợp toàn bộ pattern) và Module 8 của khoá này (advanced window frame với CTE).

12. Tự kiểm tra

Bài tiếp theo: Mini-challenge: monthly top closer + completion rate