[MySQL] LeetCode 180 - Consecutive Numbers 풀이 3가지 (Join, Lead, Leg)

문제 출처: https://leetcode.com/problems/consecutive-numbers/description/

 

1. SELF INNER JOIN을 이용한 풀이

SELECT DISTINCT L1.NUM AS CONSECUTIVENUMS
FROM LOGS L1
     JOIN LOGS L2 ON L1.ID + 1 = L2.ID
     JOIN LOGS L3 ON L2.ID + 1 = L3.ID
WHERE L1.NUM = L2.NUM
AND L2.NUM = L3.NUM

 

2. LEAD(당겨오기)를 이용한 풀이

SELECT DISTINCT NUM AS CONSECUTIVENUMS
FROM(
     SELECT NUM
          , LEAD(NUM, 1) OVER (ORDER BY ID) AS L1
          , LEAD(NUM, 2) OVER (ORDER BY ID) AS L2
     FROM LOGS) AS L
WHERE NUM = L1
AND L1 = L2

 

3. LAG(밀어내기)를 이용한 풀이

SELECT DISTINCT NUM AS CONSECUTIVENUMS
FROM(
     SELECT NUM
          , LAG(NUM, 1) OVER (ORDER BY ID DESC) AS L1
          , LAG(NUM, 2) OVER (ORDER BY ID DESC) AS L2
     FROM LOGS) AS L
WHERE NUM = L1
AND L1 = L2

 

NOTE: 당겨오고 밀어낸 부분은 NULL로 채워진다.

 

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반' 을 수강하며 작성한 내용입니다.