Comparing values from different buckets

This example uses NOAA water sample data.

This example compares the value from the latest point to an average value stored in another bucket. This is useful when using the average value to calculate a threshold check.

The following query:

• Uses range() to define a time range.
• Gets the last value in the means bucket and compares it to the last value in the noaa bucket using last().
• Uses join() to combine the results
• Uses map() to calculate the differences

means = from(bucket: "weekly_means")
    |> range(start: 2019-09-01T00:00:00Z)
    |> last()
    |> keep(columns: ["_value", "location"])
latest = from(bucket: "noaa")
    |> range(start: 2019-09-01T00:00:00Z)
    |> filter(fn: (r) => r._measurement == "average_temperature")
    |> last()
    |> keep(columns: ["_value", "location"])
join(tables: {mean: means, reading: latest}, on: ["location"])
    |> map(fn: (r) => ({r with deviation: r._value_reading - r._value_mean}))

Example results