[SQL] MySQL - Window Function으로 문제풀기(Leetcode - 180, 184, 185번)

🔊 본 포스팅에서 사용되는 테이블의 자료와 출처는 Leetcode임을 밝힙니다. 더 다양한 SQL 문제를 풀어보시려면 Leetcode 사이트를 방문해 보세요!

 

Leetcode 사이트

 

이번 포스팅에서는 MySQL 8.0 버전부터 호환이 가능한 Window Function을 이용해 문제를 풀어보자. 만약 MySQL 8.0 이하 버전이라면 MSSQL을 활용해 Window Function을 구현할 수 있다. Window Function에 대해서는 공식 문서를 참조해보자. 상당히 유용한 기능들이 많다.

 

이번에는 총 3개의 문제를 풀어볼텐데 난이도가 어느 정도 있는 문제들이다. 따라서 자세한 문제는 여기를 클릭해 180번, 184번, 185번 문제를 자세히 읽어보자.

 

# 180번

먼저 문제와 문제에서 요구하는 출력형태를 살펴보자.

 

Write an SQL query to find all numbers that appear at least three times consecutively. Return the result table in any order.
(적어도 연속으로 3번 나타나는 모든 숫자들을 쿼리해라. 결과값의 데이터들 정렬 순서는 따로 지정해주지 않아도 된다.)

Leetcode 180번 문제

 

해당 문제를 풀기 위해서는 Window Function 중 값을 밀어내는 LAG(값, 얼마나 lag시킬지) 와 값을 땡겨오는 LEAD(값, 얼마나 lead시킬지) 를 이용한 2가지 풀이가 있다. 우선 LAG() 를 사용한 풀이는 다음과 같다.

 

-- LAG를 이용한 풀이
SELECT DISTINCT sub.Num AS ConsecutiveNums
FROM (SELECT Num,
             LAG(Num, 1) OVER (ORDER BY Id) AS next,
             LAG(Num, 2) OVER (ORDER BY Id) AS nextAfter
      FROM Logs) sub
WHERE sub.Num = sub.next AND sub.next = sub.nextAfter

 

위 쿼리 중 서브쿼리에 있는 SELECT 문만 쿼리했을 때 테이블 출력 형태는 다음과 같다.

 

Id	Num	next	nextAfter
1	1	null	null
2	1	1	null
3	1	1	1
4	2	1	1
5	1	2	1
6	2	1	2
7	2	2	1

 

그래서 위 테이블을 FROM 이후의 서브쿼리로 넣어주어서 위 테이블의 결과값 중 Num, next, nextAfter 칼럼 값이 모두 일치하는 Num값을 찾으면 적어도 3번 연속으로 나오는 데이터들을 추출하게 된다.

 

다음은 LEAD() 를 사용한 쿼리문이다. 

 

-- LEAD를 사용한 풀이
SELECT DISTINCT sub.Num AS ConsecutiveNums
FROM (SELECT Num,
        LEAD(Num, 1) OVER (ORDER BY Id) AS next,
        LEAD(Num, 2) OVER (ORDER BY Id) AS nextAfter
      FROM Logs) sub
WHERE sub.Num = sub.next AND sub.next = sub.nextAfter

 

LEAD를 수행한 서브쿼리절을 테이블로 나타내면 다음과 같다.

 

Id	Num	next	nextAfter
1	1	1	1
2	1	1	2
3	1	2	1
4	2	1	2
5	1	2	2
6	2	2	null
7	2	null	null

# 184번

184번 문제와 문제에서 요구하는 형태를 살펴보자.

 

문제에서 주어진 2개의 테이블

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).
(각 부서의 가장 높은 봉급을 받는 직원들을 쿼리해라. 단 출력되는 데이터의 정렬 기준은 상관이 없다.)

문제에서 요구하는 출력의 형태

 

이번 문제는 Window Function을 사용하지 않고 일반적인 쿼리문과 Window Function 중 집계함수인 MAX() 를 사용해 해결하는 2가지 방법이 있다. 우선 일반 쿼리문 풀이는 다음과 같다. 

 

-- 1. 부서별 가장 높은 봉급을 우선 쿼리
-- 2. 1번 쿼리한 결과 테이블과 부서이름이 적혀있는 Department 테이블 조인
-- 3. 2번에서 조인한 테이블을 Employee 테이블과 DepartmentId 기준으로 조인
-- 4. Employee의 Salary(직원들이 받은 봉급)과 max_Salary(부서별 최대 봉급)이 같은 데이터 쿼리
-- 일반 쿼리

