Influx data

[tbobek]

Can it be used together with Influx database. I've got time series of different consumptions and it would be great to display them as Sankey. But after trying all grouping, merginng and outer join options I could imagine, I still cannot get a proper Sankey diagram.

Lets say I've got one input datachannel tagged as VALUE/INPUT with values along the timeline and similarly two channels VALUE/CONSUMER1 and VALUE/CONSUMER2 representing the live values of the output. All three inserted into an influx DB.

How could I prepare a query properly to get a Sankey diagram from it?
Thanks in advance for info on that!!!

[KatrinaTurner]

I haven't used it with InfluxDB before, but I think it should still work.
The sankey doesn't make use of the time series data, so you should be aggregating that probably by the sum or average, depending what is more useful to you. Then try using the group by in the query to group by 2+ tags. Ex group by consumer1, then by consumer2.

[default-student]

this works for me:

// Query for IG1/P
avg_IG1 = from(bucket: "bucketname")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "...")
  |> filter(fn: (r) => r["_field"] == "IG1/P")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> mean()
  |> map(fn: (r) => ({ origin: "IG1/P", destination: "Power", value: r._value }))

// Query for IG2/P
avg_IG2 = from(bucket: "bucketname")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "...")
  |> filter(fn: (r) => r["_field"] == "IG2/P")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> mean()
  |> map(fn: (r) => ({ origin: "IG2/P", destination: "Power", value: r._value }))


// Query for IG3/P
avg_IG3 = from(bucket: "bucketname")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "...")
  |> filter(fn: (r) => r["_field"] == "IG3/P")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> mean()
  |> map(fn: (r) => ({ origin: "IG3/P", destination: "Power", value: r._value }))

// Combine results into a single table
union(tables: [avg_IG1, avg_IG2, avg_IG3])

[kevpatts]

I've been trying to use this sankey plug-in in Grafana 9 and InfluxDB 5 within Home Assistant to create a 3 column diagram but can't find any documentation closer to my use case than this. Although this seems to be a completely different format to what the UI expects. I can only build pseudo SQL queries in the UI.

Any pointers would be greatly appreciated and if I get this working I'll share it with the Home Assistant community.