참으로 오랜만의 포스팅이다 ^_~@
개발할 때 DB 데이터를 표현하게되면 항상 고민되는 것이...
범용적인 표준SQL을 쓰느냐,
Oracle이나 MS-SQL에 최적화된 쿼리를 쓰느냐이다.
처음에는 Oracle위에 올려서 개발을 했지만, 차후에 MS-SQL위에 올려서 써야할 때가 있다.
사용자가 Oracle이나 MS-SQL 중 한쪽을 죽어라 고집하면,
어쩔 수 없이 바꿔주는 수밖에 없지않는가 ㅡㅜ
왠만하면 표준 SQL만 쓰려고 하지만...
복잡한 통계작업이나 수치계산, String 조합 등의 작업을 할때는 어쩔 수 없이 DBMS의 전용 함수를 쓸 수 밖에 없다.
이번엔 Oracle의 순위함수를 쓰는 일이다.
(물론 MS_-SQL에도 동일한 함수가 있다)
아래와 같은 사원 평점 테이블이 있다고 치고,
인사 관계자가 이 평점값들을 토대로 사원 평가 순위를 매기는 업무를 한다고 가정해보자.
SELECT 부서, 성명, 평점
FROM (
SELECT '홍길동' 성명, '영업' 부서, 55 평점 FROM DUAL
UNION ALL
SELECT '일지매' 성명, '영업' 부서, 60 평점 FROM DUAL
UNION ALL
SELECT '마동탁' 성명, '영업' 부서, 96 평점 FROM DUAL
UNION ALL
SELECT '오해성' 성명, '영업' 부서, 87 평점 FROM DUAL
UNION ALL
SELECT '한비광' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '나루토' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '이세경' 성명, '물류' 부서, 92 평점 FROM DUAL
UNION ALL
SELECT '권유리' 성명, '물류' 부서, 81 평점 FROM DUAL
)
====결과====
[평점순으로 순위를 매겨서 나열하기 #1]
여기서 평점이 제일 높은 사람을 찾는 것은 간단하다.
간단히 Order By만 넣으면 될 것이다.
SELECT 부서, 성명, 평점, ROWNUM 순위
FROM (
SELECT 부서, 성명, 평점
FROM (
SELECT '홍길동' 성명, '영업' 부서, 55 평점 FROM DUAL
UNION ALL
SELECT '일지매' 성명, '영업' 부서, 60 평점 FROM DUAL
UNION ALL
SELECT '마동탁' 성명, '영업' 부서, 96 평점 FROM DUAL
UNION ALL
SELECT '오해성' 성명, '영업' 부서, 87 평점 FROM DUAL
UNION ALL
SELECT '한비광' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '나루토' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '이세경' 성명, '물류' 부서, 92 평점 FROM DUAL
UNION ALL
SELECT '권유리' 성명, '물류' 부서, 81 평점 FROM DUAL
)
ORDER BY 평점 DESC
)
====결과====
간단하지 아니한가?
그런데 동점자를 고려해서 순위를 표시하고,
경우에 따라서 부서별로 나눠서보고 부서별 순위를 표시한다고 했을때...
좀 복잡해 진다. GROUP BY가 들어가고 JOIN을 하고 난리가 난다.
이럴땐 아래와 같이 RANK()라는 함수로 간략하게 표현을 할 수가 있다.
[평점순으로 순위를 매겨서 나열하기 #2]
Order by로 Sort를 하고 다시 외부 Select문을 만드는 번거로움을 생략하기 위해
Rank()함수를 쓰면 아래와 같이 나타난다.
SELECT 부서, 성명, 평점
,RANK() OVER(ORDER BY 평점 DESC ) 순위
FROM (
SELECT '홍길동' 성명, '영업' 부서, 55 평점 FROM DUAL
UNION ALL
SELECT '일지매' 성명, '영업' 부서, 60 평점 FROM DUAL
UNION ALL
SELECT '마동탁' 성명, '영업' 부서, 96 평점 FROM DUAL
UNION ALL
SELECT '오해성' 성명, '영업' 부서, 87 평점 FROM DUAL
UNION ALL
SELECT '한비광' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '나루토' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '이세경' 성명, '물류' 부서, 92 평점 FROM DUAL
UNION ALL
SELECT '권유리' 성명, '물류' 부서, 81 평점 FROM DUAL
)
====결과====
Order By를 따로 할것도 없이 Rank() Over()절에서 사용된 순위에 따라 표시되니 간편하기도 하다.
뭐, 어짜피 Where 조건을 통해 값을 뽑은후 Order by를 하는 것이기 때문에
전통적인 Order by절이나 Rank() Over()나 내부적으로는 비슷한 논리로 작동할 것이라 생각이 된다.
그런데 위의 결과를 보면 5위가 2명이라서 6위가 없다는 것을 알 수 있다.
[평점순으로 순위를 매겨서 나열하기 #3]
그럼 공동순위를 무시하고 6위를 표시하려면 어떻게 해야 할까?
그럴땐 RANK()가 아니라 DENSE_RANK()를 쓰면 된다.
영어 Dense의 뜻 그대로 빠진 숫자없이 촘촘한 랭킹이라고 이해하면 될려나 ^^;;
SELECT 부서, 성명, 평점
,DENSE_RANK() OVER(ORDER BY 평점 DESC ) 공통_순위
FROM (
SELECT '홍길동' 성명, '영업' 부서, 55 평점 FROM DUAL
UNION ALL
SELECT '일지매' 성명, '영업' 부서, 60 평점 FROM DUAL
UNION ALL
SELECT '마동탁' 성명, '영업' 부서, 96 평점 FROM DUAL
UNION ALL
SELECT '오해성' 성명, '영업' 부서, 87 평점 FROM DUAL
UNION ALL
SELECT '한비광' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '나루토' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '이세경' 성명, '물류' 부서, 92 평점 FROM DUAL
UNION ALL
SELECT '권유리' 성명, '물류' 부서, 81 평점 FROM DUAL
)
====결과====
그런데 전체순위말고, 특정한 Group별로 순위를 매길려면 어떻게 해야할까요?
먼저 떠오르는 것은 Group By 절이다.
하지만, Rank()함수는 Group By를 대신하는 Partition이라는 옵션을 제공하고 있기에
간단히 나타낼 수 있다.
[평점순으로 순위를 매겨서 나열하기 #4]
부서를 기준으로 Partition을 나누어 주면 된다.
SELECT 부서, 성명, 평점
RANK() OVER( PARTITION BY 부서 ORDER BY 평점 DESC ) 부서별순위
FROM (
SELECT '홍길동' 성명, '영업' 부서, 55 평점 FROM DUAL
UNION ALL
SELECT '일지매' 성명, '영업' 부서, 60 평점 FROM DUAL
UNION ALL
SELECT '마동탁' 성명, '영업' 부서, 96 평점 FROM DUAL
UNION ALL
SELECT '오해성' 성명, '영업' 부서, 87 평점 FROM DUAL
UNION ALL
SELECT '한비광' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '나루토' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '이세경' 성명, '물류' 부서, 92 평점 FROM DUAL
UNION ALL
SELECT '권유리' 성명, '물류' 부서, 81 평점 FROM DUAL
)
====결과====
그럼 (성능은 나락으로 떨어질 것이 불 보듯 뻔하지만) 전체순위와 각 부서별 순위를 함께 표시하면 어떻게 될까?
[평점순으로 순위를 매겨서 나열하기 #5]
SELECT 부서, 성명, 평점
,DENSE_RANK() OVER(ORDER BY 평점 DESC ) 전체_순위
,DENSE_RANK() OVER( PARTITION BY 부서 ORDER BY 평점 DESC ) 부서별순위
FROM (
SELECT '홍길동' 성명, '영업' 부서, 55 평점 FROM DUAL
UNION ALL
SELECT '일지매' 성명, '영업' 부서, 60 평점 FROM DUAL
UNION ALL
SELECT '마동탁' 성명, '영업' 부서, 96 평점 FROM DUAL
UNION ALL
SELECT '오해성' 성명, '영업' 부서, 87 평점 FROM DUAL
UNION ALL
SELECT '한비광' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '나루토' 성명, '물류' 부서, 62 평점 FROM DUAL
UNION ALL
SELECT '이세경' 성명, '물류' 부서, 92 평점 FROM DUAL
UNION ALL
SELECT '권유리' 성명, '물류' 부서, 81 평점 FROM DUAL
)
====결과====
당연하겠지만,
앞에서 선언한 Rank()의 값에 의해 1차 Sorting이 되고, 뒤의 Rank()값에 따라 2차 Sorting이 된다.
Oracle의 함수를 봤지만,
MS_-SQL에도 이와 유사한 동일한 함수가 생겼으니, 혹시 모른다면 컨버젼을 대비해서 함께 알아두는 것도 좋을 것 같다.
PS. 어쩌다 블로그글들을 보면 예전에 내가 썼던 글을 인용해서 만든 포스트가 보이기도 한다.
이미 오래전에 닫아버린 블로그지만, 그곳의 링크를 걸어두신 분도 있고... ^^
알량한 지식이나마 다른 개발자들이나 학생들과 공유를 했다는 사실에 뿌듯하다 ^^
PS2. 다만, 포스트 서두에 적혀있는 나의 이런저런 경험이나 상황마저 마치 자신의 것인양 옮겨담은 블로거도 간혹있네요.
그런건 좀 낮뜨거우니 좀 빼주세요 ㅡㅜ
