Rolling Standard Deviation in QuestDB

Question

I want to calculate the standard deviation in a time window. QuestDB supports stddev as an aggregate function, but not as a window function. Is there any workaround for this or do I have to calculate client side?

Answer 1

The standard deviation can be calculated from the variance, which is the average of the square differences from the mean.

In general we could write it in SQL like this

SELECT 
  symbol,
  price,
  AVG(price) OVER (PARTITION BY symbol ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rolling_mean,
  SQRT(AVG(POWER(price - AVG(price) OVER (PARTITION BY symbol  ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2)) 
       OVER (PARTITION BY symbol ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS rolling_stddev
FROM   
  trades
WHERE timestamp in yesterday()  

But in QuestDB we cannot do any operations on the return value of a window function, so we need to do this using CTEs:

WITH rolling_avg_cte AS (
  SELECT 
    timestamp,
    symbol, 
    price,
    AVG(price) OVER (PARTITION BY symbol ORDER BY timestamp) AS rolling_avg
  FROM 
    trades
  WHERE 
    timestamp IN yesterday()
),
variance_cte AS (
  SELECT 
    timestamp,
    symbol,
    price,
    rolling_avg,
    AVG(POWER(price - rolling_avg, 2)) OVER (PARTITION BY symbol ORDER BY timestamp) AS rolling_variance
  FROM 
    rolling_avg_cte
)

SELECT 
  timestamp,
  symbol, 
  price,
  rolling_avg,
  rolling_variance,
  SQRT(rolling_variance) AS rolling_stddev
FROM 
  variance_cte

I first get the rolling average/mean, then from that I get the variance, and then I can do the sqrt to get the standard deviation as requested