Last 30 days (rolling average)

We can use the SQL Integration to calculate a rolling average over the last 30 days (or any period we like) from Home Assistant's Long Term Statistics.

sql:
  - name: omie_spot_pt_30d
    unique_id: omie_spot_pt_30_day_rolling_average
    query: >
      SELECT datetime(range.thirty_days, 'unixepoch') AS window_start,
             datetime(range.start_of_day, 'unixepoch') AS window_end,
             datetime(min(start_ts), 'unixepoch') AS oldest_measurement,
             datetime(max(start_ts), 'unixepoch') AS newest_measurement,
             count(1) AS measurement_count,
             round(avg(CASE WHEN strftime('%H', datetime(s.start_ts, 'unixepoch')) < '08' OR strftime('%H', datetime(s.start_ts, 'unixepoch')) >= '22' THEN s.mean ELSE NULL END), 2) AS mean_vazio,
             round(avg(CASE WHEN strftime('%H', datetime(s.start_ts, 'unixepoch')) < '08' OR strftime('%H', datetime(s.start_ts, 'unixepoch')) >= '22' THEN NULL ELSE s.mean END), 2) AS mean_fora_vazio,
             round(avg(s.mean), 2) AS mean
      FROM   statistics s JOIN statistics_meta m ON s.metadata_id = m.id, (
             SELECT
               unixepoch(datetime('now', 'start of day', '-30 days')) AS thirty_days,
               unixepoch(datetime('now', 'start of day')) start_of_day
             ) range
      WHERE  statistic_id = 'sensor.omie_spot_price_pt'
        AND  start_ts >= range.thirty_days
        AND  start_ts < range.start_of_day
    column: 'mean'
    unit_of_measurement: '€/MWh'

MariaDB

sql:
  - name: omie_spot_pt_30_day_rolling_average
    unique_id: omie_spot_pt_30_day_rolling_average
    query: |-
      SELECT FROM_UNIXTIME(stats.thirty_days) AS window_start,
            FROM_UNIXTIME(stats.start_of_day) AS window_end,
            FROM_UNIXTIME(MIN(start_ts)) AS oldest_measurement,
            FROM_UNIXTIME(MAX(start_ts)) AS newest_measurement,
            COUNT(*) AS measurement_count,
            ROUND(AVG(s.mean), 2) AS mean
      FROM statistics s 
      JOIN statistics_meta m ON s.metadata_id = m.id
      CROSS JOIN (
          SELECT UNIX_TIMESTAMP(CURDATE() - INTERVAL 30 DAY) AS thirty_days,
                 UNIX_TIMESTAMP(CURDATE()) AS start_of_day
      ) AS stats
      WHERE  statistic_id = 'sensor.omie_spot_price_pt'
      AND  start_ts >= stats.thirty_days
      AND  start_ts < stats.start_of_day
    column: 'mean'
    unit_of_measurement: 'EUR/MWh'