데이터베이스 질문과 답변
| 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]
앗 그러네..
) 버튼을 클릭하여 작성 하시면 됩니다.