[Leetcode] Human Traffic of Stadium

연속된 숫자에서 특정 조건으로 그룹화를 하는 신박한 방법을 알게 되어서 기록을 해보려 합니다.

리트코드의 Human Traffic of Stadium 문제입니다.

 

문제 : 날짜별로 경기장에 방문한 사람들의 수가 있습니다. 3번 이상 연속으로 100명 이상 방문한 id 들의 id, visit_date, people 을 출력하세요.

 

Input: 
Stadium table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

Output: 
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

 

두 가지 방법을 알려드리겠습니다. 첫 번째는 제가 푼 방식인데, 3번 연속이면 되기 때문에 lag 를 이용해서 풀었습니다. 하지만 3번 이상일 경우는 이 방법에는 한계가 있으며, 조인 방식도 문제를 풀기 위한 방법이고 직관적이지 않은 코드입니다. 

select  distinct a.id, visit_date, people
from stadium a 
inner join 
    (
        select id
        from
        (    
            select id, visit_date, people
            , lag(people) over(order by visit_date) as bf_people1
            , lag(people,2) over(order by visit_date) as bf_people2
            from stadium
            ) a
        where 1=1
        and people >= 100
        and bf_people1 >= 100
        and bf_people2 >= 100

    ) b on a.id = b.id or a.id = (b.id-1) or a.id = (b.id-2)
;

| id | visit_date | people | bf_people1 | bf_people2 |
| -- | ---------- | ------ | ---------- | ---------- |
| 1  | 2017-01-01 | 10     | null       | null       |
| 2  | 2017-01-02 | 109    | 10         | null       |
| 3  | 2017-01-03 | 150    | 109        | 10         |
| 4  | 2017-01-04 | 99     | 150        | 109        | 
| 5  | 2017-01-05 | 145    | 99         | 150        |
| 6  | 2017-01-06 | 1455   | 145        | 99         | 
| 7  | 2017-01-07 | 199    | 1455       | 145        | 3일 연속 100 이상
| 8  | 2017-01-09 | 188    | 199        | 1455       | 3일 연속 100 이상

 

하지만 더 좋은 방법이 있습니다. id 가 연속적으로 나타난 점을 이용하여, 100명 이상이라는 조건만 추린 후 100명 이상일 때의 사람들만 따로 id를 새로 부여합니다. 그리고 (기존의 id - 100명 이상 사람들에게 부여한 id) 를 계산하면 그룹화를 시킬 수 있습니다! 같은 그룹이 3 이상이면 내용을 출력하도록 하면 됩니다! 너무 좋은 아이디어라 내 것으로 만들기 강추강추!!!

    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rownum
            ,id - ROW_NUMBER() OVER (ORDER BY id) AS grp
    FROM Stadium
    WHERE people >= 100
)
SELECT S1.id
        ,S1.visit_date
        ,S1.people
FROM CTE S1
WHERE S1.grp IN (
    SELECT grp
    FROM CTE
    GROUP BY grp
    HAVING COUNT(1) >=3
)

# with 절 내에 그룹 생성하기 결과
| id | visit_date | people | rownum | grp |
| -- | ---------- | ------ | ------ | --- |
| 2  | 2017-01-02 | 109    | 1      | 1   |
| 3  | 2017-01-03 | 150    | 2      | 1   |
| 5  | 2017-01-05 | 145    | 3      | 2   |
| 6  | 2017-01-06 | 1455   | 4      | 2   |
| 7  | 2017-01-07 | 199    | 5      | 2   |
| 8  | 2017-01-09 | 188    | 6      | 2   |