iT邦幫忙

DAY 16
13

T-SQL 應用之美系列 第 16

如何使用次序函數於查詢資料時,針對某個欄位進行排名(上)

不是每個資料表都會有編號的欄位,這次要分享如何使用 Microsoft SQL Server 2005 新增的**「次序函數」**,在查詢資料的同時,針對某個欄位進行排名。

附註:

  1. Microsoft SQL Server 2008 已經把**「次序函數」改名成「排名函數」**。
  2. 因為文章內容過長,會分成上下兩篇
    從 Microsoft SQL Server 2005 開始提供了全新的**「次序函數」來傳回資料分割(Partition)中,某個欄位的一個次序值,共有下列 4 種「次序函數」**:
    ●ROW_NUMBER
    ●RANK
    ●DENSE_RANK
    ●NTILE

這 4 種次序函數的定義,在 Microsoft SQL Server 2005 線上叢書已經有相關的範例與定義說明,所以想要瞭解的邦友,請自行閱讀相關的超連結,畢竟這次的主題是「如何使用次序函數於查詢資料時,針對某個欄位進行排名」。
為了方便解說,先在 AdventureWorks 資料庫中,建立在「使用流程控制:BEGIN...END 與 RETURN」討論過的自訂計算年齡函數:

USE AdventureWorks
GO

-- 建立自訂函數
CREATE FUNCTION dbo.fn_GetAge(
@myDate datetime)
RETURNS int
AS
BEGIN

-- 宣告變數
DECLARE @age int, @day datetime

-- 以「年」為單位計算出年齡
SET @age = DATEDIFF(yy, @myDate, getdate()) -
		CASE WHEN @day < DATEADD(yy, DATEDIFF(yy, @myDate, @day), @myDate)
			THEN 1
			ELSE 0
		END
RETURN @age

END
GO

接著要從 HumanResources.Employee 資料表中,透過上面建立的 fn_GetAge 函數,依照年齡由小至大,產生排名的欄位:

SELECT 依照年齡排名 = ROW_NUMBER() OVER (ORDER BY dbo.fn_GetAge(BirthDate)),
	年齡 = dbo.fn_GetAge(BirthDate),
	員工編號 = EmployeeID,
	性別 = CASE Gender
			WHEN 'M' THEN N'男'
			WHEN 'F' THEN N'女'
		END,
	婚姻 = CASE MaritalStatus
			WHEN 'S' THEN N'單身'
			WHEN 'M' THEN N'已婚'
		END
FROM HumanResources.Employee

執行結果的部分畫面:

從結果中,可以看到排名是依照年齡來排的,會從 1 開始排起,如果年齡一樣時,排名並不會一樣。

如果不想依照年齡來排,只是想要有一個編號的欄位,就可以把 ORDER BY 裡面的 dbo.fn_GetAge(BirthDate) 改成一個永遠會成立且只會傳回 1 個結果的查詢,例如改成 SELECT 0:

SELECT 編號 = ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
	年齡 = dbo.fn_GetAge(BirthDate),
	員工編號 = EmployeeID,
	性別 = CASE Gender
			WHEN 'M' THEN N'男'
			WHEN 'F' THEN N'女'
		END,
	婚姻 = CASE MaritalStatus
			WHEN 'S' THEN N'單身'
			WHEN 'M' THEN N'已婚'
		END
FROM HumanResources.Employee

執行結果的部分畫面:

大家有沒有發現到結果的第 1 個欄位「編號」會跟第 3 個「員工編號」欄位的值一樣?所以說,如果資料表沒有所謂的自動編號欄位時,就可以用這個方法來產生一個自動編號欄位。

如果我們希望先依照性別進行男與女的資料分組,然後再依照年齡來排名,只要在 ORDER BY 的前面,多加入 PARTITION BY Gender:

SELECT 依照性別分組後的年齡排名 = ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY dbo.fn_GetAge(BirthDate)),
	年齡 = dbo.fn_GetAge(BirthDate),
	員工編號 = EmployeeID,
	性別 = CASE Gender
			WHEN 'M' THEN N'男'
			WHEN 'F' THEN N'女'
		END,
	婚姻 = CASE MaritalStatus
			WHEN 'S' THEN N'單身'
			WHEN 'M' THEN N'已婚'
		END
FROM HumanResources.Employee

執行結果的部分畫面:

PARTITION BY 的欄位可以有多個,例如先依照性別分組,再依照婚姻來分組:

SELECT 依序以性別與婚姻分組的年齡排名 = ROW_NUMBER() OVER (PARTITION BY Gender, MaritalStatus ORDER BY dbo.fn_GetAge(BirthDate)),
	年齡 = dbo.fn_GetAge(BirthDate),
	員工編號 = EmployeeID,
	性別 = CASE Gender
			WHEN 'M' THEN N'男'
			WHEN 'F' THEN N'女'
		END,
	婚姻 = CASE MaritalStatus
			WHEN 'S' THEN N'單身'
			WHEN 'M' THEN N'已婚'
		END
FROM HumanResources.Employee

執行結果的部分畫面:



如果當年齡一樣時,排名也要一樣的話,要把第一個程式碼中,原本的 ROW_NUMBER() 換成 RANK() 就可以了:

SELECT [依照年齡排名(年齡一樣排名也會一樣)] = RANK() OVER (ORDER BY dbo.fn_GetAge(BirthDate)),
	年齡 = dbo.fn_GetAge(BirthDate),
	員工編號 = EmployeeID,
	性別 = CASE Gender
			WHEN 'M' THEN N'男'
			WHEN 'F' THEN N'女'
		END,
	婚姻 = CASE MaritalStatus
			WHEN 'S' THEN N'單身'
			WHEN 'M' THEN N'已婚'
		END
FROM HumanResources.Employee

執行結果的部分畫面:

從執行結果,可以看出當排名一樣時,中間會遺漏幾個排名,這個常見於學校的考試排名。但是,如果不希望遺漏部分的排名,要把 RANK() 換成 DENSE_RANK():

SELECT [依照年齡排名(年齡一樣排名也會一樣)] = DENSE_RANK() OVER (ORDER BY dbo.fn_GetAge(BirthDate)),
	年齡 = dbo.fn_GetAge(BirthDate),
	員工編號 = EmployeeID,
	性別 = CASE Gender
			WHEN 'M' THEN N'男'
			WHEN 'F' THEN N'女'
		END,
	婚姻 = CASE MaritalStatus
			WHEN 'S' THEN N'單身'
			WHEN 'M' THEN N'已婚'
		END
FROM HumanResources.Employee

執行結果的部分畫面:

另外的內容,請看下篇。


上一篇
如何在 T-SQL 中宣告變數
下一篇
如何使用次序函數於查詢資料時,針對某個欄位進行排名(下)
系列文
T-SQL 應用之美30

尚未有邦友留言

立即登入留言