SQL Server中的排名函數(shù)與分析函數(shù)詳解
一、排名開窗函數(shù)概述
SQL Server的排名函數(shù)是對查詢的結(jié)果進(jìn)行排名和分組，TSQL共有4個排名函數(shù)，分別是：ROW_NUMBER、RANK、DENSE_RANK和NTILE。
他們和OVER()函數(shù)搭配使用，按照特定的順序排名。
排名開窗函數(shù)可以單獨(dú)使用ORDER BY 語句，也可以和PARTITION BY同時使用。
- PARTITION BY用于將結(jié)果集進(jìn)行分組，開窗函數(shù)應(yīng)用于每一組。
- ODER BY 指定排名開窗函數(shù)的順序。在排名開窗函數(shù)中必須使用ORDER BY語句。
1、ROW_NUMBER：行號
為每一組的行按順序生成一個唯一的序號。
序列從1開始，按照順序依次 +1 遞增。分組內(nèi)序列的最大值就是該分組內(nèi)的行的數(shù)目。
ROW_NUMBER ( ) OVER ( [ PARTITION_BY_clause ] order_by_clause )
2、RANK：排名
也為每一組的行生成一個序號，但如果按照ORDER BY的排序，如果有相同的值會生成相同的序號，并且接下來的序號是不連續(xù)的。
例如，班級中，A，B分?jǐn)?shù)都是100分，C的分?jǐn)?shù)是90分，那么A和B的排名是1，C的排名是3。
3、DENSE_RANK：密集排名
和RANK（排名）類似，不同的是如果有相同的序號，那么接下來的序號不會間斷。
例如，班級中，A，B分?jǐn)?shù)都是100分，C的分?jǐn)?shù)是90分，那么A和B的排名是1，C的排名是2。
4、NTILE ：分組排名
按照指定的數(shù)目將數(shù)據(jù)進(jìn)行分組，并為每一組生成一個序號。
特別地，NTILE(4) 把一個分組分成4份，叫做Quartile。例如，以下腳本顯示各個排名函數(shù)的執(zhí)行結(jié)果：
select Department
,LastName
,Rate
,row_number() over(order by Rate) as [row number]
,rank() over(order by rate) as rate_rank
,dense_rank() over(order by rate) as rate_dense_rank
,ntile(4) over(order by rate) as quartile_by_rate
from #data
二、分析函數(shù)
分析函數(shù)基于分組，計算分組內(nèi)數(shù)據(jù)的聚合值，經(jīng)常會和窗口函數(shù)OVER()一起使用，使用分析函數(shù)可以很方便地計算同比和環(huán)比，獲得中位數(shù)，獲得分組的最大值和最小值。
分析函數(shù)和聚合函數(shù)不同，不需要GROUP BY子句，對SELECT子句的結(jié)果集，通過OVER()子句分組。
注意：distinct子句的執(zhí)行順序是在分析函數(shù)之后。
使用以下腳本插入示例數(shù)據(jù)：
;with cte_data as ( select 'Document Control' as Department,'Arifin' as LastName,17.78 as Rate union all select 'Document Control','Norred',16.82 union all select 'Document Control','Kharatishvili',16.82 union all select 'Document Control','Chai',10.25 union all select 'Document Control','Berge',10.25 union all select 'Information Services','Trenary',50.48 union all select 'Information Services','Conroy',39.66 union all select 'Information Services','Ajenstat',38.46 union all select 'Information Services','Wilson',38.46 union all select 'Information Services','Sharma',32.45 union all select 'Information Services','Connelly',32.45 union all select 'Information Services','Berg',27.40 union all select 'Information Services','Meyyappan',27.40 union all select 'Information Services','Bacon',27.40 union all select 'Information Services','Bueno ',27.40 ) select Department,LastName,Rate into #data from cte_data go
SQL Server中共有4類分析函數(shù)。
1、LAG和LEAD
在一次查詢中，對數(shù)據(jù)表進(jìn)行排序，把已排序的數(shù)據(jù)從上向下看作是一個序列，對當(dāng)前行而言，在序列上方的為后，在序列下方的為前。
在同一分組內(nèi)，對于當(dāng)前行：
- Lag()函數(shù):用于獲取從當(dāng)前行開始向后（或向上）計數(shù)的第N行。
- Lead()函數(shù):用于獲取從當(dāng)前行開始向前（或向下）計數(shù)的第N行。
LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause ) LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
參數(shù)注釋：
- sclar_expression：標(biāo)量表達(dá)式
- offset：默認(rèn)值是1，必須是正整數(shù)，對于LAG()函數(shù)表示從當(dāng)前行(current row)回退的行數(shù)，對于LEAD()表示從當(dāng)前行向前進(jìn)的行數(shù)。
- default ：當(dāng)offset超出分區(qū)范圍時要返回的值。 如果未指定默認(rèn)值，則返回NULL。 default可以是列，子查詢或其他表達(dá)式，但必須跟sclar_expression類型兼容。
結(jié)果日期，這兩個函數(shù)特別適合用于計算同比和環(huán)比。
select DepartMent ,LastName,Rate
,lag(Rate,1,0) over(partition by Department order by LastName) as LastRate
,lead(Rate,1,0) over(partition by Department order by LastName) as NextRate
from #data order by Department ,LastName按照DepartMent進(jìn)行分組，對Document Control這一小組進(jìn)行分析：
- 第一行，對于LastRate字段，向后不存在數(shù)據(jù)行，返回參數(shù)Default的值，字段NextRate的值是第二行的Rate字段的值。
- 第二行，LastRate是第一行的Rate字段的值，NextRate是第三行的Rate字段的值。對于中間行，依次類推。
- 最后一行，LastRate是倒數(shù)第二行的Rate字段的值，對于NextRate字段，由于最后一行向前不存在數(shù)據(jù)行，返回參數(shù)Default的值。
以下程序代碼用來示范如何透過 LAG 函數(shù)來計算每一列與前一列的 c2 字段相差幾天：
declare @t table
(
c1 int identity
,c2 date
)
insert into @t (c2)
select '20120101'
union all
select '20120201'
union all
select '20120110'
union all
select '20120221'
union all
select '20120121'
union all
select '20120203'
select c1,c2
,LAG(c2) OVER (ORDER BY c2) as previous_c2
,DateDiff(day,LAG(c2) OVER (ORDER BY c2),c2) as diff
from @t
order by c2
2、FIRST_VALUE和LAST_VALUE
SQL SERVER 2012引入的函數(shù)。
獲取分組內(nèi)排在最末尾的行和排在第一位的行：
LAST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause ) FIRST_VALUE ( [scalar_expression ] ) OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
例如：
select Department, LastName, Rate,
row_number() over (partition by Department order by LastName) as FIRSTVALUE,
first_value(Rate) over (partition by Department order by LastName rows between unbounded preceding and unbounded following) as FIRSTVALUE,
last_value(Rate) over (partition by Department order by LastName rows between unbounded preceding and unbounded following) as LASTVALUE
from #data
order by Department, LastName;
3、CUME_DIST 和PERCENT_RANK：累積分布和排名百分比
- CUME_DIST(） ：小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù)
- PERCENT_RANK() ：（分組內(nèi)當(dāng)前行的RANK值-1）/ （分組內(nèi)總行數(shù)-1），排名值是RANK()函數(shù)排序的結(jié)果值。
以下代碼，用于計算累積分布和排名百分比：
select Department,LastName ,Rate
,cume_dist() over(partition by Department order by Rate) as CumeDist
,percent_rank() over(partition by Department order by Rate) as PtcRank
,rank() over(partition by Department order by Rate asc) as rank_number
,count(0) over(partition by Department) as count_in_group
from #data
order by DepartMent
,Rate desc
解釋：
首先，NULL都會被當(dāng)作最小值。
1、cume_dist的計算方法：小于等于當(dāng)前行值的行數(shù)/總行數(shù)。
比如，第3行值為16.82，有4行的值小于等于16.82，本組總行數(shù)5行，因此CUME_DIST為4/5=0.8 。
再比如，第4行值為10.25，行值小于等于10.25的共2行，本組總行數(shù)5行，因此CUME_DIST為2/5=0.4 。
2、PERCENT_RANK的計算方法：當(dāng)前RANK值-1/總行數(shù)-1 。
比如，第4行的RANK值為1，本組總行數(shù)5行，因此PERCENT_RANK為1-1/5-1= 0。
再比如，第7行的RANK值為9，本組總行數(shù)10行，因此PERCENT_RANK為9-1/10-1=0.8888888888888889。
4、PERCENTILE_CONT和PERCENTILE_DISC:百分位的數(shù)值
PERCENTILE_CONT和PERCENTILE_DISC都是為了計算百分位的數(shù)值，比如計算在某個百分位時某個欄位的數(shù)值是多少。
- PERCENTILE_CONT是連續(xù)型，CONT代表continuous，連續(xù)值，意味它考慮的是區(qū)間，所以值是絕對的中間值；
- PERCENTILE_DISC是離散型，DISC代表discrete，離散值。所以它更多考慮向上或者向下取舍，而不會考慮區(qū)間。
以下腳本用于獲得分位數(shù)：
select Department ,LastName ,Rate
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Rate) OVER (PARTITION BY Department) AS MedianCont
,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Rate) OVER (PARTITION BY Department) AS MedianDisc
,row_number() over(partition by Department order by Rate) as rn
from #data order by DepartMent ,Rate asc
到此這篇關(guān)于SQL Server排名函數(shù)與分析函數(shù)的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
SQL語句刪除2條重復(fù)數(shù)據(jù)一條保留一條
SQL語句2條完全一樣的數(shù)據(jù)刪除一條保留一條，SQL Server 2005 Express 下測試通過, 僅供參考2012-06-06
SqlServer應(yīng)用之sys.dm_os_waiting_tasks 引發(fā)的疑問(下)
這篇文章主要介紹了SqlServer應(yīng)用之sys.dm_os_waiting_tasks 引發(fā)的疑問(下) 的相關(guān)資料,需要的朋友可以參考下2015-12-12
SQL Server實現(xiàn)查詢每個分組的前N條記錄
這篇文章介紹了SQL Server實現(xiàn)查詢每個分組的前N條記錄，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-06-06
這篇文章主要介紹了關(guān)于SQL查詢語句關(guān)鍵字方法，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-10-10
本篇文章介紹了，查詢表中某字段有重復(fù)記錄個數(shù)的方法。需要的朋友參考下2013-04-04
SQL Server 數(shù)據(jù)庫分離與附加 就這么簡單!
這篇文章主要介紹了SQL Server 數(shù)據(jù)庫分離與附加，很簡單的圖文教程，感興趣的小伙伴們可以參考一下2016-08-08
join 是兩張表做交連后里面條件相同的部分記錄產(chǎn)生一個記錄集，union是產(chǎn)生的兩個記錄集(字段要一樣的)并在一起，成為一個新的記錄集2012-08-08
SQL Server中通過reverse取某個最后一次出現(xiàn)的符號后面的內(nèi)容(字符串反轉(zhuǎn))
昨天在項目中遇到了一個非常簡單的問題,需要把SQL Server數(shù)據(jù)庫中保存的一段路徑地址取出其文件名,但SQL Server又沒有現(xiàn)成的方法,最后在網(wǎng)上找到這樣的一個方法,原理是先將字符串反轉(zhuǎn),取出第一個/的位置,從頭進(jìn)行截取后再次反轉(zhuǎn)2012-07-07
這篇文章主要介紹了Sql Server中REPLACE函數(shù)的使用的相關(guān)資料,需要的朋友可以參考下2016-01-01
更改SQL Server數(shù)據(jù)庫的日志恢復(fù)模式是數(shù)據(jù)庫管理中的一個重要操作,本文就來介紹一下SQL Server三種主要的恢復(fù)模式,具有一定的參考價值,感興趣的可以了解一下2024-12-12