0

I have entries coming in every day and sometimes twice a day with same value. The same value can come in next day or next week too. I need to sum these values so they are distinct only for that day and still count towards the sum even if it same value the next day. I have tried the below but this will only count as one distinct occurrence where in fact there is another one the next day. Perhaps there is another way to this. Any way I can achieve this?

Measurement   _field   _value   _time

GBP.          value.     3.2.   2023-05-10T09:00:00.000Z
GBP.          value.     3.2.   2023-05-10T12:00:00.000Z
GBP.          value.     3.2.   2023-06-02T10:00:00.000Z

Expected sum 6.4 as there is a duplicate for day 2023-05-10T09:00:00.000Z

import "date"
from(bucket: "homeassistant_db")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "GBP")
  |> filter(fn: (r) => r["entity_id"] == "my_cost1")
  |> filter(fn: (r) => r["friendly_name"] == "my_friendly_cost1")
  |> filter(fn: (r) => r["_field"] == "value")
  |> distinct(column: "_value")
  |> sum(column: "_value")
ppstacknow
  • 7
  • 3
  • use [windowing](https://docs.influxdata.com/influxdb/cloud/query-data/flux/window-aggregate/) to split records by days and then use distinct inside windows – Meowster Jun 07 '23 at 16:20
  • if I remember it can be done this way ``` import "date" from(bucket: "homeassistant_db") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "GBP") |> filter(fn: (r) => r["entity_id"] == "my_cost1") |> filter(fn: (r) => r["friendly_name"] == "my_friendly_cost1") |> filter(fn: (r) => r["_field"] == "value") |> aggregateWindow(every: 1d, fn: distinct) |> sum()``` – Meowster Jun 07 '23 at 16:25
  • This worked! Thanks. It is still picking up one or two same values at same day/time (duplicates) even though it is should be distinct. – ppstacknow Jun 08 '23 at 11:31

0 Answers0