SQL> select * from wltest;
A B C
---------- ---------- --------------------
1 10 jiejie
2 30 gege
3 25 mama
4 50 baba
5 100 didi
2 20 didi
4 30 jiejie
5 90 mama
4 60 gege
4 60 gege
已选择10行。
SQL> select a,cume_dist(50) within group(order by b desc) cum_50 from wltest group by a;
A CUM_50
---------- ----------
1 .5
2 .333333333
3 .5
4 .8
5 1
A B C
---------- ---------- --------------------
1 10 jiejie
2 30 gege
3 25 mama
4 50 baba
5 100 didi
2 20 didi
4 30 jiejie
5 90 mama
4 60 gege
4 60 gege
已选择10行。
SQL> select a,cume_dist(50) within group(order by b desc) cum_50 from wltest group by a;
A CUM_50
---------- ----------
1 .5
2 .333333333
3 .5
4 .8
5 1
---------------------------------------------------
1 50 1/2
1 10
2 50 1/3
2 30
2 20
3 50 1/2
3 25
4 60
4 60
4 50
4 50 4/5
4 30
5 100
5 90
5 50 3/3
---------------------------------------------------------------------------
所以得出:
1 0.5
2 0.333
3 0.5
4 0.8
5 1
1 10
2 50 1/3
2 30
2 20
3 50 1/2
3 25
4 60
4 60
4 50
4 50 4/5
4 30
5 100
5 90
5 50 3/3
---------------------------------------------------------------------------
所以得出:
1 0.5
2 0.333
3 0.5
4 0.8
5 1
DENSE_RANK() WITHIN GROUP (ORDER BY col_list
[ASC|DESC] [NULLS {first|last}])
[ASC|DESC] [NULLS {first|last}])
------------------------------------------------------------
SELECT department_id
,COUNT(*) emp_count
,AVG(salary) mean
,DENSE_RANK(10000) WITHIN GROUP
(ORDER BY salary DESC) dense_rank_10K
FROM hr.employees
GROUP BY department_id;
,COUNT(*) emp_count
,AVG(salary) mean
,DENSE_RANK(10000) WITHIN GROUP
(ORDER BY salary DESC) dense_rank_10K
FROM hr.employees
GROUP BY department_id;
DEPARTMENT_ID EMP_COUNT MEAN DENSE_RANK_10K
------------- ---------- ------ --------------
10 1 4400 1
20 2 9500 2
30 6 4150 2
40 1 6500 1
50 45 3476 1
60 5 5760 1
70 1 10000 1
80 34 8956 7
90 3 19333 3
100 6 8600 2
110 2 10150 2
NULL 1 7000 1
------------- ---------- ------ --------------
10 1 4400 1
20 2 9500 2
30 6 4150 2
40 1 6500 1
50 45 3476 1
60 5 5760 1
70 1 10000 1
80 34 8956 7
90 3 19333 3
100 6 8600 2
110 2 10150 2
NULL 1 7000 1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11997930/viewspace-1026570/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11997930/viewspace-1026570/