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

평균처리시간 구하기 질문입니다.

by 오라클초보 [SQL Query] [2013.11.01 16:12:03]

1) 원본 데이터

사원번호	사원명	업무유형1	업무유형2	처리상태	접수일시	처리일시
1	홍길동	A	A1	신청	2013-01-05 13:00:35
1	홍길동	A	A1	신청	2013-01-05 13:10:25
1	홍길동	A	A1	취소	2013-01-05 13:00:05
1	홍길동	A	A2	완료	2013-01-05 13:00:09	2013-01-05 13:30:45
1	홍길동	A	A2	완료	2013-01-05 13:15:10	2013-01-05 14:00:31
1	홍길동	A	A2	완료	2013-01-06 13:00:24	2013-01-07 09:57:27
2	김길동	A	A1	신청	2013-01-05 13:00:56
2	김길동	A	A1	신청	2013-01-05 13:00:34
2	김길동	A	A1	취소	2013-01-05 13:00:32
2	김길동	A	A2	완료	2013-02-01 13:00:55	2013-02-01 13:05:57
2	김길동	A	A2	완료	2013-03-05 13:00:23	2013-03-07 14:30:00
2	김길동	A	A2	완료	2013-01-06 13:00:00	2013-01-06 13:05:27
2	김길동	B	B1	완료	2013-01-07 13:00:10	2013-01-07 15:56:14
2	김길동	B	B1	신청	2013-01-05 13:00:07
2	김길동	B	B1	완료	2013-01-10 13:00:39	2013-01-10 14:00:12
2	김길동	B	B1	취소	2013-01-05 13:00:45
2	김길동	B	B1	취소	2013-01-05 13:00:20
2	김길동	B	B2	완료	2013-01-11 13:00:31	2013-01-11 13:13:57

2) 결과

사원번호	사원명	업무유형1	업무유형2	처리상태			평균처리시간
사원번호	사원명	업무유형1	업무유형2	신청	취소	완료	평균처리시간
1	홍길동	A	A1	2	1	0
1	홍길동	A	A2	0	0	3
2	김길동	A	A1	2	1	0
2	김길동	A	A2	0	0	3
2	김길동	B	B1	1	2	2
2	김길동	B	B2	0	0	1

2)의 결과와 같이 사원, 업무유형1, 업무유형2, 처리상태별 건수와 처리상태가 '완료'이면 아래와 같은 계산하여 평균처리시간을 구하고자 합니다.
평균처리시간은 '일:시:분:초'로 표현하고자 합니다.

1번. 처리시간=처리일시-접수일시
2번. 처리시간의 합 = 사원번호,업무유형1,업무유형2 그룹핑한 1번의 SUM
3번. 평균처리시간 = 2번 / 사원번호,업무유형1,업무유형2 그룹핑한 완료건수

도움 부탁 드립니다.
즐거운 하루 되세요.

by 우리집아찌 [2013.11.01 17:07:40]

