[Oracle] KEEP dense_rank PARTITION BY 이해하기

Oracle과 같은 데이터베이스에서 Select 구문을 활용하다 보면 필수적으로 사용하게 되는 것이 집계함수입니다. 집계함수는 MAX, MIN, SUM, AVG, COUNT와 같이 그룹과 자주 사용하는 함수를 뜻합니다.

KEEP dense_rank PARTITION BY

테이블에 있는 최댓값을 구하거나, 그룹별 최댓값을 구하는 기초적인 과정을 넘어서, 해당 값의 다른 속성을 구하는 방법에 대해 고민을 해보셨다면, 이때에 사용하는 것이 바로 KEEP 키워드입니다. KEEP키워드는 집계함수적 성격으로 사용할 수도 있고, 분석함수적 성격으로 사용할 수도 있습니다.

KEEP 집계함수(Aggregate function)

이번 시간에 중점으로 다루어볼 KEEP 집계함수이며, 신택스는 다음과 같습니다. GROUP 없이 OVER 키워드를 사용할 때 KEEP을 분석적으로 사용하는 것입니다. 명칭이 어떻든 실제 사용 방법을 아는 것이 더 중요하겠죠?

aggregate_function KEEP ( DENSE_RANK FIRST | LAST ORDER BY
  expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
  [, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...)
[OVER query_partitioning_clause]

다음과 같이 EMP 테이블이 있다고 가정할 때 KEEP 함수로 각 직무별 집계를 진행해 보겠습니다.

SELECT * FROM EMP ORDER BY JOB;

     EMPNO ENAME                JOB                       MGR HIREDATE        SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
      7902 FORD                 ANALYST                  7566 81/03/12       3000                    20
      7788 SCOTT                ANALYST                  7566 03/06/17       3000                    20
      7876 ADAMS                CLERK                    7788 87/05/23       1100                    20
      7369 SMITH                CLERK                    7902 07/12/01        800                    20
      7900 JAMES                CLERK                    7698 81/03/12        950                    30
      7934 MILLER               CLERK                    7782 03/01/23       1300                    10
      7782 CLARK                MANAGER                  7839 99/09/06       2450                    10
      7566 JONES                MANAGER                  7839 01/02/04       2975                    20
      7698 BLAKE                MANAGER                  7839 91/01/05       2850                    30
      7839 KING                 PRESIDENT                     96/11/17       5000                    10
      7844 TURNER               SALESMAN                 7698 81/08/09       1500          0         30
      7654 MARTIN               SALESMAN                 7698 81/09/28       1250       1400         30
      7521 WARD                 SALESMAN                 7698 81/02/22       1250        500         30
      7499 ALLEN                SALESMAN                 7698 81/02/20       1600        300         30

14 rows selected.

각 직무별 급여에 대하여 MAX와 MIN을 통한 KEEP 함수를 사용하는 예제에 대해 알아보도록 합시다. 다음 예제는 각 직무별 [SAL]이 최대인 직원과 최소인 직원의 [SAL]과 [ENAME]을 알아보는 것입니다. 집계 함수는 단순히 최대치 및 최소치를 확인할 수 있는 반면, KEEP 키워드를 통해 직원의 이름까지 알아볼 수 있다는 것을 안내해 드리겠습니다.

SELECT JOB,
              MAX (SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL DESC) AS MAX_SAL,
              MAX (ENAME) KEEP (DENSE_RANK FIRST ORDER BY SAL DESC) AS MAX_ENAME,
              MIN (SAL) KEEP (DENSE_RANK FIRST ORDER BY SAL ASC) AS MIN_SAL,
              MIN (ENAME) KEEP (DENSE_RANK FIRST ORDER BY SAL ASC) AS MIN_ENAME
         FROM EMP
        WHERE JOB IN ('CLERK', 'MANAGER', 'SALESMAN')
     GROUP BY JOB;

JOB                   MAX_SAL MAX_ENAME               MIN_SAL MIN_ENAME
------------------ ---------- -------------------- ---------- --------------------
CLERK                    1300 MILLER                      800 SMITH
MANAGER                  2975 JONES                      2450 CLARK
SALESMAN                 1600 ALLEN                      1250 MARTIN

MAX KEEP FIRST 및 ORDER BY DESC를 통해, 각 직무별 최대 급여를 받는 사원의 급여와 이름을 알 수 있습니다. FIRST 대신 LAST를 사용했다면 그 반대의 결과를 가져왔을 것입니다. 마찬가지로, MIN KEEP FIRST 및 ORDER BY ASC를 통해 최솟값을 확인할 수 있습니다. 조합을 하면 MAX KEEP [FIRST | LAST], MIN KEEP [FIRST | LAST] 등 여러 가지 활용 방안이 보이실 겁니다.

KEEP 분석함수(Analytic function)

집계함수에 OVER 키워드 및 파티션 절을 사용하는 방법입니다. 별도의 그룹 없이 파티션을 통해 분석이 가능하도록 원하는 데이터를 모두 확인할 수 있는 장점이 있습니다. PARTITION을 통해 각 로우별 직무별, 부서별 최댓값을 확인하는 예제는 다음과 같습니다.

SELECT JOB,
              DEPTNO,
              MAX (SAL)
                 KEEP (DENSE_RANK FIRST ORDER BY SAL DESC)
                 OVER (PARTITION BY JOB, DEPTNO)
                 AS MAX_SAL,
              EMPNO,
              ENAME
         FROM EMP
        WHERE JOB IN ('CLERK', 'MANAGER', 'SALESMAN')
     ORDER BY EMP.JOB, EMP.DEPTNO;

JOB                    DEPTNO    MAX_SAL      EMPNO ENAME
------------------ ---------- ---------- ---------- --------------------
CLERK                      10       1300       7934 MILLER
CLERK                      20       1100       7876 ADAMS
CLERK                      20       1100       7369 SMITH
CLERK                      30        950       7900 JAMES
MANAGER                    10       2450       7782 CLARK
MANAGER                    20       2975       7566 JONES
MANAGER                    30       2850       7698 BLAKE
SALESMAN                   30       1600       7844 TURNER
SALESMAN                   30       1600       7654 MARTIN
SALESMAN                   30       1600       7499 ALLEN
SALESMAN                   30       1600       7521 WARD

11 rows selected.
댓글 쓰기
가져가실 때, 출처 표시 부탁드려요! 감사합니다. 💗