sql rank, dense_rank, row_number 에 대하여

문제, Median Employee Salary

• https://leetcode.com/problems/median-employee-salary/
• 한 테이블에 직원, 회사, 임금이 있고, 각 회사마다 가운데 순위의 임금을 받는 레코드를 출력하는 것이 목표이다. 만약 한 회사에 6명이 있다면, 3-4위 2명을 출력한다.

Input: 
Employee table:
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 1  | A       | 2341   |
| 2  | A       | 341    |
| 3  | A       | 15     |
| 4  | A       | 15314  |
| 5  | A       | 451    |
| 6  | A       | 513    |
| 7  | B       | 15     |
| 8  | B       | 13     |
| 9  | B       | 1154   |
| 10 | B       | 1345   |
| 11 | B       | 1221   |
| 12 | B       | 234    |
| 13 | C       | 2345   |
| 14 | C       | 2645   |
| 15 | C       | 2645   |
| 16 | C       | 2652   |
| 17 | C       | 65     |
+----+---------+--------+
Output: 
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 5  | A       | 451    |
| 6  | A       | 513    |
| 12 | B       | 234    |
| 9  | B       | 1154   |
| 14 | C       | 2645   |
+----+---------+--------+

문제의 해소의 방향

• 각 회사마다 직원의 숫자에서 2를 나눈 숫자를 구한다. 그리고 회사의 직원이 짝수인지를 판별한다. 만약 직원의 숫자가 6명이면 3이 되며, 7명이면 3과 4를 출력 대상으로 한다.
• 각 회사마다 임금을 기준으로 등수를 매길 때, row_number() 함수를 사용했다.

쿼리

select 
    tb1.id
    , tb1.company
    , tb1.salary
from (
    select 
        id
        , company
        , salary
        , row_number() over(partition by company order by company asc, salary asc) rk -- 정의한 바에 따라 1부터 순서를 새롭게 매긴다.
    from employee
) tb1
join (
    select 
        company
        , round(count(salary)/2) 'mid'
        , case when count(salary)%2=0 then 'Y'
                else 'N'
                end 'even'
    from employee 
    group by company
) tb2
on tb1.company = tb2.company 
    and (tb1.rk = tb2.mid or tb1.rk = if(tb2.even='Y', tb2.mid+1, 0)) -- (6명 회사에서 3등) or (홀수 회사에서 3+1등)

순서 함수의 사용과 기능

• row_number() over(partition by company order by company asc, salary asc)

함수의 종류

• row_number() : 순서에 따라 번호를 매긴다. 1부터 하나씩 더해지는 것을 보장한다.
• dense_rank() : 등수를 매긴다. 등수는 중복이 가능하다. 등수는 1씩 가산된다 : 1, 2, 2, 2, 3
• rank() : 등수를 매긴다. 등수는 중복이 가능하다. 하지만 1씩 가산되지 않고 앞의 레코드의 갯수에 따른 등수를 보여준다 : 1, 2, 2, 2, 5

over(order by)

• order by 는 등수를 매기는 기준이며 이 기준으로 정렬한다. order by 와 같다.

over(partition by)

• 각 그룹마다 등수를 매기고 싶을 경우 사용한다. group by로 각 그룹마다 등급을 매긴 후 union all 을 한 것과 유사하다.