ok, well with the help of AI (sorry… ) I seem to have an SQL query that I am testing via the integration that takes the sensor (in my case one I trigger for solar production hourly)
it fills every hour with the average including the current value
WITH config AS (
-- Configuration variables
SELECT
7 AS x_days_to_average_over, -- Change this to set the number of days
'input_number.pv_value_this_hour_for_sql_queries' AS target_entity -- Change this to set the entity
),
hourly_max_last_x_days AS (
-- Get the maximum value for each hour over the last X days
SELECT
strftime('%Y-%m-%d %H:00:00', datetime(s.last_updated_ts, 'unixepoch', 'localtime')) AS hour_timestamp,
MAX(CAST(s.state AS FLOAT)) AS max_value
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
JOIN config c ON 1=1 -- Use the config variables
WHERE sm.entity_id = c.target_entity
AND s.state NOT IN ('unknown', '', 'unavailable')
AND s.last_updated_ts >= strftime('%s', datetime('now', printf('-%d days', c.x_days_to_average_over - 1)))
GROUP BY hour_timestamp
),
hourly_averages AS (
-- Compute the X-day rolling average for each hour
SELECT
strftime('%H', hour_timestamp) AS hour_of_day,
AVG(max_value) AS rolling_x_day_avg
FROM hourly_max_last_x_days
WHERE strftime('%H', hour_timestamp) = strftime('%H', datetime('now', 'localtime')) -- Match exact hour
GROUP BY hour_of_day
),
current_value_cte AS (
-- Get the latest current value for the target entity
SELECT
CAST(s.state AS FLOAT) AS current_value
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
JOIN config c ON 1=1
WHERE sm.entity_id = c.target_entity
AND s.state NOT IN ('unknown', '', 'unavailable')
ORDER BY s.last_updated_ts DESC
LIMIT 1
)
-- Final selection with the current value, config values, and rolling average
SELECT
ha.hour_of_day AS measurement_hour,
ha.rolling_x_day_avg,
cv.current_value,
c.x_days_to_average_over,
c.target_entity
FROM hourly_averages ha
JOIN current_value_cte cv ON 1=1
JOIN config c ON 1=1;
additionally, in configuring the integration I have entered/choosen:
column : rolling_x_day_avg
state class: Measurement
I setup an entity that it follows which updates only hourly in part to keep the system from being overloaded to do it constantly (with that said, I see I have triggered it at weird times and that then is a point it follows giving more points in the average presumably for the period given which I think can cause some strangeness in the data if it does not have points on other days… so I guess be consistent).
so after running a 24hrs I have this (pink line) on a 3 day sensor, I then shifted it -24h (-1d) to not include todays data in the average :
- in hindsight I should have not done it on an hourly delta not a accumulated daily sensor
My hope is that it will not be too hard on the system as the value that triggers it (?) is updated only hourly although I am running this for 3, 7, 10 day hourly running averages to figure out what is interesting and to debug (hidden is the same I am doing for forecasts as well… so it multiples quickly… ) 
) :
