[Oracle] Over (partition by) 집계 함수 사용 방법~ Aggregate functions 알아보기

오라클에서 그룹 함수는 SUM(), MAX(), MIN(), AVG(), COUNT() 등이 있으며, Group By 절과 함께 사용합니다. 그룹 키워드를 누락하고 그룹 함수를 사용하면 'ORA-00937: 단일 그룹의 그룹 함수가 아닙니다.' 오류가 발생하게 되지요.

그리하여 집계 함수와 함께 다른 컬럼 값을 확인하려면 해당 값을 일일이 열거해 주어야 합니다.

SELECT SUM(SAL), EMP.* FROM EMP;

ERROR at line 1:
ORA-00937: not a single-group group function

위의 예시에서 부서별 샐러리의 합계를 확인하고 싶다면, 다음과 같이 샐러리 컬럼으로 그룹을 해야 할 것입니다.

SELECT SUM(SAL), DEPTNO FROM EMP GROUP BY DEPTNO;

  SUM(SAL)     DEPTNO
---------- ----------
      9400         30
     10875         20
      8750         10

그렇다면 그룹 함수와 그룹에 포함되지 않는 일반 컬럼을 함께 조회하는 방법은 없을까요? 당연히 있습니다. 오늘 소개해드릴 함수는 바로 OVER 함수랍니다. GROUP 없이 집계 함수를 이용하도록 오라클은 OVER 구문을 지원합니다!

예시는 SUM OVER 로 급여(SAL)합계를 확인할 수도 있고, SUM OVER(PARTITION BY) 를 통해 직업별(JOB) 급여합계를 확인할 수 있습니다. 다음을 참고해 주세요~

  SELECT SUM (SAL) OVER (),
         SUM (SAL) OVER (PARTITION BY JOB),
         AVG (SAL) OVER (),
         AVG (SAL) OVER (PARTITION BY JOB),
         MAX (SAL) OVER (),
         MAX (SAL) OVER (PARTITION BY JOB),
         MIN (SAL) OVER (),
         MIN (SAL) OVER (PARTITION BY JOB),
         COUNT (SAL) OVER (),
         COUNT (SAL) OVER (PARTITION BY JOB),
         STDDEV (SAL) OVER (),
         STDDEV (SAL) OVER (PARTITION BY JOB),
         TA.*
    FROM EMP TA
ORDER BY JOB, EMPNO;

14 rows selected.

위의 쿼리는 결과가 너무 길어서 아래와 같이 단순하게 대체하였습니다. 총 합계, JOB별 합계, 사원번호, 사원명을 같이 조회할 수 있는 것을 확인할 수 있습니다.

  SELECT SUM (SAL) OVER (),
         SUM (SAL) OVER (PARTITION BY JOB),
         EMPNO,
         ENAME
    FROM EMP TA
ORDER BY JOB, EMPNO;

SUM(SAL)OVER() SUM(SAL)OVER(PARTITIONBYJOB)      EMPNO ENAME
-------------- ---------------------------- ---------- --------------------
         29025                         6000       7788 SCOTT
         29025                         6000       7902 FORD
         29025                         4150       7369 SMITH
         29025                         4150       7876 ADAMS
         29025                         4150       7900 JAMES
         29025                         4150       7934 MILLER
         29025                         8275       7566 JONES
         29025                         8275       7698 BLAKE
         29025                         8275       7782 CLARK
         29025                         5000       7839 KING
         29025                         5600       7499 ALLEN
         29025                         5600       7521 WARD
         29025                         5600       7654 MARTIN
         29025                         5600       7844 TURNER

14 rows selected.