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


摘要:
此分析函数,可以计算出,指定群组中的指定百分比的数值,返回一个浮点型数值
可用于 全体成绩百分比所对应的分数,全体销量百分比所对应的销量数
实验环境:sqlserver 2012



一、PERCENTILE_CONT 语法及功能简介

PERCENTILE_CONT(<表达式1>)
WITHIN GROUP(ORDER BY <表达式2> [ ASC | DESC ] )
OVER (< partition by 表达式3> )
参数:表达式1:
此处只能输入一个大于等于0 小于等于1的浮点数
如果输入null ,则返回错误提示信息
如果输入大于1 或小于0,则返回数据超过数据范围的提示信息
参数:表达式2:
排序字段,此处只可使用单个字段作为排序标准(默认值升序)
参数:表达式3
将结果集,以此为群组,进行多组结果集的百分比计算
返回值:
float(53)

注意事项:
1.兼容性支持
witin group 需要兼容级别110或者更高级别,才可以运行。
否则会出现以下错误提示信息:
消息 10762,级别 15,状态 1,第 48 行
在当前兼容模式下不允许使用 PERCENTILE_CONT 函数。仅在 110 模式或更高模式下允许使用它。
2.PERCENTILE_CONT可运行在子查询 having 视图,联结查询中。



二、PERCENTILE_CONT 应用举例

 ---计算学生成绩在百分之60的分数线
---创建成绩表
declare @test table(keyId int identity,name nvarchar(10),score int)
insert into @test(name,score)values('猫猫',100)
insert into @test(name,score)values('张丹',60)
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)

select keyId,name,score,
 PERCENTILE_CONT(0.6) 
  within group (order by score desc)
  over () as [contInfo]
  from @test as t 
  
  ---从输出结果,我们可以看出 成绩分数百分之60的学生处于70分

go

----计算每个学科的百分之80的分数线
  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,
 PERCENTILE_CONT(0.6) 
  within group (order by score desc)
  over (partition by sort) as [每学科百分六十分数线]
  from @test as t 
  ---输出结果如下所示----