WITH T ( 사원번호 ,사원명 ,업무유형1 ,업무유형2 ,처리상태,접수일시 ,처리일시 ) AS (
SELECT '1','홍길동','A','A1','신청','2013-01-05 13:00:35','' FROM DUAL UNION ALL
SELECT '1','홍길동','A','A1','신청','2013-01-05 13:10:25','' FROM DUAL UNION ALL
SELECT '1','홍길동','A','A1','취소','2013-01-05 13:00:05','' FROM DUAL UNION ALL
SELECT '1','홍길동','A','A2','완료','2013-01-05 13:00:09','2013-01-05 13:30:45' FROM DUAL UNION ALL
SELECT '1','홍길동','A','A2','완료','2013-01-05 13:15:10','2013-01-05 14:00:31' FROM DUAL UNION ALL
SELECT '1','홍길동','A','A2','완료','2013-01-06 13:00:24','2013-01-07 09:57:27' FROM DUAL UNION ALL
SELECT '2','김길동','A','A1','신청','2013-01-05 13:00:56','' FROM DUAL UNION ALL
SELECT '2','김길동','A','A1','신청','2013-01-05 13:00:34','' FROM DUAL UNION ALL
SELECT '2','김길동','A','A1','취소','2013-01-05 13:00:32','' FROM DUAL UNION ALL
SELECT '2','김길동','A','A2','완료','2013-02-01 13:00:55','2013-02-01 13:05:57' FROM DUAL UNION ALL
SELECT '2','김길동','A','A2','완료','2013-03-05 13:00:23','2013-03-07 14:30:00' FROM DUAL UNION ALL
SELECT '2','김길동','A','A2','완료','2013-01-06 13:00:00','2013-01-06 13:05:27' FROM DUAL UNION ALL
SELECT '2','김길동','B','B1','완료','2013-01-07 13:00:10','2013-01-07 15:56:14' FROM DUAL UNION ALL
SELECT '2','김길동','B','B1','신청','2013-01-05 13:00:07','' FROM DUAL UNION ALL
SELECT '2','김길동','B','B1','완료','2013-01-10 13:00:39','2013-01-10 14:00:12' FROM DUAL UNION ALL
SELECT '2','김길동','B','B1','취소','2013-01-05 13:00:45','' FROM DUAL UNION ALL
SELECT '2','김길동','B','B1','취소','2013-01-05 13:00:20','' FROM DUAL UNION ALL
SELECT '2','김길동','B','B2','완료','2012-01-11 13:00:31','2013-01-11 13:13:57' FROM DUAL 
)


SELECT a.* 
 , TRUNC(DD*24)||':'||TO_CHAR( TO_DATE('20000101000000' ,'YYYYMMDDHH24MISS') + DD ,'MI:SS' ) 
FROM
(SELECT 사원번호 
 , 사원명 
 , 업무유형1 
 , 업무유형2 
 , COUNT(DECODE(처리상태,'신청',1)) 신청 
 , COUNT(DECODE(처리상태,'취소',1)) 취소 
 , COUNT(DECODE(처리상태,'완료',1)) 완료 
 , AVG(DECODE(처리상태,'완료',TO_DATE(처리일시,'YYYY-MM-DD HH24:MI:SS') - TO_DATE(접수일시,'YYYY-MM-DD HH24:MI:SS'))) DD
 FROM T
 GROUP BY 사원번호 ,사원명 ,업무유형1 ,업무유형2 ) a
 ORDER BY 1 , 3 , 4

by 마농 [2013.11.01 17:45:50]

초로 환산해서 처리한 제 방법보다 초환산 없는 아찌님 방법이 더 좋아 보이네요.
일수정보 추가하고, 좀더 간단하게 살짝 보완 들어가 봅니다.
, NVL2(dd, FLOOR(dd) || ':', '') || TO_CHAR(TRUNC(sysdate) + dd, 'hh24:mi:ss') tm

by 우리집아찌 [2013.11.01 18:08:37]

FLOOR 함수 쓰셨는데 TRUNC함수 보다 어떤 잇점이 있는지요?

by 우리집아찌 [2013.11.01 18:35:26]

앗 일:시:분:초 네요 .. 시분초인줄 알았음...

by 마농 [2013.11.01 18:57:04]

Floor 는 정수를 리턴하고.
Trunc 는 지정한 자리수까지 자르죠.
Trunc 에서 3번째를 생략하거나 0을 쓴다면 결과는 같죠.

by 야신 [2013.11.02 11:47:15]

아찌님의 예제는 참 좋습니다. ^^

by 마농 [2013.11.01 17:09:54]

