쿼리 조언과 팁좀 부탁드립니다. 0 2 1,125

by 재래개 [SQL Query] [2015.08.04 10:05:40]


캡처.JPG (106,042Bytes)

WITH TEST AS
(
 SELECT  'A' LOG_CD, '2015-07-01 15:45:39.072' LOG_START_DATETIME, '2015-07-01 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'A' LOG_CD, '2015-07-01 15:45:00.072' LOG_START_DATETIME, '2015-07-01 15:45:01.072' LOG_END_DATETIME, '1' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'A' LOG_CD, '2015-07-03 15:45:39.072' LOG_START_DATETIME, '2015-07-03 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'B' LOG_CD, '2015-07-01 15:45:39.072' LOG_START_DATETIME, '2015-07-01 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'B' LOG_CD, '2015-07-01 15:45:00.072' LOG_START_DATETIME, '2015-07-01 15:45:01.072' LOG_END_DATETIME, '1' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'B' LOG_CD, '2015-07-03 15:45:39.072' LOG_START_DATETIME, '2015-07-03 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'C' LOG_CD, '2015-07-01 15:45:39.072' LOG_START_DATETIME, '2015-07-01 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'C' LOG_CD, '2015-07-01 15:45:00.072' LOG_START_DATETIME, '2015-07-01 15:45:01.072' LOG_END_DATETIME, '1' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'C' LOG_CD, '2015-07-03 15:45:39.072' LOG_START_DATETIME, '2015-07-03 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'D' LOG_CD, '2015-07-01 15:45:39.072' LOG_START_DATETIME, '2015-07-01 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'D' LOG_CD, '2015-07-01 15:45:00.072' LOG_START_DATETIME, '2015-07-01 15:45:01.072' LOG_END_DATETIME, '1' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'D' LOG_CD, '2015-07-03 15:45:39.072' LOG_START_DATETIME, '2015-07-03 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'E' LOG_CD, '2015-07-01 15:45:39.072' LOG_START_DATETIME, '2015-07-01 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'E' LOG_CD, '2015-07-01 15:45:00.072' LOG_START_DATETIME, '2015-07-01 15:45:01.072' LOG_END_DATETIME, '1' LOG_EXEC_TIME FROM DUAL
UNION ALL SELECT  'E' LOG_CD, '2015-07-03 15:45:39.072' LOG_START_DATETIME, '2015-07-03 15:45:49.072' LOG_END_DATETIME, '10' LOG_EXEC_TIME FROM DUAL
)

 

안녕하세요.

저런 로그 기록 테이블이 1개이고, LOG_CD는 종류가 대략 6개 이상있습니다.(대략 30만건 정도)  

제가 구하는것이

1. LOG_CD 명 과 LOG_CD 의 A, D, E에 대한 LOG_DATE

2. 해당 일에 최대로 걸린시간

3. 해당 일에 최소로 걸린시간

4. 해당 일에 2초 이상인 (LOG_EXEC_TIME(걸린시간)= LOG_START_DATETIME - LOG_END_DATETIME) 건수,

5. 해당 월에 대한 LOG_CD(A,D,E)의 평균걸린 시간

6. 해달 월에 2초이상인 것들이 총 % 인지

 

 

를 구하려고 쿼리를 만들었습니다.

 

