Window functions intro — OVER + PARTITION BY giữ row + tính cross-row

TaskFlow cần báo cáo: "task nào có due_at gần nhất trong từng project?" Câu truy vấn đầu tiên bạn thử dùng GROUP BY project_id — gom 30 task về 1 row per project, mất toàn bộ thông tin từng task. Bạn không thể giữ id, title, assignee_id vì chúng không nằm trong GROUP BY và không được aggregate.

Window function giải quyết bài toán này: giữ nguyên 30 row, thêm column rn tính từ thứ tự due_at trong từng project. Filter rn = 1 cho bạn đúng task muốn mà không mất chi tiết. Đây là pattern xuất hiện trong 79% job description analytics SQL — ranking per group, aggregate kết hợp so sánh cross-row, lag/lead giữa kỳ.

Bài này map khái niệm cửa sổ, cú pháp OVER, 4 use case cơ bản, và 1 pitfall mà hầu hết dev mới đều dính lần đầu.

1. Analogy — Cửa sổ nhìn xung quanh

Hãy tưởng tượng một bữa tiệc công ty: mọi người ngồi theo bàn (project). GROUP BY giống ban tổ chức gom mọi người thành từng bàn rồi chỉ báo cáo tổng số người mỗi bàn — bạn mất danh sách từng người. Window function giống mỗi người vẫn ngồi tại chỗ của mình, nhìn quanh bàn để biết mình đứng thứ mấy trong nhóm — identity vẫn giữ nguyên, thêm thông tin cross-row.

2. Cú pháp + cơ chế

SELECT
  id,
  project_id,
  title,
  due_at,
  ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY due_at) AS rn
FROM tasks;

Ba phần trong OVER(...):

Output minh họa:

id | project_id | title       | due_at     | rn
---+------------+-------------+------------+----
 1 |          1 | Deploy v1   | 2026-05-01 |  1
 2 |          1 | Fix auth    | 2026-05-03 |  2
 3 |          1 | Update docs | 2026-05-10 |  3
 4 |          2 | Mockup home | 2026-05-02 |  1
 5 |          2 | Logo design | 2026-05-05 |  2

Mỗi row vẫn xuất hiện đầy đủ. rn reset cho mỗi project_id vì PARTITION BY project_id. Task id=4 của project 2 có rn=1 độc lập với project 1.

3. So sánh window vs aggregate vs subquery

-- AGGREGATE: collapse, mat row chi tiet
SELECT project_id, AVG(EXTRACT(DAY FROM (updated_at - created_at)))
FROM tasks
GROUP BY project_id;
-- Output: 1 row per project, khong xem duoc task individual

-- WINDOW: giu row + add column cross-row
SELECT
  id,
  title,
  project_id,
  EXTRACT(DAY FROM (updated_at - created_at))              AS my_days,
  AVG(EXTRACT(DAY FROM (updated_at - created_at)))
    OVER (PARTITION BY project_id)                         AS project_avg
FROM tasks;
-- Output: moi row task + column project_avg tinh tren ca project

-- SUBQUERY: verbose hon, plan thuong cham hon voi bang lon
SELECT
  t.id,
  t.title,
  t.project_id,
  EXTRACT(DAY FROM (t.updated_at - t.created_at))          AS my_days,
  (SELECT AVG(EXTRACT(DAY FROM (updated_at - created_at)))
   FROM tasks
   WHERE project_id = t.project_id)                        AS project_avg
FROM tasks t;
-- Output: same result nhung correlated subquery chay lai cho moi row

Window là lựa chọn tốt nhất cho pattern "giữ row chi tiết + thêm cross-row metric". Subquery correlated đạt cùng kết quả nhưng thường chậm hơn với bảng lớn vì chạy lại cho mỗi row.

4. 4 use case — tổng quan

Bài tiếp theo (Module 3 bài 7 của khoá này) đi sâu cả 4 pattern với ví dụ thực chiến. Bài này focus intro + pattern 1 (ranking) + pattern 2 (aggregate over window) để xây nền.

5. Pattern 1 — Ranking với ROW_NUMBER

-- "Task co due_at som nhat per project"
SELECT
  id,
  project_id,
  title,
  due_at,
  ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY due_at) AS rn
FROM tasks;
-- rn=1 la task due som nhat trong project do

Filter rn = 1 cho top-1 per project. Nhưng WHERE rn = 1 trực tiếp báo lỗi — đây là pitfall quan trọng, xem section 6.

Ba hàm ranking hay dùng:

6. Pitfall — WHERE rn = 1 không hợp lệ

-- BUG: WHERE chay TRUOC SELECT (trong do chua window result)
SELECT
  id,
  project_id,
  title,
  ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY due_at) AS rn
FROM tasks
WHERE rn = 1;
-- ERROR: column "rn" does not exist

Ba cách fix:

-- Fix 1: wrap subquery -- universal, moi dialect
SELECT * FROM (
  SELECT
    id,
    project_id,
    title,
    ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY due_at) AS rn
  FROM tasks
) sub
WHERE rn = 1;