WITH t AS
(
SELECT empno, ename, gb1, gb2, st
     , TO_DATE(sdt, 'yyyy-mm-dd hh24:mi:ss') sdt
     , TO_DATE(edt, 'yyyy-mm-dd hh24:mi:ss') edt
FROM
(
SELECT 1 empno, '홍길동' ename, 'A' gb1, 'A1' gb2, '신청' st, '2013-01-05 13:00:35' sdt, '' edt FROM dual
UNION ALL SELECT 1, '홍길동', 'A', 'A1', '신청', '2013-01-05 13:10:25', '' FROM dual
UNION ALL SELECT 1, '홍길동', 'A', 'A1', '취소', '2013-01-05 13:00:05', '' FROM dual
UNION ALL SELECT 1, '홍길동', 'A', 'A2', '완료', '2013-01-05 13:00:09', '2013-01-05 13:30:45' FROM dual
UNION ALL SELECT 1, '홍길동', 'A', 'A2', '완료', '2013-01-05 13:15:10', '2013-01-05 14:00:31' FROM dual
UNION ALL SELECT 1, '홍길동', 'A', 'A2', '완료', '2013-01-06 13:00:24', '2013-01-07 09:57:27' FROM dual
UNION ALL SELECT 2, '김길동', 'A', 'A1', '신청', '2013-01-05 13:00:56', '' FROM dual
UNION ALL SELECT 2, '김길동', 'A', 'A1', '신청', '2013-01-05 13:00:34', '' FROM dual
UNION ALL SELECT 2, '김길동', 'A', 'A1', '취소', '2013-01-05 13:00:32', '' FROM dual
UNION ALL SELECT 2, '김길동', 'A', 'A2', '완료', '2013-02-01 13:00:55', '2013-02-01 13:05:57' FROM dual
UNION ALL SELECT 2, '김길동', 'A', 'A2', '완료', '2013-03-05 13:00:23', '2013-03-07 14:30:00' FROM dual
UNION ALL SELECT 2, '김길동', 'A', 'A2', '완료', '2013-01-06 13:00:00', '2013-01-06 13:05:27' FROM dual
UNION ALL SELECT 2, '김길동', 'B', 'B1', '완료', '2013-01-07 13:00:10', '2013-01-07 15:56:14' FROM dual
UNION ALL SELECT 2, '김길동', 'B', 'B1', '신청', '2013-01-05 13:00:07', '' FROM dual
UNION ALL SELECT 2, '김길동', 'B', 'B1', '완료', '2013-01-10 13:00:39', '2013-01-10 14:00:12' FROM dual
UNION ALL SELECT 2, '김길동', 'B', 'B1', '취소', '2013-01-05 13:00:45', '' FROM dual
UNION ALL SELECT 2, '김길동', 'B', 'B1', '취소', '2013-01-05 13:00:20', '' FROM dual
UNION ALL SELECT 2, '김길동', 'B', 'B2', '완료', '2013-01-11 13:00:31', '2013-01-11 13:13:57' FROM dual
)
)
SELECT empno, ename, gb1, gb2
     , cnt1, cnt2, cnt3
     , NVL2(sec, FLOOR(sec / (24*60*60)) || ':', '') ||
       TO_CHAR(TO_DATE(MOD(sec, 24*60*60), 'sssss'), 'hh24:mi:ss') tm
  FROM (SELECT empno, ename, gb1, gb2
             , COUNT(DECODE(st, '신청', 1)) cnt1
             , COUNT(DECODE(st, '취소', 1)) cnt2
             , COUNT(DECODE(st, '완료', 1)) cnt3
             , ROUND(AVG(edt - sdt)*24*60*60) sec
          FROM t
         GROUP BY empno, ename, gb1, gb2
        )
 ORDER BY empno, gb1, gb2
;

by 우리집아찌 [2013.11.01 17:17:51]

올리자마자 올리시네요.. 기다렸다 올리시는듯 ㅡㅡ+

by 오라클초보 [2013.11.11 12:50:11]

마농님, 우리집아찌님 답변 감사합니다.

댓글등록

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

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

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

데이터베이스 질문과 답변