-- use traffic;

-- explain

select @proto := protoID from proto where name = 'udp';
select @begin_date := '2009-04-01';
select @end_date := '2009-05-1';
select @dst_ip := inet_aton('192.168.21.115');

/* надо создавать подтаблицу в которой будут ip клиентов и
	ее так же применять в where */
select
	inet_ntoa(dstIp) as dstIp,
	count(*) as count,
	sum(psize) as psize
from
	traffic
where
	(srcPort = 0) &&
	(date >= @begin_date && date < @end_date)
group by dstIp
order by count desc;

select
	inet_ntoa(srcIp) as srcIp,
	count(*) as count
from
	traffic
where
	(dstIp = @dst_ip) && (srcPort = 0) &&
	(date >= @begin_date && date < @end_date)
group by srcIp;


select
	count(*), sum(psize)
from
	traffic
where
	(dstIp = @dst_ip) && (srcPort = 0) &&
	(date >= @begin_date && date < @end_date);
