[MySQL] 윈도우함수로 예제 문제 풀어보기 (LeetCode - consecutive-numbers)

문제

Table: Logs

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id is the primary key for this table.

Write an SQL query to find all numbers that appear at least three times consecutively.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Output:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
Explanation: 1 is the only number that appears consecutively for at least three times.

코드

/* Write your T-SQL query statement below */

SELECT DISTINCT num AS ConsecutiveNums
FROM (
    SELECT  num,
        LEAD(num,1) OVER (ORDER BY id) AS e1,
        LEAD(num,2) OVER (ORDER BY id) AS e2
    FROM Logs
 )l
WHERE l.num = l.e1 and l.num = l.e2 

풀이

LEAD 윈도우 함수를 이용하여 1칸과 2칸씩 row를 당겨온다. 그후 subquery를 이용하여 당긴 테이블의 값들을 WHERE절을 이용하여 column을 접근한다음 조건에 맞은 값들을 갖고온다. 이때 1행,2행,3행,4행이 전부다 id가 같으면 출력이 2개가 될수 있기 때문에 DISTCINT를 이용한다.