分析函数CUME_DIST
微软的定义:
计算某个值在SQL Server 2012中的一组值内的累积分布。CUME_DIST计算某指定值在一组值中的相对位置。对于行r,假定采用升序,r的CUME_DIST是值低于或等于r的值的行数除以在分区或查询结果集中求出的行数。
函数解析:
执行如下代码,构造一组数据。
- DECLARE
- @analytic TABLE(
- name varchar(35) ,
- dept varchar(35),
- salary money
- )
- INSERT INTO @analytic
- VALUES
- --bd
- ('andy01','bd',15000),
- ('andy02','bd',12000),
- ('andy03','bd',12000),
- ('andy04','bd',10000),
- ('andy05','bd',8000),
- --ca
- ('andy06','ca',20000),
- ('andy07','ca',18000),
- ('andy08','ca',18000),
- ('andy09','ca',15000),
- ('andy10','ca',12000),
- ('andy11','ca',12000),
- ('andy12','ca',10000),
- ('andy13','ca',8000),
- ('andy14','ca',8000),
- ('andy15','ca',8000)
- SELECT
- dept,name ,salary,
- CUME_DIST() OVER(PARTITION BY dept ORDER BY salary) AS cume_dist_
- FROM @analytic
- ORDER BY dept,salary DESC
返回结果如下:
[url=http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/pic/67532/cn_zh/1519810205856/CUME_DIST%E8%BF%94%E5%9B%9E%E7%BB%93%E6%9E%9C.png][/url]
示例分析:
按照dept分区,根据salary排序,比较当前记录和后一条记录(偏移量为1)的salary值的差值,这个非常实用。