# SQL Server 分析函数和排名函数

;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
View Code

## 一，分析函数

### 1，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

### 2，PERCENTILE_CONT和PERCENTILE_DISC

PERCENTILE_CONT和PERCENTILE_DISC都是为了计算百分位的数值，比如计算在某个百分位时某个栏位的数值是多少。

PERCENTILE_CONT ( numeric_literal )  WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] ) OVER ( [ <partition_by_clause> ] )
PERCENTILE_DISC ( numeric_literal )  WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] ) OVER ( [ <partition_by_clause> ] )

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

### 3，LAG和LEAD

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

• 第一行，对于LastRate字段，向后不存在数据行，返回参数Default的值，字段NextRate的值是第二行的Rate字段的值。
• 第二行，LastRate是第一行的Rate字段的值，NextRate是第三行的Rate字段的值。对于中间行，依次类推。
• 最后一行，LastRate是倒数第二行的Rate字段的值，对于NextRate字段，由于最后一行向前不存在数据行，返回参数Default的值。

### 4，FIRST_VALUE和LAST_VALUE

获取分组内排在最末尾的行和排在第一位的行：

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 ] )

## 二，排名函数

SQL Server的排名函数是对查询的结果进行排名和分组，TSQL共有4个排名函数，分别是：RANK、NTILE、DENSE_RANK和ROW_NUMBER，和OVER()函数搭配使用，按照特定的顺序排名。

1，ROW_NUMBER函数

ROW_NUMBER函数实际上是一个序列，每个分组内都会创建一个序列，序列从1开始，按照顺序依次 +1 递增。

ROW_NUMBER ( )
OVER ( [ PARTITION_BY_clause ] order_by_clause )

2，RANK函数

RANK函数用于排名时，不会返回连续的整数。RANK函数的语法是：在分组内，按照特定的顺序排名，序号从1依次递增，排名函数以tie为单位，每个tie中的所有行的排名是相同的，排名可能是不连续的。

RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

• step1：按照指定的分区字段分组，在每个分组内按照指定的字段排序。
• step2：在每个分组内，如果相邻的两行或多行相同在排序字段上的值相同，那么这些行称作一个tie，每个tie中的所有行都会获得相同的排名。
• step3：后面的排名会计算每个tie中的行数，RANK函数不总是返回连续的整数，例如，班级中，A，B分数都是100分，C的分数是90分，那么A和B的排名是1，C的排名是3

3，DENSE_RANK

DENSE_RANK函数用于排名时，会返回连续的整数。每个tie占用一个排名，每个tie中的所有行的排名是相同的。排名值是连续的

DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )

• step1：按照指定的分区字段分组，在每个分组内按照指定的字段排序。
• step2：在每个分组内，如果相邻的两行或多行相同在排序字段上的值相同，那么这些行称作一个tie，每个tie中的所有行都会获得相同的排名。
• step3：后面的排名会计算每个tie中的行数，RANK函数总是返回连续的整数，例如，班级中，A，B分数都是100分，C的分数是90分，那么A和B的排名是1，C的排名是2

4，NTILE

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

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

Analytic Functions (Transact-SQL)

Ranking Functions (Transact-SQL)

posted @ 2019-03-14 16:33  悦光阴  阅读(6376)  评论(0编辑  收藏  举报