-- use traffic;

-- explain

select @proto := protoID from proto where name = 'udp';

explain

select
        out.srcIp as client,
        out.udpOut as udpOut,
        in.udpIn as udpIn
from
        (
                select srcIp, sum(psize) as udpOut
                from traffic
                where (protoId=@proto) && (srcPort=0) &&
					(date >= "2009-07-01" && date < "2009-08-01")
                group by srcIp
        ) as `out` join
        (
                select dstIp, sum(psize) as udpIn
                from traffic
                where (protoId=@proto) && (dstPort=0) &&
					(date >= "2009-07-01" && date < "2009-08-01")
                group by dstIp
        ) as `in` on out.srcIp=in.dstIp

union all

select
        out.srcIp as client,
        out.udpOut as udpOut,
        ifnull(in.udpIn,0) as udpIn
from
        (
                select srcIp, sum(psize) as udpOut
                from traffic
                where (protoId=@proto) && (srcPort = 0) &&
					(date >= "2009-07-01" && date < "2009-08-01")
                group by srcIp
        ) as `out` left outer join
        (
                select dstIp, sum(psize) as udpIn
                from traffic
                where (protoId=@proto) && (dstPort = 0) &&
					(date >= "2009-07-01" && date < "2009-08-01")
                group by dstIp
        ) as `in` on out.srcIp=in.dstIp
where in.dstIp is null

union all

select
        in.dstIp as client,
        ifnull(out.udpOut,0) as udpOut,
        in.udpIn as udpIn
from
        (
                select srcIp, sum(psize) as udpOut
                from traffic
                where (protoId=@proto) && (srcPort = 0) &&
					(date >= "2009-07-01" && date < "2009-08-01")
                group by srcIp
        ) as `out` right outer join
        (
                select dstIp, sum(psize) as udpIn
                from traffic
                where (protoId=@proto) && (dstPort = 0) &&
					(date >= "2009-07-01" && date < "2009-08-01")
                group by dstIp
        ) as `in` on out.srcIp=in.dstIp
where out.srcIp is null;
