/* Query: Generate the stream of packets whose length is greater than twice the
   average packet length over the last 1 hour */

/* WITHOUT THE WINDOW CONSTRUCT*/
STREAM Packets(Pid char(6), Leng int, Minutes int);
AGGREGATE wavg(Pid char(6), Length int, Timein int): int
{
  TABLE onehour(sl INT, time  INT) MEMORY;
  TABLE temp(cnt INT, sm INT) MEMORY;
  INITIALIZE:{ 
	INSERT INTO temp VALUES(1, Length);
        INSERT INTO onehour VALUES(Length, Timein);
  }
  ITERATE:{
	UPDATE temp SET cnt= cnt+1, sm= sm + Length;
        UPDATE temp SET 
           cnt = cnt - (SELECT count(sl) FROM onehour 
                        WHERE time + 60 < Timein ),
           sm = sm - (SELECT sum(sl) FROM onehour 
                      WHERE time + 60 < Timein );
        DELETE FROM onehour WHERE time + 60 < Timein;
        INSERT INTO return SELECT Timein FROM temp
          WHERE sm/cnt< Length/2  
  }
}

LOAD FROM 'packets.dat' INTO Packets;
SELECT  wavg(P.Pid, P.Leng, P.Minutes) 
FROM Packets AS P;

/* USING THE  WINDOW CONSTRUCT on a STREAM*/
STREAM Packets(Pid char(6), Leng int, Minutes int);
SELECT Pid
FROM Packets 
WHERE length >
  (SELECT 2 * Avg(length) FROM Packets [RANGE 60 Minutes])
*/
    
/* we can also specify a window UDA that computes the average and return the
calls more than twice in the lenght */