mssql sqlserver 分析函数 PERCENT_RANK 使用说明简介


摘要:
下文介绍PERCENT_RANK 分析函数的用法
实验环境:sqlserver 2012


一、PERCENT_RANK 分析函数功能及语法说明

1.1 PERCENT_RANK 功能说明
返回特定值在一个数据集合中位置, 同PERCENTILE_CONT互为相反(一个生成百分比 一个根据百分比生成值)
PERCENT_RANK的计算方法: (RANK()–1)/(群组中的总行数–1)
—————————————
1.2 PERCENT_RANK 语法说明
    PERCENT_RANK( )
    OVER ( [partition by 列名] order by 列名 )
partition by 列名 :
分组集合
order by 列名:
分组集合中的排序列
————————
返回类型
float(53)



二、PERCENT_RANK 分析函数举例应用

     declare @test table(keyId int identity,name nvarchar(10),score int)
insert into @test(name,score)values('猫猫',100)
insert into @test(name,score)values('张丹',100)
insert into @test(name,score)values('小豪',70)
insert into @test(name,score)values('刘琦',50)
insert into @test(name,score)values('吴越',88)
insert into @test(name,score)values('琳达',80)

/*
 keyId=1  rank = 1 (1-1)/(6-1)
 keyId=2  rank =2 (1-1)/(6-1)
 keyId=5  rank =3 (3-1)/(6-1) =0.4
 keyId=6  rank=4  (4-1)/(6-1) = 0.6
 ...
*/
select keyId,name,score,
rank()  over (order by score desc  ) as [rankValue],
PERCENT_RANK()    
  over (order by score desc  ) as [score在组数据中的占比]
from @test as t 
go
 
declare @test table(keyId int identity,name nvarchar(10),sort varchar(10),score int)
insert into @test(name,sort,score)values('猫猫','语文',100)
insert into @test(name,sort,score)values('张丹','语文',60)
insert into @test(name,sort,score)values('小豪','语文',70)
insert into @test(name,sort,score)values('刘琦','数学',50)
insert into @test(name,sort,score)values('吴越','数学',88)
insert into @test(name,sort,score)values('琳达','数学',80)

select keyId,name,score,sort,
rank()  over (order by score desc  ) as [rankValue],
 PERCENT_RANK()  
  over (partition by sort order by score asc) as [score在组数据中的占比]
  from @test as t