| 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; |