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
  1. means = from(bucket: "weekly_means")
  2. |> range(start: 2019-09-01T00:00:00Z)
  3. |> last()
  4. |> keep(columns: ["_value", "location"])
  5. latest = from(bucket: "noaa")
  6. |> range(start: 2019-09-01T00:00:00Z)
  7. |> filter(fn: (r) => r._measurement == "average_temperature")
  8. |> last()
  9. |> keep(columns: ["_value", "location"])
  10. join(tables: {mean: means, reading: latest}, on: ["location"])
  11. |> map(fn: (r) => ({r with deviation: r._value_reading - r._value_mean}))

Example results

location_value_mean_value_readingdeviation
coyote_creek79.82710622710623899.172893772893772
santa_monica80.20451339915374854.79548660084626