[DATA ON AIR/ 퀴즈로 배우는 SQL] 연속된 날짜를 하나의 그룹으로 표현해보자

본 문제는 한국데이터산업진흥원 DATA ON-AIR에서 '기술자료'로 제공하는 SQL 퀴즈입니다.

 

 

https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?pageid=4&mod=document&keyword=%ED%80%B4%EC%A6%88&uid=236593 

퀴즈로 배우는 SQL - 연속된 날짜를 하나의 그룹으로 표현해보자

 

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

---

문제

번호와 날짜를 가진 테이블(<표 1> 참조)에서 번호별로 연속된 날짜를 하나의 그룹으로 묶어서 <표 2>와 같이 번호, 시작일, 종료일, 일수를 보여주는 쿼리를 작성하세요.

<표 1> 원본 테이블

---

풀이

<참고사항>

풀이 할 때 테이블을 아래와 같이 만들었다.

또한 테이블 생성에서 visit_dt가 timestamp 형태로 만들어져 쿼리에서 DATE 변환을 해줬다.

 

 · 테이블명 : user_log

 · 컬럼명 : NO → id, DT → visit_dt

 

문제에서 중요한 점은 id별로 연속되는 visit_dt를 나누는 것이었다.

  ① 사용자 변수*를 선언해서 id, visit_dt 순으로 순번을 부여해주었다.

  ② visit_dt 에서 ROWNUM(경과일수)을 빼줘서 연속되지 않은 경우 다른 일자(GRP)가 나오도록 했다.

SELECT ul.id, DATE(ul.visit_dt) visit_dt,
	   @ROWNUM := @ROWNUM +1 AS ROWNUM,
       DATE_ADD(DATE(ul.visit_dt), INTERVAL -(@ROWNUM) DAY) AS GRP
 FROM data_on_air.user_log ul, (SELECT @ROWNUM := 0) NUM

 

위 결과를 기본 테이블로 하여 id, GRP별로 그룹핑해서 집계함수를 사용하면 id별 → 일자구간별 결과를 출력할 수 있다.

SELECT T1.id, MIN(T1.visit_dt) AS from_dt
			, MAX(T1.visit_dt) AS to_dt
			, COUNT(*) AS CNT
         FROM (
                 SELECT ul.id, DATE(ul.visit_dt) visit_dt,
                        @ROWNUM := @ROWNUM +1 AS ROWNUM,
                        DATE_ADD(DATE(ul.visit_dt), INTERVAL -(@ROWNUM) DAY) AS GRP
                 FROM data_on_air.user_log ul, (SELECT @ROWNUM := 0) NUM
             ) T1
GROUP BY T1.id, T1.GRP;

---

✏  참고

 

* 사용자 변수

[삼쾌한 IT강의] MySQL 변수 종류 및 사용법