Bolek Ziobrowski

Bolek Ziobrowski

08/23/2022, 7:39 AM
Hi @Haoyuan CHEN. The only window function available at the moment is row_number(). There's an open ticket you might want to upvote -> https://github.com/questdb/questdb/issues/1351 . As for workarounds - I need to have a look .
7:47 AM
This sql calculates moving avg on last 10 elements assuming fixed 1h delay between data points :
create table prices (
  ts timestamp,
  price double  
) timestamp(ts);

select ts+offst.v*3600000000, avg(price), count(*)
  from prices p1,
  ( select x-1 as v from long_sequence(10) ) offst
  where ts between '1970-01-01T01:00:00.000000Z' and '1973-01-01T01:00:00.000000Z'
  group by ts+offst.v*3600000000
You could use similar approach even if delay is not fixed or even use time-based window .

Haoyuan CHEN

08/23/2022, 8:47 AM
thanks a lot. will give it a shot