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달밖에 안되서...

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