[Oracle] OVER 함수 - GROUP BY 없이 최대,최소,평균 구하기

오라클에서 OVER() 를 사용하면 GROUP BY나 서브쿼리를 사용하지 않고
분석 함수(SUM, MAX, COUNT)와
집계 함수(GROUP BY, ORDER BY)를 사용할 수 있다.
집계 함수 사용 시 서브쿼리가 길어지기 때문에
OVER 함수를 사용해 쿼리 길이를 줄일 수 있다.

 

1) OVER 함수란?

: OVER 함수는 GROUP BY, ORDER BY 서브쿼리를 개선하기 위해 나온 함수이다.
분석 함수를 사용해야 하는 경우,

OVER 함수를 활용한다면 복잡한 서브쿼리를 최소화할 수 있다.
간결하게 쿼리문을 작성하고 싶다면 해당 함수를 활용하는 것이 좋다.
뒤에 PARTITION BY를 함께 사용해야 GROUP BY의 기능을 활용할 수 있다.
추가적으로 ORDER BY도 지정해줄 수 있다.

 

2) OVER 함수 구문

• COUNT(*) OVER() : 전체행 카운트
• COUNT(*) OVER(PARTITION BY 컬럼) : 그룹 단위로 나누어 카운트
• MAX(컬럼) OVER() : 전체행 중에 최대값
• MAX(컬럼) OVER(PARTITION BY 컬럼) : 그룹내 최대값
• MIN(컬럼) OVER() : 전체행 중에 최소값
• MIN(컬럼) OVER(PARTITION BY 컬럼) : 그룹내 최소값
• SUM(컬럼) OVER() : 전체행 합
• SUM(컬럼) OVER(PARTITION BY 컬럼) : 그룹내 합
• AVG(컬럼) OVER() : 전체행 평균
• AVG(컬럼) OVER(PARTITION BY 컬럼) : 그룹내 평균

 

3) OVER 함수 예시

예시는 아래의 임시 테이블을 활용하여 작성해보았다.

-- EMP_NO: 사원번호, EMP_NM: 사원명, HIRE_DATE: 입사일, SAL: 연봉
WITH EMP_TABLE AS (
    SELECT '5020' AS EMP_NO, 'SCOOT' AS EMP_NM, '20120302' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5004' AS EMP_NO, 'BLAKE' AS EMP_NM, '20100821' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5001' AS EMP_NO, 'ANDREW' AS EMP_NM, '20100103' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5006' AS EMP_NO, 'ADAM' AS EMP_NM, '20100901' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5038' AS EMP_NO, 'VICTORIA' AS EMP_NM, '20150617' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5039' AS EMP_NO, 'MARY' AS EMP_NM, '20150617' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5007' AS EMP_NO, 'JAMES' AS EMP_NM, '20100911' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5024' AS EMP_NO, 'NICOLE' AS EMP_NM, '20120302' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5003' AS EMP_NO, 'SMITH' AS EMP_NM, '20130810' AS HIRE_DATE FROM DUAL
),
SAL_TABLE AS (
    SELECT '5020' AS EMP_NO, 2100 AS SAL FROM DUAL
    UNION ALL
    SELECT '5004' AS EMP_NO, 2100 AS SAL FROM DUAL
    UNION ALL
    SELECT '5001' AS EMP_NO, 2280 AS SAL FROM DUAL
    UNION ALL
    SELECT '5006' AS EMP_NO, 3100 AS SAL FROM DUAL
    UNION ALL
    SELECT '5038' AS EMP_NO, 2900 AS SAL FROM DUAL
    UNION ALL
    SELECT '5039' AS EMP_NO, 1460 AS SAL FROM DUAL
    UNION ALL
    SELECT '5007' AS EMP_NO, 1900 AS SAL FROM DUAL
    UNION ALL
    SELECT '5024' AS EMP_NO, 1820 AS SAL FROM DUAL
    UNION ALL
    SELECT '5003' AS EMP_NO, 2450 AS SAL FROM DUAL
)
SELECT A.*, B.SAL
FROM EMP_TABLE A
LEFT JOIN SAL_TABLE B
    ON A.EMP_NO = B.EMP_NO
ORDER BY A.EMP_NO;

 

 

 

기존 GROUP BY를 활용한 경우와, OVER 함수를 활용한 경우 두 가지를 예시로 들어보자.
원하는 결과값은 입사년도별 최고 연봉이다.

3-1) GROUP BY, ORDER BY 활용

SELECT SUBSTR(A.HIRE_DATE, 0, 4) AS YEAR,
       MAX(SAL) AS MAX_SAL
FROM EMP_TABLE A
LEFT JOIN SAL_TABLE B
    ON A.EMP_NO = B.EMP_NO
GROUP BY SUBSTR(A.HIRE_DATE, 0, 4)
ORDER BY SUBSTR(A.HIRE_DATE, 0, 4), A.EMP_NM;

3-2) OVER PARTITION BY 활용

SELECT A.EMP_NM, 
       SUBSTR(A.HIRE_DATE, 0, 4) AS YEAR, 
       MAX(SAL) OVER (PARTITION BY SUBSTR(A.HIRE_DATE, 0, 4) ORDER BY SUBSTR(A.HIRE_DATE, 0, 4), A.EMP_NM) AS MAX_SAL 
FROM EMP_TABLE A
LEFT JOIN SAL_TABLE B
    ON A.EMP_NO = B.EMP_NO;

기존 GROUP BY 구문 대신 OVER PARTITION BY 내에
그룹을 지을 컬럼을 명시해준다.
ORDER BY도 함께 해당 함수 내에 사용하여 적용할 수 있다.

3-3) 결과값

 

두 가지 경우 모두 결과값은 위와 같고 상황에 맞게 더 편한 함수를 사용하면 되지만,
OVER 함수를 사용한 경우가 더 간결하게 보여지기 때문에 소개를 해보았다.

 

 

[reference]
https://javaexpert.tistory.com/503
https://wakestand.tistory.com/243