NEO4j Moving averages in cypher

Question

I have a day node and another node I want to average for every n days. I want there to be a moving window for every 7 days. And, I want to return every day. So, every row return will be the average of that day and the previous 7 days. I can't show code because the data is not mine.

(thing)-[] ->(day)

....

RETURN thing.metric as sevenDayAvg, day

Answer 1

Does something like this work for you?

MATCH (t1:Thing)-->(d1:Day)
MATCH (t2:Thing)-->(d2:Day)
WHERE 0 <= duration.inDays(d2.date, d1.date).days < 7
WITH d1.date as day, collect(t2.value) as values
RETURN apoc.coll.avg(values) as sevenDayAvg, day

If you don't have the apoc library installed, this slightly longer version should work.

MATCH (t1:Thing)-->(d1:Day)
MATCH (t2:Thing)-->(d2:Day)
WHERE 0 <= duration.inDays(d2.date, d1.date).days < 7
WITH d1.date as day, collect(t2.value) as values
RETURN reduce(s = 0 , v in values | s + v)/size(values) as sevenDayAvg, day