select한 결과를 특정 column을 기준으로 나누고 싶은 경우가 있다.
다음과 같은 테이블에서 각 사용자의 점수 중 상위 3개씩만 추리고 싶다. 어떻게 할까?? 사용자 column을 기준으로 레코드를 나누고 나뉜 것들 내에서 점수 순으로 정렬을 해서 상위 3개만 추리면 된다.
SQL> SELECT * FROM TMP_TABLE;
USERID | SCORE
----------|----------
aaa | 10
aaa | 30
aaa | 50
aaa | 90
bbb | 80
bbb | 50
bbb | 20
bbb | 40
aaa | 50
9 rows selected.
위의 테이블을 아래와 같이 userid column을 기준으로 나눈다.
USERID | SCORE
----------|----------
aaa | 10
aaa | 30
aaa | 50
aaa | 90
aaa | 50
USERID | SCORE
----------|----------
bbb | 80
bbb | 50
bbb | 20
bbb | 40
그러면 aaa와 bbb로 나뉘고, 이를 다시 각 사용자 마다 점수순으로 정렬한다.
USERID | SCORE
----------|----------
aaa | 90
aaa | 50
aaa | 50
aaa | 30
aaa | 10
USERID | SCORE
----------|----------
bbb | 80
bbb | 50
bbb | 40
bbb | 20
그리고 상위 3개 레코드를 잡아서 결과를 모은다.
USERID | SCORE
----------|----------
aaa | 90
aaa | 50
aaa | 50
bbb | 80
bbb | 50
bbb | 40
음.. 말이 쉽지.. 이걸 어떻게 하면 될까?? PARTITION BY 구문을 사용하면 간단해 진다. 그리고 RANK() OVER 또는 ROW_NUMBER() OVER를 사용해서 각 레코드에 순서를 매겨 상위 3개를 추린다. 위의 내용을 query 구문으로 옮기면 아래와 같이 된다.
SELECT *
FROM
(
SELECT USERID, SCORE, RANK() OVER (PARTITION BY USERID ORDER BY SCORE DESC) RANK
FROM TMP_TABLE
)
WHERE RANK < 4;
USERID | SCORE| RANK
----------|----------|----------
aaa | 90| 1
aaa | 50| 2
aaa | 50| 2
bbb | 80| 1
bbb | 50| 2
bbb | 40| 3
6 rows selected.
PARTITION BY는 select한 결과를 특정 column을 기준으로 나누는 역할을 한다. 여기서는 userid를 기준으로 partition을 나눴다. 그리고 나뉜 partition은 score column을 기준으로 내림차순으로 정렬을 하고, 정렬된 내용을 RANK() OVER를 통해 순서를 매긴다. 마지막으로 한 번 더 select하여 상위 3개를 가져온다.