oracle 百分位数,Oracle分析函数总结- 数值分布 - cume_dist,percent_rank,ntile,percentile_disc,percentile_cont,ratio_...

Oracle分析函数的基本概念和语法 ->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>selectcume_dist(20,4000)

2      withingroup(orderbydeptno, sal) cume_dist

3fromemp;

CUME_DIST

----------

.6

b)作为分析函数的用法

语法：cume_dist() over([partition_clause] order_by_clause)

例如计算每个人在本部门按照薪水排列中的相对位置。

www.linuxidc.com @ORA11GR2>selectename, sal, deptno,

2      cume_dist() over(partitionbydeptnoorderbysal) cume_dist

3fromemp;

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>selectpercent_rank(20,4000)

2      withingroup(orderbydeptno, sal) percent_rank

3fromemp;

PERCENT_RANK

------------

.571428571

b)作为分析函数的用法

语法： percent_rank() over([partition_clause] order_by_clause)

例如计算每个人在本部门按照薪水排列中的相对位置。

www.linuxidc.com @ORA11GR2>selectename, sal, deptno,

2      percent_rank() over(partitionbydeptnoorderbysal) percent_rank

3fromemp;

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>selectdeptno,

2       percentile_disc(0.4) withingroup(orderbysal) percentile_disc

3fromempgroupbydeptno;

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>selectename, sal, deptno,

2  cume_dist() over(partitionbydeptnoorderbysal) cume_dist,

3  percentile_disc(0.4) withingroup(orderbysal)

4      over(partitionbydeptno) percentile_disc

5fromemp;

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行。