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

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

 

목차
  1. ROW_NUMBER 함수를 사용하여 최근 데이터 가져오기
  2. RANK 함수를 사용하여 최근 데이터 가져오기
  3. 서브쿼리와 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] 분석함수 사용법

오라클에서 분석함수를 사용할 때 PARTITION BY를 사용하여 그룹으로 묶어서 연산을 할 수 있다. GROUP BY 절을 사용하지 않고, 조회된 각 행에 그룹으로 집계된 값을 표시할 때 OVER 절과 함께 PARTITION B

gent.tistory.com

 

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

오라클에서 상위 3개, 5개, 10개, 100개 등 n개의 데이터를 뽑기 위해서는 서브 쿼리를 활용하면 쉽게 해결할 수 있다. 서브 쿼리(Inline View)를 사용하여 데이터를 먼저 정렬한 후, 원하는 개수만큼

gent.tistory.com

 

[Oracle] ROW_NUMBER 함수 사용법

1. ROWNUM 키워드 이용하는 방법 - 조회된 순서되로 순번을 매긴다. SELECT ROWNUM , a.* FROM emp a - ORDER BY 를 사용하면 순번이 뒤섞이므로 정렬된 서브쿼리 결과에 ROWNUM 을 매겨야 한다. SELECT ROWNUM , x.* FRO

gent.tistory.com

 

[Oracle] RANK 함수 사용법

오라클에서 성적, 급여, 매출 등 순위를 구하기 위해서 순위 함수(RANK, DENSE_RANK)를 사용하면 된다. 순위 함수의 순위는 OVER 함수 내부의 ORDER BY 컬럼 값으로 결정된다. RANK() : 중복 순위 개수만큼

gent.tistory.com

 

댓글(0)

Designed by JB FACTORY