데이터베이스 질문과 답변

평균처리시간 구하기 질문입니다. 0 9 3,556

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 홍길동 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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입