oracle很有用的統計函數rank和cume

標籤：clerk

oracle 10g 裡面有scott使用者

alter user scott account unlock ;

使用 scott 密碼 tiger 進入，修改密碼，改使用者有emp 表；

rank函數 ：

SELECT deptno, job, SUM(sal),
RANK() OVER(PARTITION BY deptno ORDER BY
SUM(sal) DESC)
AS jobdep_rank,RANK() OVER(ORDER BY SUM(sal) DESC) AS sumsal_rank
FROM emp
GROUP BY deptno, job ;

DEPTNO	JOB	SUM(SAL)	JOBDEP_RANK	SUMSAL_RANK
20	ANALYST	6000	1	1
30	SALESMAN	5600	1	2
10	PRESIDENT	5000	1	3
20	MANAGER	2975	2	4
30	MANAGER	2850	2	5
10	MANAGER	2450	2	6
20	CLERK	1900	3	7
10	CLERK	1300	3	8
30	CLERK	950	3	9

cume_dist

SELECT deptno, job, SUM(sal),
CUME_DIST() OVER(PARTITION BY deptno ORDER BY
SUM(sal) DESC)
AS cume_dist_per_dep
FROM emp
GROUP BY deptno, job 
ORDER BY deptno, SUM(sal);

DEPTNO	JOB	SUM(SAL)	CUME_DIST_PER_DEP
10	CLERK	1300	1
10	MANAGER	2450	0.666667
10	PRESIDENT	5000	0.333333
20	CLERK	1900	1
20	MANAGER	2975	0.666667
20	ANALYST	6000	0.333333
30	CLERK	950	1
30	MANAGER	2850	0.666667
30	SALESMAN	5600	0.333333

 有了這兩個有用的函數，排名和百分比很快就能得到了吧。

oracle很有用的統計函數rank和cume