Moving Average - How to?


#1

Hi,

how to calculate the 3-day moving average for this (no window functions :pensive:):
(sample data)
{
datetime: " 2018-01-01 08:00:00"
data: {
speed: 10
}
},
datetime: " 2018-01-01 09:00:00"
data: {
speed: 15
}
},
datetime: " 2018-01-02 08:00:00"
data: {
speed: 8
}
}


#2

In the current release you need to do joins (https://stackoverflow.com/questions/10624902/sql-moving-average) and calculate your self or do in application.

In the next release you will have window functions. cc @keshav_m

https://blog.couchbase.com/on-par-with-window-functions-in-n1ql/
https://blog.couchbase.com/json-to-insights-fast-and-easy/
https://blog.couchbase.com/json-insights-analyze-usa-healthcare-data/


#3

I’ve tried with joins and subqueries but It won’t run!
I get different error messages all the time - while on SQL it is working fine!

the closest I got is this (but not working):

select t1.datetime,t1.data.speed,
        (select avg(tonumber(t2.data.speed)) as avg
         from `Bucket` t2
         USE KEYS t1
         where t2.datetime between DATE_ADD_STR(t1.datetime, -3, 'day' ) and t1.datetime )[0].avg as avg
from `Bucket` t1

#4

OK. I think I got it. but it is really slow:

SELECT t1.datetime , avg(tonumber(t2.data.speed))
FROM  mybuckey  t1 join mybucket t2
ON t2.datetime between DATE_ADD_STR( t1.datetime ,-3, 'day') and t1.datetime
WHERE  t2.data.speed IS NOT MISSING
group by t1.datetime
Limit 10

#5

Yes… That’s the reason we’re adding window functions in the upcoming release. Stay tuned for a beta release sometime in 2Q2019…