SQL Server中的排名函數與分析函數詳解

2022-05-19 16:00:09

一、排名開窗函數概述

SQL Server的排名函數是對查詢的結果進行排名和分組，TSQL共有4個排名函數，分別是：ROW_NUMBER、RANK、DENSE_RANK和NTILE。

他們和OVER()函數搭配使用，按照特定的順序排名。

排名開窗函數可以單獨使用ORDER BY 語句，也可以和PARTITION BY同時使用。

PARTITION BY用於將結果集進行分組，開窗函數應用於每一組。
ODER BY 指定排名開窗函數的順序。在排名開窗函數中必須使用ORDER BY語句。

1、ROW_NUMBER：行號

為每一組的行按順序生成一個唯一的序號。

序列從1開始，按照順序依次 +1 遞增。分組內序列的最大值就是該分組內的行的數目。

ROW_NUMBER ( ) OVER ( [ PARTITION_BY_clause ] order_by_clause )

2、RANK：排名

也為每一組的行生成一個序號，但如果按照ORDER BY的排序，如果有相同的值會生成相同的序號，並且接下來的序號是不連續的。

例如，班級中，A，B分數都是100分，C的分數是90分，那麼A和B的排名是1，C的排名是3。

3、DENSE_RANK：密集排名

和RANK（排名）類似，不同的是如果有相同的序號，那麼接下來的序號不會間斷。

例如，班級中，A，B分數都是100分，C的分數是90分，那麼A和B的排名是1，C的排名是2。

4、NTILE ：分組排名

按照指定的數目將資料進行分組，併為每一組生成一個序號。

特別地，NTILE(4) 把一個分組分成4份，叫做Quartile。例如，以下指令碼顯示各個排名函數的執行結果：

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

二、分析函數

分析函數基於分組，計算分組內資料的聚合值，經常會和視窗函數OVER()一起使用，使用分析函數可以很方便地計算同比和環比，獲得中位數，獲得分組的最大值和最小值。

分析函數和聚合函數不同，不需要GROUP BY子句，對SELECT子句的結果集，通過OVER()子句分組。

注意：distinct子句的執行順序是在分析函數之後。

使用以下指令碼插入範例資料：

;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類分析函數。

1、LAG和LEAD

在一次查詢中，對資料表進行排序，把已排序的資料從上向下看作是一個序列，對當前行而言，在序列上方的為後，在序列下方的為前。

在同一分組內，對於當前行：

Lag()函數:用於獲取從當前行開始向後（或向上）計數的第N行。
Lead()函數:用於獲取從當前行開始向前（或向下）計數的第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 )

引數註釋：

sclar_expression：標量表示式
offset：預設值是1，必須是正整數，對於LAG()函數表示從當前行(current row)回退的行數，對於LEAD()表示從當前行向前進的行數。
default ：當offset超出分割區範圍時要返回的值。如果未指定預設值，則返回NULL。 default可以是列，子查詢或其他表示式，但必須跟sclar_expression型別相容。

結果日期，這兩個函數特別適合用於計算同比和環比。

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進行分組，對Document Control這一小組進行分析：

第一行，對於LastRate欄位，向後不存在資料行，返回引數Default的值，欄位NextRate的值是第二行的Rate欄位的值。
第二行，LastRate是第一行的Rate欄位的值，NextRate是第三行的Rate欄位的值。對於中間行，依次類推。
最後一行，LastRate是倒數第二行的Rate欄位的值，對於NextRate欄位，由於最後一行向前不存在資料行，返回引數Default的值。

以下程式程式碼用來示範如何透過 LAG 函數來計算每一列與前一列的 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引入的函數。

獲取分組內排在最末尾的行和排在第一位的行：

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(）：小於等於當前值的行數/分組內總行數
PERCENT_RANK() ：（分組內當前行的RANK值-1）/ （分組內總行數-1），排名值是RANK()函數排序的結果值。

以下程式碼，用於計算累積分佈和排名百分比：

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都會被當作最小值。

1、cume_dist的計算方法：小於等於當前行值的行數/總行數。

比如，第3行值為16.82，有4行的值小於等於16.82，本組總行數5行，因此CUME_DIST為4/5=0.8 。

再比如，第4行值為10.25，行值小於等於10.25的共2行，本組總行數5行，因此CUME_DIST為2/5=0.4 。

2、PERCENT_RANK的計算方法：當前RANK值-1/總行數-1 。

比如，第4行的RANK值為1，本組總行數5行，因此PERCENT_RANK為1-1/5-1= 0。

再比如，第7行的RANK值為9，本組總行數10行，因此PERCENT_RANK為9-1/10-1=0.8888888888888889。

4、PERCENTILE_CONT和PERCENTILE_DISC:百分位的數值

PERCENTILE_CONT和PERCENTILE_DISC都是為了計算百分位的數值，比如計算在某個百分位時某個欄位的數值是多少。

PERCENTILE_CONT是連續型，CONT代表continuous，連續值，意味它考慮的是區間，所以值是絕對的中間值；
PERCENTILE_DISC是離散型，DISC代表discrete，離散值。所以它更多考慮向上或者向下取捨，而不會考慮區間。

以下指令碼用於獲得分位數：

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

到此這篇關於SQL Server排名函數與分析函數的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。