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
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