Leetcode 601 - Human Traffic of Stadium

링크: https://leetcode.com/problems/human-traffic-of-stadium/

 

습득한 점: 

연속되는 숫자를 찾는 새로운 아이디어.

GROUP BY, PARTITION BY 차이 재확인.

hard 난이도는 아니었다.

 

---

문제:

사람 수 (people)가 100이상일 때, 3개 이상의 연속되는 "id"값에 대한 행을 모두 조회하는 쿼리 작성 

결과값은 "visit_date" 기준으로 오름차순 정렬

 

 

문제파악:

일단 visit_date는 정렬 빼고는 신경 쓸 필요는 없을 것 같았고,

우선 사람 수가 100명 이상인 테이블을 걸러낸 결과값을

WITH문으로 테이블을 새롭게 구성한 뒤 쿼리 작성을 시작하면 될 것 같다.

 

대략적으로 2가지 풀이법이 보인다. 셀프 조인 혹은 윈도우 함수 사용하기

 

풀이:

 

1. 'people'이 100 이상인 행들을 걸러냈을 때, id 값의 모양을 예상해본다.

데이터 사이사이에 100보다 낮은 값들이 포함되어 있었을 것이니,
아래 표와 같이 row_number()를 활용해서 강제적으로 맨 위부터 순위를 매긴 후
id 값에서 row_number값을 뺀 차이값 diff를 구하면,
연속되는 id값에서 벗어나는 순간에 diff값은 숫자가 바뀌게 된다.

 

ex) 아래 표를 예시로 든다면, id_1이 4에서 6으로 변할때, 차이값은 0에서 1로 변한다.

ex), id_2에서 5로 변할때, 차이값은 0에서 2로 변한다.

id_1	row_number	diff_1	id_2	row_number	diff_2
1	1	0	1	1	0
2	2	0	2	2	0
3	3	0	5	3	2
4	4	0	6	4	2
6	5	1	7	5	2
7	6	1	9	6	3

 

일종의 파티션을 나눈다고 생각하면 된다.

이런식의 계산을 이용하려면, 반드시 id 값을 기준으로 오름차순 정렬이 먼저 이루어져 있어야 한다.

WITH Sorted AS (
SELECT id
     , visit_date
     , people
     , id - ROW_NUMBER() OVER (ORDER BY id) AS diff
FROM Stadium
WHERE people >=100
ORDER BY id     
)​

 

 

2.  diff를 기준으로 GROUP BY 이후,  행의 갯수 조회

 

기존 만들어진 WITH문을 그대로 이용해서 SELECT문에는 count(*)을 추가하고,  diff 기준으로 groupby,
HAVING count(*) >= 3을 조건으로 쿼리를 실행하면 diff의 그룹별 행의 갯수가 표현된다.

WITH Sorted AS (
SELECT id
     , visit_date
     , people
     , id - ROW_NUMBER() OVER (ORDER BY id) AS diff
FROM Stadium
WHERE people >=100
ORDER BY id     
)

, Ranked AS (
    SELECT id
         , visit_date
         , people
         , diff
         , COUNT(*) AS consecutive_num
    FROM Sorted
    GROUP BY diff
    HAVING COUNT(*) >=3
)

3. 찾아낸 diff를 활용하여 정답 찾기

"consecutive_num" 은 3이상이면 나타나는 값이니 신경 쓸 필요는 없다.

2번의 테스트 케이스에 따르면, diff 가 2인 것이 연속된 id 4개를 가지고 있다는 것이니, 
연속되는 id값의 행들만 찾고 싶다면, diff를 기준으로 Sorted 테이블과 inner 조인 시켜주면 된다.

문제에 날짜기준 오름차순 정렬조건도 있지만, id가 커짐에따라 방문 일자도 하루씩 늘어나기 때문. 

 

WITH Sorted AS (
SELECT id
     , visit_date
     , people
     , id - ROW_NUMBER() OVER (ORDER BY id) AS diff
FROM Stadium
WHERE people >=100
ORDER BY id     
)

, Ranked AS (
    SELECT id
         , visit_date
         , people
         , diff
         , COUNT(*) AS consecutive_num
    FROM Sorted
    GROUP BY diff
    HAVING COUNT(*) >=3
)

SELECT Sorted.id
     , Sorted.visit_date
     , Sorted.people
       
FROM Sorted
   INNER JOIN Ranked ON Sorted.diff = Ranked.diff

 

 

 

PARTITION BY 이용:

WITH Sorted AS (
    SELECT id
         , visit_date
         , people
         , id - ROW_NUMBER() OVER (ORDER BY id) AS diff
    FROM Stadium
    WHERE people >= 100
),
    consecutive_count AS (
        SELECT id
            , visit_date
            , people
            , diff
            , COUNT(*) OVER (PARTITION BY diff) AS consecutive_num
        FROM Sorted
    )

SELECT *
FROM consecutive_count
ORDER BY id

 

큰 차이는 없지만, PARTITION BY를 활용하면 더욱 직관적으로 쿼리를 작성할 수 있다.

무엇보다 GROUP BY는 조건에 따른 그룹의 첫번째 행만을 남기고, 이외 행은 다 날리게 되지만
(아래 output케이스는 Ranked 테이블에서 HAVING count(*) >=3 조건만 없다면 count(*) id가 2, 5인 행만 남을 것이다)

PARTITION BY는 조건에 해당하는 모든 id 행을 보여준다.

이후 consecutive_num >=3  인 값들만 찾으면 된다.

또한 Sorted테이블 이후에는, 정렬순서가 유지되지 않을 수도 있기에

+ ORDER BY id 추가해주면 된다.

 

 (*** GROUP BY를 활용하는 경우, 이미 id 기준 오름차순 정렬된 테이블에 inner 조인하는 것이 때문에 따로 추가할 필요는 없다)