[LeetCode] 184. Department Highest Salary (MySQL)

문제

• employee테이블은 모든 직원들을 가지고 있다
• 모든 직원들은 id, salary, departmentId를 갖고있다
• Department테이블은 회사의 모든 부서들을 갖고있다
• 각각의 부서에서 가장 높은 월급을 받는 직원들을 조회해라

코드

INNER JOIN + SUBQUERY

1. 직원별 각 부서 이름이 필요하므로 Employee 테이블과 Department 테이블 조인

SELECT *
FROM Employee e
     INNER JOIN Department d ON e.DepartmentId = d.Id

2. group by를 해줘서 부서별 max 값 확인하기

SELECT d.name as Department, MAX(e.salary) as Salary
FROM Employee e
     INNER JOIN Department d ON e.DepartmentId = d.Id
GROUP BY d.name

# ["Department", "Salary"]
# ["IT", 90000], 
# ["Sales", 80000]

3. MAX 조건을 WHERE절에 넣으면 employee의 이름이 잘 뽑힐 거라고 생각됨

SELECT d.name as Department, e.name as Employee, e.salary
FROM Employee e
     INNER JOIN Department d ON e.DepartmentId = d.Id
WHERE e.salary IN (
    SELECT max(Salary)
    FROM Employee
    GROUP BY DepartmentId
)
GROUP BY d.name, e.name

이 조건을 WHERE 절에 IN 조건으로 넣어주면 되겠다! 싶었는데 테스트 케이스에서 실패!
이유는 salary가 최대값에 포함되면 부서에 최대값이 아니어도 다 뽑힌다
예를 들어 최대값은 IT팀 90000, HR팀 80000인데 IT팀에 90000, 80000이 있으면 90000만 조회되야 하는데 80000인 애도 같이 출력됨

4. employee 테이블의 salary와 max(salary)가 같은 애들만 뽑아오려면? Self Join 하기

SELECT e.name as Employee, e.salary as Salary
FROM Employee e
     INNER JOIN (
            SELECT id, departmentId, MAX(salary) as maxSal
            FROM Employee 
            GROUP BY id, departmentId
     ) sub ON e.departmentId = sub.departmentId and e.salary = sub.maxSal

선미님께 질문해서 얻은 힌트! Group by는 SELECT 와 세트이기 때문에 집계함수를 제외한 애들은 무조건 각각 똑같이 써줘야한다는 것
Group By에 DepartmentId만 썼다가 고쳤다. 그런데 살펴보니 sub테이블에 왜 id가 필요한건지 의문이 들었고 id는 굳이 필요없다는 생각이 들었다.

SELECT d.name as Department, e.name as Employee, e.salary as Salary
FROM Employee e
	 -- 우리는 부서별 max(salary)만 필요하다!
     INNER JOIN (
            SELECT departmentId, MAX(salary) as maxSal
            FROM Employee 
            GROUP BY departmentId
     ) sub ON e.departmentId = sub.departmentId and e.salary = sub.maxSal
     INNER JOIN Department d ON e.departmentId = d.id

우리가 필요한 것은 부서별 max(salary)이기 때문에 저것만 조회를 해주면 된다.
역시 문제의 맥락을 생각하면서 해야 감이 잡히는 것 같다!

WINDOW FUNCTION

• MAX() 

SELECT name, salary, departmentId
      , MAX(salary) OVER (PARTITION BY departmentId) as maxSal
FROM employee

# ["Joe", 70000, 1, 90000],
# ["Jim", 90000, 1, 90000],
# ["Max", 90000, 1, 90000],
# ["Henry", 80000, 2, 80000], 
# ["Sam", 60000, 2, 80000]

select d.name as Department
     , e.name as Employee
     , e.maxSal as Salary
from ( SELECT name, salary, departmentId
              , MAX(salary) OVER (PARTITION BY departmentId) as maxSal
        FROM employee) e
        INNER JOIN department d ON e.departmentId = d.id
where e.salary = e.maxSal

• RANK() : 같은 값일 때 같은 순위를 주는 함수

SELECT name, salary, departmentId
      , RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) as maxSal
FROM employee

# ["Jim", 90000, 1, 1],
# ["Max", 90000, 1, 1], 
# ["Joe", 70000, 1, 3], 
# ["Henry", 80000, 2, 1], 
# ["Sam", 60000, 2, 2]

SELECT d.name as Department
     , e.name as Employee
     , e.salary as Salary
FROM (
    SELECT name, salary, departmentId
          , RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) as maxSal
    FROM employee
    ) e INNER JOIN department d ON e.departmentId = d.id
WHERE e.maxSal = 1

출처