Tesla Ladeverbrauch mit Teslamate in Home Assistant auswerten (Monatsübersicht)

WITH selected_car AS (
  SELECT id AS car_id
  FROM cars
  WHERE UPPER(vin) = UPPER('<Deine VIN>')
  LIMIT 1
),

time_range AS (
  SELECT 
    date_trunc('month', CURRENT_DATE + INTERVAL '2 hours') AS month_start,
    date_trunc('month', CURRENT_DATE + INTERVAL '2 hours') + INTERVAL '1 month' AS month_end
),

charging_sessions AS (
  SELECT
    cp.id,
    COALESCE(NULLIF(cp.charge_energy_used, 0), cp.charge_energy_added) AS charge_energy,
    cp.cost,
    CASE 
      WHEN NULLIF(mode() WITHIN GROUP (ORDER BY c.charger_phases), 0) IS NULL THEN 'DC'
      ELSE 'AC'
    END AS charge_type,
    COALESCE(
      geofences.name,
      CONCAT_WS(', ', 
        COALESCE(addresses.name, NULLIF(CONCAT_WS(' ', addresses.road, addresses.house_number), '')), 
        addresses.city
      )
    ) AS address
  FROM charging_processes cp
  LEFT JOIN charges c ON cp.id = c.charging_process_id
  LEFT JOIN addresses ON addresses.id = cp.address_id
  LEFT JOIN public.geofences ON geofences.id = cp.geofence_id
  CROSS JOIN time_range t
  CROSS JOIN selected_car sc
  WHERE 
    cp.car_id = sc.car_id AND
    cp.start_date >= t.month_start AND
    cp.start_date < t.month_end
  GROUP BY 
    cp.id, cp.charge_energy_used, cp.charge_energy_added, cp.cost, 
    geofences.name, addresses.name, addresses.road, addresses.house_number, addresses.city
),

aggregated AS (
  SELECT
    address,
    charge_type,
    ROUND(SUM(charge_energy)::numeric, 2) AS total_kwh,
    ROUND(SUM(cost)::numeric, 2) AS total_cost
  FROM charging_sessions
  GROUP BY address, charge_type
),

monthly_km AS (
  SELECT 
    ROUND((MAX(odometer) - MIN(odometer))::numeric, 0) AS month_total_km
  FROM public.positions p
  CROSS JOIN time_range t
  CROSS JOIN selected_car sc
  WHERE 
    p.car_id = sc.car_id AND
    DATE_TRUNC('month', p.date + INTERVAL '2 hours') = t.month_start
),

monthly_cost AS (
  SELECT 
    ROUND(SUM(cp.cost)::numeric, 2) AS month_total_cost
  FROM public.charging_processes cp
  CROSS JOIN time_range t
  CROSS JOIN selected_car sc
  WHERE 
    cp.car_id = sc.car_id AND
    cp.end_date >= t.month_start AND
    cp.end_date < t.month_end
),

drive_data AS (
  SELECT
    distance,
    duration_min,
    end_date,
    start_date,
    car.efficiency AS car_efficiency,
    NULLIF(GREATEST(start_ideal_range_km - end_ideal_range_km, 0), 0) AS range_diff,
    duration_min > 1 AND distance > 1 AND ( 
      start_position.usable_battery_level IS NULL OR end_position.usable_battery_level IS NULL OR
      (end_position.battery_level - end_position.usable_battery_level) = 0 
    ) AS is_sufficiently_precise
  FROM drives
  LEFT JOIN positions start_position ON start_position_id = start_position.id
  LEFT JOIN positions end_position ON end_position_id = end_position.id
  LEFT JOIN cars car ON car.id = drives.car_id
  CROSS JOIN time_range t
  CROSS JOIN selected_car sc
  WHERE
    start_date >= t.month_start AND
    start_date < t.month_end AND
    drives.car_id = sc.car_id AND
    distance >= 1 AND
    distance / COALESCE(NULLIF(duration_min, 0) * 60, extract(epoch from end_date - start_date)) * 3600 >= 5
),

avg_consumption AS (
  SELECT
    ROUND(AVG(
      CASE 
        WHEN is_sufficiently_precise AND distance > 0 THEN
          range_diff * car_efficiency / distance * 1000
        ELSE NULL
      END
    )::numeric) AS month_avg_consumption_Wh_per_km
  FROM drive_data
),

