[ORACLE] ORACLE 오라클 9i부터 등장한 분석함수 사용방법 정리입니다.
분석함수의 사용
분석함수는 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 6
*/
SELECT
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 deptno
/*
DEPTNO 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.22321428
*/
--
SELECT 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
*/
퍼옴 http://cafe.daum.net/itbanksecu/CYF7/11
이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받고 있습니다.
이 포스팅은 제휴마케팅이 적용되어 작성자에게 일정액의 커미션이 제공될수 있습니다.
