advent of sql 2024 후기 및 유용한 개념 정리

후기

https://solvesql.com/collections/advent-of-sql-2024/

긱뉴스 (https://news.hada.io/topic?id=18046) 에서 본 챌린지가 있어서, 12월 1일부터 25일간 노력해서 하루에 한 문제씩 풀었다.

advent of code 쪽은 제한 시간이 없으니까 오히려 아직까지도 25일치를 다 끝내지 못했는데, 이쪽은 모든 문제를 공개되고 24시간 내로 풀면 추가 특전이 있다길래 더 노력해버렸다는거..

하지만 막상 공개된 혜택은 조금 아쉽긴 해.. 하지만 혜택을 처음부터 알려주지 않고, 챌린지 중간에 알려줘서 안할 수도 없었음. 나중엔 아까워서 마저 했고.

아무튼 이번 챌린지를 진행하면서 몇 가지 개념은 새로 알게 되었다. 물론 실전에서 적용할 수 있을지는 잘 모르겠지만 아는게 어디야~

새로 알게된 유용한 개념들

참고로 sqlite.

(단순) 이동 평균 구하기

avg(FIELD) over(partition by FIELD2 order by end_at rows between 5 preceding and current row)

최장 연속 데이터 구하기 (streak)

with ranked as (
    select
        NAME,
        YEAR,
        row_number() over (partition by NAME order by YEAR) as RN
    from 
        yearly_best_games
)
, grouped as (
    select
        NAME,
        YEAR,
        (YEAR - RN) as YEAR_GROUP
    from
        ranked
)
, streaks as(
    select
        NAME,
        min(YEAR) as START_YEAR,
        max(YEAR) as END_YEAR,
        count(*) as STREAKS,
    from
        grouped
    group by 
        NAME,
        YEAR_GROUP
)

select
    NAME,
    max(STREAKS) as MAX_STREAKS
from
    streaks
group by
    NAME

데이터의 간격이 10분 이상 벌어지면 새 그룹으로 지정하기

with check_is_new_session as ( 
    select
        CATEGORY,
        case 
             when lag(TIMESTAMP) over (partition by CATEGORY over by TIMESTAMP) is null then 1
             when unixepoch(TIMESTAMP) - unixepoch(lag(TIMESTAMP)) over (partition by CATEGORY order by TIMESTAMP)) >= 600 then 1
             else 0
         end as IS_NEW_SESSION
     from
         data
)
select
    CATEGORY,
    SUM(IS_NEW_SESSION) over (partition by CATEGORY over TIMESTAMP) as NEW_SESSION_ID
from
    check_is_new_session

누적합

sum(FIELD) over (order by FIELD2)