日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學無先后，達者為師

網站首頁 編程語言 正文

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

作者：springsnow ? 更新時間： 2022-07-15 編程語言

一、排名開窗函數概述

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

原文鏈接：https://www.cnblogs.com/springsnow/p/9591083.html

欄目分類
最近更新