데이터베이스 질문과 답변
by 토리토리 [2018.01.16 08:47:51]
다음의 결과값을 날짜와 인덱스를 기준으로 grouping하여 시간을 합산하고 싶습니다.
hh:mm 형식의 값을 분으로 환산하여 합산하고 hh:mm으로 나타내고 싶은데 어떻게 할지 막막합니다 ㅠㅠ
날짜 인덱스 시간
2017-12-01 01 06:00
2017-12-01 02 10:00
2017-12-01 03 10:00
2017-12-01 04 10:00
2017-12-01 05 10:00
2017-12-01 06 10:00
2017-12-01 07 10:00
2017-12-01 08 01:12
2017-12-01 08 09:28
2017-12-01 09 09:45
2017-12-01 10 09:45
2017-12-01 11 06:00
2017-12-01 12 09:00
2017-12-01 13 10:00
[원하는 결과값]
날짜 인덱스 시간
2017-12-01 01 06:00
2017-12-01 02 10:00
2017-12-01 03 10:00
2017-12-01 04 10:00
2017-12-01 05 10:00
2017-12-01 06 10:00
2017-12-01 07 10:00
2017-12-01 08 10:40
2017-12-01 09 09:45
2017-12-01 10 09:45
2017-12-01 11 06:00
2017-12-01 12 09:00
2017-12-01 13 10:00
-- 마농님은 아니지만...
WITH T AS (
SELECT '2017-12-01' DT , '01' IDX , '06:00' HH FROM DUAL UNION ALL
SELECT '2017-12-01' , '02' , '10:00' FROM DUAL UNION ALL
SELECT '2017-12-01' , '03' , '10:00' FROM DUAL UNION ALL
SELECT '2017-12-01' , '04' , '10:00' FROM DUAL UNION ALL
SELECT '2017-12-01' , '05' , '10:00' FROM DUAL UNION ALL
SELECT '2017-12-01' , '06' , '10:00' FROM DUAL UNION ALL
SELECT '2017-12-01' , '07' , '10:00' FROM DUAL UNION ALL
SELECT '2017-12-01' , '08' , '01:12' FROM DUAL UNION ALL
SELECT '2017-12-01' , '08' , '09:28' FROM DUAL UNION ALL
SELECT '2017-12-01' , '09' , '09:45' FROM DUAL UNION ALL
SELECT '2017-12-01' , '10' , '09:45' FROM DUAL UNION ALL
SELECT '2017-12-01' , '11' , '06:00' FROM DUAL UNION ALL
SELECT '2017-12-01' , '12' , '09:00' FROM DUAL UNION ALL
SELECT '2017-12-01' , '13' , '10:00' FROM DUAL
)
SELECT DT , IDX
, LPAD(TRUNC(HH/60 ),2,'0')|| ':' || LPAD(MOD(HH,60),2,'0') HH
FROM ( SELECT DT , IDX
, SUM(TO_NUMBER(SUBSTR(HH,1,2))) * 60 + SUM(TO_NUMBER(SUBSTR(HH,4,2))) HH
FROM T
GROUP BY DT , IDX
)
ORDER BY DT , IDX
-- 저도 마농님이 아니지만...
SELECT DT, IDX
, TO_CHAR(HH+HH1, 'FM00')||':'||MI
FROM (SELECT DT , IDX
, SUM(SUBSTR(HH,1,2)) HH
, FLOOR(SUM(SUBSTR(HH,4,5))/60) HH1
, TO_CHAR(SUM(SUBSTR(HH,4,5)), 'FM00') MI
FROM T
GROUP BY DT , IDX
ORDER BY DT , IDX
)
;
) 버튼을 클릭하여 작성 하시면 됩니다.