0,0 → 1,77 |
select |
d.day, |
round((c2.value - c3.value + d2.value - d3.value) / 1024 / 1024) as count |
from |
( select 1 as day |
union select 2 as day |
union select 3 as day |
union select 4 as day |
union select 5 as day |
union select 6 as day |
union select 7 as day |
union select 8 as day |
union select 9 as day |
union select 10 as day |
union select 11 as day |
union select 12 as day |
union select 13 as day |
union select 14 as day |
union select 15 as day |
union select 16 as day |
union select 17 as day |
union select 18 as day |
union select 19 as day |
union select 20 as day |
union select 21 as day |
union select 22 as day |
union select 23 as day |
union select 24 as day |
union select 25 as day |
union select 26 as day |
union select 27 as day |
union select 28 as day |
union select 29 as day |
union select 30 as day |
union select 31 as day |
) as d |
left join tcount c2 |
on c2.source = 'tun0_in' |
and c2.stamp = ( |
select max(stamp) as max_stamp |
from tcount c1 |
where c1.source = 'tun0_in' |
and c1.stamp >= date(d.day || ' Jan 2004') |
and c1.stamp < date(d.day || ' Jan 2004') + interval '1 day' |
group by date_trunc('day', c1.stamp) |
) |
left join tcount c3 |
on c3.source = 'tun0_in' |
and c3.stamp = ( |
select max(stamp) as max_stamp |
from tcount c4 |
where c4.source = 'tun0_in' |
and c4.stamp >= date(d.day || ' Jan 2004') - interval '1 day' |
and c4.stamp < date(d.day || ' Jan 2004') |
group by date_trunc('day', c4.stamp) |
) |
left join tcount d2 |
on d2.source = 'tun0_out' |
and d2.stamp = ( |
select max(stamp) as max_stamp |
from tcount d1 |
where d1.source = 'tun0_out' |
and d1.stamp >= date(d.day || ' Jan 2004') |
and d1.stamp < date(d.day || ' Jan 2004') + interval '1 day' |
group by date_trunc('day', d1.stamp) |
) |
left join tcount d3 |
on d3.source = 'tun0_out' |
and d3.stamp = ( |
select max(stamp) as max_stamp |
from tcount d4 |
where d4.source = 'tun0_out' |
and d4.stamp >= date(d.day || ' Jan 2004') - interval '1 day' |
and d4.stamp < date(d.day || ' Jan 2004') |
group by date_trunc('day', d4.stamp) |
) |
order by d.day; |