184. Department Highest Salary
문제
- 각 부서의 최고 연봉자를 추출
- 연봉이 같다면 둘다 출력
문제 접근
- name은 pk가 아니기 때문에 중복되어서 발생할 가능성이 있어 조심히 다루어야 한다.
- cte 구문을 통하여 Employee의 첫번째 값을 추출하여 Employee와 Department를 엮은 값에서 조건을 추가해 결과 추출 → runtime이 생각보다 꽤 걸린다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
with max_user_id as (
select
id,
name,
salary,
rank() over(partition by departmentId order by salary desc) rn
from Employee
)
select
Department.name as Department,
Employee.name as Employee,
Employee.salary as Salary
from Employee
join Department
on Employee.departmentId = Department.id
where Employee.id in (select id from max_user_id where rn = 1)
좋은 방안
- 어차피 cte를 통해서 Employee를 full scan할 것이라면, group by 를 통해 특정 원하는 값만 aggregate해서 추출
- Employee와 Department 사이에서 연결 고리 역할로 조건 제외
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
with max_user_id as (
select
departmentId,
max(salary) salary
from Employee
group by departmentId
)
select
Department.name as Department,
Employee.name as Employee,
Employee.salary as Salary
from Employee
join max_user_id
on Employee.departmentId = max_user_id.departmentId
and Employee.salary = max_user_id.salary
join Department
on Employee.departmentId = Department.id