오라클 시간 대칭차집합(합집합 - 교집합) ? 구하기 0 6 1,881

by 구기웅 [SQL Query] [2018.07.19 18:38:28]


ID START_TIME END_TIME
1 090000 113000
2 100000 120000
3 140000 143000

테이블에 데이터가 위의 형태로 들어가 있습니다.

START_TIME 과 END_TIME 의 차이를 구해서 시간의 합을 구하고 있습니다.

예를들면 

ID 1번의 시간은 9시 부터 11시 30분 = 2시간 30분

ID 2번의 시간은 10시 부터 12시 = 2시간 

ID 3번의 시간은 14시 부터 14시30분 = 30분

총 합은 5시간 이런식으로 구하고 있습니다. 

진행하고 싶은거는 총 합은 3시간 30분이 나와게 처리를 하고 싶습니다.

즉 10시부터 11시 30분간의 시간이 1번 더 더해지는 경우가 생기는거죠 ... 해당 부분의 차이 시간을 어떤식으로 진행해야 할지 머리가 안돌아가서 도움을 요청드립니다.

감사합니다. 무턱대고 도움글 남긴점 죄송합니다.

by jkson [2018.07.19 20:14:41]
--연속된 시간별 합계
with t as
(
select '090000' start_time, '113000' end_time from dual union all
select '100000' start_time, '120000' end_time from dual union all
select '140000' start_time, '143000' end_time from dual
)
select trunc(day * 24) hour, trunc(mod(day * 24,1) * 60) min
  from (
       select sum(to_date(max(end_time),'hh24miss') - to_date(min(start_time),'hh24miss')) over() day
         from (
              select start_time, end_time, sum(case when last_end_time >= start_time then 0 else 1 end) over(order by start_time) grp
                from (
                     select start_time, end_time, max(end_time) over(order by start_time rows between unbounded preceding and 1 preceding) last_end_time
                       from t
                     )
              )
         group by grp  
       )
 where rownum = 1

 


by jkson [2018.07.20 07:11:14]
-- 전체시간에서 끊긴 시간 빼기
with t as
(
select '090000' start_time, '113000' end_time from dual union all
select '100000' start_time, '120000' end_time from dual union all
select '140000' start_time, '143000' end_time from dual
)
select trunc(day * 24) hour, trunc(mod(day * 24,1) * 60) min
  from (
        select (to_date(max(end_time),'hh24miss') - to_date(min(start_time),'hh24miss')) 
             -  sum(greatest(to_date(start_time,'hh24miss') - to_date(last_end_time,'hh24miss'),0)) day
          from (
               select start_time, end_time, max(end_time) over(order by start_time rows between unbounded preceding and 1 preceding) last_end_time
                 from t
               )
       )

 


by 구기웅 [2018.07.22 17:09:52]

도움 감사합니다 !!!!!!! 좀더 케이스별 확인 후에 적용 하면 될거 같습니다 대단히 감사합니다 !


by 우리집아찌 [2018.07.20 11:15:40]
WITH T AS (
SELECT '090000' START_TIME, '113000' END_TIME FROM DUAL UNION ALL
SELECT '100000' START_TIME, '120000' END_TIME FROM DUAL UNION ALL
SELECT '140000' START_TIME, '143000' END_TIME FROM DUAL 
)

SELECT MIN(START_TIME) START_TIME
     , MAX(END_TIME) END_TIME
     , TRUNC(((TO_DATE( MAX(END_TIME) , 'HH24MISS' ) - TO_DATE( MIN(START_TIME) , 'HH24MISS' )) * 60 * 60 * 24) / ( 60 * 60 ) ) HOUR
     , MOD(((TO_DATE( MAX(END_TIME) , 'HH24MISS' ) - TO_DATE( MIN(START_TIME) , 'HH24MISS' )) * 60 * 60 * 24) , ( 60 * 60 )) / 60  MIN
     , MOD(((TO_DATE( MAX(END_TIME) , 'HH24MISS' ) - TO_DATE( MIN(START_TIME) , 'HH24MISS' )) * 60 * 60 * 24) , ( 60 )  ) SEC
  FROM (SELECT START_TIME
             , END_TIME 
             , SUM(FLAG) OVER(ORDER BY START_TIME) GRP
         FROM ( SELECT START_TIME 
                     , END_TIME 
                     , LAG(END_TIME) OVER(ORDER BY START_TIME) 
                     , CASE WHEN START_TIME <= LAG(END_TIME) OVER(ORDER BY START_TIME) THEN 0 ELSE 1 END FLAG
                 FROM T
             )
        )
GROUP BY GRP

-- 참고 : http://www.gurubee.net/article/52463

 


by jkson [2018.07.20 12:55:48]

lag로 하면 이 경우 문제가 발생해요.

WITH T AS (
SELECT '090000' START_TIME, '130000' END_TIME FROM DUAL UNION ALL
SELECT '090000' START_TIME, '093000' END_TIME FROM DUAL UNION ALL
SELECT '100000' START_TIME, '120000' END_TIME FROM DUAL UNION ALL
SELECT '140000' START_TIME, '143000' END_TIME FROM DUAL 
)

 


by 우리집아찌 [2018.07.20 13:00:16]

앗 그러네..

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입