꿈꾸는 개발자, DBA 커뮤니티 구루비

sql 문의 드립니다.

by zzhozang [2015.12.22 12:06:47]

안녕하세요 쿼리 작성을 어떻게 해야 좋을지 문의드립니다.

조회 기간에 대한 비가동 실적을 조별로 시간 합계를 내야합니다.

조별기준은

06:30~ 14:30 1조
14:30~ 22:30 2조
22:30~ 06:30 3조

이고

비가동실적이 아래와 같을때
1. 2015/12/20 07:00:00 ~ 2015/12/20 09:00:00
2. 2015/12/21 15:00:00 ~ 2015/12/21 23:50:00
3. 2015/12/22 08:00:00 ~ 2015/12/25 17:00:00

일자 1조    2조    3조
20    120
21              450    80
22    90      480    480
23    480   480   480
24    480   480   480
25   480   150   0
TOT 2070 3240 1520

위처럼 합계 계산되어 최종적으로 tot 에 해당하는 값을 결과값으로 가져올 방법이 있을지 문의드립니다.

by jkson [2015.12.22 17:52:16]

with t as
(select to_date('2015-12-20 07:00:00','yyyy-mm-dd hh24:mi:ss') stime, 
        to_date('2015-12-20 09:00:00','yyyy-mm-dd hh24:mi:ss') etime
   from dual 
 union all
 select to_date('2015-12-21 15:00:00','yyyy-mm-dd hh24:mi:ss') stime, 
        to_date('2015-12-21 23:50:00','yyyy-mm-dd hh24:mi:ss') etime
   from dual
 union all
 select to_date('2015-12-22 08:00:00','yyyy-mm-dd hh24:mi:ss') stime, 
        to_date('2015-12-25 17:00:00','yyyy-mm-dd hh24:mi:ss') etime
   from dual 
)
SELECT stime
     , etime
     , SUM(ROUND (NVL ((etime1 - stime1) * 24 * 60, 0))) time1
     , SUM(ROUND (NVL ((etime2 - stime2) * 24 * 60, 0))) time2
     , SUM(ROUND (NVL ((etime3 - stime3) * 24 * 60, 0) + NVL ((etime31 - stime31) * 24 * 60, 0))) time3
  FROM (SELECT stime
             , etime
             , CASE
                  WHEN stime >= TRUNC (stime) + 6.5 / 24
                  AND stime < TRUNC (stime) + 14.5 / 24
                     THEN stime
                  WHEN stime < TRUNC (stime) + 6.5 / 24
                     THEN TRUNC (stime) + 6.5 / 24
               END stime1
             , CASE
                  WHEN etime >= TRUNC (etime) + 6.5 / 24
                  AND etime < TRUNC (etime) + 14.5 / 24
                     THEN etime
                  WHEN etime > TRUNC (etime) + 14.5 / 24
                     THEN TRUNC (etime) + 14.5 / 24 - 0.00001
               END etime1
             , CASE
                  WHEN stime >= TRUNC (stime) + 14.5 / 24
                  AND stime < TRUNC (stime) + 22.5 / 24
                     THEN stime
                  WHEN stime < TRUNC (stime) + 14.5 / 24
                     THEN TRUNC (stime) + 14.5 / 24
               END stime2
             , CASE
                  WHEN etime >= TRUNC (etime) + 14.5 / 24
                  AND etime < TRUNC (etime) + 22.5 / 24
                     THEN etime
                  WHEN etime > TRUNC (etime) + 22.5 / 24
                     THEN TRUNC (etime) + 22.5 / 24 - 0.00001
               END etime2
             , CASE
                  WHEN stime >= TRUNC (stime) + 22.5 / 24
                  AND stime < TRUNC (stime) + 1
                     THEN stime
                  WHEN stime < TRUNC (stime) + 22.5 / 24
                     THEN TRUNC (stime) + 22.5 / 24
               END stime3
             , CASE
                  WHEN etime >= TRUNC (etime) + 22.5 / 24
                  AND etime < TRUNC (etime) + 1
                     THEN etime
                  ELSE NULL
               END etime3
             , CASE
                  WHEN stime >= TRUNC (stime)
                  AND stime < TRUNC (stime) + 06.5 / 24
                     THEN stime
                  ELSE NULL
               END stime31
             , CASE
                  WHEN etime >= TRUNC (etime)
                  AND etime < TRUNC (etime) + 06.5 / 24
                     THEN etime
                  WHEN etime > TRUNC (etime) + 6.5 / 24
                     THEN TRUNC (etime) + 6.5 / 24 - 0.00001
               END etime31
          FROM (SELECT DECODE (TRUNC (stime), TRUNC (etime), stime, DECODE (lv, 1, stime, TRUNC (stime))) + lv - 1 stime
                     , DECODE (TRUNC (stime)
                             , TRUNC (etime), etime
                             , DECODE (TRUNC (etime), TRUNC (stime) + lv - 1, etime, TRUNC (stime) + lv - 1 + 0.99999)
                              ) etime
                  FROM t
                     , (SELECT     LEVEL lv
                              FROM DUAL
                        CONNECT BY LEVEL < 3600)
                 WHERE lv <= TRUNC (etime) - TRUNC (stime) + 1))
                 GROUP BY ROLLUP ((stime,etime))

