[LeetCode Hard] 601. Human Traffic of Stadium

 

 

이번 문제는 개인적으로 많이 어려웠던 문제였다...ㅜ

다른 사람들의 풀이를 보니 LEAD()와 LAG()를 이용한 문제 풀이가 있었는데

솔직히 이번 문제를 풀면서 저런 함수가 있다는걸 처음 알았다

꾸준히 복습하면서 내껄로 만들어야지...

 

테이블 정보

 

 

문제

Write a solution to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each.
Return the result table ordered by visit_date in ascending order.
The result format is in the following example

 

 

결과 예시

 

 

정답 코드 1 (MySQL)

WITH tmp AS (
    SELECT *, id - ROW_NUMBER() OVER(ORDER BY id) AS gap
    FROM Stadium
    WHERE people >= 100
)

SELECT id, visit_date, people
FROM tmp
WHERE gap IN (
    SELECT gap
    FROM tmp
    GROUP BY gap
    HAVING COUNT(*) >= 3
)

 

 

문제 풀이 1

각 행의 people 수가 100명 이상이면서 연속된 id를 가진 행이 3개 이상인 레코드를 표시해야한다.

ROW_NUMBER()는 각 행에 연속적인 숫자를 부여하고 싶을 때 사용하는 함수로,

WITH 절을 보다시피, people 수가 100명 이상인 id들에 대해서 행 번호가 결정되기 때문에

결과 예시의 input 테이블은 아래의 과정으로 번호가 결정된다.

 

id	visit_date	people	ROW_NUMBER() OVER(ORDER BY id)	id - ROW_NUMBER() OVER(ORDER BY id)
1	2017-01-01	10
2	2017-01-02	109	1	1
3	2017-01-03	150	2	1
4	2017-01-04	99
5	2017-01-05	145	3	2
6	2017-01-06	1455	4	2
7	2017-01-07	199	5	2
8	2017-01-08	188	6	2

 

이후 gap(id - ROW_NUMBER() OVER(ORDER BY id))을 기준으로 각 그룹의 수가 3 이상에

해당하는 그룹을 select 문으로 조회하면 된다.

 

 

 

---

 

 

정답 코드 2 (MySQL)

WITH tmp AS (
    SELECT LAG(id, 2) OVER (ORDER BY id) AS pre_pre_id,
            LAG(id, 1) OVER (ORDER BY id) AS pre_id,
            id,
            LEAD(id, 1) OVER (ORDER BY id) AS next_id,
            LEAD(id, 2) OVER (ORDER BY id) AS next_next_id,
            visit_date,
            people
    FROM Stadium
    WHERE people >= 100
)

SELECT id, visit_date, people
FROM tmp
WHERE (pre_pre_id + 1 = pre_id AND pre_id + 1 = id) OR
        (pre_id + 1 = id AND id + 1 = next_id) OR
        (id + 1 = next_id AND next_id + 1 = next_next_id)
ORDER BY visit_date;

 

 

문제 풀이 2 

LAG와 LEAD는 각각 사전적인 의미로는 뒤떨어지다, 이끌다의 의미를 가지고 있는데

데이터베이스에서 비슷한 의미로

LAG는 이전 값을 가져오고, LEAD는 다음 것을 가져온다고 생각하면 될 것 같다.

 

 

LAG/LEAD(컬럼명 {,offset} {,default}) OVER({PARTITION BY} ORDER BY)

• LAG 함수 : 이전 행 값 return
• LEAD 함수 : 다음 행의 값 return
• offset : 값을 가져올 행의 위치, default 1
• default : 값이 없을 경우 지정 할 기본 값
• PARTITION BY : 현재 행 기준으로 이후 행 전부
• ORDER BY : 현재 행

(PARTITION BY는 생략이 가능하나, ORDER BY는 생략이 불가능)

 

 

결과 예시의 input 테이블을 기준으로 WITH 절 부분을 조회하면 아래와 같이 결과가 나온다.

 

 

 

문제 풀이 2 참고 링크

https://thenicesj.tistory.com/602

LAG, LEAD 함수에 대해서

 

https://velog.io/@ginoy/consecutive-numbers

MYSQL에서 consecutive numbers 구하기

 

https://jjjk84.tistory.com/64

Leetcode - 601. Human Traffic of Stadium