app/lib/sensor/query/helpers/sql/total.rb:173
- Average Self
-
7.5ms
- Average calls
- 1x
- Total Self
- 7.5ms
- Total Children
- 0ms
- Total runtime
- 7.5ms
WITH price_ranges AS ( SELECT name, starts_at, LEAD(starts_at, ?, ?::date) OVER (PARTITION BY name ORDER BY starts_at) AS next_start, value::numeric AS eur_per_kwh FROM prices WHERE name IN (?) ),
daily AS ( SELECT sv.date, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS battery_charging_power_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS battery_charging_power_grid_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS battery_discharging_power_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS grid_export_power_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS grid_import_power_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS heatpump_power_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS heatpump_power_grid_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS house_power_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS house_power_grid_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS inverter_power_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS inverter_power_1_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS inverter_power_2_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS inverter_power_3_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS wallbox_power_sum, SUM(sv.value) FILTER (WHERE sv.aggregation = ? AND sv.field = ?) AS wallbox_power_grid_sum, MAX(pb.eur_per_kwh) AS pb_eur_per_kwh, MAX(pf.eur_per_kwh) AS pf_eur_per_kwh FROM summary_values sv LEFT JOIN price_ranges pb ON pb.name = ? AND sv.date >= pb.starts_at AND sv.date < pb.next_start LEFT JOIN price_ranges pf ON pf.name = ? AND sv.date >= pf.starts_at AND sv.date < pf.next_start WHERE sv.date BETWEEN DATE ? AND DATE ? AND sv.aggregation IN (?) AND sv.field IN (?) GROUP BY sv.date )
SELECT
SUM(battery_charging_power_sum) AS battery_charging_power_sum_sum,
SUM(battery_charging_power_grid_sum) AS battery_charging_power_grid_sum_sum,
SUM(battery_charging_power_grid_sum * pb_eur_per_kwh / ?) AS battery_charging_costs_sum_sum,
SUM(battery_discharging_power_sum) AS battery_discharging_power_sum_sum,
SUM((COALESCE(battery_discharging_power_sum, ?) * pb_eur_per_kwh - COALESCE(battery_charging_power_sum, ?) * pf_eur_per_kwh) / ?) AS battery_savings_sum_sum,
SUM(grid_export_power_sum) AS grid_export_power_sum_sum,
SUM(grid_import_power_sum) AS grid_import_power_sum_sum,
SUM(COALESCE(grid_import_power_sum,?) * pb_eur_per_kwh / ?) AS grid_costs_sum_sum,
SUM(COALESCE(grid_export_power_sum,?) * pf_eur_per_kwh / ?) AS grid_revenue_sum_sum,
SUM(heatpump_power_sum) AS heatpump_power_sum_sum,
SUM(heatpump_power_grid_sum) AS heatpump_power_grid_sum_sum,
SUM(heatpump_power_grid_sum * pb_eur_per_kwh / ?) AS heatpump_costs_grid_sum_sum,
SUM(GREATEST(COALESCE(heatpump_power_sum,?) - COALESCE(heatpump_power_grid_sum,?), ?) / ? * pf_eur_per_kwh) AS heatpump_costs_pv_sum_sum,
SUM(house_power_sum) AS house_power_sum_sum,
SUM(house_power_grid_sum) AS house_power_grid_sum_sum,
SUM(house_power_grid_sum * pb_eur_per_kwh / ?) AS house_costs_grid_sum_sum,
SUM(GREATEST(COALESCE(house_power_sum,?) - COALESCE(house_power_grid_sum,?), ?) / ? * pf_eur_per_kwh) AS house_costs_pv_sum_sum,
SUM(inverter_power_sum) AS inverter_power_sum_sum,
SUM(inverter_power_1_sum) AS inverter_power_1_sum_sum,
SUM(inverter_power_2_sum) AS inverter_power_2_sum_sum,
SUM(inverter_power_3_sum) AS inverter_power_3_sum_sum,
SUM(GREATEST((COALESCE(inverter_power_sum,?) - COALESCE(grid_export_power_sum,?)), ?) * pf_eur_per_kwh / ?) AS opportunity_costs_sum_sum,
SUM(wallbox_power_sum) AS wallbox_power_sum_sum,
SUM((COALESCE(house_power_sum,?) + COALESCE(heatpump_power_sum,?) + COALESCE(wallbox_power_sum,?)) * pb_eur_per_kwh / ?) AS traditional_costs_sum_sum,
SUM(wallbox_power_grid_sum) AS wallbox_power_grid_sum_sum,
SUM(wallbox_power_grid_sum * pb_eur_per_kwh / ?) AS wallbox_costs_grid_sum_sum,
SUM(GREATEST(COALESCE(wallbox_power_sum,?) - COALESCE(wallbox_power_grid_sum,?), ?) / ? * pf_eur_per_kwh) AS wallbox_costs_pv_sum_sum
FROM daily
|
app/lib/sensor/summarizer.rb:123
- Average Self
-
7ms
- Average calls
- 0.5x
- Total Self
- 3.5ms
- Total Children
- 0ms
- Total runtime
- 3.5ms
INSERT INTO "summary_values" ("field","aggregation","value","date") VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?) ON CONFLICT ("date","aggregation","field") DO UPDATE SET "value"=excluded."value" RETURNING "date","aggregation","field"
|