本文对比了两种不同的SQL查询方法，一种被标记为低效，另一种则更为高效。通过对每个小时的记录进行计数并按主机分组，高效的方法显著减少了查询的复杂性和执行时间。
低效
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
扫一扫
597
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
