통계정보를 보여주는 조회화면은 기본적으로 로우데이터를 특정기준으로 그룹핑하여 합계, 최고, 최저 값을 보여주죠.
보통은 group by [기준컬럼] having [그룹조건] 형식으로 구현되지만
그룹내 특정 순위조건에 해당되는 값을 추출할때는 아래와 같이 조금 다른방법을 쓸 수 있습니다.
rank() over(partition by [그룹컬럼] order by [그룹내 정렬컬럼])
group by 와 달리 전체 로우데이터를 조회하면서 그룹핑된 정렬값을 같이 보여줄 수 있습니다.
예시 1) 부서, 인원별 월급 순위 구하기
SQL>>>>>>>>>>>>>>>
with pay_info as (
select 4500000 as pay , '영업팀' as dept, '김민정' as emp from dual union all
select 3180000 , '영업팀' , '강백호' from dual union all
select 8300000 , '영업팀' , '손석희' from dual union all
select 8300000 , '영업팀' , '강호동' from dual union all
select 3300000 , '인사팀' , '이수근' from dual union all
select 4300000 , '인사팀' , '신정환' from dual union all
select 5500000 , '인사팀' , '정우성' from dual union all
select 3500000 , '홍보팀' , '홍진영' from dual union all
select 2700000 , '홍보팀' , '사나' from dual union all
select 5700000 , '홍보팀' , '이효리' from dual
)
select rank( ) OVER (partition by dept ORDER BY pay DESC ) a_rank,
a.*
from pay_info a;
결과>>>>>>>>>>>>>>>
A_RANK PAY DEPT EMP
1 8,300,000 영업팀 손석희
1 8,300,000 영업팀 강호동
3 4,500,000 영업팀 김민정
4 3,180,000 영업팀 강백호
1 5,500,000 인사팀 정우성
2 4,300,000 인사팀 신정환
3 3,300,000 인사팀 이수근
1 5,700,000 홍보팀 이효리
2 3,500,000 홍보팀 홍진영
3 2,700,000 홍보팀 사나
예시 2) 부서, 인원별 월급을 가장 많이 받는사람 구하기
SQL>>>>>>>>>>>>>>>
with pay_info as ( [예시1 과 동일한 내용 ] )
select pay, dept, emp from (
select rank( ) OVER (partition by dept ORDER BY pay DESC ) a_rank,
a.*
from pay_info a )
where a_rank = 1;
결과>>>>>>>>>>>>>>>PAY DEPT EMP
8,300,000 영업팀 손석희
8,300,000 영업팀 강호동
5,500,000 인사팀 정우성
5,700,000 홍보팀 이효리
예시 2) 부서 별 월급 최고금액 구하기 (row_number( ) OVER() , 중복되지 않는 순서값 활용 )
SQL>>>>>>>>>>>>>>>
with pay_info as ( [예시1 과 동일한 내용 ] )
select dept, pay from (
select row_number( ) OVER (partition by dept ORDER BY pay DESC ) a_row,
a.*
from pay_info a )
where a_row = 1;
결과>>>>>>>>>>>>>>>DEPT PAY
영업팀 8,300,000
인사팀 5,500,000
홍보팀 5,700,000