CODEWITHSUNDEEP
sqltime-seriesquestdb
Javier Ramirez
Javier Ramirez

Reputation: 4032

Rolling percentile on QuestDB. Too Many Arguments

I am trying to apply inter-quartile range filtering to ignore outliers from a noisy time series, and I thought I could do a rolling median/percentile/standard dev for a series. I tried using the same window function structure I can use for a rolling average, but this doesn't seem to be implemented.

This query for a rolling average works as expected:

select timestamp, symbol, avg(price) OVER (partition by symbol order by timestamp)
from trades;

But when I add the approx_percentile function I get "too many arguments"

select timestamp, symbol, 
     avg(price) OVER (partition by symbol order by timestamp),  
     approx_percentile(price, 0.25) OVER (partition by symbol order by timestamp)
from trades;

Any ideas how can I get the results I want?

Upvotes: 0

Views: 59

Answers (1)

Javier Ramirez
Javier Ramirez

Reputation: 4032

You can first apply the approx_percentile sampling in 1 day chunks, then do the moving avg for those percentiles, comparing with a past range that suits your needs, for example this query would go over the past 90 days.

with q25 AS ((
SELECT timestamp, symbol, approx_percentile(price, 0.25) q25 FROM trades
where timestamp in '2024-04'
sample by 1d
) timestamp(timestamp))
select timestamp, symbol, 
       avg(q25) OVER (
          partition by symbol 
          order by timestamp 
         RANGE BETWEEN 90 day preceding and current row
       )
from q25;

Upvotes: 0

Related Questions