Calculate average cumulative aggregate

CrateDB SQL
1

Hello to all,

I try to cumulative an average.
this is the query for average

select date_trunc('hour', timestamp) as time, avg(value) FROM logs where timestamp BETWEEN 1595844260934 AND 1595930660934 AND sensorid = 5 group by time order by time

now I try to cumulative the average

select date_trunc('hour', timestamp) as time, SUM(avg(value)) over (order by 'time') FROM logs where timestamp BETWEEN 1595844260934 AND 1595930660934 AND sensorid = 5 group by time order by time

but I get the sum in every row and not cumulated

here is an example where they sum a aggregate count function with postgres

maybe someone can point me the right direction

2

but I get the sum in every row and not cumulated

this is expected, as the window you defined, is over all rows.

order by 'time'

is no correct reference to the time column. This probably should throw an exception though.