一、排名開窗函數(shù)概述

SQL Server的排名函數(shù)是對(duì)查詢的結(jié)果進(jìn)行排名和分組，TSQL共有4個(gè)排名函數(shù)，分別是：ROW_NUMBER、RANK、DENSE_RANK和NTILE。

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

排名開窗函數(shù)可以單獨(dú)使用ORDER BY 語句，也可以和PARTITION BY同時(shí)使用。

• PARTITION BY用于將結(jié)果集進(jìn)行分組，開窗函數(shù)應(yīng)用于每一組。
• ODER BY 指定排名開窗函數(shù)的順序。在排名開窗函數(shù)中必須使用ORDER BY語句。

1、ROW_NUMBER：行號(hào)

為每一組的行按順序生成一個(gè)唯一的序號(hào)。

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

ROW_NUMBER ( ) OVER ( [ PARTITION_BY_clause ] order_by_clause )

2、RANK：排名

也為每一組的行生成一個(gè)序號(hào)，但如果按照ORDER BY的排序，如果有相同的值會(huì)生成相同的序號(hào)，并且接下來的序號(hào)是不連續(xù)的。

例如，班級(jí)中，A，B分?jǐn)?shù)都是100分，C的分?jǐn)?shù)是90分，那么A和B的排名是1，C的排名是3。

3、DENSE_RANK：密集排名

和RANK（排名）類似，不同的是如果有相同的序號(hào)，那么接下來的序號(hào)不會(huì)間斷。

例如，班級(jí)中，A，B分?jǐn)?shù)都是100分，C的分?jǐn)?shù)是90分，那么A和B的排名是1，C的排名是2。

4、NTILE ：分組排名

按照指定的數(shù)目將數(shù)據(jù)進(jìn)行分組，并為每一組生成一個(gè)序號(hào)。

特別地，NTILE(4) 把一個(gè)分組分成4份，叫做Quartile。例如，以下腳本顯示各個(gè)排名函數(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ù)基于分組，計(jì)算分組內(nèi)數(shù)據(jù)的聚合值，經(jīng)常會(huì)和窗口函數(shù)OVER()一起使用，使用分析函數(shù)可以很方便地計(jì)算同比和環(huán)比，獲得中位數(shù)，獲得分組的最大值和最小值。

分析函數(shù)和聚合函數(shù)不同，不需要GROUP BY子句，對(duì)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

在一次查詢中，對(duì)數(shù)據(jù)表進(jìn)行排序，把已排序的數(shù)據(jù)從上向下看作是一個(gè)序列，對(duì)當(dāng)前行而言，在序列上方的為后，在序列下方的為前。

在同一分組內(nèi)，對(duì)于當(dāng)前行：

• Lag()函數(shù):用于獲取從當(dāng)前行開始向后（或向上）計(jì)數(shù)的第N行。
• Lead()函數(shù):用于獲取從當(dāng)前行開始向前（或向下）計(jì)數(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ù)，對(duì)于LAG()函數(shù)表示從當(dāng)前行(current row)回退的行數(shù)，對(duì)于LEAD()表示從當(dāng)前行向前進(jìn)的行數(shù)。
• default ：當(dāng)offset超出分區(qū)范圍時(shí)要返回的值。 如果未指定默認(rèn)值，則返回NULL。 default可以是列，子查詢或其他表達(dá)式，但必須跟sclar_expression類型兼容。

結(jié)果日期，這兩個(gè)函數(shù)特別適合用于計(jì)算同比和環(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)行分組，對(duì)Document Control這一小組進(jìn)行分析：

• 第一行，對(duì)于LastRate字段，向后不存在數(shù)據(jù)行，返回參數(shù)Default的值，字段NextRate的值是第二行的Rate字段的值。
• 第二行，LastRate是第一行的Rate字段的值，NextRate是第三行的Rate字段的值。對(duì)于中間行，依次類推。
• 最后一行，LastRate是倒數(shù)第二行的Rate字段的值，對(duì)于NextRate字段，由于最后一行向前不存在數(shù)據(jù)行，返回參數(shù)Default的值。

以下程序代碼用來示范如何透過 LAG 函數(shù)來計(jì)算每一列與前一列的 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é)果值。

以下代碼，用于計(jì)算累積分布和排名百分比：

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都會(huì)被當(dāng)作最小值。

1、cume_dist的計(jì)算方法：小于等于當(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的計(jì)算方法：當(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都是為了計(jì)算百分位的數(shù)值，比如計(jì)算在某個(gè)百分位時(shí)某個(gè)欄位的數(shù)值是多少。

• PERCENTILE_CONT是連續(xù)型，CONT代表continuous，連續(xù)值，意味它考慮的是區(qū)間，所以值是絕對(duì)的中間值；
• PERCENTILE_DISC是離散型，DISC代表discrete，離散值。所以它更多考慮向上或者向下取舍，而不會(huì)考慮區(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ù)的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論