How to merge (join) two tables in a specific way in Grafana using InfluxDB flux query?

Question

Grafana: 7.1.5 InfluxDB: 1.8

I currently have three separate table panels in Grafana where the only difference between each query is the time range (Year, Month, Day). I would like to combine these three tables into one, where the measurement's value is separated into three columns (one for each time range).

More explicitly, what I have currently is:

• Table1 Columns: [Tag1+Tag2, _value] where _value is the units this year
• Table2 Columns: [Tag1+Tag2, _value] where _value is the units this month
• Table3 Columns: [Tag1+Tag2, _value] where _value is the units this day

What I want is:

Table Columns: [Tag1+Tag2, Table1_value (Year), Table2_value (Month), Table3_value (Day)]

These are my queries:

import "date"
thisYearSoFar = date.truncate(t: now(), unit: 1y)
thisMonthSoFar = date.truncate(t: now(), unit: 1mo)
thisDaySoFar = date.truncate(t: now(), unit: 1d)

from(bucket: "consumption") 
|> range(start: thisYearSoFar, stop: now()) 
|> filter(fn: (r) => r._measurement == "stuff" and r._field == "units" and r._value > 0) 
|> group(columns: ["datacenter","tenant"]) 
|> sum(column: "_value")
|> map(fn: (r) => ({r with _value: r._value / 4.0}))

from(bucket: "consumption") 
|> range(start: thisMonthSoFar, stop: now()) 
|> filter(fn: (r) => r._measurement == "stuff" and r._field == "units" and r._value > 0) 
|> group(columns: ["datacenter","tenant"]) 
|> sum(column: "_value")
|> map(fn: (r) => ({r with _value: r._value / 4.0}))

from(bucket: "consumption") 
|> range(start: thisDaySoFar, stop: now()) 
|> filter(fn: (r) => r._measurement == "stuff" and r._field == "units" and r._value > 0) 
|> group(columns: ["datacenter","tenant"]) 
|> sum(column: "_value")
|> map(fn: (r) => ({r with _value: r._value / 4.0}))

I've tried joining these tables in various ways, but nothing I'm doing is working properly to get me the one table with 4 columns that I'm looking for.

Anyone have ideas on how to achieve this? Thanks!

Answer 1

I worked with a Flux developer that helped me come up with the solution:

import "date"
sum_over_range = (unit) =>
    from(bucket: "consumption")
    |> range(start: date.truncate(t: now(), unit: unit))
    |> filter(fn: (r) => r._measurement == "stuff" and r._field == "units" and r._value > 0)
    |> group(columns: ["datacenter", "tenant"])
    |> sum()
    |> map(fn: (r) => ({r with _value: r._value / 4.0, _field: string(v: unit), _time: 0}))

union(tables: [sum_over_range(unit: 1y), sum_over_range(unit: 1mo), sum_over_range(unit: 1d)
])
    |> group(columns: ["datacenter", "tenant"])
    |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> drop(columns: ["_time", "_start", "_stop", "result"])
    |> group()

Then additionally in Grafana, I had to apply the 'Filter by name' transformation to hide the 'result' and 'table' columns that showed.