[Oracle]
SELECT empno, ename, job, sal,
ROW_NUMBER() OVER(PARTITION BY job ORDER BY sal) AS rnum
FROM scott.emp;
[MySQL]
SELECT empno, ename, job, sal, rnum
FROM (
SELECT a.*,
(CASE @vjob WHEN a.job THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) rnum,
(@vjob:=a.job) vjob
FROM emp a, (SELECT @vjob:='', @rownum:=0 FROM DUAL) b
ORDER BY a.job, a.sal
) c;
저작자표시비영리변경금지
'개발 > RDBMS' 카테고리의 다른 글
| 2018.08.02 |
| 2018.08.02 |
| 2018.08.02 |
| 2018.08.02 |
| 2018.08.02 |
| 2018.08.02 |
0. 문제
이와 같은 데이터가 있다.
이 때,
접수번호를 중복제거하되, 자녀명, 자녀 나이까지 SELECT하는 방법은?
(단, 자녀명과 자녀 나이는 중복된 접수번호 row 중 자녀 나이가 가장 높은 row를 가져와야 한다.)
원하는 결과
=> 접수번호가 중복처리되었으며, 동일한 접수번호 당 자녀 나이가 가장 높은 ROW 1개만 출력됨
1. 고민
Oracle 쿼리를 통한 중복 데이터 제거를 위해서 흔히 GROUP BY 또는 DISTINCT를 사용한다.
그러나, 위의 두 예약어는 특정 Column 을 지정하여, 지정된 Column의 중복 값을 제거한 결과를 반환한다.
중복처리할 데이터이외의 Column을 가져올 수 없다.
그러면 위의 문제를 어떻게 해결할 것인가?
문제 해결을 위해 먼저,
위의 데이터가 특정 테이블 전체 데이터라고 가정해보자.
2. 예제 데이터
SQL)SELECT * FROM MYTABLE;
결과)
동사무소에서 접수번호 별로 자녀명과 자녀 나이를 테이블 정보로 가지고 있다고 가정해 보자.
(실제로는 말도 안 되는 가정이지만...)
GROUP BY 또는 DISTINCT를 사용하면 다음과 같은 중복 제거가 가능하다.
3. GROUP BY, DISTINCT 를 통한 중복 제거
-위에서 이야기한 것처럼,
GROUP BY와 DISTINCT는 원본 데이터의 Column 중 중복제거 처리할 Column만 SELECT가 가능하다.
(GROUP BY 절에서 사용 가능한 COUNT, MAX, AVG와 같은 그룹 함수는 원본 데이터 Column이 아니니 논외로 하자)
SQL1)
SELECT MYTABLE.접수번호
,MYTABLE.자녀명
,MYTABLE."자녀 나이"
FROM MYTABLE
GROUP BY
MYTABLE.접수번호,MYTABLE.자녀명
,MYTABLE."자녀 나이"
결과)
=> 전체 ROW를 GROUP BY 처리하면 당연히 모든 데이터가 SELECT되고
SQL2)
SELECT MYTABLE.접수번호
,MYTABLE.자녀명
,MYTABLE."자녀 나이"
FROM MYTABLE
GROUP BY
MYTABLE.접수번호결과)
=>접수번호로만 GROUP BY를 하는데 그 이외의 COLUMN을 SELECT하려고 하면 ORA-00979 에러가 난다.
이번에는 DISTINCT를 사용해보자.
SQL3)
SELECT DISTINCT MYTABLE.접수번호
,MYTABLE.자녀명
,MYTABLE."자녀 나이"
FROM MYTABLE
결과)
=>DISTINCT 역시 SELECT하는 모든 Column에 중복처리가 적용된다.
4. PARTITION BY를 활용한 중복제거 및 정렬을 통한 특정 ROW 전체 출력 방법
-서론이 길었다. 나는 0.문제의 해결을 위해 ROW_NUMBER() PARTITION BY를 활용했다.
-다음의 쿼리를 보자.
SQL)
SELECT ROW_NUMBER() OVER(PARTITION BY MYTABLE.접수번호 ORDER BY MYTABLE."자녀 나이" DESC ) AS RNUM, MYTABLE.*
FROM
MYTABLE
결과)
=> '접수번호' 기준으로 분류하여 번호를 부여하는데 '자녀 나이' 오름차 순으로 정렬하여 numbering을 하는 것이다.
그럼 이 상태에서 중복제거를 어떻게 수행해야 할까?
간단하다.
RNUM이 1인 row만 가져오면 중복제거가 되며, 자녀 나이가 가장 높은 row가 SELECT되는 결과가 된다.
SQL)
SELECT TB1.접수번호
,TB1.자녀명
,TB1."자녀 나이"
FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY MYTABLE.접수번호 ORDER BY MYTABLE."자녀 나이" DESC ) AS RNUM, MYTABLE.*
FROM MYTABLE
) TB
WHERE RNUM = 1
결과)
=>드디어 원하는 결과를 얻었다.
-접수번호 중복제거처리 (O)
-동일 접수번호 중 자녀 나이 가장 높은 ROW만 전체 출력 (O)
5. 정리
-위와 같은 요구 조건이 필요할 상황이 얼마나 있을지는 모르겠지만,
oracle 9i 에 row_number() over partition by 라는 기능이 추가되면서 많은 이들의 환호를 받았다고 한다.
정리해두고 필요할 때마다 꺼내먹으면 좋을 듯.
끝.