[ORACLE] 분석용함수의이용 (9i이상) ~ 화랑천's 소소하기

1 기본 사항
2 Windowing절에 대한 설명
3 활용 예제

1 기본 사항 #

분석함수는 8i 버전에서 persnal edition, enterprise edition에서 지원하고, 9i버전에서는 모든 버전에 지원된다. 문법은 다음과 같다.

SELECT Analytic_Function ( arguments )
       OVER( [ PARTITION BY 칼럼 ] [ ORDER BY 절 ] [ Windowing 절] )
FROM 테이블 명;

* IE에서 소스 복사시 줄바꿈 안됨. MS-Word 등에 붙여 넣으세요. 줄바꿈 되는 브라우저: Chrome, Opera, Safari

Partition By : 전체 집합을 기준에 의해 소그룹으로 나눔
Order By : PARTITION BY에 나열된 그룹을 정렬함
Windowing : 펑션의 대상이 되는 행 기준으로 범위를 세밀하게 조정 (메뉴얼: window IS a physical or logical SET of rows)

2 Windowing절에 대한 설명 #

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

3 활용 예제 #

요것만 이해하면 분석용 함수는 끝난다.

 
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
*/

* IE에서 소스 복사시 줄바꿈 안됨. MS-Word 등에 붙여 넣으세요. 줄바꿈 되는 브라우저: Chrome, Opera, Safari

화랑천's 소소하기
살아가면서 소소한것들을 적어봅니다.

2010. 6. 17.

[ORACLE] 분석용함수의이용 (9i이상)

Contents

1 기본 사항 #

2 Windowing절에 대한 설명 #

3 활용 예제 #

댓글 1개:

화랑천's 소소하기살아가면서 소소한것들을 적어봅니다.

2010. 6. 17.

[ORACLE] 분석용함수의이용 (9i이상)

Contents

1 기본 사항 #

2 Windowing절에 대한 설명 #

3 활용 예제 #

댓글 1개:

화랑천's 소소하기
살아가면서 소소한것들을 적어봅니다.