-- Determine the cumulative total, two hour moving average, and hourly
-- total of network flow broken down by hour of the day (example in
-- lecture notes, page 17)

set linesize 80;

SELECT HDesc, HStart, HEnd,
       SUM(SUM(NB)) OVER (ORDER BY HDesc ROWS UNBOUNDED PRECEDING) CSum,
       -- AVG(SUM(NB)) OVER (ORDER BY HDesc ROWS 1 PRECEDING) MWrong,
       SUM(SUM(NB)) OVER (ORDER BY HDesc ROWS 1 PRECEDING) MSum,
       SUM(COUNT(NB)) OVER (ORDER BY HDesc ROWS 1 PRECEDING) MCnt,
       SUM(NB)
FROM  Hours, Flows
WHERE Flows.T >= Hours.HStart and Flows.T <= Hours.HEnd
GROUP BY HDesc, HStart, HEnd;

-- quit;

--
-- Create table Hours
--
-- drop table Hours;
-- create table Hours (
--  HDesc 	number(8,2) not null,
--  HStart number(5) not null,
--  HEnd 	number(5) not null
-- );
-- INSERT INTO Hours VALUES( 1, 0, 59);
-- INSERT INTO Hours VALUES( 2, 6, 119);
-- INSERT INTO Hours VALUES( 3, 120, 179);

--
-- Create table Flows
--
-- drop table Flows;
-- create table Flows (
--  SIP number(5) not null,
--  DIP number(5) not null,
--  NB  number(5) not null,
--  T   number(5) not null
-- );
-- INSERT INTO Flows VALUES( 5, 29, 3, 30);
-- INSERT INTO Flows VALUES( 5, 7, 8, 45);
-- INSERT INTO Flows VALUES( 5, 29, 6, 110);
-- INSERT INTO Flows VALUES( 7, 29, 6, 161);
-- INSERT INTO Flows VALUES( 6, 29, 10, 170);