SQL SERVER 2012窗口函数-五 求中位数 累计分布 PERCENTILE_DISC <>笔记(
SQL SERVER 2012窗口函数-5 求中位数 累计分布 PERCENTILE_DISC <<Microsoft.SQL.Server.2012.窗口窗数>>笔记(
求中位数,我想大家以前学过统计学,肯定明白,中位数就是,一个序列中的中间那个数,
例如 1,2,3, 中位数就是 2
但是 1,2,3,4 中位数 就是(2+3)/2=2.5, 数据库提供了二个中位数函数, 其中一函数中位数,是取 2 作中位位,但是另一个则是 2.5
ok,我们还是运行下面脚本,然后再看图
WITH XX AS (select 'lilei' as name,63 as score union select 'lili' ,55 union select 'zhangshan',50 union select 'wangwu',43 union select 'zhangde',76 union select 'hebin',90 union select 'liming',78 union select 'zhouyi',74 union select 'chengfei',89 union select '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,score FROM xx ) select 总数, ( select max(序号) as id from ct1 a where a.排名=ct1.排名 group by 排名 ) 以最大值作为排名, 排名 as rank的排名, CUME_DIST_value, name,score, PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY score)over() AS 中位数, PERCENTILE_CONT(0.5) within group(order by score) over() as 中位数1 , score/10*10 as 组距 from ct1
我们看图
其中中位数 65 取 CUME_DIST_value 中 0.5 的值,
中位数 1 则取 计算的中位数。(65+74)/2
PERCENTILE_DISC(0.5) 表示累积频数 是
0.5 的值, 是累积频数的概念