연속 일자를 하나의 그룹으로 표시하기

요즘 구루비(gurubee.net)에서 쉬운 질문 위주로 SQL작성 연습을 해보고 있습니다. 생각보다 다양한 DBMS에 대한 질문이 꾸준히 올라오고 있어서 많은 공부가 되네요.

연속된 일자를 그룹으로 표시하는 내용인데 참고가 될 만한 내용이라 정리해보았습니다. 먼저 그룹을 만들고자 하는 테이블의 형태는 아래와 같습니다.

 

<원본 테이블>

KEY	DATE
99769	2020-09-02
99769	2020-09-03
99769	2020-09-04
99769	2020-09-05
99769	2020-09-10
99769	2020-09-11
99769	2020-09-14
99769	2020-09-15
99769	2020-09-16
99769	2020-09-17
99769	2020-09-18
99769	2020-09-19
99769	2020-09-20

<원하는 결과세트>

KEY	기간	일수
99769	2020-09-02 ~ 2020-09-05	4
99769	2020-09-10 ~ 2020-09-11	2
99769	2020-09-14 ~ 2020-09-20	7

처음엔 LAG, LEAD, LEFT OUTER JOIN등을 사용해서 SQL을 작성해보았는데, 찾아보니 기막히고 간결한 방법이 있었습니다. 바로 ROWNUM을 사용한 것인데요. 저는 ROW_NUMBER 함수를 사용했습니다.

with t as (
select 99769 key, date'2020-09-02' dt from dual
union all select 99769, date'2020-09-03' from dual
union all select 99769, date'2020-09-04' from dual
union all select 99769, date'2020-09-05' from dual
union all select 99769, date'2020-09-10' from dual
union all select 99769, date'2020-09-11' from dual
union all select 99769, date'2020-09-14' from dual
union all select 99769, date'2020-09-15' from dual
union all select 99769, date'2020-09-16' from dual
union all select 99769, date'2020-09-17' from dual
union all select 99769, date'2020-09-18' from dual
union all select 99769, date'2020-09-19' from dual
union all select 99769, date'2020-09-20' from dual
)
select key, min(dt) || ' ~ ' || max(dt), count(*)
from (
select key, dt, dt - row_number() over(order by key, dt) grp
from t
)
group by key, grp
order by key, min(dt)

 

오라클에서 작동하는 SQL입니다. 순서대로 정렬된 날짜와 ROW_NUMBER를 빼면 동일한 차이인 것들을 그룹화할 수 있습니다. 집계 함수를 지원하는 DBMS라면 유사한 방법으로 활용할 수 있습니다.

 

blog.jooq.org/2015/11/07/how-to-find-the-longest-consecutive-series-of-events-in-sql/

How to Find the Longest Consecutive Series of Events in SQL

gurubee.net/lecture/2194

[퀴즈] 연속된 날짜를 하나의 그룹으로 표현해 보자