데이터베이스 질문과 답변

두 시간의 평균값을 구하고자 합니다. 0 4 3,966

by 손님 AVG 시간단위 평균 SELECT [2012.04.23 11:19:47]

안녕하세요 초보 개발자입니다.
반나절 고민하다가 안 되겠다 싶어 글 남깁니다. 

현재 제가 조회하고자 하는 데이터는 아래와 같습니다.

웹페이지에 사용자가 문의를 하면 등록일(DATE 타입) 데이터가 기록됩니다.
그리고, 관리자가 해당 문의에 대해서 답변을 하면, 답변일(DATE 타입) 데이터가 기록됩니다.

SELECT MAX(CASE
               WHEN CHANNEL = '00113' THEN
                'COOLOTS'
               WHEN CHANNEL = '00114' THEN
                'COOLOTS'
               ELSE
                'CHATON'
             END) GROUP_CODE,
         MAX(SUBSTR(TO_CHAR(REGDATE, 'YYYYMMDDhh24miss'), 1, 8)) REG_DATE,
         MAX(CHANNEL) AS CHANNEL,
         MAX(SECT) AS SECT,   
         COUNT(*) AS COUNT,
         SUM(CASE
               WHEN STATUS_CODE = 'AR' THEN
                1
               ELSE
                0
             END) AR_COUNT,
         SUM(CASE
               WHEN STATUS_CODE = 'NR' THEN
                1
               ELSE
                0
             END) NR_COUNT,
         AVG(REPLY_REGDATE - REGDATE) AS AVG_AR_TIME          <<<<- 이 부분인데 임의로 작성했습니다.
            FROM MAIL_CONSULT A
           GROUP BY SUBSTR(TO_CHAR(REGDATE, 'YYYYMMDDhh24miss'), 1, 8),
                    CHANNEL,
                    SECT

그렇게 기록된 데이터들을 위의 쿼리를 통해 평균응대시간을 구하려고 하는데, 잘 안 되네요.
제가 원하는 결과는 아래와 같습니다.
등록일 2012-04-12 오후 1:18:08
답변일 2012-04-12 오후 2:18:08
평균응대시간 1시간

그럼 고수님들의 가르침 기다리며 이만 줄이겠습니다.

오늘 하루도 행복하시기를....
by 손님 [2012.04.23 11:39:17] 
SELECT MAX(CASE WHEN CHANNEL IN ('00113','00114') THEN 'COOLOTS'
         ELSE 'CHATON') GROUP_CODE,
    MAX(TO_CHAR(REGDATE, 'YYYYMMDD')) REG_DATE,
    MAX(CHANNEL) AS CHANNEL,
    MAX(SECT) AS SECT,  
    COUNT(*) AS COUNT,
    COUNT(DECODE(STATUS_COD,'AR')) AR_COUNT,
    COUNT(DECODE(STATUS_COD,'NR')) NR_COUNT,
    FLOOR(AVG(REPLY_REGDATE - REGDATE) / 86400) AVG_AR_DAY
    TO_CHAR(TO_DATE(MOD(AVG(REPLY_REGDATE - REGDATE),86400),'SSSSS'),'HH24:MI:SS') AVG_AR_TIME
FROM MAIL_CONSULT A
GROUP BY TO_CHAR(REGDATE, 'YYYYMMDD'),
     CHANNEL,
     SECT
by 마농 [2012.04.23 12:49:05]
Group By 항목에 대해서 Max 를 붙이는 것은 괜한 짓을 하는거구요.
날짜 차이는 일수를 의미하며, 거기에 24를 곱하면 시간
다시 60을 곱하면 분, 또 60을 곱하면 초로 환산됩니다.
하지만 이렇게 환산된 초를 'sssss' 포멧으로 날짜로 변경할때
특정 시간대에서는 에러가 발생할 수 있습니다.
환산된 초가 정확하게 정수가 아니라서 발생하는 문제로
Round 를 이용해 정수로 환산해주는 과정이 필요합니다.
SUM(CASE ... THEN 1 ELSE 0 END) 는 else문을 빼고 count 하는 것이 좋습니다.
COUNT(CASE ... THEN 1 END) 
SELECT group_code
     , reg_date
     , channel
     , sect
     , count
     , ar_count
     , nr_count
     , FLOOR(sec / (24*60*60)) || ' ' ||
       TO_CHAR(TO_DATE(MOD(sec, 24*60*60), 'sssss'), 'hh24:mi:ss') AS avg_ar_time
 FROM (
        SELECT CASE WHEN channel IN ('00113', '00114') THEN 'COOLOTS'
                    ELSE 'CHATON' END AS group_code
             , TO_CHAR(regdate, 'yyyymmdd') AS reg_date
             , channel
             , sect
             , COUNT(*) AS count
             , COUNT(CASE WHEN status_code = 'AR' THEN 1 END) AS ar_count
             , COUNT(CASE WHEN status_code = 'NR' THEN 1 END) AS nr_count
             , ROUND(AVG(reply_regdate - regdate)*24*60*60) AS sec
          FROM mail_consult a
         GROUP BY TO_CHAR(regdate, 'yyyymmdd') reg_date
             , channel
             , sect
        )
;
by 손님 [2012.04.23 13:40:07]
글 남긴 초보 개발자입니다.

댓글 남겨주신 두 분 진심으로 감사드립니다.

많이 막막했었는데 두 분 덕분에 진행할 수 있을 거 같습니다.

다시 한 번 감사드립니다.

by 손님 [2012.04.25 14:09:29]

평균시간이 하루를 넘으면 오류나지 않을까요..?

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입