[SQL] LeetCode: 180. Consecutive Numbers

180. Consecutive Numbers

 

문제

 

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

Return the result table in any order.

 

 

풀이1) JOIN 사용

SELECT DISTINCT a.num AS ConsecutiveNums
FROM logs a
    LEFT JOIN logs b ON b.id = ( a.id + 1)
    LEFT JOIN logs c ON c.id = ( a.id + 2)
WHERE a.num = b.num AND b.num= c.num

 

 

풀이2) 윈도우 함수 - LAG함수 사용 + FORM절 서브쿼리

SELECT DISTINCT num AS ConsecutiveNums
FROM (
      SELECT num
            ,LAG(num) OVER (ORDER BY id) AS 'num2'
            ,LAG(num,2) OVER (ORDER BY id) AS 'num3'
      FROM  logs
      ) window
WHERE num = num2 and num2 = num3

 

# LAG(num,2) OVER (ORDER BY id) : id 순서에 따라 두 칸 앞에 있는 num값 출력 

 

 

풀이3) 윈도우 함수 - LEAD함수 사용 + FORM절 서브쿼리

SELECT DISTINCT num AS ConsecutiveNums
FROM (
      SELECT num
            ,LEAD(num) OVER (ORDER BY id) AS next
            ,LEAD(num,2) OVER (ORDER BY id) AS afternext
      FROM  logs
      )l
WHERE num = next and next = afternext

 

# LEAD(num,2) OVER (ORDER BY id) : id 순서에 따라 두 칸 뒤에 있는 num값 출력