Oracle과 같은 데이터베이스에서 Select 구문을 활용하다 보면 필수적으로 사용하게 되는 것이 집계함수입니다. 집계함수는 MAX, MIN, SUM, AVG, COUNT와 같이 그룹과 자주 사용하는 함수를 뜻합니다.
테이블에 있는 최댓값을 구하거나, 그룹별 최댓값을 구하는 기초적인 과정을 넘어서, 해당 값의 다른 속성을 구하는 방법에 대해 고민을 해보셨다면, 이때에 사용하는 것이 바로 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 MARTINMAX 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.