mssql sqlserver cume_dist函数计算原理详解


摘要:
下文讲述sqlserver2012中cume_dist函数的详细使用说明
实验环境:sqlserver 2012


cume_dist计算原理:
在分组中小于等于当前值的行数/当前分组的总行数
例:

  declare @test table(keyId int,info varchar(20),qty int)
insert into @test (keyId,info,qty)values(-1,'a',2)
insert into @test (keyId,info,qty)values(1,'a',2)
insert into @test (keyId,info,qty)values(2,'a',10)
insert into @test (keyId,info,qty)values(3,'b',8)
insert into @test (keyId,info,qty)values(4,'c',8)
insert into @test (keyId,info,qty)values(5,'d',8)
insert into @test (keyId,info,qty)values(6,'b',9) 

/*
从下文的输出我们可以看出 
  当前无分组
  所以总行数为7
  第一行keyId=-1 在整个行中的分布比它小或等于它的值没有 所以它为第一行
  cume_dist = 1/7 
  依次类推
  keyId=1  cume_dist 2/7 
*/
select *,
cume_dist() over(order by keyId) as cume_dist 
 from @test 
  -------输出-------
 

mssql_sqlserver_cume_dist详解举例

mssql_sqlserver_cume_dist详解举例


mssql_sqlserver_cume_dist

mssql_sqlserver_cume_dist


mssql_sqlserver_cume_dist详解举例-3

mssql_sqlserver_cume_dist详解举例-3