출처 : http://blog.naver.com/dudwo00/130013004957
분석함수는 8i 버전에서 persnal edition, enterprise edition에서 지원하고,
9i버전에서는 모든 버전에 지원된다. 문법은 다음과 같다.
SELECT Analytic_Function ( arguments )
OVER( [ PARTITION BY 칼럼 ] [ ORDER BY 절 ] [ Windowing 절] )
FROM 테이블 명;
- Partition By : 전체 집합을 기준에 의해 소그룹으로 나눔
- Order By : PARTITION BY에 나열된 그룹을 정렬함
- Windowing : 펑션의 대상이 되는 행 기준으로 범위를 세밀하게 조정
(메뉴얼: window IS a physical or logical SET of rows)
* Windowing절에 대한 설명
1. ROWS/RANGE UNBOUNDED PRECEDING/
CURRENT ROW/
value_expr PRECEDING
2. ROWS/RANGE BETWEEN UNBOUNDED PRECEDING/
CURRENT ROW/
value_expr PRECEDING/FOLLOWING
AND UNBOUNDED FOLLOWING
CURRENT ROW/
value_expr PRECEDING/FOLLOWING
: RANGE는 값이며, ROWS는 행의 수를 의미한다.
- RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING
: BETWEEN Current_Row -50 AND 150 의 파티션 내의 모든행이 window
즉, Current_Row의 값 - 50 ~ Current_Row의 값 + 150의 값은 모두 하나의 그룹
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
: 현재행을 기준으로 파티션 내의 앞/뒤 한건이 Window
- RANGE UNBOUNDED PRECEDING
: 현재 행을 기준으로 파티션 내의 첫 번째 행까지가 Window
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
: 현재행을 기준으로 파티션 내의 첫 번째 행부터 끝 행까지가 Window. 즉, 파티션 모두가 Window
select
ename
, sal
, sum(sal) over(partition by deptno order by sal) ASC순으로_부서별_sal누적
, sum(sal) over(partition by deptno) 부서별_sal합계
, sum(sal) over(order by deptno, sal) deptno_sal_ASC정렬후_누적
, sum(sal) over() sal총계
from emp
order by deptno, sal;
--over() 안의 oder by는 정렬 후 해당 행과 이전행을 모두 함친 것의 계산 - 여기에서는 SUM()
--over() 안의 partition by는 group by 역할을 한다. 분석에 대한 범위를 지정하는 역할.
/*
ENAME SAL ASC순으로_부서별_SAL누적 부서별_SAL합계 DEPTNO_SAL_ASC정렬후_누적 SAL총계
---------- --------- ------------------------ -------------- ------------------------- ----------
MILLER 1300.00 1300 8750 1300 29025
CLARK 2450.00 3750 8750 3750 29025
KING 5000.00 8750 8750 8750 29025
SMITH 800.00 800 10875 9550 29025
ADAMS 1100.00 1900 10875 10650 29025
JONES 2975.00 4875 10875 13625 29025
SCOTT 3000.00 10875 10875 19625 29025
FORD 3000.00 10875 10875 19625 29025
JAMES 950.00 950 9400 20575 29025
WARD 1250.00 3450 9400 23075 29025
MARTIN 1250.00 3450 9400 23075 29025
TURNER 1500.00 4950 9400 24575 29025
ALLEN 1600.00 6550 9400 26175 29025
BLAKE 2850.00 9400 9400 29025 29025
14 rows selected
*/
* 사원 중 직무별로 가장 많은 월급을 받는 사람의 사원번호, 직무명, 월급을 출력하라SELECT T1.EMPNO, T2.JOB, T2.MAX_SAL
FROM EMP T1,
(SELECT JOB, MAX(SAL) AS MAX_SAL
FROM EMP
GROUP BY JOB) T2
WHERE T1.JOB = T2.JOB
AND T1.SAL = T2.MAX_SAL;
EMPNO JOB MAX_SAL ----- --------- ---------- 7788 ANALYST 3000 7902 ANALYST 3000 7934 CLERK 1300 7566 MANAGER 2975 7839 PRESIDENT 5000 7499 SALESMAN 1600 6 rows selected* 아래와 같이 분석함수를 사용하면 위와 똑같은 결과를 가져올 수 있다. --놀라운 것은 테이블을 한번만 읽어서 처리를 할 수 있다는 것이다.
select empno, job, max_job_sal sal from ( select empno , job , sal , max(sal) over (partition by job) max_job_sal from emp ) where sal = max_job_sal;EMPNO JOB SAL ----- --------- ---------- 7788 ANALYST 3000 7902 ANALYST 3000 7934 CLERK 1300 7566 MANAGER 2975 7839 PRESIDENT 5000 7499 SALESMAN 1600 6 rows selected
* 모든 사원의 월급의 평균에 가장 근접한 사원의 사번과 월급 출력select empno, sal from ( select empno , min(abs_sal) over() min_sal , abs_sal , sal from ( select empno , sal , abs(sal - avg(sal) over()) abs_sal from emp) ) where abs_sal = min_sal;SQL> select avg(sal) from emp; AVG(SAL) ---------- 2073.21428 EMPNO SAL ----- --------- 7782 2450.00 부서별로 일련번호를 붙이되 각각의 부서마다 1로 시작하는 일련번호를 붙인다.SELECT deptno , empno , ename , row_number() over(PARTITION BY deptno ORDER BY deptno) FROM emp;DEPTNO EMPNO ENAME ROW_NUMBER()OVER(PARTITIONBYDE ------ ----- ---------- ------------------------------ 10 7782 CLARK 1 10 7839 KING 2 10 7934 MILLER 3 20 7369 SMITH 1 20 7876 ADAMS 2 20 7902 FORD 3 20 7788 SCOTT 4 20 7566 JONES 5 30 7499 ALLEN 1 30 7698 BLAKE 2 30 7654 MARTIN 3 30 7900 JAMES 4 30 7844 TURNER 5 30 7521 WARD 6 14 rows selected
* 부서별 월급이 많은 순서대로 순위구하기SELECT empno , ename , sal , rank() over(PARTITION BY deptno ORDER BY sal DESC) FROM emp;--rank() over(PARTITION BY deptno ORDER BY sal DESC)를 --rank() over(PARTITION BY deptno ORDER BY sal ASC)로 고치면 역순이 된다. --rank()는 동일 순위에 대하여 동일한 값을 리턴하지만 공백이 생긴다. --즉, 1위에 해당된느 행이 2개이면 2는 존재하지 않고, 바로 3으로 넘어간다. EMPNO ENAME SAL RANK()OVER(PARTITIONBYDEPTNOOR ----- ---------- --------- ------------------------------ 7839 KING 5000.00 1 7782 CLARK 2450.00 2 7934 MILLER 1300.00 3 7788 SCOTT 3000.00 1 7902 FORD 3000.00 1 7566 JONES 2975.00 3 7876 ADAMS 1100.00 4 7369 SMITH 800.00 5 7698 BLAKE 2850.00 1 7499 ALLEN 1600.00 2 7844 TURNER 1500.00 3 7521 WARD 1250.00 4 7654 MARTIN 1250.00 4 7900 JAMES 950.00 6SELECT empno , ename , sal , dense_rank() over(PARTITION BY deptno ORDER BY sal DESC) FROM emp;dense_rank()는 rank()와는 달리 1위가 2개 존재하면 다음의 순위는 2가 된다. EMPNO ENAME SAL DENSE_RANK()OVER(PARTITIONBYDE ----- ---------- --------- ------------------------------ 7839 KING 5000.00 1 7782 CLARK 2450.00 2 7934 MILLER 1300.00 3 7788 SCOTT 3000.00 1 7902 FORD 3000.00 1 7566 JONES 2975.00 2 7876 ADAMS 1100.00 3 7369 SMITH 800.00 4 7698 BLAKE 2850.00 1 7499 ALLEN 1600.00 2 7844 TURNER 1500.00 3 7521 WARD 1250.00 4 7654 MARTIN 1250.00 4 7900 JAMES 950.00 5
* TOP n개 가져오기SELECT empno , ename , sal , rank FROM ( SELECT empno , ename , sal , rank() over(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp) WHERE rank <= 3;경우에 따라서 rank(), dense_rank()를 사용한다. EMPNO ENAME SAL RANK ----- ---------- --------- ---------- 7839 KING 5000.00 1 7782 CLARK 2450.00 2 7934 MILLER 1300.00 3 7788 SCOTT 3000.00 1 7902 FORD 3000.00 1 7566 JONES 2975.00 3 7698 BLAKE 2850.00 1 7499 ALLEN 1600.00 2 7844 TURNER 1500.00 3
* 피봇SELECT deptno , max(CASE WHEN rank = 1 THEN sal END) rank_1 , max(CASE WHEN rank = 2 THEN sal END) rank_2 , max(CASE WHEN rank = 3 THEN sal END) rank_3 FROM ( SELECT sal , deptno , rank FROM ( SELECT sal , deptno , rank() over(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp) WHERE rank <= 3 ) GROUP BY deptnoDEPTNO RANK_1 RANK_2 RANK_3 ------ ---------- ---------- ---------- 10 5000 2450 1300 20 3000 2975 30 2850 1600 1500
* 앞 뒤행 가져오기 lead() lag() * 입사일에 차이에 따른 전/후 월급차이SELECT empno , ename , sal - lag(sal, 1) over(ORDER BY hiredate) pre , sal , sal - lead(sal, 1) over(ORDER BY hiredate) NEXT FROM emp;EMPNO ENAME PRE SAL NEXT ----- ---------- ---------- --------- ---------- 7369 SMITH 800.00 -800 7499 ALLEN 800 1600.00 350 7521 WARD -350 1250.00 -1725 7566 JONES 1725 2975.00 125 7698 BLAKE -125 2850.00 400 7782 CLARK -400 2450.00 950 7844 TURNER -950 1500.00 250 7654 MARTIN -250 1250.00 -3750 7839 KING 3750 5000.00 4050 7900 JAMES -4050 950.00 -2050 7902 FORD 2050 3000.00 1700 7934 MILLER -1700 1300.00 -1700 7788 SCOTT 1700 3000.00 1900 7876 ADAMS -1900 1100.00* 특정 범위내의 첫행/끝행SELECT DEPTNO,ENAME,SAL, FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FV, LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LV FROM EMP;DEPTNO ENAME SAL FV LV ------ ---------- --------- ---------- ---------- 10 KING 5000.00 KING MILLER 10 CLARK 2450.00 KING MILLER 10 MILLER 1300.00 KING MILLER 20 SCOTT 3000.00 SCOTT SMITH 20 FORD 3000.00 SCOTT SMITH 20 JONES 2975.00 SCOTT SMITH 20 ADAMS 1100.00 SCOTT SMITH 20 SMITH 800.00 SCOTT SMITH 30 BLAKE 2850.00 BLAKE JAMES 30 ALLEN 1600.00 BLAKE JAMES 30 TURNER 1500.00 BLAKE JAMES 30 WARD 1250.00 BLAKE JAMES 30 MARTIN 1250.00 BLAKE JAMES 30 JAMES 950.00 BLAKE JAMES* RATIO_TO_REPORT : 그룹에서 현재 행의 비율SELECT JOB, ENAME, SAL, RATIO_TO_REPORT(SAL) OVER(PARTITION BY JOB) AS RR FROM EMP ORDER BY JOB, ENAME;JOB ENAME SAL RR --------- ---------- --------- ---------- ANALYST FORD 3000.00 0.5 ANALYST SCOTT 3000.00 0.5 CLERK ADAMS 1100.00 0.26506024 CLERK JAMES 950.00 0.22891566 CLERK MILLER 1300.00 0.31325301 CLERK SMITH 800.00 0.19277108 MANAGER BLAKE 2850.00 0.34441087 MANAGER CLARK 2450.00 0.29607250 MANAGER JONES 2975.00 0.35951661 PRESIDENT KING 5000.00 1 SALESMAN ALLEN 1600.00 0.28571428 SALESMAN MARTIN 1250.00 0.22321428 SALESMAN TURNER 1500.00 0.26785714 SALESMAN WARD 1250.00 0.22321428SELECT ENAME,SAL, COUNT(*) OVER(ORDER BY SAL RANGE BETWEEN 149 PRECEDING AND 0 FOLLOWING) AS m_cnt FROM EMP;ENAME SAL M_CNT ---------- --------- ---------- SMITH 800.00 1 JAMES 950.00 2 ---> 950 - 150 ~ 950 + 0, 만약 149로 범위를 줄이면 1로 된다. ADAMS 1100.00 2 WARD 1250.00 3 MARTIN 1250.00 3 MILLER 1300.00 3 TURNER 1500.00 1 ALLEN 1600.00 2 CLARK 2450.00 1 BLAKE 2850.00 1 JONES 2975.00 2 SCOTT 3000.00 4 FORD 3000.00 4 KING 5000.00 1
'DB 이야기 > OracleLearn' 카테고리의 다른 글
| 10G purge 휴지통 비우기/복원 기능 (0) | 2012.06.28 |
|---|---|
| Oracle Text를 이용한 전체 텍스트 검색 애플리케이션의 구현 (0) | 2009.03.27 |
| ROWNUM의 동작 원리와 활용 방법 (0) | 2009.02.05 |
| Oracle BPEL Process Manager를 이용한 PeopleSoft CRM과 Oracle E-Business Suite의 통합 (0) | 2008.06.03 |
| 이기종 EAI 환경에 BPEL 추가하기 (0) | 2008.06.03 |