avg_prices AS (
  SELECT
    ROUND(SUM(total_cost) / NULLIF(SUM(total_kwh), 0), 2) AS month_avg_price_per_kwh,
    json_agg(json_build_object(
      'address', address,
      'charge_type', charge_type,
      'avg_price_per_kwh', 
        CASE WHEN total_kwh > 0 THEN ROUND(total_cost / total_kwh, 2) ELSE 0 END
    ) ORDER BY address, charge_type) AS avg_price_per_kwh_by_location
  FROM aggregated
)

SELECT 
  (
    SELECT json_agg(json_build_object(
      'address', address,
      'charge_type', charge_type,
      'total_kwh', total_kwh,
      'total_cost', total_cost,
      'avg_price_per_kwh', 
        CASE WHEN total_kwh > 0 THEN ROUND(total_cost / total_kwh, 2) ELSE 0 END
    ) ORDER BY total_kwh DESC, address, charge_type)
    FROM aggregated
  ) AS charging_sessions_json,

  COALESCE(mk.month_total_km, 0) AS month_total_km,
  COALESCE(mc.month_total_cost, 0) AS month_total_cost,
  COALESCE(ac.month_avg_consumption_Wh_per_km, 0) AS month_avg_consumption_Wh_per_km,
  COALESCE((SELECT ROUND(SUM(total_kwh)::numeric, 2) FROM aggregated), 0) AS month_total_kwh,
  COALESCE(ROUND(
    (mc.month_total_cost / NULLIF((SELECT ROUND(SUM(total_kwh)::numeric, 2) FROM aggregated), 0)) 
    * (ac.month_avg_consumption_Wh_per_km / 1000)
    , 3), 0.0) AS month_cost_per_km_estimated,
  COALESCE(ROUND(mc.month_total_cost / NULLIF(mk.month_total_km, 0), 3), 0.0) AS month_cost_per_km_actual,
  COALESCE(ap.month_avg_price_per_kwh, 0.0) AS month_avg_price_per_kwh

FROM avg_prices ap
CROSS JOIN monthly_km mk
CROSS JOIN monthly_cost mc
CROSS JOIN avg_consumption ac;

WITH selected_car AS (
  SELECT id AS car_id
  FROM cars
  WHERE UPPER(vin) = UPPER('<Deine VIN>')
  LIMIT 1
),

time_range AS (
  SELECT 
    date_trunc('month', CURRENT_DATE + INTERVAL '2 hours') AS month_start,
    date_trunc('month', CURRENT_DATE + INTERVAL '2 hours') + INTERVAL '1 month' AS month_end
),

charging_sessions AS (
  SELECT
    cp.id,
    COALESCE(NULLIF(cp.charge_energy_used, 0), cp.charge_energy_added) AS charge_energy,
    cp.cost,
    CASE 
      WHEN NULLIF(mode() WITHIN GROUP (ORDER BY c.charger_phases), 0) IS NULL THEN 'DC'
      ELSE 'AC'
    END AS charge_type,
    COALESCE(
      geofences.name,
      CONCAT_WS(', ', 
        COALESCE(addresses.name, NULLIF(CONCAT_WS(' ', addresses.road, addresses.house_number), '')), 
        addresses.city
      )
    ) AS address
  FROM charging_processes cp
  LEFT JOIN charges c ON cp.id = c.charging_process_id
  LEFT JOIN addresses ON addresses.id = cp.address_id
  LEFT JOIN public.geofences ON geofences.id = cp.geofence_id
  CROSS JOIN time_range t
  CROSS JOIN selected_car sc
  WHERE 
    cp.car_id = sc.car_id AND
    cp.start_date >= t.month_start AND
    cp.start_date < t.month_end
  GROUP BY 
    cp.id, cp.charge_energy_used, cp.charge_energy_added, cp.cost, 
    geofences.name, addresses.name, addresses.road, addresses.house_number, addresses.city
),

aggregated AS (
  SELECT
    address,
    charge_type,
    ROUND(SUM(charge_energy)::numeric, 2) AS total_kwh,
    ROUND(SUM(cost)::numeric, 2) AS total_cost
  FROM charging_sessions
  GROUP BY address, charge_type
),

