Các hàm Ranking,Thứ tự theo record mới trong SQL Server 2k5
Cùng với SQL Server 2005, Microsoft đã giới thiệu một số tính năng mới và những tính năng này sẽ giúp cho chuyên viên về DBA hay SQL Server dễ dàng hơn trong việc viết mã và duy trì cơ sở dữ liệu SQL Server. Bài này sẽ thảo luận về các hàm ranking mới được cung cấp trong SQL Server 2005. Các tính năng mới đó sẽ giúp bạn dễ dàng viết mã T-SQL để kết hợp xếp loại được tập hợp kết quả của bạn. Bài sẽ hướng dẫn từng phần trong các hàm ranking mới và cung cấp một số ví dụ nhằm minh họa hoạt động của hàm.
Các 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…
Dữ liệu chạy thử cho các ví dụ
Để có một vài ví dụ cho từng hàm ranking, tôi cần thiết lập một số dữ liệu chạy thử. Trong dữ liệu chạy thử, tôi sử dụng một bảng “Person” khá đơn giản. Bảng sẽ bao gồm 3 cột “FirstName”, “Age” và “Gender”. Đoạn mã dưới nhằm tạo ra và ghi lại dữ liệu chạy thử vào file.
SET NOCOUNT ON
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')
Hàm ROW_NUMBER
Hàm đầu tiên tôi muốn nói tới là 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ả. Hàm ROW_NUMBER sẽ có cú pháp sau:
ROW_NUMBER ( ) OVER ( [ ] )
Trong đó:
là cột hay tập hợp các cột được sử dụng để quyết định việc gộp nhóm cho hàm ROW_NUMBER áp dụng cho việc đánh số tuần tự.
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)
Để hiểu thêm về cách sử dụng hàm ROW_NUMBER, 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
Bạn có thể thấy tôi đã đánh số liên tục cho toàn bộ các dòng trong bảng Person bắt đầu từ số 1, và tập hợp kết quả được sắp xếp theo cột Age. Sự sắp xếp này được hoàn thiện là do tiêu chuẩn “ORDER BY Age” trong mệnh đề ORDER BY của hàm ROW_NUMBER.
Giả sử bạn không muốn tập hợp kết quả của bạn được sắp xếp mà muốn đưa bảng trở lại sắp xếp theo số bản ghi của từng dòng. Hàm ROW_NUMBER lại luôn yêu cầu phải có mệnh đề ORDER BY, vậy bạn cần phải đưa một giá trị nào đó vào trong mệnh đề này. Trong hàm truy vấn bên dưới tôi đã chỉ định “SELECT 1” vào trong mệnh đề ORDER BY, điều này sẽ chỉ trả lại kết quả là bảng như đã lưu trữ ban đầu và tất nhiên cách đánh số tuần tự vẫn bắt đầu từ 1:
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:
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
Hàm ROW_NUMBER không chỉ cho phép bạn sắp xếp toàn bộ tập hợp dòng mà còn có thể sử dụng mệnh đề PARTITION để lọc ra nhóm dòng cần đánh số. Các dòng sẽ được đánh số tuần tự trong từng giá trị PARTITION độc nhất. Các dãy số được đánh sẽ luôn bắt đầu từ 1 cho từng giá trị PARTITION mới trong tập hợp bản ghi của bạn. Hãy xem hàm truy vấn dưới đây
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:
Partition by Gender FirstName Age Gender
-------------------- ---------- ----------- ------
1 Doris 6 F
2 Mary 11 F
3 Sherry 11 F
4 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
5 Marty 23 M
6 Frank 38 M
7 John 40 M
Trong ví dụ này tôi đã phân vùng bởi Gender và sắp xếp theo Age. Thực hành theo ví dụ này sẽ cho phép tôi đánh số tuần tự các bản ghi là Female trong bảng Person theo độ tuổi, và sau đó việc đánh số sẽ bắt đầu lại với nhóm là Male.
Các 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…
Dữ liệu chạy thử cho các ví dụ
Để có một vài ví dụ cho từng hàm ranking, tôi cần thiết lập một số dữ liệu chạy thử. Trong dữ liệu chạy thử, tôi sử dụng một bảng “Person” khá đơn giản. Bảng sẽ bao gồm 3 cột “FirstName”, “Age” và “Gender”. Đoạn mã dưới nhằm tạo ra và ghi lại dữ liệu chạy thử vào file.
SET NOCOUNT ON
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')
Hàm ROW_NUMBER
Hàm đầu tiên tôi muốn nói tới là 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ả. Hàm ROW_NUMBER sẽ có cú pháp sau:
ROW_NUMBER ( ) OVER ( [ ] )
Trong đó:
là cột hay tập hợp các cột được sử dụng để quyết định việc gộp nhóm cho hàm ROW_NUMBER áp dụng cho việc đánh số tuần tự.
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)
Để hiểu thêm về cách sử dụng hàm ROW_NUMBER, 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
Bạn có thể thấy tôi đã đánh số liên tục cho toàn bộ các dòng trong bảng Person bắt đầu từ số 1, và tập hợp kết quả được sắp xếp theo cột Age. Sự sắp xếp này được hoàn thiện là do tiêu chuẩn “ORDER BY Age” trong mệnh đề ORDER BY của hàm ROW_NUMBER.
Giả sử bạn không muốn tập hợp kết quả của bạn được sắp xếp mà muốn đưa bảng trở lại sắp xếp theo số bản ghi của từng dòng. Hàm ROW_NUMBER lại luôn yêu cầu phải có mệnh đề ORDER BY, vậy bạn cần phải đưa một giá trị nào đó vào trong mệnh đề này. Trong hàm truy vấn bên dưới tôi đã chỉ định “SELECT 1” vào trong mệnh đề ORDER BY, điều này sẽ chỉ trả lại kết quả là bảng như đã lưu trữ ban đầu và tất nhiên cách đánh số tuần tự vẫn bắt đầu từ 1:
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:
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
Hàm ROW_NUMBER không chỉ cho phép bạn sắp xếp toàn bộ tập hợp dòng mà còn có thể sử dụng mệnh đề PARTITION để lọc ra nhóm dòng cần đánh số. Các dòng sẽ được đánh số tuần tự trong từng giá trị PARTITION độc nhất. Các dãy số được đánh sẽ luôn bắt đầu từ 1 cho từng giá trị PARTITION mới trong tập hợp bản ghi của bạn. Hãy xem hàm truy vấn dưới đây
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:
Partition by Gender FirstName Age Gender
-------------------- ---------- ----------- ------
1 Doris 6 F
2 Mary 11 F
3 Sherry 11 F
4 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
5 Marty 23 M
6 Frank 38 M
7 John 40 M
Trong ví dụ này tôi đã phân vùng bởi Gender và sắp xếp theo Age. Thực hành theo ví dụ này sẽ cho phép tôi đánh số tuần tự các bản ghi là Female trong bảng Person theo độ tuổi, và sau đó việc đánh số sẽ bắt đầu lại với nhóm là Male.
