Heating energy in function of outside temperature

Configuration
1

I am trying to determine the energy needed to heat my house depending on the outside temperature. The idea is to calculate the median outside temperature along with the total consumed heating energy per day and then group by resulting temperature while calculating min, median and max energy (kWh) per degree outside temperature. The output I am envisioning would look like this:

image
image523×505 7.2 KB

The code below is as far as I got. The tables return the correct results (median degrees outside temp per day and total kWh heating energy per day):

image
image1649×265 17.8 KB

How can I group by and sort by temp and then calculate min, median and max energy consumption?

Here is my code so far:

import "math"

temp=
 from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["entity_id"] == "zws_wetterstation_temperatur")
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)
  |> drop(columns: ["_field","domain","entity_id","_measurement","_start","_stop"])
  |> map(fn: (r) => ({r with _value: math.roundtoeven(x: r._value)}))
 
energy=
 from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["entity_id"] == "heating_enercal_kwh_daily_heizung")
  |> aggregateWindow(every: 1d, fn: max, createEmpty: false)
  |> drop(columns: ["_field","domain","entity_id","_measurement","_start","_stop"])
  |> map(fn: (r) => ({r with _value: math.roundtoeven(x: r._value)}))
  
join(tables: {temp: temp, energy: energy}, on: ["_time"])

  |> drop(columns: ["_field","domain","entity_id"])
)

I would be grateful for any help, pointers on how to get to the desired result.
Thanks!

1 Like