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
我们看看它有图像
