[ORACLE SQL 문제] 연속된 숫자만 그룹핑하기

문제 링크: http://www.gurubee.net/lecture/2194

(내 댓글 확인하기)

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

---

내 쿼리

 

WITH TMP
as (
SELECT 100 NO, '20090101' DT FROM dual
  UNION ALL SELECT 100, '20090102' FROM dual
  UNION ALL SELECT 100, '20090103' FROM dual
  UNION ALL SELECT 100, '20090105' FROM dual
  UNION ALL SELECT 100, '20090106' FROM dual
  UNION ALL SELECT 100, '20090109' FROM dual
  UNION ALL SELECT 100, '20090120' FROM dual
  UNION ALL SELECT 200, '20090101' FROM dual
  UNION ALL SELECT 200, '20090102' FROM dual
  UNION ALL SELECT 200, '20090103' FROM dual
  UNION ALL SELECT 200, '20090104' FROM dual
  UNION ALL SELECT 200, '20090131' FROM dual
  UNION ALL SELECT 200, '20090201' FROM dual
)
SELECT   NO
       , MIN(DT) FROM_DT
       , MAX(DT) TO_DT
       , COUNT(*) CNT
FROM       
         (
         SELECT   NO
                , DT
         --       , FLAG
                , SUM(FLAG) OVER (PARTITION BY NO ORDER BY DT) GRP
         FROM 
         (
                  SELECT   NO
                         , DT
                         , DECODE(LAG(DT) OVER (PARTITION BY NO ORDER BY DT), DT-1, 0, 1) FLAG --연속된 값이면 0 아니면 1. 이전값 알 수 있는 LAG 함수 이용
                  FROM     TMP
         )
)         
GROUP BY NO, GRP
ORDER BY NO, FROM_DT
;

 

---

깨달은 점

 

[내가 생각한 것]
LAG 함수까지는 생각해냄

[내가 생각 못한 것]

1. 이중 쿼리(FROM절)를 생각 못함.
2. 삼중 쿼리로 COUNT(*) 하고 GROUP BY 할 수 있다는 점...