Moving Average - How to?



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


In the current release you need to do joins ( and calculate your self or do in application.

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


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,,
        (select avg(tonumber( 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


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

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


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