RANKING in SQL (ROW_NUMBER, RANK, DENSE_RANK,NTILE)

Hàm Ranking là gì?

Các hàm Ranking cho phép bạn có thể đánh số liên tục (xếp loại) cho các tập hợp kết quả. Các hàm này có thể được sử dụng để cung cấp số thứ tự trong hệ thống đánh số tuần tự khác nhau. Có thể hiểu đơn giản như sau: bạn có từng con số nằm trên từng dòng liên tục, tại dòng thứ nhất xếp loại số 1, dòng thứ 2 xếp loại số là 2… Bạn có thể sử dụng hàm ranking theo các nhóm số tuần tự, mỗi một nhóm sẽ được đánh số theo lược đồ 1,2,3 và nhóm tiếp theo lại bắt đầu bằng 1,2,3…
Chúng ta bắt đầu xem xét cách hàm trong sql hổ trợ từ 2005

Dữ liệu thử:

CREATE TABLE Person(    
     FirstName VARCHAR(10),
     Age INT,
     Gender CHAR(1)
)

INSERT INTO Person VALUES ('Ted', 23, 'M')
INSERT INTO Person VALUES ('John', 40, 'M')
INSERT INTO Person VALUES ('George', 6, 'M')
INSERT INTO Person VALUES ('Mary', 11, 'F')
INSERT INTO Person VALUES ('Sam', 17, 'M')
INSERT INTO Person VALUES ('Doris', 6, 'F')
INSERT INTO Person VALUES ('Frank', 38, 'M')
INSERT INTO Person VALUES ('Larry', 5, 'M')
INSERT INTO Person VALUES ('Sue', 29, 'F')
INSERT INTO Person VALUES ('Sherry', 11, 'F')
INSERT INTO Person VALUES ('Marty', 23, 'F')

1. Hàm ROW_NUMBER

Hàm này trả lại một dãy số tuần tự bắt đầu từ 1 cho mỗi dòng hay nhóm trong tập hợp kết quả.
cú pháp:

ROW_NUMBER () OVER ( [<partition_by_clause> ] <order_by_clause>)

<partition_by_clause>: đánh số theo thứ tự sắp xếp này. cú pháp giống order by

<order_by_clause>: Dùng phân nhóm để đánh số thứ tự. cú pháp

PARTITION BY <Tên cột>

Để hiểu thêm về cách sử dụng hàm ROW_NUMBER, các ví dụ dưới sẽ đánh số liên tục cho tất cả các dòng trong bảng Person và sắp xếp chúng theo trường Age

SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],FirstName,Age
FROM Person

Và đây là tập hợp kết quả mã T-SQL trên:

Row Number by Age FirstName Age
-------------------- ---------- ------
1 Larry 5
2 Doris 6
3 George 6
4 Mary 11
5 Sherry 11
6 Sam 17
7 Ted 23
8 Marty 23
9 Sue 29
10 Frank 38
11 John 40

---------- ----------- ---------------

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],FirstName,Age
FROM Person

Đây là tập hợp kết quả khi chạy hàm truy vấn trên: (với SELECT 1 ta sẽ đánh số theo thứ tự insert vào).

Row Number by Record FirstName Age
-------------------- ---------- ------
1 Ted 23
2 John 40
3 George 6
4 Mary 11
5 Sam 17
6 Doris 6
7 Frank 38
8 Larry 5
9 Sue 29
10 Sherry 11
11 Marty 23

---------- ----------- ---------------

SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],FirstName,Age,Gender
FROM Person

Khi chạy truy vấn trên, tập hợp kết quả sẽ ra như sau, đánh số theo nhóm Gender:

Partition by Gender FirstName Age Gender
-------------------- ---------- ----------- 

1 Doris 6 F
2 Mary 11 F
3 Sherry 11 F

4 Marty 23 F
5 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M

5 Frank 38 M
6 John 40 M

---------- ----------- --------------------

2. Hàm RANK

Đây là một hàm dùng để xếp hạng, trong ROW_NUMBER nếu 2 giá trị (trong cột sắp xếp) bằng nhau nhưng vẫn có số thứ tự khác nhau, trong RANK thì hai giá trị trùng nhau sẽ được xếp hạng giống nhau. Theo cách xếp hạng thông thường.
Hàm RANK có cú pháp như sau:

RANK() OVER ([<partition_by_clause>] <order_by_clause>)

Trong đó:
<partition_by_clause> : là một cột hay tập hợp các cột được sử dụng để quyết đinh việc đánh số liên tục trong hàm RANK, phân nhóm.

<order_by_clause> : là một cột hay tập hợp các cột được sử dụng để sắp xếp tập hợp kết quả trong nhóm (partition)