SELECT sub2.Name AS Department, e.Name AS Employee, e.Salary
FROM Employee AS e
 INNER JOIN (SELECT *
             FROM (SELECT DepartmentId, MAX(Salary) AS max_Salary
                   FROM Employee 
                   GROUP BY DepartmentId) sub1
              INNER JOIN Department AS d ON sub1.DepartmentId = d.Id) sub2 ON e.DepartmentId = sub2.DepartmentId
WHERE e.Salary = sub2.max_Salary

 

쿼리문을 작성할 때 고려한 사항을 순차적으로 어떻게 구상했는지 위 코드의 주석으로 작성해보았다. 서브쿼리 하나씩 일일이 결과화면을 관찰하면 이해가 수월할 것이다.

 

다음은 Window Function 의 MAX() 를 사용한 풀이이다. 아래 코드를 보면 Window Function 구문을 사용함으로써 서브쿼리가 1개 감소하는 코드의 간결성이 향상되었음을 볼 수 있다.

 

-- 1.우선 부서 id - 부서이름 매칭시키기 위해 두 개의 테이블 조인
-- 2.윈도우 펑션사용해서 부서별 최고 봉급 테이블 만들기
-- 3.2번 결과 테이블을 Employee 테이블과 조인시키기
-- 4.봉급과 최대봉급이 같은 데이터 쿼리
-- Window Function 사용 - 부서별 ~ 최대봉급

SELECT sub.Department, E.name AS Employee, E.Salary
FROM Employee AS E
 INNER JOIN (SELECT DISTINCT d.Id AS DepartmentId, d.name AS Department, MAX(Salary) OVER (PARTITION BY DepartmentId) AS max_Salary
             FROM Employee AS e
              INNER JOIN Department AS d ON e.DepartmentId = d.Id) sub ON E.DepartmentId = sub.DepartmentId
WHERE E.Salary = sub.max_Salary

# 185번

다음은 185번의 문제와 요구하는 출력 형태를살펴보자.

 

문제에서 주어진 2개의 테이블

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).
(각 부서별로 가장 높은 상위 봉급 3번째 까지 받는 직원들을 쿼리해라. 출력결과의 데이터 순서는 상관이 없다.

문제에서 요구하는 출력 형태

 

해당 문제를 풀기 위해서는 Window Function 중 DENSE_RANK() 함수를 사용하면 매우 유용하다. DENSE_RANK()는 특정 값을 기준으로 내림(또는 오름) 차순으로 정렬 후 순위를 부여하는 함수인데,  DENSE_RANK() 이외에 ROW_NUMBER(), RANK() 함수도 있는데, 간단하게 이 3가지의 차이점에 대해 살펴보자. 예를 들어 만약 다음과 같은 테이블이 있다고 가정하자.

 

Name	Level
John	154
Mike	122
Eliot	122
Sara	115
Laura	200

 

위 테이블에서 3가지 함수를 적용한 각각 결과는 다음과 같다. 참고로 Level을 기준으로 내림차순 정렬한다는 가정하게 ROW_NUMBER(), RANK(), DENSE_RANK()를 사용해 만든 파생변수들은 다음과 같아진다.

 

Name	Level	ROW_NUMBER()	RANK()	DENSE_RANK()
Laura	200	1	1	1
John	154	2	2	2
Mike	122	3	3	3
Eliot	122	4	3	3
Sara	115	5	5	4

 

이제 어떤 개념인지 피부에 확 와닿을 것이다. 그럼 이제 이를 활용해 185번 문제를 풀이한 SQL 구문은 다음과 같다.

 

-- 1.우선 부서id, 부서이름 매칭시키기
-- 2.부서별 봉급 순으로 댄스랭크 정렬한 테이블 만들기
-- 3.2번에서 만든 테이블을 FROM 서브쿼리로 넣어주고 순위가 3위 이하인 데이터들만 쿼리
-- 참고로 윈도우 펑션으로 만든 변수를 새로운 칼럼으로 네이밍해줄때는 '' 따옴표 붙여주어야 함!!

SELECT sub.Department, sub.Employee, sub.Salary
FROM (SELECT d.Name AS Department,
             e.Name AS Employee,
             e.Salary AS Salary,
             DENSE_RANK() OVER (PARTITION BY d.Name ORDER BY e.Salary DESC) AS 'Rank'
      FROM Employee AS e
       INNER JOIN Department AS d ON e.DepartmentId = d.Id) sub
WHERE sub.Rank <= 3

 

위에서 주의해야 할 부분(필자도 처음 알았다..)은 바로 DENSE_RANK()로 만든 새로운 칼럼을 하나의 새로운 이름으로 네이밍해줄 때는 반드시 ' ' (따옴표)를 붙여야 한다.(쌍따옴표도 가능하다) 

DENSE_RANK() OVER (PARTITION BY columnA  ORDER BY columnB  DESC|ASC) AS 'New column name'