Context
I am currently learning PromQL and I try to do an equivalent of the SQL "groupby" (with an aggregator like avg).
Tests
I try a few steps toward the solution on PromLens based on the doc and this SoF solution.
I test my queries with the following base query:
node_cpu_seconds_total{cpu='0', mode='user'}.
It gives me this graph:
The first query uses the avg_over_time to get the average value per month:
avg_over_time(node_cpu_seconds_total{cpu='0', mode='user'}[1y:4w])
It gives me the following graph:
It looks fine but the constant value of each month does not make any sense when checking the original query. In fact, the avg divide the sum_over_time by the count_over_time (like in this solution). So, I tried to execute the count_over_time function and it only count the month of the year, which explains the results of avg_over_time.
Combining the query with the solution cited above does not bring interesting results too.
avg_over_time(node_cpu_seconds_total{cpu='0', mode='user'}[1y:4w]) + ignoring(year, month, day) group_right
count_values without() ("year", year(timestamp(
count_values without() ("month", month(timestamp(
avg_over_time(node_cpu_seconds_total{cpu='0', mode='user'}[1y:4w])
)))
))) * 0
It only labels the series:
Finally, I can try to execute a subquery to get a range vector of a value in each month, but it is still not the average value of a month.
node_cpu_seconds_total{cpu='0', mode='user'}[1y:4w]
Question
Is it really possible to achieve what I want to do? I assume this is an essential feature but I do not know how to do this. Thank you.
