Các window function cơ bản: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

Ở bài trước tụi mình đã hiểu tại sao cần window function. Giờ thì cùng xem chi tiết từng function và kết quả của chúng. Còn cú pháp chi tiết thì để bài sau nha.

Hàm ROW_NUMBER()

Hàm ROW_NUMBER() trả về số thứ tự duy nhất cho mỗi dòng trong một cửa sổ (window). Nói đơn giản là đánh số thứ tự cho từng dòng theo thứ tự được xác định trong ORDER BY.

Cú pháp:

ROW_NUMBER() OVER ([PARTITION BY column] ORDER BY column)

Trong đó:

• PARTITION BY column (không bắt buộc): chia dữ liệu thành các nhóm nhỏ. Nếu bỏ qua thì đánh số toàn bộ bảng luôn.
• ORDER BY column: xác định thứ tự dòng để đánh số.

Ví dụ. Đánh số thứ tự dòng trong bảng

Cùng xem bảng students chứa thông tin sinh viên và điểm số của họ nha.

SELECT * FROM students;

Giờ tụi mình sẽ đánh số thứ tự cho các dòng theo thứ tự giảm dần của điểm (score):

SELECT
    name, 
    score, 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

Kết quả:

Mỗi dòng đều có số thứ tự riêng — dựa trên sắp xếp giảm dần theo điểm.

Đây là thao tác đơn giản mà cực kỳ mạnh — thêm số thứ tự vào kết quả query. Nếu chỉ dùng SELECT truyền thống thì không làm được đâu, phải dùng window function mới được.

Hàm RANK()

Hàm RANK() khá giống ROW_NUMBER(), nhưng nó để ý đến giá trị giống nhau. Nếu các dòng có cùng giá trị khi sắp xếp, chúng sẽ nhận cùng một rank, và rank tiếp theo sẽ bị nhảy số.

Cú pháp:

RANK() OVER ([PARTITION BY column] ORDER BY column)

Ví dụ. Xếp hạng sinh viên theo điểm số

Cùng dùng RANK() cho dữ liệu trên nha:

SELECT
    name, 
    score, 
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Kết quả:

Ở đây các dòng có cùng điểm (95 và 87) sẽ nhận cùng một rank, còn rank tiếp theo thì bị nhảy số.

Hàm DENSE_RANK()

DENSE_RANK() giống RANK(), nhưng không nhảy số rank. Nghĩa là nếu có dòng trùng nhau, rank tiếp theo chỉ tăng lên 1 thôi.

Cú pháp:

DENSE_RANK() OVER ([PARTITION BY column] ORDER BY column)

Ví dụ. Xếp hạng liên tục

Dùng DENSE_RANK() cho dữ liệu trên nha:

SELECT
    name, 
    score, 
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

Kết quả:

Ở đây, khác với RANK(), rank chỉ tăng đều, không bị nhảy số.

Hàm NTILE()

Hàm NTILE() chia các dòng thành các nhóm đều nhau (quantile) và gán số nhóm cho từng dòng.

Cú pháp:

NTILE(n) OVER ([PARTITION BY column] ORDER BY column)

• n: số nhóm muốn chia dữ liệu ra.

Ví dụ. Chia sinh viên thành 3 nhóm

Cùng chia sinh viên thành 3 nhóm theo điểm giảm dần nha:

SELECT
    name, 
    score, 
    NTILE(3) OVER (ORDER BY score DESC) AS group_num
FROM students;

Kết quả:

Lưu ý: nếu không chia đều được các dòng vào nhóm, thì nhóm đầu sẽ có nhiều dòng hơn. Ở ví dụ này, hai nhóm đầu mỗi nhóm có hai dòng, nhóm cuối chỉ có một dòng thôi.

Khi nào dùng function nào?

• ROW_NUMBER(): khi cần đánh số thứ tự duy nhất cho từng dòng theo thứ tự sắp xếp.
• RANK(): khi muốn xếp hạng có xét giá trị trùng và nhảy số rank tiếp theo.
• DENSE_RANK(): khi muốn xếp hạng có xét giá trị trùng nhưng không nhảy số rank.
• NTILE(): Khi cần chia đều các dòng thành các nhóm.

Tất cả mấy function này giúp bạn phân tích dữ liệu ở một level hoàn toàn mới luôn. Dùng chúng khi bạn cần linh hoạt trong việc tính số thứ tự hoặc chia nhóm dữ liệu nha.