http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=74&page=1&position=1글쓴이 : 김홍선


시간선분(time segment) 혹은 선분이력의 중복을 제거한 합집합의 총시간과 각각의 선분을 구해보자.

선분이력은 아래와 같이 주어졌다.

select '20060519000000' start_time, '20060519000100' end_time from dual union all
select '20060519010000', '20060519020000'from dual union all
select '20060519014000', '20060519020200'from dual union all
select '20060519015000', '20060519030300'from dual union all
select '20060519040000', '20060519043000'from dual


총시간은 아래와 같이 구한다.
결과값의 단위는 (date타입 - date타입) 즉, 일수이므로 적당한 값을 곱하여 시간, 분, 초로 변환해 준다.
flag1 컬럼이 어떤 역할을 하는지가 흥미롭다.
그래프에서 flag1의 값이 지그재그로 변하는 것을 상상할 수 있는가?


SELECT SUM (lag_time - se_time) total
FROM (
    SELECT se_time
        , LEAD (se_time) OVER (ORDER BY se_time) lag_time
        , SUM (DECODE (flag0, 'S', 1, -1)) OVER (ORDER BY se_time) flag1
    FROM (
        SELECT TO_DATE (start_time, 'yyyymmddhh24miss') se_time
            , 'S' flag0
        FROM TEST
            UNION ALL
        SELECT TO_DATE (end_time, 'yyyymmddhh24miss')
            , 'E'
        FROM TEST
        )
    )
WHERE flag1 > 0

쿼리결과
-----------------
TOTAL
0.106944444444444


결과 합집합으로 인해 주어지는 각각의 선분은 아래와 같이 구한다.

SELECT MIN (se_time) start_time, MAX (se_time) end_time
FROM (
    SELECT se_time
           , SUM (flag2) OVER (ORDER BY se_time) grp
    FROM (
        SELECT se_time
               , DECODE (LAG (flag1, 1, 0) OVER (ORDER BY 1), 0, 1, 0) flag2
        FROM (
            SELECT se_time
                   , SUM (DECODE (flag0, 'S', 1, -1)) OVER (ORDER BY se_time) flag1
            FROM (
                SELECT TO_DATE (start_time, 'yyyymmddhh24miss') se_time
                       , 'S' flag0
                FROM TEST
                    UNION ALL
                SELECT TO_DATE (end_time, 'yyyymmddhh24miss')
                       , 'E'
                FROM TEST
                )
            )
        )
    )
GROUP BY grp

쿼리결과
-----------------------------------------------
START_TIME              END_TIME
2006-05-19              2006-05-19 오전 12:01:00
2006-05-19 오전 1:00:00  2006-05-19 오전 3:03:00
2006-05-19 오전 4:00:00  2006-05-19 오전 4:30:00