[ex24] rank

ex24.rank

 

순위함수
    - rownum의 사용을 여러가지 용도로 구현해놓은 함수
    
    1. rank() over()
        - rank() over(order by 컬럼명 [asc|desc])
        - 순위 부여시, 중복값이 발생하면 동일한 순위를 부여하고, 중복값의 개수만큼 건너뛰기 한다.
        - 1 1 > 3
    
    2. dense_rank() over()
        - dense_rank() over(order by 컬럼명 [asc|desc])
        - 순위 부여시, 중복값이 발생하면 동일한 순위를 부여하고, 그 뒤의 순위는 건너뛰기 없이 순차적으로 부여한다.
        - 1 1 > 2
        
    3. row_number() over()
        - row_number() over(order by 컬럼명 [asc|desc])
        - 순위 부여시, 중복값과 상관없이 순차적으로 부여
        - 직접 rownum을 사용한 결과와 동일
        - 1 2 3

 

-- 급여순으로 가져오시오. + 순위
-- rownum
select a.*, rownum from   (select
                    name, buseo, basicpay
                from tblInsa
                    order by basicpay desc) a;

--rank over
select 
    name, buseo, basicpay,
    rank() over(order by basicpay desc) as rnum 
from tblInsa;

--dense_rank over
select 
    name, buseo, basicpay,
    dense_rank() over(order by basicpay desc) as rnum 
from tblInsa;

--row_number over
select 
    name, buseo, basicpay,
    row_number() over(order by basicpay desc) as rnum 
from tblInsa;




-- 급여 5위
select 
    name, buseo, basicpay,
    row_number() over(order by basicpay desc) as rnum --3.
from tblInsa --1.
    where (row_number() over(order by basicpay desc)) = 5 ; --2. (아직 3 실행 전이라 rnum 조건 못씀)
    

--순위함수 쓰려면 서브쿼리 필수
select * from (select 
                name, buseo, basicpay,
                row_number() over(order by basicpay desc) as rnum 
            from tblInsa)
                where rnum = 5;

--건너뛴 순위는 안나옴
select * from (select 
                name, buseo, basicpay,
                rank() over(order by basicpay desc) as rnum 
            from tblInsa)
                where rnum = 9;

update tblInsa set sudang = 200000 where name = '이미인';

--2차 정렬하면 수당순으로 또 정렬 > 8,8 > 8,9 (but 수당도 똑같으면 등수 같음)
select 
    name, buseo, basicpay, sudang,
    rank() over(order by basicpay desc, sudang desc) as rnum 
from tblInsa;

---

그룹별 순위 구하기
    - 순위 함수 + group by

-- 급여 순위
select
    name, buseo, basicpay
from tblInsa;

select
    name, buseo, basicpay,
    dense_rank() over(order by basicpay desc) as rnum
from tblInsa;

-- 부서별(급여 순위)
select
    name, buseo, basicpay,
    dense_rank() over(partition by buseo order by basicpay desc) as rnum
from tblInsa;