低效
select host,max(h0), max(h1), max(h2), max(h3), max(h4) from (select
tmp.host,
(case tmp.hour when '00' then if(tmp.count is null,0,1) end) h0,
(case tmp.hour when '01' then tmp.count end) h1,
(case tmp.hour when '02' then tmp.count end) h2,
(case tmp.hour when '03' then tmp.count end) h3,
(case tmp.hour when '04' then tmp.count end) h4
from (select host,substr(ymdhms,9,2) as hour,
count(*) as count
from cndnslogsec where dt = '20131220' and node='njtel' and host ='t.cn' and ymdhms is not null
group by host,substr(ymdhms,9,2)
) tmp limit 10) table1 group by host;
tmp.host,
(case tmp.hour when '00' then if(tmp.count is null,0,1) end) h0,
(case tmp.hour when '01' then tmp.count end) h1,
(case tmp.hour when '02' then tmp.count end) h2,
(case tmp.hour when '03' then tmp.count end) h3,
(case tmp.hour when '04' then tmp.count end) h4
from (select host,substr(ymdhms,9,2) as hour,
count(*) as count
from cndnslogsec where dt = '20131220' and node='njtel' and host ='t.cn' and ymdhms is not null
group by host,substr(ymdhms,9,2)
) tmp limit 10) table1 group by host;
高效:
select host,
count(*) as h,
sum (case substr(ymdhms,9,2) when '00' then 1 else 0 end) as h0,
sum (case substr(ymdhms,9,2) when '01' then 1 else 0 end) as h1,
sum (case substr(ymdhms,9,2) when '02' then 1 else 0 end) as h2,
sum (case substr(ymdhms,9,2) when '03' then 1 else 0 end) as h3,
sum (case substr(ymdhms,9,2) when '04' then 1 else 0 end) as h4,
sum (case substr(ymdhms,9,2) when '05' then 1 else 0 end) as h5,
sum (case substr(ymdhms,9,2) when '06' then 1 else 0 end) as h6,
sum (case substr(ymdhms,9,2) when '07' then 1 else 0 end) as h7,
sum (case substr(ymdhms,9,2) when '08' then 1 else 0 end) as h8,
sum (case substr(ymdhms,9,2) when '09' then 1 else 0 end) as h9,
sum (case substr(ymdhms,9,2) when '10' then 1 else 0 end) as h10,
sum (case substr(ymdhms,9,2) when '11' then 1 else 0 end) as h11,
sum (case substr(ymdhms,9,2) when '12' then 1 else 0 end) as h12,
sum (case substr(ymdhms,9,2) when '13' then 1 else 0 end) as h13,
sum (case substr(ymdhms,9,2) when '14' then 1 else 0 end) as h14,
sum (case substr(ymdhms,9,2) when '15' then 1 else 0 end) as h15,
sum (case substr(ymdhms,9,2) when '16' then 1 else 0 end) as h16,
sum (case substr(ymdhms,9,2) when '17' then 1 else 0 end) as h17,
sum (case substr(ymdhms,9,2) when '18' then 1 else 0 end) as h18,
sum (case substr(ymdhms,9,2) when '19' then 1 else 0 end) as h19,
sum (case substr(ymdhms,9,2) when '20' then 1 else 0 end) as h20,
sum (case substr(ymdhms,9,2) when '21' then 1 else 0 end) as h21,
sum (case substr(ymdhms,9,2) when '22' then 1 else 0 end) as h22,
sum (case substr(ymdhms,9,2) when '23' then 1 else 0 end) as h23
from cndnslogsec where dt = '20131220' and node='njtel' and ymdhms is not null
group by host