**分析函数的基本概念和语法 -> http://blog.csdn.net/fw0124/article/details/7842039
下面这些函数计算某个值在一组有序数据中的累计分布(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。
[sql] view plain copy
tony@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)
例如计算每个人在本部门按照薪水排列中的相对位置。
[sql] view plain copy
tony@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
[sql] view plain copy
tony@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)
例如计算每个人在本部门按照薪水排列中的相对位置。
[sql] view plain copy
tony@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%位置的人的薪水。
[sql] view plain copy
tony@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()的输出结果作对照。
[sql] view plain copy
tony@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行。 4) percentile_cont()
返回一个与输入的分布百分比值相对应的数据值，分布百分比的计算方法同函数PERCENT_RANK()，
如果没有正好对应的数据值，就通过下面算法来得到值：
RN = 1+ (P*(N-1)) 其中P是输入的分布百分比值，N是组内的行数
CRN = CEIL(RN) FRN = FLOOR(RN)
If (CRN = FRN = RN) then the result is
(value of expression from row at RN)
Otherwise the result is
(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)
上面的算法比较难理解，实际上可以看作X轴上最小坐标0，最大坐标(行数-1),
先根据输入百分比找到对应的X轴坐标，然后按比例计算对应的数值。
a)作为聚合函数的用法
语法percentile_cont(expr) with group (order by expr)
下面的例子中,部门号30中薪水处于50%位置的数额计算方法：
RN=1+(0.5*(6-1))=3.5;CRN=4;FRN=3;
结果=(4-3.5)*1250+(3.5-3)*1500=1375
[sql] view plain copy
tony@ORA11GR2> select deptno,
2 percentile_cont(0.5) within group (order by sal) percentile_cont
3 from emp group by deptno;
DEPTNO PERCENTILE_CONT
---------- ---------------
10 2450
20 2975
30 1375 b)作为分析函数的用法
语法：percentile_cont(expr) with group (order by expr) over([partition_clause])
例如，计算各部门中薪水处于50%位置的薪水数额，注意和percent_rank()的输出结果作对照。
[sql] view plain copy
tony@ORA11GR2> select ename, sal, deptno,
2 percent_rank() over(partition by deptno order by sal) percent_rank,
3 percentile_cont(0.5) within group (order by sal)
4 over(partition by deptno) percentile_cont
5 from emp;
ENAME SAL DEPTNO PERCENT_RANK PERCENTILE_CONT
-------------------- ---------- ---------- ------------ ---------------
MILLER 1300 10 0 2450
CLARK 2450 10 .5 2450
KING 5002 10 1 2450
SMITH 800 20 0 2975
ADAMS 1100 20 .25 2975
JONES 2975 20 .5 2975
SCOTT 3000 20 .75 2975
FORD 3000 20 .75 2975
JAMES 950 30 0 1375
MARTIN 1250 30 .2 1375
WARD 1250 30 .2 1375
TURNER 1500 30 .6 1375
ALLEN 1600 30 .8 1375
BLAKE 2850 30 1 1375
已选择14行。5) ntile()
只能用作分析函数。
语法: ntile(expr) over([partition_clause] order_by_clause)
可以看作是把有序的数据集合平均分配到expr指定的数量的桶中,将桶号分配给每一行。
如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的行数最多相差1。
例如如果桶的数量为4，一共有6行，则：
1号桶:1行,2行;
2号桶:3行,4行;
3号桶:5行;
4号桶:6行;
例如，NTILE列返回每一行所应该被分配的桶号。
[sql] view plain copy
tony@ORA11GR2> select ename, sal, deptno,
2 ntile(4) over(partition by deptno order by sal) ntile
3 from emp;
ENAME SAL DEPTNO NTILE
-------------------- ---------- ---------- ----------
MILLER 1300 10 1
CLARK 2450 10 2
KING 5002 10 3
SMITH 800 20 1
ADAMS 1100 20 1
JONES 2975 20 2
SCOTT 3000 20 3
FORD 3000 20 4
JAMES 950 30 1
MARTIN 1250 30 1
WARD 1250 30 2
TURNER 1500 30 2
ALLEN 1600 30 3
BLAKE 2850 30 4
已选择14行。
6) ratio_to_report()
计算当前行的数值在分组中所有行的数值总和中所占的比例。
语法：ratio_to_report(expr) over([partition_clause])
例如，计算每个人的薪水在部门薪水总额中的比例。
[sql] view plain copy
tony@ORA11GR2> select ename, sal, deptno,
2 sum(sal) over(partition by deptno) dept_sal_sum,
3 ratio_to_report(sal) over(partition by deptno) ratio_to_report
4 from emp;
ENAME SAL DEPTNO DEPT_SAL_SUM RATIO_TO_REPORT
-------------------- ---------- ---------- ------------ ---------------
CLARK 2450 10 8752 .279936015
KING 5002 10 8752 .571526508
MILLER 1300 10 8752 .148537477
JONES 2975 20 10875 .273563218
FORD 3000 20 10875 .275862069
ADAMS 1100 20 10875 .101149425
SMITH 800 20 10875 .073563218
SCOTT 3000 20 10875 .275862069
WARD 1250 30 9400 .132978723
TURNER 1500 30 9400 .159574468
ALLEN 1600 30 9400 .170212766
JAMES 950 30 9400 .10106383
BLAKE 2850 30 9400 .303191489
MARTIN 1250 30 9400 .132978723
已选择14行。 **
扫一扫
1437
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
