Lập trình · 29/09/2026

SQL Window Functions: Xếp hạng, Top-N và tổng lũy kế

Window function tính toán trên một tập dòng liên quan nhưng vẫn giữ từng dòng trong kết quả. Khác với GROUP BY, nó không gom nhiều bản ghi thành một dòng. Nhờ đó, ta có thể hiển thị mỗi đơn hàng cùng thứ hạng của nó, doanh thu lũy kế, giá trị của dòng trước hoặc trung bình trượt mà không cần self join phức tạp.

Window function tính toán trên một tập dòng liên quan nhưng vẫn giữ từng dòng trong kết quả. Khác với GROUP BY, nó không gom nhiều bản ghi thành một dòng. Nhờ đó, ta có thể hiển thị mỗi đơn hàng cùng thứ hạng của nó, doanh thu lũy kế, giá trị của dòng trước hoặc trung bình trượt mà không cần self join phức tạp.

Bài viết dùng một bảng giao dịch đơn giản và cú pháp phổ biến giữa PostgreSQL, MySQL 8+ cùng SQLite hiện đại. Một số chi tiết về frame, kiểu dữ liệu ngày tháng và filtering khác nhau theo hệ quản trị; hãy kiểm tra tài liệu phiên bản đang chạy trước khi đưa truy vấn vào production.

1. Window function khác GROUP BY như thế nào?

Giả sử bảng sales có các cột id, department, sold_at và amount. Truy vấn nhóm làm giảm số dòng:

SELECT department, SUM(amount) AS department_total
FROM sales
GROUP BY department;

Window function giữ lại từng giao dịch và thêm tổng của phòng ban vào mỗi dòng:

SELECT id, department, sold_at, amount,
       SUM(amount) OVER (
         PARTITION BY department
       ) AS department_total
FROM sales;

GROUP BY trả một dòng cho mỗi nhóm; window function trả một kết quả cho mỗi dòng đầu vào còn lại sau WHERE, đồng thời cho phép nhìn sang các dòng trong cùng cửa sổ.

2. Giải phẫu mệnh đề OVER

Một window thường có ba thành phần:

function(expression) OVER (
  PARTITION BY group_columns
  ORDER BY sort_columns
  ROWS BETWEEN frame_start AND frame_end
)

PARTITION BY chia dữ liệu thành các nhóm độc lập nhưng không collapse dòng.
ORDER BY xác định thứ tự bên trong mỗi partition.
Frame xác định tập con quanh dòng hiện tại mà aggregate window sẽ đọc.

Cả ba đều có thể được bỏ tùy hàm, nhưng bỏ ORDER BY ở bài toán xếp hạng thường khiến kết quả không có thứ tự xác định hữu ích.

3. ROW_NUMBER cần tie-breaker ổn định

ROW_NUMBER() cấp số tuần tự trong mỗi partition:

SELECT id, department, amount,
       ROW_NUMBER() OVER (
         PARTITION BY department
         ORDER BY amount DESC, id ASC
       ) AS row_num
FROM sales;

Nếu hai dòng có cùng amount mà truy vấn chỉ sort theo amount, database được phép đặt chúng theo thứ tự không ổn định. Thêm khóa duy nhất như id làm tie-breaker giúp pagination, deduplication và test cho kết quả lặp lại được.

4. ROW_NUMBER, RANK và DENSE_RANK

Hàm	Điểm số 100, 100, 90	Semantics
`ROW_NUMBER()`	1, 2, 3	Mỗi dòng có số riêng, kể cả đồng hạng
`RANK()`	1, 1, 3	Đồng hạng và để khoảng trống sau tie
`DENSE_RANK()`	1, 1, 2	Đồng hạng nhưng không để khoảng trống

SELECT employee_id, department, revenue,
       ROW_NUMBER() OVER w AS row_num,
       RANK()       OVER w AS rank_num,
       DENSE_RANK() OVER w AS dense_rank_num
FROM employee_sales
WINDOW w AS (
  PARTITION BY department
  ORDER BY revenue DESC
);