-- Fix 2: CTE (Module 8 cua khoa nay -- readable hon cho query phuc tap)
WITH ranked AS (
  SELECT
    id,
    project_id,
    title,
    ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY due_at) AS rn
  FROM tasks
)
SELECT * FROM ranked WHERE rn = 1;

-- Fix 3: DISTINCT ON (PostgreSQL-only) -- ngan nhat cho top-1 per group
SELECT DISTINCT ON (project_id)
  id, project_id, title, due_at
FROM tasks
ORDER BY project_id, due_at;

DISTINCT ON là lựa chọn ngắn gọn nhất cho "top-1 per group" trong PostgreSQL. Window function cần thiết khi muốn top-N (N vượt 1) hoặc khi cần giữ rank value trong kết quả để downstream logic dùng.

7. Pattern 2 — Aggregate over window

-- Per task: so ngay hoan thanh cua task nay + trung binh cua project + tong cua assignee
SELECT
  id,
  title,
  EXTRACT(DAY FROM (updated_at - created_at))              AS my_days,
  AVG(EXTRACT(DAY FROM (updated_at - created_at)))
    OVER (PARTITION BY project_id)                         AS project_avg,
  SUM(EXTRACT(DAY FROM (updated_at - created_at)))
    OVER (PARTITION BY assignee_id)                        AS my_total_days
FROM tasks
WHERE status = 'done';

Hai window khác nhau (PARTITION BY project_id và PARTITION BY assignee_id) trong cùng một query hoàn toàn hợp lệ. PostgreSQL tính từng window function độc lập — mỗi OVER(...) định nghĩa một cửa sổ riêng.

8. Applied — TaskFlow leaderboard nâng cao

-- "Per user: so task done + rank trong project theo so task done"
SELECT
  u.id,
  u.name,
  t.project_id,
  COUNT(t.id) FILTER (WHERE t.status = 'done')           AS done_count,
  RANK() OVER (
    PARTITION BY t.project_id
    ORDER BY COUNT(t.id) FILTER (WHERE t.status = 'done') DESC
  )                                                        AS project_rank
FROM users u
LEFT JOIN tasks t ON t.assignee_id = u.id
GROUP BY u.id, u.name, t.project_id;

Điểm đáng chú ý: window function chạy sau GROUP BY trong logical order (GROUP BY là bước 3, window function nằm trong SELECT là bước 5). Vì vậy RANK() OVER (...) có thể dùng kết quả của COUNT(...) FILTER (...) — aggregate được tính xong trước khi window function đọc giá trị đó.

id | name    | project_id | done_count | project_rank
---+---------+------------+------------+--------------
 1 | An      |          1 |          8 |            1
 2 | Binh    |          1 |          5 |            2
 3 | Chi     |          1 |          5 |            2
 4 | An      |          2 |          3 |            1

RANK() tạo gap: project_rank = 4 không xuất hiện sau hai người rank 2. Dùng DENSE_RANK() nếu muốn liên tục không gap.

9. Deep Dive — Window functions

10. Tóm tắt

• Window function = "cửa sổ nhìn nhóm row liên quan" — KHÔNG collapse như GROUP BY, mỗi row vẫn xuất hiện trong kết quả.
• Cú pháp: func() OVER (PARTITION BY ... ORDER BY ...) — PARTITION BY chia group, ORDER BY xác định thứ tự trong window.
• 4 use case: ranking (ROW_NUMBER/RANK/DENSE_RANK), aggregate over window (SUM/AVG/COUNT OVER), lag/lead so sánh row trước/sau, frame cho running total và moving average.
• WHERE không thấy window function result — wrap subquery hoặc CTE để filter theo column window.
• DISTINCT ON (col) là alternative ngắn gọn cho "top-1 per group" trong PostgreSQL, window function cần thiết cho top-N hoặc khi cần rank value.
• Window function chạy sau GROUP BY trong logical order — có thể OVER trên aggregate result.
• Module 3 bài 7 của khoá này: rank/lag/running total — 4 pattern thực chiến. Module 8 của khoá này: CTE wrap pattern cho window filter.

11. Tự kiểm tra

SELECT DISTINCT ON (t.assignee_id)
t.id,
t.assignee_id,
t.project_id,
t.due_at,
PERCENTILE_CONT(0.5) WITHIN GROUP (
  ORDER BY t.due_at - CURRENT_DATE
) OVER (PARTITION BY t.project_id) AS project_median_days
FROM tasks t
ORDER BY t.assignee_id, t.due_at;

SELECT * FROM (
SELECT
  t.id, t.assignee_id, t.project_id, t.due_at,
  ROW_NUMBER() OVER (PARTITION BY t.assignee_id ORDER BY t.due_at) AS rn,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY t.due_at - CURRENT_DATE)
    OVER (PARTITION BY t.project_id) AS project_median_days
FROM tasks t
) sub
WHERE rn = 1;

Bài tiếp theo: Window rank/lag/running total — 4 pattern phổ biến nhất analytics