Hàm RANK sẽ đánh số liên tục một tập hợp bản ghi nhưng khi có 2 dòng có cùng giá trị sắp xếp thì hàm sẽ đánh giá là cùng bậc giá trị. Giá trị xếp loại vẫn sẽ tăng kể cả khi có 2 dòng cùng giá trị, vì vậy khi đánh giá một giá trị sắp xếp tiếp theo thì số thứ tự vẫn tiếp tục được đánh nhưng sẽ tăng thêm 1 giá trị vào các dòng tiếp theo trong tập hợp.

Đây là ví dụ của hàm rank trong tập hợp bản ghi sắp xếp theo Age:

SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],FirstName,Age
FROM Person

Và kết quả trả về (lưu ý các mẩu tin trùng):

Rank by Age FirstName Age
-------------------- ---------- ------
1 Larry 52 Doris 6
2 George 64 Mary 11
4 Sherry 11
6 Sam 177 Ted 23
7 Marty 239 Sue 29
10 Frank 38
11 John 40

---------- ----------- --------------------

Khi bạn phân nhóm theo Gender để xếp hạng theo Age

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],FirstName,Age,Gender
FROM Person

Đây là kết quả khi chạy các hàm truy vấn trên:

Partition by Gender FirstName Age Gender
-------------------- ---------- ----------- ------
1 Doris 6 F
2 Mary 11 F
2 Sherry 11 F

4 Marty 23 F
5 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
5 Frank 38 M
6 John 40 M

---------- ----------- --------------------

3. Hàm DENSE_RANK
Hàm DENSE_RANK cũng giống như hàm RANK, tuy vậy, hàm này không cung cấp khoảng cách giữa các số xếp loại. Thay vào đó, hàm này sẽ xếp loại liên tục cho từng giá trị ORDER BY cụ thể. Với hàm DENSE_RANK, kể cả khi có hai dòng có cùng giá trị xếp loại thì dòng tiếp theo vẫn chỉ tăng thêm một giá trị so với dòng trên. Hàm DENSE_RANK có cú pháp như hàm RANK.

SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age],FirstName,Age
FROM Person

Đoạn mã trên sẽ xuất ra như sau:

Dense Rank by Age FirstName Age
-------------------- ---------- -----------
1 Larry 5
2 Doris 6
2 George 6
3 Mary 11
3 Sherry 11
4 Sam 17
5 Ted 23
5 Marty 23
6 Sue 29
7 Frank 38
8 John 40---------- ----------- --------------------

Như bạn thấy các số trong cột “Dense Rank By Age” vẫn đảm bảo tính liên tục, không hề bị ngắt quãng kể cả khi có hai dòng cùng giá trị ORDER BY và giá trị xếp loại như Ted và Marty.
4. Hàm NTILE
Hàm cuối cùng là hàm NTILE. Đây là hàm được sử dụng để chia các mẩu tin ra theo một số nhóm nhất định . Hàm NTILE cũng sử dụng cú pháp như các hàm ranking khác.

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)

integer_expression: là số nhóm cần phải chia, lưu ý số nhóm này khổng thể lấy từ FROM

Trong ví dụ đầu của hàm này, tôi sẽ nhóm các bản ghi trong bảng Person thành 3 nhóm khác nhau. Tôi muốn các nhóm này dựa trên cột Age. Để làm được điều này, tôi sẽ chạy T-SQL sau:

SELECT FirstName,Age,NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
FROM Person

Đây là tập hợp kết quả của tôi từ câu lệnh T-SQL trên:

Age Groups FirstName Age
---------- ----------- --------------------
1 Larry 5
1 Doris 6
1 George 6
1 Mary 11
2 Sherry 11
2 Sam 17
2 Ted 23
2 Marty 23
3 Sue 29
3 Frank 38
3 John 40---------- ----------- --------------------

Trong tập hợp kết quả đã có ở trên với 3 nhóm Age khác nhau. Hàm NTILE chỉ có tác dụng chia đều số lượng các bản ghi và đưa vào từng nhóm số. Sử dụng hàm NTILE cho từng bản ghi trong một nhóm sẽ đưa gia các xếp loại giống nhau.

SELECT NTILE(3) OVER (PARTITION BY Gender ORDER BY Age) AS [Age Groups], FirstName,Age, Gender
FROM Person

Partition by Gender FirstName Age Gender
-------------------- ---------- ----------- ------
1 Doris 6 F
1 Mary 11 F
2 Sherry 11 F

2 Marty 23 F
3 Sue 29 F
1 Larry 5 M
1 George 6 M
2 Sam 17 M
2 Ted 23 M

3 Frank 38 M
3 John 40 M
---------- ----------- --------------------