[PostgreSQL]연속된 날짜를 하나의 그룹으로 표현

문제 

번호와 날짜를 가진 [그림1]테이블에서 번호별 연속된 날짜를 하나의 그룹으로 묶어 [그림2]와 같이 번호, 시작일, 종료일, 일수를 보여주는 쿼리 작성

테이블의 번호와 날짜는 중복되지 않는 유일한 값 

출처 : 구루비

 

<풀이>

SELECT NUM , MIN(DT) FROM_DT, MAX(DT)TO_DT, COUNT(DT) CNT
FROM (SELECT NUM, DT
        , SUM(FLAG)OVER(PARTITION BY NUM ORDER BY DT) GRP
       FROM (SELECT NUM, DT
                , CASE WHEN TO_DATE(LAG(DT)OVER(PARTITION BY NUM ORDER BY DT),'YYYYMMDD')= TO_DATE(DT,'YYYYMMDD')-1 
                       THEN 0 ELSE 1 END FLAG
              FROM T) A) B
GROUP BY NUM, GRP
ORDER BY 1, MIN(DT);

1.  LAG 함수를 이용해서 바로 전행 비교하여 연속되는 날 확인

SELECT NUM, DT
  , CASE WHEN TO_DATE(LAG(DT)OVER(PARTITION BY NUM ORDER BY DT),'YYYYMMDD')= TO_DATE(DT,'YYYYMMDD')-1 
         THEN 0 ELSE 1 END FLAG
  FROM T

2. 인라인뷰로 만든 FLAG(연속되는 날짜 아닌지 찾은 값)을 SUM해서 그룹핑 할 수있는 값을 만듬

    연속되지 않으면 FLAG값이 1 연속되면 값이 0

    연속될때는 SUM값이 같음

SELECT NUM, DT
  , SUM(FLAG)OVER(PARTITION BY NUM ORDER BY DT) GRP
  FROM (SELECT NUM, DT
           , CASE WHEN TO_DATE(LAG(DT)OVER(PARTITION BY NUM ORDER BY DT),'YYYYMMDD')= TO_DATE(DT,'YYYYMMDD')-1 
                  THEN 0 ELSE 1 END FLAG
         FROM T) A

3. SUM값을 GROUP BY 하여 MIN과 MAX값을 가져오면 시작날짜와 끝나는 날짜  출력

SELECT NUM , MIN(DT) FROM_DT, MAX(DT)TO_DT, COUNT(DT) CNT
FROM (SELECT NUM, DT
        , SUM(FLAG)OVER(PARTITION BY NUM ORDER BY DT) GRP
       FROM (SELECT NUM, DT
                , CASE WHEN TO_DATE(LAG(DT)OVER(PARTITION BY NUM ORDER BY DT),'YYYYMMDD')= TO_DATE(DT,'YYYYMMDD')-1 
                       THEN 0 ELSE 1 END FLAG
              FROM T) A) B
GROUP BY NUM, GRP
ORDER BY 1, MIN(DT);

 

 

출처 : http://www.gurubee.net/lecture/2194

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