SQL SERVER中PERCENTILE_CONT和PERCENTILE_DISC
来源:互联网 发布:在线算法测试网站 编辑:程序博客网 时间:2024/06/06 01:35
SQL SERVER中PERCENTILE_CONT和PERCENTILE_DISC
PERCENTILE_CONT和PERCENTILE_DISC
看下面一组SQL语句:
WITH testas( select N'LeeWhoeeUniversity' as name,10 as score UNION ALL select N'LeeWhoeeUniversity',20 UNION ALL select N'LeeWhoeeUniversity',30 UNION ALL select N'LeeWhoeeUniversity',40 UNION ALL select N'LeeWhoeeUniversity',50 UNION ALL select N'DePaul',60 UNION ALL select N'DePaul',70 UNION ALL select N'DePaul',80 UNION ALL select N'DePaul',90 UNION ALL select N'DePaul',100)select name,score,PERCENT_RANK() over(partition by name order by score) as per_rnk,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_5,PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_6,PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_7,PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score) over(partition by name) as percont0_75,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_5,PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_6,PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_7,PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY score) over(partition by name) as perdist0_75from test
运行结果:
name score per_rnk percont0_5 percont0_6 percont0_7 percont0_75 perdist0_5 perdist0_6 perdist0_7 perdist0_75
LeeWhoeeUniversity 10 0 30 34 38 40 30 30 40 40
LeeWhoeeUniversity 20 0.25 30 34 38 40 30 30 40 40
LeeWhoeeUniversity 30 0.5 30 34 38 40 30 30 40 40
LeeWhoeeUniversity 40 0.75 30 34 38 40 30 30 40 40
LeeWhoeeUniversity 50 1 30 34 38 40 30 30 40 40
DePaul 60 0 80 84 88 90 80 80 90 90
DePaul 70 0.25 80 84 88 90 80 80 90 90
DePaul 80 0.5 80 84 88 90 80 80 90 90
DePaul 90 0.75 80 84 88 90 80 80 90 90
DePaul 100 1 80 84 88 90 80 80 90 90
简单理解,PERCENT_RANK前面介绍过,求出score百分比后,PERCENTILE_CONT和PERCENTILE_DISC就是根据百分比求出对应的score。但不同的是,某一百分比没有对应的score时,PERCENTILE_CONT会根据百分比的偏差计算出一个新值,此值可能并不存在于score中。PERCENTILE_DISC得出的是偏向于最近百分比对应的score值,因此此值肯定存在于score中。
如PERCENTILE_CONT(0.6)对应的值为34,偏差值我猜测是这样计算出的:百分比75%和50%PERCENTILE_CONT计算出的值分别是40和30.那么等式:(40-30)/(0.75-0.5)=新偏差值/0.6-0.5。PERCENTILE_CONT(0.6)在50%上的偏差值=4。所以,PERCENTILE_CONT(0.6)对应PERCENTILE_CONT(0.5)+4=34。
再看PERCENTILE_DISC(0.6),直接将最近的0.5计算出的,PERCENTILE_DISC值取过来了。
- SQL SERVER中PERCENTILE_CONT和PERCENTILE_DISC
- MEDIAN CUME_DIST PERCENT_RANK PERCENTILE_CONT PERCENTILE_DISC
- Oracle分析函数总结(3)- 数值分布 - cume_dist,percent_rank,ntile,percentile_disc,percentile_cont,ratio_to_report
- Oracle分析函数总结(3)- 数值分布 - cume_dist,percent_rank,ntile,percentile_disc,percentile_cont,ratio_to_report
- Oracle分析函数总结(3)- 数值分布 - cume_dist,percent_rank,ntile,percentile_disc,percentile_cont,ratio_to_report
- SQL Server中存储和读取图片
- SQL Server中存储和读取图片
- SQL SERVER中主键和外键
- SQL Server中常用全局变量和函数
- sql server中PAGELATCH_x和PAGEIOLATCH_x解析
- SQL SERVER中PIVOT和UNPIVOT
- SQL SERVER中CUME_DIST和PERCENT_RANK函数
- SQL SERVER中LEAD和LAG函数
- SQL SERVER中FIRST_VALUE和LAST_VALUE
- sql server中varchar和nvrchar浅解
- 分页查询中sql server和mysql
- sql server中dense_rank和row_number
- sql server中新建和删除数据库
- SQL SERVER 数据类型
- 妈球的,C语言常识!!
- SQL SERVER中CUME_DIST和PERCENT_RANK函数
- SQL SERVER中LEAD和LAG函数
- SQL SERVER中FIRST_VALUE和LAST_VALUE
- SQL SERVER中PERCENTILE_CONT和PERCENTILE_DISC
- SQL SERVER中GROUPING SETS,CUBE,ROLLUP
- SQL SERVER中XML查询:FOR XML指定RAW
- Stack_Queue 固定容量的栈组SetOfStacks @CareerCup
- 循环不变性(loop invariant)-证明算法的正确性的一种方法
- SQL SERVER中XML查询:FOR XML指定AUTO
- SQL SERVER中XML查询:FOR XML指定EXPLICIT
- SQL SERVER中XML查询:FOR XML指定PATH
- 一个hello程序的android内核模块编译方法及在模拟器中进行测试结论