Named window làm truy vấn dễ đọc khi nhiều hàm dùng cùng partition/order. Cú pháp và khả năng mở rộng named window cần được kiểm tra theo database cụ thể.

5. Top-N theo từng nhóm

Không thể dùng window function trực tiếp trong WHERE ở nhiều hệ quản trị vì WHERE được xử lý trước bước window. Hãy tính thứ hạng trong CTE hoặc subquery rồi lọc ở lớp ngoài:

WITH ranked AS (
  SELECT id, department, amount,
         ROW_NUMBER() OVER (
           PARTITION BY department
           ORDER BY amount DESC, id ASC
         ) AS rn
  FROM sales
)
SELECT id, department, amount
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

Dùng ROW_NUMBER nếu cần đúng ba dòng mỗi nhóm. Dùng RANK hoặc DENSE_RANK nếu nghiệp vụ muốn giữ tất cả dòng đồng hạng, chấp nhận số dòng có thể vượt N.

6. Xóa bản ghi trùng bằng ROW_NUMBER

Một mẫu phổ biến là giữ bản ghi mới nhất theo khóa nghiệp vụ:

WITH marked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY updated_at DESC, id DESC
         ) AS rn
  FROM customers
)
SELECT *
FROM marked
WHERE rn = 1;

Trước khi biến truy vấn thành DELETE, hãy chạy dạng SELECT, review số dòng, giữ backup và xử lý concurrent write. Window function chỉ đánh dấu theo snapshot truy vấn; nó không tự tạo unique constraint ngăn dữ liệu trùng quay lại.

7. Tổng lũy kế với frame tường minh

SELECT id, account_id, posted_at, amount,
       SUM(amount) OVER (
         PARTITION BY account_id
         ORDER BY posted_at, id
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND CURRENT ROW
       ) AS running_total
FROM ledger;

ROWS đếm dòng vật lý theo thứ tự window. Viết frame tường minh tránh phụ thuộc vào default và làm rõ mong muốn: từ đầu partition đến dòng hiện tại. id được thêm vào order để các giao dịch cùng timestamp vẫn có thứ tự xác định.

8. Bẫy RANGE và các dòng peer

Khi có ORDER BY nhưng không chỉ rõ frame, nhiều database dùng default gần với RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Với RANGE, các dòng có cùng giá trị order là peers và có thể cùng xuất hiện trong frame. Tổng lũy kế vì thế có thể “nhảy” qua cả nhóm tie:

-- Có thể cộng cả các dòng cùng posted_at một lúc
SUM(amount) OVER (ORDER BY posted_at)