더 간단하게 할 수 있을 것 같은데.. 머리가 안 좋으니 노가다네요;;

결과가 좀 다른데

22일 같은 경우 1조가 아침 8시부터 오후 14:30분까지 390분

3조가 오후 22시30분부터 24시까지 90분 이렇게 되어야 하는 것 아닌가요?

25일도 마찬가지구요. 확인해보세요.

by 마농 [2015.12.23 16:01:11]

새벽 0시부터 6시반 까지의 자료는 당일이 아닌 전일 자료로 집계되는 모양입니다.

by 마농 [2015.12.23 16:08:25]

WITH t AS
(
SELECT 1 idx, '2015/12/20 07:00:00' stm, '2015/12/20 09:00:00' etm FROM dual
UNION ALL SELECT 2, '2015/12/21 15:00:00', '2015/12/21 23:50:00' FROM dual
UNION ALL SELECT 3, '2015/12/22 08:00:00', '2015/12/25 17:00:00' FROM dual
)
SELECT TO_CHAR(s, 'yyyy/mm/dd') dt
     , ROUND(SUM(DECODE(lv2, 1, e - s)) *24*60) t1
     , ROUND(SUM(DECODE(lv2, 2, e - s)) *24*60) t2
     , ROUND(SUM(DECODE(lv2, 3, e - s)) *24*60) t3
  FROM (SELECT lv2
             , GREATEST(stm, TRUNC(stm) + lv1 - 1 + 8/24*(lv2-1)) s
             , LEAST   (etm, TRUNC(stm) + lv1 - 1 + 8/24*(lv2  )) e
          FROM (SELECT idx
                     , TO_DATE(stm, 'yyyy/mm/dd hh24:mi:ss') - 6.5/24 stm
                     , TO_DATE(etm, 'yyyy/mm/dd hh24:mi:ss') - 6.5/24 etm
                  FROM t
                )
            , (SELECT LEVEL lv1 FROM dual CONNECT BY LEVEL <= 99)
            , (SELECT LEVEL lv2 FROM dual CONNECT BY LEVEL <= 3)
         WHERE lv1 <= TRUNC(etm) - TRUNC(stm) + 1
        )
 WHERE s < e
 GROUP BY ROLLUP(TO_CHAR(s, 'yyyy/mm/dd'))
 ORDER BY dt
;

by jkson [2015.12.23 16:56:34]

우아~~ 우아~~

이건 진짜 쿼리가 문제가 아니라

생각의 전환이!!

우아~~ 뇌섹남 인정이요!!

닮고 싶어요ㅠ

by zzhozang [2015.12.24 09:57:43]

정말 감사합니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변