Multiple Aggregation by Day (Max calculation) by Month (Sum Calculation)alue

InfluxDB 2
1

Hello,

I’m beginner of Influx v2.2, I do not know how to run 2 aggregates to get the needed result.

I need to know the electricity consumption by Month.
To be able to generate this report I will need to do it in 2 steps :

  1. Get the maximum consumption by Day (multiple value and 3 counters per day in the database)
  2. sum this maximum consumption by day for the month
from(bucket: "Electricity")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "STAT_TODAY" or r["_measurement"] == "STAT_TODAY_HC" or r["_measurement"] == "STAT_TODAY_HP")
  |> filter(fn: (r) => r["_field"] == "Compteur")
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")
  |> keep(columns: ["_time","STAT_TODAY","STAT_TODAY_HC","STAT_TODAY_HP"])
  |> yield(name: "COMPTER_DAY")

This query works well with the expected information (the maximum of the 3 Counter per Day)

image
image1509×147 10.8 KB

Now I need to use this table result to run monthly aggregate (the sum of Day).
I should need to run this aggregate (If I’m not wrong)

  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)

How to create to good query to get in final the monthly consumption per month with my sum of the days?

2

can you explain your problem a bit more?
Like I am not able to understand why you are using three measurements.
First, do you want to take the maximum at the day level and then do the sum for the month of those maximum values?

3

The 3 measurements are delivered by the electricity power box provider depending the cost of the day (not needed here), but it’s needed for future usage. That is why I have these 3 measurements that I need to keep in my query.
Yes, you’re right, I need to take the maximum at the day level and then do the sum for the month of those maximum values.
Sorry for my English, I’m not English fluent speaker :wink:

4

you can follow any of the approaches
First

from(bucket: "Electricity")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "STAT_TODAY" or r["_measurement"] == "STAT_TODAY_HC" or r["_measurement"] == "STAT_TODAY_HP")
  |> filter(fn: (r) => r["_field"] == "Compteur")
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
  |> window(1mo)
  |> cumulativeSum()
  |> last()
  |> yield(name: "COMPTER_DAY")

Second

from(bucket: "Electricity")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "STAT_TODAY" or r["_measurement"] == "STAT_TODAY_HC" or r["_measurement"] == "STAT_TODAY_HP")
  |> filter(fn: (r) => r["_field"] == "Compteur")
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
 |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
  |> yield(name: "COMPTER_DAY")
5

Thanks a lot for your reply,
It works very well, I was disturbed to know how to integrate the second aggregatewindow instruction (never at the good position)
First alternative is good to understand step stages also

6

Using the data, it looks strange, the sum of January month is assigned to Feb month for exemple. One moth later than daily consumption month reference.

You can see that with the aggregate by day, I got well the consumption day by day starting January 5th.

|> aggregateWindow(every: 1d, fn: max, createEmpty: false)

image
image1163×171 12.4 KB

7

When I add the aggregate by month, January consumption is assigned to Feb, Feb to March, I do not undestand why because all looks good with the timestamp ?

 |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
 |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)

image
image1057×176 12.8 KB

8

Using the daily aggregate result in Excel of course I found the good data assigned to the good month.
image

Below quit extract of the January month for exemple
…

_time STAT_TODAY STAT_TODAY_HC STAT_TODAY_HP
2022-01-05T00:00:00Z 4088 4088 1433
2022-01-06T00:00:00Z 90669 36509 54160
2022-01-07T00:00:00Z 89761 30601 59160
2022-01-08T00:00:00Z 94022 37639 56383
2022-01-09T00:00:00Z 93776 21158 72618
2022-01-10T00:00:00Z 98598 32475 66123
2022-01-11T00:00:00Z 81261 21188 60073
2022-01-12T00:00:00Z 85237 23067 62170
2022-01-13T00:00:00Z 79449 42295 37154
2022-01-14T00:00:00Z 54634 20491 34143
2022-01-15T00:00:00Z 82423 42694 39729
2022-01-16T00:00:00Z 96765 21904 74861
2022-01-17T00:00:00Z 82291 37412 44879
2022-01-18T00:00:00Z 80803 31956 48847
2022-01-19T00:00:00Z 76069 21749 54320
2022-01-20T00:00:00Z 85806 27862 57944
2022-01-21T00:00:00Z 105443 48122 57321
2022-01-22T00:00:00Z 97367 32855 64512
2022-01-23T00:00:00Z 108457 20777 87680
2022-01-24T00:00:00Z 102690 34958 67732
2022-01-25T00:00:00Z 79331 26179 53152
2022-01-26T00:00:00Z 91012 23842 67170
2022-01-27T00:00:00Z 103342 34364 68978
2022-01-28T00:00:00Z 108215 44966 63249
2022-01-29T00:00:00Z 94538 32461 62077
2022-01-30T00:00:00Z 84681 19414 65267
2022-01-31T00:00:00Z 79357 19880 59477
2022-02-01T00:00:00Z 55975 14585 41390
2022-02-02T00:00:00Z 69013 17706 51307
2022-02-03T00:00:00Z 58711 16093 42618
2022-02-04T00:00:00Z 83617 38661 44956

…

9

Actually, if you do aggregation on a daily basis then suppose for 16 May your last data point came at 23:59:00 then it will show for 17 May at 00:00:00
same for last data point for 17 May came at 23:59 then it will show at 18 May at 00:00.

The same thing is happening for the month as well but you can use the

timeShift(duration: 1mo, columns: ["_time"])

and it will shift the time column Jan data will show for the Jan month

10

Thanks again, not easy to understand standard rules compare to other database like SQL.
thanks a lot again