231023 Oracle SQL TOP_N 분석 rownum, rank(), dense_rank()

■ TOP-N 분석
ex) 최고급여자 중에 10위까지 출력하세요

1# 내림차순 

SELECT employee_id, salary 
FROM hr.employees
ORDER BY salary desc;

2# 정렬한 결과 집합을 이용해서 10위까지만 출력.
    - rownum : fetch 번호를 리턴하는 가상컬럼
  
#1 주의할 점  
    - salary 컬럼안에 data 값이 중복성이 없다면 아래 코드로 작성가능.
    - 중복성이 있을 경우는 절대 사용하면 안된다.
SELECT rownum, employee_id, salary
FROM ( SELECT employee_id, salary 
       FROM hr.employees
       ORDER BY salary desc ) 
WHERE rownum <= 10;

#2 주의할 점
    - random 하게 데이터 10건만 추출해서 정렬한다., inline view 에 rownum 를 쓰지 말것!
    SELECT rownum, employee_id, salary 
    FROM hr.employees
    WHERE rownum <= 10
    ORDER BY salary desc;

# 순위를 구하는 함수. rank() VS dense_rank()
    ★ rank() : 순위를 구하는 함수. 동일한 순위가 있을 경우, 다음 순위의 갭이 생긴다. 
    ★ dense_rank() : 순위를 구하는 함수. 동일한 순위가 있더라도 '연이은 순위'를 구한다.
    

SELECT 
    employee_id, 
    salary , 
    rank() over(order by salary desc) rank , 
    dense_rank() over(order by salary desc) dense_rank
FROM hr.employees;

# 10위까지 추출 : rank()

SELECT * 
FROM ( SELECT 
            employee_id, 
            salary , 
            rank() over(order by salary desc) rank 
       FROM hr.employees)
WHERE rank <= 10 ;

# 10위까지 추출 : dense_rank()

SELECT * 
FROM ( SELECT 
            employee_id, 
            salary , 
            dense_rank() over(order by salary desc) dense_rank 
       FROM hr.employees)
WHERE dense_rank <= 10 ;

# 부서별 순위

SELECT 
    employee_id, 
    salary , 
    department_id , 
    rank() over(partition by department_id order by salary desc) rank , 
    dense_rank() over(order by salary desc) dense_rank
FROM hr.employees;