[Oracle] OVER ... PARTITION BY 분석절 분석함수 ANALYTIC FUNCTION

사원 정보를 입력하는데, 전체 급여 평균값과 함께 출력하시오.

ORACLE SCOTT 계정 EMP 테이블로 출력된 결과입니다.

위 문제를 풀기 위해 어떻게 쿼리를 작성하실건가요?

1
2
3
4
5
6
7
8

SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,JOB
    ,SAL
    ,ROUND((SELECT AVG(SAL) FROM EMP)) AS AVG
FROM EMP;
 

SUBQUERY를 이용할 수도 있지만... ANALYTIC FUNCTION 분석함수를 사용해서 좀 더 편한 방법으로 해결할 수 있습니다.

1
2
3
4
5
6
7
8

SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,JOB
    ,SAL
    ,ROUND(AVG(SAL) OVER()) AS AVG
FROM EMP;

분석함수 OVER 절을 사용해서 문제를 풀어봤습니다.

간단하게 전체 평균값을 구하는 문제라 두개의 쿼리에는 별 차이가 없어 보입니다. 그럼 각 부서별 평균을 표시하면 어떨까요?

사원 정보를 입력하는데, 각각 부서 평균값과 함께 출력하시오.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT
    E1.DEPTNO
    ,EMPNO
    ,ENAME
    ,JOB
    ,SAL
    ,SUM_SAL
FROM EMP E1, (
    SELECT
        DEPTNO
        ,SUM(SAL) AS SUM_SAL
    FROM EMP
    GROUP BY DEPTNO
) E2
WHERE E1.DEPTNO = E2.DEPTNO
ORDER  BY DEPTNO;

SUBQUERY를 사용하면 위와 같이 해결할 수 있습니다. 그럼 똑같은 문제를 분석함수를 이용해서 풀면 어떨까요?

1
2
3
4
5
6
7
8

SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,JOB
    ,SAL
    ,SUM(SAL) OVER(PARTITION BY DEPTNO) AS SUM_SAL
FROM EMP;
 

눈으로만 봐도 확실히 SUBQUERY를 사용하는 것 보다 간단하다는 것을 알 수 있습니다.

그럼 OVER ... PARTITION BY를 사용하는 분석함수란 무엇일까요?

분석함수란?

SELECT
분석함수 OVER([PARTITION BY 칼럼] [ORDER BY 칼럼] [WINDOWING 절])
FROM 테이블;

분석함수는 이미 사용해보셨을 겁니다. COUNT(), AVG(), SUM() 등과 같이 GROUP BY 절과 함께 사용했던 함수들도 있고, RANK(), ROW_NUMBER(), DENSE_RANK(), LEAD(), LAG(), NTITLE() 등 ORACLE 버전이 올라가면서 많이 생겨나고 있습니다. 이런 분석함수 뒤에 ANALYTIC CLAUSE( OVER 절)을 통해서 행 그룹의 정의를 지정하고 각 그룹당 결과값을 반복하여 출력하는 것 입니다. 여기서 행 그룹은 칼럼에 대한 행들이고 행 그룹의 범위(WINDOW)를 PARTITION BY, ORDER BY, WINDOWING으로 조절이 가능합니다.

위의 문제를 예시로 봤을때,

SUM(SAL) --> 분석함수 SUM을 사용했고 SAL 칼럼에 대한 행들이 행 그룹입니다.
OVER --> 분석절이라고 합니다. 분석함수에 대한 조절을 OVER절 안에서 합니다.
PARTITION BY --> GROUP BY와 동일하게 그룹지어 결과를 출력 합니다.

ORDER BY --> PARTITION BY로 정의된 WINDOW 내에서 행들의 정렬순서를 정의해줍니다.

1
2
3
4
5
6
7
8

SELECT
    DEPTNO
    ,EMPNO
    ,ENAME
    ,JOB
    ,SAL
    ,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS SUM_SAL
FROM EMP;
 