20160111-log switch 統計SQL

tolilong發表於2016-01-11
SQL

日誌切換統計SQL,參考論壇網友，

select trunc(first_time) "Date",to_char(first_time,'Dy') "day",count(1) "Total",
sum(decode(to_char(first_time,'HH24'),'00',1,0)) H0,
sum(decode(to_char(first_time,'HH24'),'01',1,0)) H1,
sum(decode(to_char(first_time,'HH24'),'02',1,0)) H2,
sum(decode(to_char(first_time,'HH24'),'03',1,0)) H3,
sum(decode(to_char(first_time,'HH24'),'04',1,0)) H4,
sum(decode(to_char(first_time,'HH24'),'05',1,0)) H5,
sum(decode(to_char(first_time,'HH24'),'06',1,0)) H6,
sum(decode(to_char(first_time,'HH24'),'07',1,0)) H7,
sum(decode(to_char(first_time,'HH24'),'08',1,0)) H8,
sum(decode(to_char(first_time,'HH24'),'09',1,0)) H9,
sum(decode(to_char(first_time,'HH24'),'10',1,0)) H10,
sum(decode(to_char(first_time,'HH24'),'11',1,0)) H11,
sum(decode(to_char(first_time,'HH24'),'12',1,0)) H12,
sum(decode(to_char(first_time,'HH24'),'13',1,0)) H13,
sum(decode(to_char(first_time,'HH24'),'14',1,0)) H14,
sum(decode(to_char(first_time,'HH24'),'15',1,0)) H15,
sum(decode(to_char(first_time,'HH24'),'16',1,0)) H16,
sum(decode(to_char(first_time,'HH24'),'17',1,0)) H17,
sum(decode(to_char(first_time,'HH24'),'18',1,0)) H18,
sum(decode(to_char(first_time,'HH24'),'19',1,0)) H19,
sum(decode(to_char(first_time,'HH24'),'20',1,0)) H20,
sum(decode(to_char(first_time,'HH24'),'21',1,0)) H21,
sum(decode(to_char(first_time,'HH24'),'22',1,0)) H22,
sum(decode(to_char(first_time,'HH24'),'23',1,0)) H23,round(count(1)/24,2) "Avg"
from gv$log_history
where first_time>sysdate-10 group by trunc(first_time),to_char(first_time,'Dy')
order by 1 desc;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24237320/viewspace-1974800/，如需轉載，請註明出處，否則將追究法律責任。

相關文章