[Oracle] 그룹별 가장 최근 데이터 1건 가져오기

오라클에서 그룹별 가장 최근 데이터 또는 마지막 데이터 1건만 조회하기 위해서는 분석함수(ROW_NUMBER, RANK)를 사용하면 쉽게 해결할 수 있다. 또 다른 방법으로 서브 쿼리를 사용하여 MAX 값을 추출하여 일치하는 데이터만 조회하는 방법도 있다. 마지막 날짜 데이터, 그룹별 최솟값 최댓값 등 1건의 데이터를 조회하는 방법은 아래의 예시를  참고하여 활용하면 된다.

 

목차 ROW_NUMBER 함수를 사용하여 최근 데이터 가져오기 RANK 함수를 사용하여 최근 데이터 가져오기 서브쿼리와 MAX 함수를 사용하여 최근 데이터 가져오기

 

ROW_NUMBER 함수를 사용하여 최근 데이터 가져오기

SELECT job
     , empno
     , ename
     , hiredate
  FROM (
         SELECT empno
              , ename
              , job
              , hiredate
              , ROW_NUMBER() OVER(PARTITION BY job ORDER BY hiredate DESC) AS rn
           FROM emp
       )
 WHERE rn = 1

 

 

위의 예제는 직업(job) 별로 입사일(hiredate)을 내림차순으로 정렬 후 그룹별 첫 번째 데이터만 조회한 예제이다.

ROW_NUMBER 함수를 사용하면 그룹별로 정렬 후 순번을 부여할 수 있다.

 

정확한 순번을 부여하기 위해서는 OVER 절 내부의 ORDER BY 절에 유일한 값이 되도록 칼럼을 지정해야 한다. 최근 입사일이 동일한 사원이 있을 경우 ORDER BY hiredate DESC, empno DESC 이렇게 세부적으로 지정하는 것이 좋다.

 

RANK 함수를 사용하여 최근 데이터 가져오기

SELECT job
     , empno
     , ename
     , hiredate
  FROM (
         SELECT empno
              , ename
              , job
              , hiredate
              , RANK() OVER(PARTITION BY job ORDER BY hiredate DESC) AS rn
           FROM emp
       )
 WHERE rn = 1

 

 

RANK 함수를 사용해도 ROW_NUMBER 함수를 사용할 때와 동일한 결과를 얻을 수 있다.

ROW_NUMBER 함수와  차이점은 RANK 함수는 정렬 조건이 동일한 데이터가 존재할 경우 2건 이상이 조회될 수 있다. (ROW_NUMBER 함수는 무조건 1건의 데이터만 조회됨)

 

※ RANK 함수 사용 시 주의점

WITH temp AS (
  SELECT '7698' AS empno, 'BLAKE'  AS ename, 'MANAGER'   AS job, '7839' AS mgr, '1981-05-01' AS hiredate FROM dual UNION ALL
  SELECT '7782' AS empno, 'CLARK'  AS ename, 'MANAGER'   AS job, '7839' AS mgr, '1981-06-09' AS hiredate FROM dual UNION ALL
  SELECT '7566' AS empno, 'JONES'  AS ename, 'MANAGER'   AS job, '7839' AS mgr, '1981-04-02' AS hiredate FROM dual UNION ALL
  SELECT '7499' AS empno, 'ALLEN'  AS ename, 'SALESMAN'  AS job, '7698' AS mgr, '1981-02-20' AS hiredate FROM dual UNION ALL
  SELECT '7521' AS empno, 'WARD'   AS ename, 'SALESMAN'  AS job, '7698' AS mgr, '1981-02-22' AS hiredate FROM dual UNION ALL
  SELECT '7654' AS empno, 'MARTIN' AS ename, 'SALESMAN'  AS job, '7698' AS mgr, '1981-09-28' AS hiredate FROM dual UNION ALL
  SELECT '7844' AS empno, 'TURNER' AS ename, 'SALESMAN'  AS job, '7698' AS mgr, '1981-09-28' AS hiredate FROM dual 
)

SELECT job
     , empno
     , ename
     , hiredate
  FROM (
         SELECT empno
              , ename
              , job
              , hiredate
              , RANK() OVER(PARTITION BY job ORDER BY hiredate DESC) AS rn
           FROM temp
       )
 WHERE rn = 1

 

 

ORDER BY 절에 최근 데이터의 정렬 조건이 동일한 데이터가 존재할 경우 1건의 데이터만 필요하다면 조금 더 상세한 기준이 필요하다. (예, ORDER BY hiredate DESC, empno DESC)

 

서브 쿼리와 MAX 함수를 사용하여 최근 데이터 가져오기

SELECT job
     , empno
     , ename
     , hiredate
  FROM emp a
 WHERE hiredate = ( SELECT MAX(b.hiredate)
                      FROM emp b
                     WHERE b.job = a.job )
 ORDER BY job

 

 

그룹으로 사용할 칼럼에 인덱스가 존재한다면 서브 쿼리를 사용하여 MAX 값을 가져온 후 해당 입사일의 사원만 조회할 수 있다. 테이블에 데이터가 많고 조인할 칼럼에 인덱스가 없다면 사용하지 않는 것을 권장한다.

 

예제쿼리(테이블포함).txt

0.00MB

 

[Oracle] 분석함수 사용법

[Oracle] 상위 n개 데이터를 추출하는 2가지 방법

[Oracle] ROW_NUMBER 함수 사용법

[Oracle] RANK 함수 사용법