[mySQL] 리트코드 180. Consecutive Numbers - 윈도우 함수 LAG, LEAD를 사용한 풀이

https://leetcode.com/problems/consecutive-numbers/

조회할 데이터 : 3번 연속 나오는 숫자를 출력하기

1. LAG 함수 사용

SELECT DISTINCT l.num AS ConsecutiveNums
FROM (
    SELECT id, num,
          LAG(num, 1) OVER (ORDER BY id) AS L1,
          LAG(num, 2) OVER (ORDER BY id) AS L2
    FROM Logs) AS l
WHERE l.num = l.L1 AND l.num = l.L2

2. LEAD 함수 사용

SELECT DISTINCT l.num AS ConsecutiveNums
FROM (
      SELECT id, num,
            LEAD(num, 1) OVER (ORDER BY id) AS L1,
            LEAD(num, 2) OVER (ORDER BY id) AS L2
      FROM logs) AS l
WHERE l.num = l.L1 AND l.num = l.L2