오라클 KEEP , FIRST , LAST 예제로 확인하는 사용법

오라클 KEEP

KEEP 단어 자체의 의미를 보면

"유지하다" 라는 의미를 가지고 있습니다.

오라클에서 사용하는 KEEP 은 단독으로 사용하기 보다는

FIRST , LAST 와 함께 사용하게 되는데

정렬을 통해 첫번째나 마지막에 나온 row 를 KEEP 해놓고 ( 유지 / 보관해놓고 )

해당 row 의 다른 컬럼을 사용한다고 생각하면 쉽게 사용할 수 있습니다.

다만 어떤 기준으로 추출하는지 명시적으로 사용할 수 있는 장점이 있습니다.

잠깐 예를 들어볼까요?

어느 회사에서 "부서별로 가장 높은 급여를 받는 사람이 얼마지?" 라고 하면

단순히 부서별 GROUP BY 후 MAX 값으로 추출하면 되겠죠?

그런데 "부서별로 가장 오래 근무한 사람의 급여가 얼마지?" 라고 하면

부서별 입사일이 가장 빠른 사람을 추출 후 급여를 찾아야 해서 서브쿼리나 다른 방법으로 쿼리를 해야겠죠?

하지만 KEEP 문법을 사용하면 한번에 찾을수 있더라고요!

그럼 샘플 데이터를 통해서 확인해볼께요!

샘플데이터

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

 

 

FIRST 와 LAST 는 비슷하게 사용할 수 있는데

집계함수나 분석함수로 조회된 ROW 에 대해 동작할 수 있습니다.

FIRST나 LAST 를 사용하면

HIREDATE 라는 특정 컬럼을 기준으로 정렬해야 하지만 필요한 값이 HIREDATE 가 아닌 경우

FIRST 나 LAST 함수를 사용하면 SELF JOIN 이나 VIEW 가 필요 없이 더 좋은 성능으로 사용할 수 있습니다.

SELECT DEPTNO
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY HIREDATE ) AS JOIN_FIRST_SAL
     , MAX(SAL) KEEP ( DENSE_RANK LAST  ORDER BY HIREDATE ) AS JOIN_LAST_SAL
  FROM EMP
 GROUP BY DEPTNO

결과

DEPTNO	JOIN_FIRST_SAL	JOIN_LAST_SAL
10	2450	1300
20	800	1100
30	1600	950

위처럼 입사일(HIREDATE)을 기준으로

입사일이 오래된 순으로 정렬(FIRST)할 것인지

최근 입사한 순으로 정렬(LAST)할 것인지 정렬 후

집계함수(MAX, MIN)를 사용하면 집계함수에서 조회 된

첫번째나 마지막에 출력된 row 에 존재하는 급여(SAL)컬럼을 사용 할 수 있습니다.

CAUTION

하지만, FIRST 나 LAST 를 사용 시 주의 할 점이 있는데

바로, 정렬 시 null 이 존재하는 경우입니다.

정렬을 FIRST 로 입력 시 null 값은 기본적으로 맨 처음에 조회되기 때문에

의도된 바와 다른 결과를 조회할 수 있는데 아래 예를 확인해보겠습니다.

SELECT DEPTNO
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY MGR NULLS FIRST ) AS MGR_NULLS_FST
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY MGR NULLS LAST  ) AS MGR_NULLS_LST
  FROM EMP
 GROUP BY DEPTNO

결과

DEPTNO	MGR_NULLS_FST	MGR_NULLS_LST
10	5000	1300
20	3000	3000
30	950	950

같은 문법이지만 NULLS FIRST 로 사용했느냐 NULLS LAST 로 사용했느냐에 따라

처리결과가 다르게 조회된 것을 확인할 수 있습니다.

NULLS FIRST 와 NULLS LAST 는 null 값이 존재할 때

null 을 앞에 조회되게 할 지, 뒤에 조회되게 할 지를 명시하는 문장이라고 보시면 됩니다.

같은 사용방법으로 맨 하단 ORDER BY 문장에도 사용할 수 있어요

SELECT *
   FROM EMP
  ORDER BY MGR NULLS LAST>

결과

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

SELECT *
   FROM EMP
  ORDER BY MGR NULLS FIRST

결과

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

결과와 같이 MGR 을 기준으로 오름차순으로 정렬되는건 동일하지만

null 의 위치가 어디에 위치하라고 명시해놓는 문법이라고 보시면 됩니다

또한, OVER 절을 사용하면 분석함수로도 사용할 수 있습니다.

부서에서 제일먼저 입사한 직원과 나중에 입사한 직원의 급여가 본인의 급여와 어느정도 차이나는지 비교해보세요.

SELECT DEPTNO, ENAME, SAL, HIREDATE
     , MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO )       AS JOIN_FIRST_SAL   /* 부서내 가장 먼저 입사한 직원의 급여 */
     , MAX(SAL) KEEP ( DENSE_RANK LAST  ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO )       AS JOIN_LAST_SAL    /* 부서내 최근 입사한 직원의 급여 */
     , SAL - MIN(SAL) KEEP ( DENSE_RANK FIRST ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO ) AS JOIN_FIRST_DIFF  /* 부서내 가장 먼저 입사한 직원과 본인의 급여 비교 */
     , SAL - MAX(SAL) KEEP ( DENSE_RANK LAST  ORDER BY HIREDATE ) OVER ( PARTITION BY DEPTNO ) AS JOIN_LAST_DIFF   /* 부서내 최근 입사한 직원과 본인의 급여 비교 */
  FROM EMP
 ORDER BY DEPTNO, HIREDATE

결과

DEPTNO	ENAME	SAL	HIREDATE	JOIN_FIRST_SAL	JOIN_LAST_SAL	JOIN_FIRST_DIFF	JOIN_LAST_DIFF
10	CLARK	2450	1981-06-09	2450	1300	0	1150
10	KING	5000	1981-11-17	2450	1300	2550	3700
10	MILLER	1300	1982-01-23	2450	1300	-1150	0
20	SMITH	800	1980-12-17	800	1100	0	-300
20	JONES	2975	1981-04-02	800	1100	2175	1875
20	FORD	3000	1981-12-03	800	1100	2200	1900
20	SCOTT	3000	1987-04-19	800	1100	2200	1900
20	ADAMS	1100	1987-05-23	800	1100	300	0
30	ALLEN	1600	1981-02-20	1600	950	0	650
30	WARD	1250	1981-02-22	1600	950	-350	300
30	BLAKE	2850	1981-05-01	1600	950	1250	1900
30	TURNER	1500	1981-09-08	1600	950	-100	550
30	MARTIN	1250	1981-09-28	1600	950	-350	300
30	JAMES	950	1981-12-03	1600	950	-650	0

위의 쿼리는 부서별로 파티션을 나눈 후 최초입사, 최근입사로 정렬 후 급여를 추출했습니다.

입사일로 MAX 와 MIN 을 조회했지만 실질적으로 급여를 추출할 수 있었습니다.

이상 KEEP 문법을 사용하여 쿼리를 간단하게 작성하는법을 확인해보았습니다.

집계함수 MIN, MAX, SUM, AVG, COUNT, VARIANCE, STDDEV 함수에 적용하여

FIRST 나 LAST 에 해당하는 row 의 값에 대해 동작할 수 있으니 직접 사용하여 확인해보세요^^