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

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

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

DENSE_RANK 函式，，同上，忽略重複值，返回名次

DENSE_RANK() WITHIN GROUP (ORDER BY col_list
[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;

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

[@more@]

cume_dist 函式

相關文章