CODEWITHSUNDEEP
sqldatabasetime-seriesquestdb
Javier Ramirez
Javier Ramirez

Reputation: 4032

Get the running minimum for 5 rows before on sampled data using QuestDB

I have a QuestDB table in which I get a few records every second. I am aggregating in 1 minute intervals, as in

select timestamp, symbol, avg(price) as price from trades 
where timestamp in yesterday() and side='buy' sample by 1m

Now I would like to get, for every sampled row, which was the minimum value for the rows with the same symbol within the past 5 minutes.

I tried using window functions, but QuestDB doesn't support yet the min function as a window function. Otherwise I could do something like:

select timestamp, symbol, avg(price) as price,
min(avg(price)) over (partition by symbol range 5 minutes preceding) as min_price from trades 
where timestamp in yesterday() and side='buy' sample by 1m

Are there any workarounds for this, or do I need to implement client-side?

Upvotes: 0

Views: 30

Answers (1)

Javier Ramirez
Javier Ramirez

Reputation: 4032

At the time of this writing, the min function is one of the requested ones via github issue, so maybe it will get implemented at some point. In the meantime, there is a workaround I can think of. Please note the workaround is not perfect, but it might work for some use cases. The steps are:

  • Sample by 1m, as in the original query, but using FILL(null) so we have a dense result in which every minute we get exactly 1 row per different symbol.
  • Number the sampled rows, so now I can do an integer division of the position by 5, and will classify rows within 5 minute blocks
  • Now we can use a window function sorting by price, and partitioning by both symbol and 5-minute-block, so for each 5 minutes we get a minimum.

Note that with this solution, each row gets assigned to a single 5-minute block and the minimum is found for each block, it is not a truly moving minimum, as it does not take in consideration the five rows before me, but the rows within the same 5 minute block. If that's fine for your use case, then perfect, otherwise there might be some other more appropriate solution.

My sql attempt to solve this is:

with sampled as 
(
select timestamp, symbol, avg(price) as price from trades 
where timestamp in yesterday() and side='buy' and symbol = 'BTC-USDT' sample by 1m fill(null) 
), numbered AS (
select *, row_number over(partition by symbol) as pos from sampled
), grouped_by_interval AS (
select timestamp, symbol, price, pos, pos / 5 as modulo
from numbered
)
select timestamp, symbol, pos, modulo, price , first_value(price) over(partition by symbol, modulo order by price asc) as min_price_5_mins
from grouped_by_interval;

Upvotes: 0

Related Questions