MetricsQL, Victoria Metrics - Compute average of value every hour

Question

I'm using Victoria Metrics and I have a MetricsQL query to return an array of timeseries (timestamp and value) matching a condition.

I want to compute the averages of these values over a particular period in a single query execution for performance reasons.

For instance, I'm querying the timeseries for the past 6 hours. I need averages of the values within 1 hour buckets. So, The first average value should cover the time range from current time to 1 hour ago. The second average should cover the time range from 1 hour ago to 2 hours ago, and so on

I figured I need to use avg_over_time for this. However, that can only return a single value where as I would need 6 different value pairs in the result.

Does anyone happen to know how can I solve this?

Answer 1

You are wrong about avg_over_time: it returns results for every expected time point (based on parameters start, end and step of api/v1/query_range endpoint of API).

So if you query something like avg_over_time(my_metric [1h]) with start and end corresponding to some six hour period, and step 1h, you'll get exactly what you want.

Here is demo of query to Prometheus, but Victoria should produce same results.

Answer 2

The per-hour average of some metric can be obtained via avg_over_time function passed to the following APIs in VictoriaMetrics and Prometheus:

1. /api/v1/query_range. For example, the request to /api/v1/query_range?query=avg_over_time(some_metric[1h])&start=-5h&step=1h should return per-hour average values per each time series matching some_metric filter for the last 6 hours.

2. /api/v1/query. For example, the request to /api/v1/query?query=avg_over_time(some_metric[1h])[6h:1h] should return the same results as the previous query to /api/v1/query_range. It uses subquery functionality together with little-known ability to return multiple results per series from /api/v1/query as described here.

P.S. Do not forget properly escaping query args passed to the http API handlers mentioned above.