SQL SERVER 2012窗口函数-5　统计函数 CUME_DIST <<Microsoft.SQL.Server.2012.窗口窗数>>笔记

　cume_dist 我的理解就是，把一个数值排序，从头端到最尾部 ，把它看成1,　然后看各占成员在属于哪个点上．

我先这个函数的计算过程写下．

WITH XX AS(select 'lilei' as name,75 as scoreunionselect 'lili' ,55unionselect 'zhangshan',50unionselect 'wangwu',65unionselect 'zhangde',89unionselect 'hebin',90unionselect 'liming',75unionselect 'zhouyi',75unionselect 'chengfei',89unionselect 'gaoxi',65) ,ct1   as (select CUME_DIST() over (order by score) as CUME_DIST_value,(select count(*) from xx) as 总数,rank() over (order by score) as 排名,ROW_NUMBER() over(order by score) as 序号,name,scoreFROM xx )select 总数,(select   max(序号) as id  from ct1 a where a.排名=ct1.排名group by 排名)以最大值作为排名,排名  as rank的排名,CUME_DIST_value,name,scorefrom ct1

大家可以看发现 以最大值作为排名/总数　　＝CUME_DIST＿value

我们看看它有图像

1楼zengxinwen5天前 23:08

对于 PERCENT_RANK() 它的计算公式是 (排名-1)/(总数-1)nn对于它的统计意义，我没有弄清是什么意思，它的表达的图像代表什么？我也不晓得的。请大家给出其统计意义。nn还有为什么要减1 ，减不减统计意义有什么分别吗