monthly_km AS (
  SELECT 
    ROUND((MAX(odometer) - MIN(odometer))::numeric, 0) AS month_total_km
  FROM public.positions p
  CROSS JOIN time_range t
  CROSS JOIN selected_car sc
  WHERE 
    p.car_id = sc.car_id AND
    DATE_TRUNC('month', p.date + INTERVAL '2 hours') = t.month_start
),

monthly_cost AS (
  SELECT 
    ROUND(SUM(cp.cost)::numeric, 2) AS month_total_cost
  FROM public.charging_processes cp
  CROSS JOIN time_range t
  CROSS JOIN selected_car sc
  WHERE 
    cp.car_id = sc.car_id AND
    cp.end_date >= t.month_start AND
    cp.end_date < t.month_end
),

drive_data AS (
  SELECT
    distance,
    duration_min,
    end_date,
    start_date,
    car.efficiency AS car_efficiency,
    NULLIF(GREATEST(start_ideal_range_km - end_ideal_range_km, 0), 0) AS range_diff,
    duration_min > 1 AND distance > 1 AND ( 
      start_position.usable_battery_level IS NULL OR end_position.usable_battery_level IS NULL OR
      (end_position.battery_level - end_position.usable_battery_level) = 0 
    ) AS is_sufficiently_precise
  FROM drives
  LEFT JOIN positions start_position ON start_position_id = start_position.id
  LEFT JOIN positions end_position ON end_position_id = end_position.id
  LEFT JOIN cars car ON car.id = drives.car_id
  CROSS JOIN time_range t
  CROSS JOIN selected_car sc
  WHERE
    start_date >= t.month_start AND
    start_date < t.month_end AND
    drives.car_id = sc.car_id AND
    distance >= 1 AND
    distance / COALESCE(NULLIF(duration_min, 0) * 60, extract(epoch from end_date - start_date)) * 3600 >= 5
),

avg_consumption AS (
  SELECT
    ROUND(AVG(
      CASE 
        WHEN is_sufficiently_precise AND distance > 0 THEN
          range_diff * car_efficiency / distance * 1000
        ELSE NULL
      END
    )::numeric) AS month_avg_consumption_Wh_per_km
  FROM drive_data
),

avg_prices AS (
  SELECT
    ROUND(SUM(total_cost) / NULLIF(SUM(total_kwh), 0), 2) AS month_avg_price_per_kwh,
    json_agg(json_build_object(
      'address', address,
      'charge_type', charge_type,
      'avg_price_per_kwh', 
        CASE WHEN total_kwh > 0 THEN ROUND(total_cost / total_kwh, 2) ELSE 0 END
    ) ORDER BY address, charge_type) AS avg_price_per_kwh_by_location
  FROM aggregated
)

SELECT 
  (
    SELECT json_agg(json_build_object(
      'address', address,
      'charge_type', charge_type,
      'total_kwh', total_kwh,
      'total_cost', total_cost,
      'avg_price_per_kwh', 
        CASE WHEN total_kwh > 0 THEN ROUND(total_cost / total_kwh, 2) ELSE 0 END
    ) ORDER BY total_kwh DESC, address, charge_type)
    FROM aggregated
  ) AS charging_sessions_json,

  COALESCE(mk.month_total_km, 0) AS month_total_km,
  COALESCE(mc.month_total_cost, 0) AS month_total_cost,
  COALESCE(ac.month_avg_consumption_Wh_per_km, 0) AS month_avg_consumption_Wh_per_km,
  COALESCE((SELECT ROUND(SUM(total_kwh)::numeric, 2) FROM aggregated), 0) AS month_total_kwh,
  COALESCE(ROUND(
    (mc.month_total_cost / NULLIF((SELECT ROUND(SUM(total_kwh)::numeric, 2) FROM aggregated), 0)) 
    * (ac.month_avg_consumption_Wh_per_km / 1000)
    , 3), 0.0) AS month_cost_per_km_estimated,
  COALESCE(ROUND(mc.month_total_cost / NULLIF(mk.month_total_km, 0), 3), 0.0) AS month_cost_per_km_actual,
  COALESCE(ap.month_avg_price_per_kwh, 0.0) AS month_avg_price_per_kwh

FROM avg_prices ap
CROSS JOIN monthly_km mk
CROSS JOIN monthly_cost mc
CROSS JOIN avg_consumption ac;