Oracle查询最近几天每小时归档日志产生数量的脚本写法

SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS' ),1,5) Day ,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '00' ,1,0)) H00,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '01' ,1,0)) H01,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '02' ,1,0)) H02,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '03' ,1,0)) H03,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '04' ,1,0)) H04,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '05' ,1,0)) H05,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '06' ,1,0)) H06,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '07' ,1,0)) H07,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '08' ,1,0)) H08,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '09' ,1,0)) H09,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '10' ,1,0)) H10,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '11' ,1,0)) H11,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '12' ,1,0)) H12,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '13' ,1,0)) H13,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '14' ,1,0)) H14,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '15' ,1,0)) H15,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '16' ,1,0)) H16,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '17' ,1,0)) H17,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '18' ,1,0)) H18,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '19' ,1,0)) H19,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '20' ,1,0)) H20,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '21' ,1,0)) H21,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '22' ,1,0)) H22,

     SUM (DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS' ),10,2), '23' ,1,0)) H23,

     COUNT (*) TOTAL

FROM v$log_history a

WHERE first_time>=to_char(sysdate-10)

GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS' ),1,5)

ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS' ),1,5) DESC ;