[SQL : 서브쿼리 / INNER JOIN / WINDOW FUNCTION ] LeetCode: 184. Department Highest Salary

184. Department Highest Salary

---

>> 문제

 

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

 

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

 

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Explanation:

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

 

>> 내가 푼 코드 

SELECT d.Name as Department , e2.Name as Employee,
        e2.Salary as Salary
FROM Department d, Employee as e2,
    (SELECT max(e.Salary) as m, e.name , e.departmentId
     FROM Employee as e
     Group by e.DepartmentId) sub1
WHERE d.ID = sub1.DepartmentId AND e2.Salary = sub1.m

단순하게, FROM  의 서브쿼리를 넣고, WHERE 절로 조건을 해준 코드이다. 

TEST SET에는 통과를 하였지만, 전체 SET 에는 잘못된 코드라고 나왔다. 

 

>> 정답 코드

 

1. 먼저 각 부서별로 가장 많은 연봉과 ID의 값을 불러온다.

SELECT departmentid, max(salary)
FROM employee
GROUP BY departmentid 

위에서 나온 결과를 FROM절의 서브쿼리로 넣어준다. 

※ FROM 절의 서브쿼리를 '인라인뷰'라고 부른다.

 

2. 각부서별로 가장 많은 연봉과 아이디의 값들만 INNER JOIN 을 해준다.

 - INNER JOIN 은 두 테이블 모두 가지고 있을때, 사용할 수 있다.

 - 즉, 기존 Employee 테이블과 departmentid 는 같으면서 서브쿼리 (de_max) 의 값만 뽑아줄 수 있다. 

SELECT *
FROM employee as e
    INNER JOIN ( 
        SELECT departmentid, max(salary) AS max_salary
        FROM employee
        GROUP BY departmentid 
        ) as de_max  ON e.departmentid = de_max.departmentiD
        
        
 -- output
     
{"headers": ["Id", "Name", "Salary", "DepartmentId", "departmentid", "max_salary"], 
"values": [[2, "Jim", 90000, 1, 1, 90000], 
            [3, "Henry", 80000, 2, 2, 80000], 
            [5, "Max", 90000, 1, 1, 90000]]}

문제를 풀면서, 왜? WHERE 도 아니고, SELECT 도 아닌, FROM 절에 서브쿼리를 넣는가 ? 에 대한 생각을 했다.

-> MAX값 1개의 ROW만 가져오는 것이 아니라, 그 값을 가지고 있는 모든 값을 다 불러와야한다.

 

언제 이걸 쓰는지 완전히 이해하지 못했다. 꾸준히 문제를 풀면서 이해해야할 것 같다. 

 

3. 최종적으로 결과물을 맞춰주기 위해서, department 테이블과 조인하기

SELECT D.Name as Department 
    , E.Name as Employee
    , e.Salary as Salary
FROM employee as e
    INNER JOIN ( 
        SELECT departmentid, max(salary) AS max_salary
        FROM employee
        GROUP BY departmentid 
        ) as de_max  ON e.departmentid = de_max.departmentid
                        AND e.salary = de_max.max_salary
                        
    INNER JOIN department AS D on d.id = e.departmentID
    
    
    -- output
 {"headers": ["Department", "Employee", "Salary"], 
 "values": [["IT", "Jim", 90000], 
 	["Sales", "Henry", 80000],
 	["IT", "Max", 90000]]}

 

>> WINDOW FUNCTION 를 사용한 다른 풀이

1. 파티션을 departmentid를 기준으로 묶어준후, max 값을 도출한다

< inner join 을 한것은 눈에 보이는 결과를 알기 위해서 적어둔 것 > 

SELECT employee.name, employee.salary , 
        department.name, MAX(salary) OVER ( PARTITION BY departmentid ) max_salary 
FROM employee
    INNER JOIN department ON employee.departmentid = department.id
    
    
-- {"headers": ["name", "salary", "name", "max_salary"], 
-- "values": [["Joe", 70000, "IT", 90000],
--            ["Jim", 90000, "IT", 90000], 
--            ["Max", 90000, "IT", 90000],
--            ["Henry", 80000, "Sales", 80000],
--            ["Sam", 60000, "Sales", 80000]]}

: 결과적으로, 같은 부서 중에서 가장 높은 값을 가지는 salary 에 대해서 하나의 컬럼이 만들어진다.

 

 

2. 서브쿼리를 이용해서 select 문안에 넣기 

SELECT ms.department
        , ms.name as Employee
        , ms.salary
FROM (
    SELECT employee.name, employee.salary , 
            department.name as department
            , MAX(salary) OVER ( PARTITION BY departmentid ) max_salary 
    FROM employee
        INNER JOIN department ON employee.departmentid = department.id
    ) ms
WHERE ms.salary = ms.max_salary -- 가장 높은 연봉을 받는 사람 찾기

 

 

+ ) 이외, rank 등을 이용해서 문제를 풀수 있다. 

 

 

leetcode.com/problems/department-highest-salary/submissions/

Department Highest Salary - LeetCode