Oracle分析函数总结- 数值分布 - cume_dist,percent_rank,ntile,percentile_disc,percentile_cont,ratio_to_report

Oracle分析函数的基本概念和语法 -> http://www.linuxidc.com/Linux/2012-08/67670.htm

下面这些函数计算某个值在一组有序数据中的累计分布（cumulative distribution）

1） cume_dist（）
计算结果为相对位置/总行数。返回值（0,1]。
例如在一个5行的组中，返回的累计分布值为0.2,0.4,0.6,0.8,1.0;
注意对于重复行，计算时取重复行中的最后一行的位置。

a）作为聚合函数的用法
语法：cume_dist（expr） with group （order by exp）
cume_dist（）用参数中的指定的数据构造一条假定的数据并插入到现存行中，
然后计算这条假定数据在所有行中的相对位置。
例如下面的查询中,emp中总共有14行数据，
假定的数据（deptno=20,sal=4000）会插入到第9行，
因此相对位置 9/（14+1）=0.6。

www.linuxidc.com @ORA11GR2> select cume_dist（20,4000）
2 within group （order by deptno, sal） cume_dist
3 from emp;
CUME_DIST
----------
.6

b）作为分析函数的用法
语法：cume_dist（） over（[partition_clause] order_by_clause）
例如计算每个人在本部门按照薪水排列中的相对位置。

www.linuxidc.com @ORA11GR2> select ename, sal, deptno,
2 cume_dist（） over（partition by deptno order by sal） cume_dist
3 from emp;
ENAME SAL DEPTNO CUME_DIST
-------------------- ---------- ---------- ----------
MILLER 1300 10 .333333333
CLARK 2450 10 .666666667
KING 5002 10 1
SMITH 800 20 .2
ADAMS 1100 20 .4
JONES 2975 20 .6
SCOTT 3000 20 1
FORD 3000 20 1
JAMES 950 30 .166666667
MARTIN 1250 30 .5
WARD 1250 30 .5
TURNER 1500 30 .666666667
ALLEN 1600 30 .833333333
BLAKE 2850 30 1
已选择14行。

2） percent_rank（）
和cume_dist的不同点在于计算分布结果的方法。
计算方法为（相对位置-1）/（总行数-1）,
因此第一行的结果为0。返回值[0,1]。
例如在一个5行的组中，返回的累计分布值为0,0.25,0.5,0.75,1.0;
注意对于重复行，计算时取重复行中的第一行的位置。

a）作为聚合函数的用法
语法：percent_rank（expr） with group （order by exp）
下面的例子中,类似cume_dist，假定数据（deptno=20,sal=4000）会插入到第9行，
计算相对位置（9-1）/（（14+1）-1）=0.57

www.linuxidc.com @ORA11GR2> select percent_rank（20,4000）
2 within group （order by deptno, sal） percent_rank
3 from emp;
PERCENT_RANK
------------
.571428571

b）作为分析函数的用法
语法： percent_rank（） over（[partition_clause] order_by_clause）
例如计算每个人在本部门按照薪水排列中的相对位置。

www.linuxidc.com @ORA11GR2> select ename, sal, deptno,
2 percent_rank（） over（partition by deptno order by sal） percent_rank
3 from emp;
ENAME SAL DEPTNO PERCENT_RANK
-------------------- ---------- ---------- ------------
MILLER 1300 10 0
CLARK 2450 10 .5
KING 5002 10 1
SMITH 800 20 0
ADAMS 1100 20 .25
JONES 2975 20 .5
SCOTT 3000 20 .75
FORD 3000 20 .75
JAMES 950 30 0
MARTIN 1250 30 .2
WARD 1250 30 .2
TURNER 1500 30 .6
ALLEN 1600 30 .8
BLAKE 2850 30 1
已选择14行。

3） percentile_disc（）
返回一个与输入的分布百分比值相对应的数据值，
分布百分比的计算方法同函数CUME_DIST（），
如果没有正好对应的数据值，就取大于该分布值的下一个值。

a）作为聚合函数的用法
语法： percentile_disc（expr） with group （order by expr）
例如，查询各部门中薪水分布处于40%位置的人的薪水。

www.linuxidc.com @ORA11GR2> select deptno,
2 percentile_disc（0.4） within group （order by sal） percentile_disc
3 from emp group by deptno;
DEPTNO PERCENTILE_DISC
---------- ---------------
10 2450
20 1100
30 1250

2）作为分析函数的用法
语法：percentile_disc（expr） with group （order by expr） over（[partition_clause]）
例如，查询各部门中薪水分布处于40%位置的人的薪水。注意和cume_dist（）的输出结果作对照。

www.linuxidc.com @ORA11GR2> select ename, sal, deptno,
2 cume_dist（） over（partition by deptno order by sal） cume_dist,
3 percentile_disc（0.4） within group （order by sal）
4 over（partition by deptno） percentile_disc
5 from emp;
ENAME SAL DEPTNO CUME_DIST PERCENTILE_DISC
-------------------- ---------- ---------- ---------- ---------------
MILLER 1300 10 .333333333 2450
CLARK 2450 10 .666666667 2450
KING 5002 10 1 2450
SMITH 800 20 .2 1100
ADAMS 1100 20 .4 1100
JONES 2975 20 .6 1100
SCOTT 3000 20 1 1100
FORD 3000 20 1 1100
JAMES 950 30 .166666667 1250
MARTIN 1250 30 .5 1250
WARD 1250 30 .5 1250
TURNER 1500 30 .666666667 1250
ALLEN 1600 30 .833333333 1250
BLAKE 2850 30 1 1250
已选择14行。

Oracle分析函数总结 - 排序 - rank,dense_rank,row_number,first,first_value,last,last_value,lag,leadOracle实现分页相关资讯 Oracle函数 Oracle分析函数

Oracle字符串函数总结（06月20日）
Oracle lag（）与lead（）函数（12/01/2015 20:41:24）
Oracle ascii函数（07/26/2015 08:46:01）

Oracle使用简单函数（06月09日）
REGEXP_SUBSTR函数的整理（08/20/2015 20:41:01）
Oracle dump函数（07/26/2015 08:40:01）

本文评论查看全部评论（0）