Where The Streets Have No Name

분석함수의 사용 본문

Developement/DataBase

분석함수의 사용

highheat 2006. 4. 21. 13:12
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
*/



'Developement/DataBase' Related Articles more