0,0 → 1,69 |
select |
d.month, |
round(( |
(c2.base+c2.delta) - (c3.base+c3.delta) |
+ (d2.base+d2.delta) - (d3.base+d3.delta) |
) / 1024 / 1024) as count |
from |
( select 1 as month |
union select 2 as month |
union select 3 as month |
union select 4 as month |
union select 5 as month |
union select 6 as month |
union select 7 as month |
union select 8 as month |
union select 9 as month |
union select 10 as month |
union select 11 as month |
union select 12 as month |
) as d |
left join tcount c2 |
on c2.source = 'fw_in' |
and c2.host = 'mercury' |
and c2.stamp = ( |
select max(stamp) as max_stamp |
from tcount c1 |
where c1.source = 'fw_in' |
and c1.host = 'mercury' |
and c1.stamp >= date(d.month || '/1/2003') |
and c1.stamp < date(d.month || '/1/2003') + interval '1 month' |
group by date_trunc('month', c1.stamp) |
) |
left join tcount c3 |
on c3.source = 'fw_in' |
and c3.host = 'mercury' |
and c3.stamp = ( |
select max(stamp) as max_stamp |
from tcount c4 |
where c4.source = 'fw_in' |
and c4.host = 'mercury' |
and c4.stamp >= date(d.month || '/1/2003') - interval '1 month' |
and c4.stamp < date(d.month || '/1/2003') |
group by date_trunc('month', c4.stamp) |
) |
left join tcount d2 |
on d2.source = 'fw_out' |
and d2.host = 'mercury' |
and d2.stamp = ( |
select max(stamp) as max_stamp |
from tcount d1 |
where d1.source = 'fw_out' |
and d1.host = 'mercury' |
and d1.stamp >= date(d.month || '/1/2003') |
and d1.stamp < date(d.month || '/1/2003') + interval '1 month' |
group by date_trunc('month', d1.stamp) |
) |
left join tcount d3 |
on d3.source = 'fw_out' |
and d3.host = 'mercury' |
and d3.stamp = ( |
select max(stamp) as max_stamp |
from tcount d4 |
where d4.source = 'fw_out' |
and d4.host = 'mercury' |
and d4.stamp >= date(d.month || '/1/2003') - interval '1 month' |
and d4.stamp < date(d.month || '/1/2003') |
group by date_trunc('month', d4.stamp) |
) |
order by d.month; |