-- Tiến từng dòng theo thứ tự hoàn chỉnh
SUM(amount) OVER (
  ORDER BY posted_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

Không có lựa chọn đúng cho mọi bài toán. Báo cáo theo ngày có thể muốn các peer cùng ngày nhận một tổng giống nhau; sổ cái theo giao dịch lại thường muốn tiến từng dòng.

9. Moving average bằng frame trượt

Trung bình của dòng hiện tại và sáu dòng trước:

SELECT measured_at, value,
       AVG(value) OVER (
         ORDER BY measured_at
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7_rows
FROM measurements;

Đây là bảy dòng, không chắc là bảy ngày. Nếu thiếu ngày hoặc có nhiều mẫu mỗi ngày, semantics khác “trung bình bảy ngày”. Khi cần khoảng thời gian, hãy aggregate về một dòng mỗi ngày trước hoặc dùng frame theo range mà database hỗ trợ với kiểu ngày.

10. LAG và LEAD để so với dòng lân cận

SELECT account_id, posted_at, balance,
       LAG(balance) OVER (
         PARTITION BY account_id
         ORDER BY posted_at, id
       ) AS previous_balance,
       balance - LAG(balance) OVER (
         PARTITION BY account_id
         ORDER BY posted_at, id
       ) AS change_amount
FROM daily_balances;

LAG đọc dòng trước, LEAD đọc dòng sau theo thứ tự window. Dòng đầu/cuối thường nhận NULL nếu không cung cấp default. Đừng thay NULL bằng 0 nếu 0 mang ý nghĩa nghiệp vụ khác “không có dòng trước”.

11. FIRST_VALUE và LAST_VALUE dễ gây bất ngờ

LAST_VALUE tôn trọng frame. Với frame mặc định kết thúc ở current row, “last” có thể chính là dòng hiện tại thay vì dòng cuối partition:

SELECT employee_id, department, salary,
       LAST_VALUE(salary) OVER (
         PARTITION BY department
         ORDER BY salary, employee_id
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND UNBOUNDED FOLLOWING
       ) AS highest_salary
FROM employees;

Nếu mục tiêu chỉ là mức cao nhất, MAX(salary) OVER (PARTITION BY department) thường rõ hơn. Chỉ dùng FIRST_VALUE/LAST_VALUE khi cần giá trị gắn với vị trí trong order và đã xác định frame.

12. Hai ORDER BY phục vụ hai mục đích

ORDER BY trong OVER điều khiển phép tính; ORDER BY cuối truy vấn điều khiển thứ tự hiển thị:

SELECT id, department, amount,
       RANK() OVER (
         PARTITION BY department
         ORDER BY amount DESC
       ) AS department_rank
FROM sales
ORDER BY department ASC, id ASC;

Kết quả có thể hiển thị theo ID dù rank được tính theo amount. Không nên dựa vào thứ tự phát sinh ngẫu nhiên từ bước window; muốn output ổn định phải có ORDER BY ngoài cùng.

13. Hiệu năng và index

Window function thường cần sort theo partition/order. Với bảng lớn, bước sort có thể dùng nhiều bộ nhớ hoặc spill xuống disk. Hãy dùng EXPLAIN và dữ liệu gần production để kiểm tra.

Lọc sớm bằng WHERE khi đúng semantics để giảm số dòng đi vào window.
Index bắt đầu bằng cột partition rồi order có thể hỗ trợ một số plan, nhưng không bảo đảm bỏ sort.
Tránh tính nhiều window với order hoàn toàn khác nhau nếu không cần.
Chỉ select các cột cần thiết để giảm row width trong sort.
Không pagination bằng row number trên tập thay đổi mà thiếu snapshot/cursor ổn định.

Tối ưu phải dựa trên plan thực tế của đúng hệ quản trị và phiên bản; cú pháp giống nhau không đồng nghĩa optimizer hành xử giống nhau.

14. Kiểm thử truy vấn window

Tạo dữ liệu tie để phân biệt ROW_NUMBER, RANK và DENSE_RANK.
Tạo hai partition để chắc chắn state không rò giữa nhóm.
Test NULL, timestamp trùng và partition chỉ có một dòng.
Test thứ tự ổn định bằng unique tie-breaker.
Với running total, so sánh ROWS và default frame trên dữ liệu peer.
Kiểm tra top-N có giữ hay loại toàn bộ tie theo yêu cầu nghiệp vụ.
Chạy EXPLAIN với cardinality thực tế, không chỉ fixture nhỏ.

Checklist lựa chọn

Bài toán	Hàm/mẫu
Số thứ tự duy nhất	`ROW_NUMBER` với tie-breaker
Xếp hạng có đồng hạng	`RANK` hoặc `DENSE_RANK`
Top-N mỗi nhóm	CTE + ranking + lọc lớp ngoài
Tổng lũy kế từng dòng	`SUM` + frame `ROWS` tường minh
So sánh kỳ trước/sau	`LAG` / `LEAD`
Trung bình trượt	`AVG` + frame phù hợp đơn vị nghiệp vụ

Tài liệu tham khảo

Kết luận

Window functions giúp SQL diễn đạt xếp hạng, top-N, chênh lệch và thống kê lũy kế mà không đánh mất chi tiết từng dòng. Muốn dùng đáng tin cậy, hãy phân biệt partition với frame, thêm tie-breaker duy nhất, chỉ rõ ROWS khi cần tiến từng bản ghi và lọc kết quả window qua CTE hoặc subquery. Sau cùng, kiểm thử dữ liệu tie và đọc execution plan—vì truy vấn đúng trên tập nhỏ vẫn có thể không ổn định hoặc tốn kém ở production.