-1

I have a stream of market data events with (Price, Trade Time) properties.

I want to calculate the simple average for past time windows for every new market data event. Simple average = Sum of trade prices / # of events

However, the tricky part is that I want to calculate this for multiple sub-timewindows from the current event time. So, say the simple average for [t-0 min, t-2 min] , [t-2 min, t-4 min], [t-4 min, t-6 min], ...

These time windows would be recalculated for each new event.

Right now I just use multiple streams and sum up the prices and events over [t-0 min, t-2 min], [t-0 min, t-4 min], t-0 min, t-6 min], ... and find their individual simple averages through subtraction. There must be a better way of doing this, possible using only one or two streams?

yuu
  • 1

1 Answers1

0

For anyone trying to solve the same problem:

Create five staggered time windows where the events leaving one windows enters the next...

            create window W1.win:time(5 min) as select * from yourSource                
            create window W2.win:time(5 min) as select * from yourSource                 
            create window W3.win:time(5 min) as select * from yourSource
            create window W4.win:time(5 min) as select * from yourSource
            create window W5.win:time(5 min) as select * from yourSource

            insert into W1 select * from yourSource
            insert rstream into W2 select rstream * from W1
            insert rstream into W3 select rstream * from W2
            insert rstream into W4 select rstream * from W3
            insert rstream into W5 select rstream * from W4

            select sum(price)/count(*) as sma, symbol from W1
            GROUP BY symbol
            select sum(price)/count(*) as sma, symbol from W2
            GROUP BY symbol
            select sum(price)/count(*) as sma, symbol from W3
            GROUP BY symbol
       select sum(price)/count(*) as sma, symbol from W4
            GROUP BY symbol
            select sum(price)/count(*) as sma, symbol from W5
            GROUP BY symbol
yuu
  • 1