Is it possible to calculate a moving average / EMA / windowed aggregate over a time series in RavenDb 5+?

user_310487 0
Viewed 342

RavenDb 5+ adds support for timeseries storage against documents. Included in this support is indexing, although in order to create storage and indexing performance benefits, indexing is done in batches of TimeSeriesSegments.

RavenDb includes support for calculating basic Count/Avg/Sum aggregates on these TimeSeries in totality. However, I can't find a way to calculate a moving average, over a window.

For example, I might have 50 days of values, one value per day. Using the built-in Avg, I can trivially index the average value across all 50 days. However, I'd like to create an index field which contains the moving average over a window of, say, 5 days. The first X-1 days' values would therefore be null/blank, and from that point onwards the average would be calculated over the preceding X days.

I can't find a trivial way to accomplish this in RavenDb's map/reduce indexing strategy, but I'm hoping I'm missing some clever way to (for example) run a query from within the Map operation in order to query for the previous values even though they might not fall within the current TimeSeriesSegment.

If it is possible, I'd also like to be able to take it further and calculate a compound of several of these moving averages - for example, the exponential moving average of a value over time.

This is an ideal operation for an index, since the data doesn't change after being written, but I don't know if there is windowing / cursor-style support when defining a RavenDb index.

I could achieve this using triggers etc in a SQL-like store instead of RavenDb, but if possible I'd prefer not to get into having some code stored in sprocs and some within my C# codebase.

TYIA!

time-series ravendb moving-average mapreduce indexing
1 Answers

Not sure if there is a nicer way, but a map-reduce index should work.

Map:

from segment in timeseries.Companies.StockPrices
from entry in segment.Entries
let window = Enumerable.Range(0,5).Cast<object>()
from i in window
select new {
    Date = entry.Timestamp.AddDays((int)i),
    Value = entry.Values[0]*((double)1/(i+1)),
    Count = 1
}

Reduce:

from r in results
group r by r.Date into g
select new 
{
    Date = g.Key,
    Value = g.Average(x=>x.Value),
    Count = g.Sum(x=>x.Count)
}
user_71101950
Related