How to find standard deviation of averages in PromQL?

Consider a variable that varies randomly, the only pattern in its behavior is that the distribution of the variable over the current day's hour is similar to the previous day's hour. For example, considering time 2 pm to 3 pm on 19-Jan-2022, the distribution of the variable's values will be similar to, 2 pm to 3 pm on 18-Jan-2022 or to, 2 pm to 3 pm on 17-Jan-2022 or any other previous days values but on same hours. I have to find the average of this variable's value for each hour (e.g 2 pm to 3 pm), and then I have to find the standard deviation of each hour's average over the past 7 days. Refer this image for more clarity. I expose the variables values as metric to Prometheus using Histogram, assume the name of histogram to be variable_metric. I tried achieving the same calculation using PromQL's subquery like this stddev_over_time((sum(rate(variable_metric_sum{key = "value"}[1h]))/sum(rate(variable_metric_count{key = "value"}[1h])))[7d:1d]) but the query calculates standard deviation for hours 23:00 to 00:00 hours only, but I wanted to calculate it for every hour over 7 days, so I ended up explicitly using the formula of standard deviation which made the PromQL too lengthy, can anyone help me in coming up with a query for this problem ?

For anyone interested, here is the query which I came up with (it's just the standard deviation formula written in PromQL explicitly without using functions), although for brevity I have only taken into account 2 days, you can imagine the length of query for 7 days or more.

sqrt (
(

    (
        (
            (sum(rate(variable_metric_sum{key = "value"}[1h] offset 1d)))/(sum(rate(variable_metric_count{key = "value"}[1h] offset 1d)))
            - (
                (
                    (sum(rate(variable_metric_sum{key = "value"}[1h] offset 1d)))/(sum(rate(variable_metric_count{key = "value"}[1h] offset 1d))) + 
                    (sum(rate(variable_metric_sum{key = "value"}[1h] offset 2d)))/(sum(rate(variable_metric_count{key = "value"}[1h] offset 2d)))
                ) / 2
            ) 
        ) ^ 2
    )
    +
    (
        (
            (sum(rate(variable_metric_sum{key = "value"}[1h] offset 2d)))/(sum(rate(variable_metric_count{key = "value"}[1h] offset 2d))) 
            - (
                (
                    (sum(rate(variable_metric_sum{key = "value"}[1h] offset 1d)))/(sum(rate(variable_metric_count{key = "value"}[1h] offset 1d))) + 
                    (sum(rate(variable_metric_sum{key = "value"}[1h] offset 2d)))/(sum(rate(variable_metric_count{key = "value"}[1h] offset 2d)))
                ) / 2
            ) 
        ) ^ 2
    )
) / 2 )

Solution 1:

I'm not sure how to write PromQL query for Prometheus, but it should look like the following when written in MetricsQL for VictoriaMetrics:

with (
  cf = {key="value"},
  m = sum(rate(variable_metric_sum{cf}))/sum(rate(variable_metric_count{cf})),
)
default_rollup(
  stddev(
    m offset 1d,
    m offset 2d,
    m offset 3d,
    m offset 4d,
    m offset 5d,
    m offset 6d,
    m offset 7d,
  )
  [:1h]
)