WITH TABLE1 AS 
(
SELECT M1.*
  FROM (
        SELECT LOG_CD 
              ,SUBSTR (LOG_START_DATETIME, 1, 10)                                                     AS LOG_DATE
              ,LOG_EXEC_TIME
              ,AVG(LOG_EXEC_TIME) OVER(PARTITION BY LOG_CD)                                           AS AVG_C_GAN
              ,COUNT(LOG_EXEC_TIME) OVER(PARTITION BY LOG_CD)                                         AS TOTAL_CNT
              ,SUM(COUNT(DECODE(SIGN(LOG_EXEC_TIME-2), 1, 1))) OVER(PARTITION BY LOG_CD)              AS TOTAL_E_CHO_CNT 
          FROM TEST
         GROUP BY LOG_CD
                 ,LOG_START_DATETIME
                 ,LOG_EXEC_TIME         
        HAVING LOG_CD = 'A' 
            OR LOG_CD = 'D'
            OR LOG_CD = 'E'
         ORDER BY LOG_DATE ASC
       ) M1
)


  SELECT M2.*
    FROM (
          SELECT LOG_CD
                ,LOG_DATE
                ,MAX(LOG_EXEC_TIME) KEEP(DENSE_RANK FIRST ORDER BY LOG_EXEC_TIME DESC)              AS CHOI_DAE
                ,MIN(LOG_EXEC_TIME) KEEP(DENSE_RANK FIRST ORDER BY LOG_EXEC_TIME ASC)               AS CHOI_SO   
                ,COUNT(DECODE(SIGN(LOG_EXEC_TIME-2), 1, 1))                                         AS E_CHO_E_SANG   
                ,CASE ROW_NUMBER() OVER(PARTITION BY LOG_CD ORDER BY LOG_CD )  WHEN 1 THEN SUBSTR(LOG_DATE, 1, 7)|| ' '||LOG_CD
                                                                               WHEN 2 THEN SUBSTR(LOG_DATE, 1, 7)||'월 평균 시간    : '|| RTRIM(TO_CHAR(SUBSTR(AVG_C_GAN, 1, 4), 'FM9990D99'), '.') ||'초'
                                                                               WHEN 3 THEN SUBSTR(LOG_DATE, 1, 7)||'월 2초 이상%   :'  ||' '|| RTRIM(TO_CHAR(TRUNC(DECODE(ROW_NUMBER() OVER(PARTITION BY TOTAL_E_CHO_CNT ORDER BY LOG_DATE), 3, TOTAL_E_CHO_CNT) / DECODE(ROW_NUMBER() OVER(PARTITION BY TOTAL_CNT ORDER BY LOG_DATE), 3, TOTAL_CNT) *100, 2),'FM9990D99'),'.')||'%'
                                                                                END                 AS YO_YAK                      
           FROM TABLE1
            GROUP BY LOG_CD
                    ,LOG_DATE
                    ,AVG_C_GAN                  
                    ,TOTAL_CNT  
                    ,TOTAL_E_CHO_CNT
         )M2

 

 

 

결과 는 파일첨부한 것 처럼 나왔습니다.

추가로 제가 또 하고싶은거는 지금 테이블은 7월 뿐이지만 , 만약 한 테이블에 7월과 8월 9월이 있을경우에 모든(각각) 월에 대한 결과(7월달과 마찬가리고 최소,최대,건수,등등)를  출력하고싶을경우에는 어떻게 해야되는지.. 어떻게 해야되는지

도무지 감을 못잡겠습니다.. 

 

 

최대한 간략하게 짧게 만들려고 했는데 ... 오라클 SQL 을 공부한지 1~2달밖에 안되서...

부족한점이 많아 조언과 팁을 얻고자  이렇게 올려봅니다.  부탁드립니다.

 

 

 

 

 

 

 

 

by 마농 [2015.08.04 11:45:27]

1. KEEP 을 사용한 부분은 불필요한 부분이네요.
2. 월별 자료 뽑으려면 Partition By 에 월을 추가하면 됩니다.

SELECT log_cd
     , log_date
     , MAX(log_exec_time) choi_dae
     , MIN(log_exec_time) choi_so
     , COUNT(CASE WHEN log_exec_time > 2 THEN 1 END) e_cho_e_sang
     , DECODE(ROW_NUMBER() OVER(PARTITION BY log_cd, ym ORDER BY log_date)
       , 1, ym || ' ' || log_cd
       , 2, ym || ' 월 평균 시간    : ' || avg_tm
       , 3, ym || ' 월 2초 이상%    : ' || rat2
       ) yo_yak
  FROM (SELECT log_cd
             , SUBSTR(log_start_datetime, 1,  7) ym
             , SUBSTR(log_start_datetime, 1, 10) log_date
             , log_end_datetime
             , log_exec_time
             , RTRIM(TO_CHAR(
               AVG(log_exec_time) OVER(
               PARTITION BY log_cd, SUBSTR(log_start_datetime, 1,  7))
               , 'FM9990D99'), '.') || '초' avg_tm
             , RTRIM(TO_CHAR(
               COUNT(CASE WHEN log_exec_time > 2 THEN 1 END) OVER(
               PARTITION BY log_cd, SUBSTR(log_start_datetime, 1,  7))
               / COUNT(*) OVER(
               PARTITION BY log_cd, SUBSTR(log_start_datetime, 1,  7))
               * 100, 'FM990D99'), '.') || '%' rat2
          FROM test
         WHERE log_cd IN ('A','D','E')
        ) yo_yak
 GROUP BY log_cd, ym, log_date, avg_tm, rat2
;

 


by 재래개 [2015.08.11 15:45:29]

감사합니다.

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