[더비's 퀴즈]연속된 날짜 구간의 일수 찾기.

2개의 댓글이 등록되었습니다.

1. 김민호 2014년 11월 16일 23:16

   * 풀이.

   순차적으로 정렬된 날짜값에서 Rownum을 뺀 값은 날짜가 연속적일 경우 같은 날짜 값이 나오게 되며,
   연속적이지 않은 경우에는 다른값이 나오게 된다는 점을 이용. <Ref. 1>
   이 값을 Order by와 Group by 함수를 이용하여 문제 해결.

   Oracle PL/SQL

   WITH t AS (SELECT 1 no, '20140101' dt FROM dual  UNION ALL SELECT 1, '20140102' FROM dual  UNION ALL SELECT 1, '20140103' FROM dual  UNION ALL SELECT 1, '20140203' FROM dual  UNION ALL SELECT 1, '20140303' FROM dual) SELECT no, dt      , ROWNUM rn      , TO_DATE(dt,'yyyymmdd') - ROWNUM gubn_flag   FROM (SELECT * FROM  t ORDER BY no, dt) ; NO DT RN GUBN_FLA ---------- -------- ---------- -------- 1 20140101 1 13/12/31 1 20140102 2 13/12/31 1 20140103 3 13/12/31 1 20140203 4 14/01/30 1 20140303 5 14/02/26

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

   WITH t AS (SELECT 1 no, '20140101' dt FROM dual  UNION ALL SELECT 1, '20140102' FROM dual  UNION ALL SELECT 1, '20140103' FROM dual  UNION ALL SELECT 1, '20140203' FROM dual  UNION ALL SELECT 1, '20140303' FROM dual) SELECT no, dt      , ROWNUM rn      , TO_DATE(dt,'yyyymmdd') - ROWNUM gubn_flag   FROM (SELECT * FROM  t ORDER BY no, dt) ;         NO DT               RN GUBN_FLA ---------- -------- ---------- --------          1 20140101          1 13/12/31          1 20140102          2 13/12/31          1 20140103          3 13/12/31          1 20140203          4 14/01/30          1 20140303          5 14/02/26

   
   * 결과값.

   Oracle PL/SQL

   WITH t AS (SELECT 1 NO ,'20141101' dt FROM dual UNION ALL SELECT 1, '20141102' FROM dual UNION ALL SELECT 1, '20141103' FROM dual UNION ALL SELECT 1, '20141105' FROM dual UNION ALL SELECT 1, '20141106' FROM dual UNION ALL SELECT 1, '20141109' FROM dual UNION ALL SELECT 1, '20141120' FROM dual UNION ALL SELECT 2, '20141101' FROM dual UNION ALL SELECT 2, '20141102' FROM dual UNION ALL SELECT 2, '20141103' FROM dual UNION ALL SELECT 2, '20141104' FROM dual UNION ALL SELECT 2, '20141130' FROM dual UNION ALL SELECT 2, '20141201' FROM dual) SELECT No , MIN(dt) from_dt , MAX(dt) to_dt , COUNT(*) cnt FROM (SELECT * FROM t ORDER BY no, dt) GROUP BY no, TO_DATE(dt, 'yyyymmdd') - ROWNUM /* ★퀴즈의 핵심 */ ORDER BY no, from_dt ; NO FROM_DT TO_DT CNT ---------- -------- -------- ---------- 1 20141101 20141103 3 1 20141105 20141106 2 1 20141109 20141109 1 1 20141120 20141120 1 2 20141101 20141104 4 2 20141130 20141201 2 6 개의 행이 선택되었습니다.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

   WITH t AS (SELECT 1 NO  ,'20141101' dt FROM dual UNION ALL SELECT 1, '20141102' FROM dual UNION ALL SELECT 1, '20141103' FROM dual UNION ALL SELECT 1, '20141105' FROM dual UNION ALL SELECT 1, '20141106' FROM dual UNION ALL SELECT 1, '20141109' FROM dual UNION ALL SELECT 1, '20141120' FROM dual UNION ALL SELECT 2, '20141101' FROM dual UNION ALL SELECT 2, '20141102' FROM dual UNION ALL SELECT 2, '20141103' FROM dual UNION ALL SELECT 2, '20141104' FROM dual UNION ALL SELECT 2, '20141130' FROM dual UNION ALL SELECT 2, '20141201' FROM dual) SELECT No      , MIN(dt)  from_dt      , MAX(dt)  to_dt      , COUNT(*) cnt   FROM (SELECT *         FROM t         ORDER BY no, dt) GROUP BY no, TO_DATE(dt, 'yyyymmdd') - ROWNUM  /* ★퀴즈의 핵심 */ ORDER BY no, from_dt ;         NO FROM_DT  TO_DT           CNT ---------- -------- -------- ----------          1 20141101 20141103          3          1 20141105 20141106          2          1 20141109 20141109          1          1 20141120 20141120          1          2 20141101 20141104          4          2 20141130 20141201          2   6 개의 행이 선택되었습니다.

   응답
2. 김지룡 2014년 12월 11일 16:22

   연속된 날짜 구간의 일수 찾기 퀴즈의 정답을 알아볼까요?
   이 문제의 핵심은 어떻게 연속되는 구간의 날짜를 찾아 그룹을 만들어 일수를 세는 것 입니다.
   그렇다면 중간 중간 띄어져 있는 날짜들의 연속된 구간을 찾을 수 있을까요?
   이 의문의 정답은 오라클 8.1.6부터 추가된 분석함수의 기능 중 하나를 쓰면 해결할 수 있습니다.
   바로 LAG함수이죠.
   이제 어떻게 풀어나가는지 같이 해보도록 해요.
    

   Oracle PL/SQL

   --1단계 with base as(SELECT 1 no, '20141101' dt FROM dual               UNION ALL               SELECT 1, '20141102' FROM dual               UNION ALL               SELECT 1, '20141103' FROM dual               UNION ALL               SELECT 1, '20141105' FROM dual               UNION ALL                             SELECT 1, '20141106' FROM dual               UNION ALL               SELECT 1, '20141109' FROM dual               UNION ALL               SELECT 1, '20141120' FROM dual               UNION ALL               SELECT 2, '20141101' FROM dual               UNION ALL               SELECT 2, '20141102' FROM dual               UNION ALL               SELECT 2, '20141103' FROM dual               UNION ALL               SELECT 2, '20141104' FROM dual               UNION ALL               SELECT 2, '20141130' FROM dual               UNION ALL               SELECT 2, '20141201' FROM dual) SELECT no,        dt,        DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)             , TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')             , 0, 1) flag     FROM base ;         NO DT                     FLAG ---------- ---------------- ----------          1 20141101                  1          1 20141102                  0          1 20141103                  0          1 20141105                  1          1 20141106                  0          1 20141109                  1          1 20141120                  1          2 20141101                  1          2 20141102                  0          2 20141103                  0          2 20141104                  0          2 20141130                  1          2 20141201                  0 위의 쿼리 실행결과를 보면 FLAG 항목에 바로 앞 날짜가 연속되는 경우는 0 그렇지 않으면 1로 표현되고 있습니다. FLAG값을 구한 로직부분을 보면 신기한게 보일겁니다 LAG함수인데요 이 함수는 계산 대상 데이터(로우)들을 ORDER BY 절로 정렬하여  명시된 값을 기준으로 이전 로우의 값을 반환하는 함수입니다. 이 성능을 사용하여 DECODE문을 이용해 두 날짜를 비교하여 같으면 0 아니면 1 값을 얻었습니다. --단계 2 with base as(SELECT 1 no, '20141101' dt FROM dual               UNION ALL               SELECT 1, '20141102' FROM dual               UNION ALL               SELECT 1, '20141103' FROM dual               UNION ALL               SELECT 1, '20141105' FROM dual               UNION ALL               SELECT 1, '20141106' FROM dual               UNION ALL              SELECT 1, '20141109' FROM dual               UNION ALL               SELECT 1, '20141120' FROM dual               UNION ALL               SELECT 2, '20141101' FROM dual               UNION ALL               SELECT 2, '20141102' FROM dual               UNION ALL               SELECT 2, '20141103' FROM dual               UNION ALL               SELECT 2, '20141104' FROM dual               UNION ALL               SELECT 2, '20141130' FROM dual               UNION ALL               SELECT 2, '20141201' FROM dual) SELECT no, dt              , SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp           FROM (                            SELECT no, dt                                            , DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)                                            , TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')                                             , 0, 1) flag                                  FROM base                            )          NO DT                      GRP ---------- ---------------- ----------          1 20141101                  1          1 20141102                  1          1 20141103                  1          1 20141105                  2          1 20141106                  2          1 20141109                  3          1 20141120                  4                  2 20141101                  1          2 20141102                  1          2 20141103                  1          2 20141104                  1          2 20141130                  2          2 20141201                  2 위의 실행결과를 보게 되면 거의 마지막 단계까지 다 온게 느껴지실 겁니다. GRP 항목 값이 연속된 날짜끼리 동일하게 조회되는 것을 확인할 수 있으니까요. 이번 단계도 분석함수가 사용되었는데, no 그룹 별로 날짜로 정렬하면서 FLAG 값을 합산해 나간 것 입니다. 누적합계를 구한 값이죠. 이제 최종적으로 GROUP BY를 하면 최종 결과가 나올 수 있겠죠? --정답 with base as(SELECT 1 no, '20141101' dt FROM dual               UNION ALL               SELECT 1, '20141102' FROM dual               UNION ALL               SELECT 1, '20141103' FROM dual               UNION ALL               SELECT 1, '20141105' FROM dual               UNION ALL               SELECT 1, '20141106' FROM dual               UNION ALL               SELECT 1, '20141109' FROM dual               UNION ALL               SELECT 1, '20141120' FROM dual               UNION ALL               SELECT 2, '20141101' FROM dual               UNION ALL               SELECT 2, '20141102' FROM dual               UNION ALL               SELECT 2, '20141103' FROM dual               UNION ALL                SELECT 2, '20141104' FROM dual               UNION ALL               SELECT 2, '20141130' FROM dual               UNION ALL               SELECT 2, '20141201' FROM dual) SELECT no      , MIN(dt)  from_dt      , MAX(dt)  to_dt      , COUNT(*) cnt    FROM ( -- 2단계 --         SELECT no, dt              , SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp           FROM ( -- 1단계 --                 SELECT no, dt                      , DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)                      , TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')                      , 0, 1) flag                   FROM base                 )         )  GROUP BY no, grp  ORDER BY no, from_dt;          NO FROM_DT          TO_DT                   CNT ---------- ---------------- ---------------- ----------          1 20141101         20141103                  3          1 20141105         20141106                  2          1 20141109         20141109                  1          1 20141120         20141120                  1          2 20141101         20141104                  4          2 20141130         20141201                  2 수고하셨습니다.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160

   --1단계 with base as(SELECT 1 no, '20141101' dt FROM dual               UNION ALL               SELECT 1, '20141102' FROM dual               UNION ALL               SELECT 1, '20141103' FROM dual               UNION ALL               SELECT 1, '20141105' FROM dual               UNION ALL                             SELECT 1, '20141106' FROM dual               UNION ALL               SELECT 1, '20141109' FROM dual               UNION ALL               SELECT 1, '20141120' FROM dual               UNION ALL               SELECT 2, '20141101' FROM dual               UNION ALL               SELECT 2, '20141102' FROM dual               UNION ALL               SELECT 2, '20141103' FROM dual               UNION ALL               SELECT 2, '20141104' FROM dual               UNION ALL               SELECT 2, '20141130' FROM dual               UNION ALL               SELECT 2, '20141201' FROM dual) SELECT no,        dt,        DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)             , TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')             , 0, 1) flag     FROM base ;         NO DT                     FLAG ---------- ---------------- ----------          1 20141101                  1          1 20141102                  0          1 20141103                  0          1 20141105                  1          1 20141106                  0          1 20141109                  1          1 20141120                  1          2 20141101                  1          2 20141102                  0          2 20141103                  0          2 20141104                  0          2 20141130                  1          2 20141201                  0 위의 쿼리 실행결과를 보면 FLAG 항목에 바로 앞 날짜가 연속되는 경우는 0 그렇지 않으면 1로 표현되고 있습니다. FLAG값을 구한 로직부분을 보면 신기한게 보일겁니다 LAG함수인데요 이 함수는 계산 대상 데이터(로우)들을 ORDER BY 절로 정렬하여  명시된 값을 기준으로 이전 로우의 값을 반환하는 함수입니다. 이 성능을 사용하여 DECODE문을 이용해 두 날짜를 비교하여 같으면 0 아니면 1 값을 얻었습니다. --단계 2 with base as(SELECT 1 no, '20141101' dt FROM dual               UNION ALL               SELECT 1, '20141102' FROM dual               UNION ALL               SELECT 1, '20141103' FROM dual               UNION ALL               SELECT 1, '20141105' FROM dual               UNION ALL               SELECT 1, '20141106' FROM dual               UNION ALL              SELECT 1, '20141109' FROM dual               UNION ALL               SELECT 1, '20141120' FROM dual               UNION ALL               SELECT 2, '20141101' FROM dual               UNION ALL               SELECT 2, '20141102' FROM dual               UNION ALL               SELECT 2, '20141103' FROM dual               UNION ALL               SELECT 2, '20141104' FROM dual               UNION ALL               SELECT 2, '20141130' FROM dual               UNION ALL               SELECT 2, '20141201' FROM dual) SELECT no, dt              , SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp           FROM (                            SELECT no, dt                                            , DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)                                            , TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')                                             , 0, 1) flag                                  FROM base                            )          NO DT                      GRP ---------- ---------------- ----------          1 20141101                  1          1 20141102                  1          1 20141103                  1          1 20141105                  2          1 20141106                  2          1 20141109                  3          1 20141120                  4                  2 20141101                  1          2 20141102                  1          2 20141103                  1          2 20141104                  1          2 20141130                  2          2 20141201                  2 위의 실행결과를 보게 되면 거의 마지막 단계까지 다 온게 느껴지실 겁니다. GRP 항목 값이 연속된 날짜끼리 동일하게 조회되는 것을 확인할 수 있으니까요. 이번 단계도 분석함수가 사용되었는데, no 그룹 별로 날짜로 정렬하면서 FLAG 값을 합산해 나간 것 입니다. 누적합계를 구한 값이죠. 이제 최종적으로 GROUP BY를 하면 최종 결과가 나올 수 있겠죠? --정답 with base as(SELECT 1 no, '20141101' dt FROM dual               UNION ALL               SELECT 1, '20141102' FROM dual               UNION ALL               SELECT 1, '20141103' FROM dual               UNION ALL               SELECT 1, '20141105' FROM dual               UNION ALL               SELECT 1, '20141106' FROM dual               UNION ALL               SELECT 1, '20141109' FROM dual               UNION ALL               SELECT 1, '20141120' FROM dual               UNION ALL               SELECT 2, '20141101' FROM dual               UNION ALL               SELECT 2, '20141102' FROM dual               UNION ALL               SELECT 2, '20141103' FROM dual               UNION ALL                SELECT 2, '20141104' FROM dual               UNION ALL               SELECT 2, '20141130' FROM dual               UNION ALL               SELECT 2, '20141201' FROM dual) SELECT no      , MIN(dt)  from_dt      , MAX(dt)  to_dt      , COUNT(*) cnt    FROM ( -- 2단계 --         SELECT no, dt              , SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp           FROM ( -- 1단계 --                 SELECT no, dt                      , DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)                      , TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')                      , 0, 1) flag                   FROM base                 )         )  GROUP BY no, grp  ORDER BY no, from_dt;          NO FROM_DT          TO_DT                   CNT ---------- ---------------- ---------------- ----------          1 20141101         20141103                  3          1 20141105         20141106                  2          1 20141109         20141109                  1          1 20141120         20141120                  1          2 20141101         20141104                  4          2 20141130         20141201                  2 수고하셨습니다.

    

   응답

댓글작성시 Code-Highlighter 삽입방법