Скользящее и накопительное среднее годовой температуры
Представьте, что вы работаете специалистом по данным в метеорологическом исследовательском центре. Ваша задача — получить сглаженное во времени среднее значение годовой температуры по каждому штату по мере её изменения из года в год. Затем нужно сравнить среднюю температуру текущего года со скользящим средним годовых температур за предыдущие пять лет.
Обобщённое табличное выражение вычисляет среднегодовую температуру на основе среднемесячных значений для каждого штата. Поскольку в каждом штате находится только одна метеостанция, группировка по штату равнозначна группировке по станции.
Это упражнение является частью курса
Анализ временных рядов в PostgreSQL
Интерактивное практическое упражнение
Попробуйте выполнить это упражнение, дополнив этот пример кода.
-- Running average of the yearly avg temperature
WITH yearly AS(
SELECT
state,
DATE_PART('year', year_month)::INTEGER AS year,
AVG(t_monthly_avg) AS yearly_avg
FROM temperatures_monthly JOIN temperature_stations USING(station_id)
WHERE t_monthly_avg > -9999
GROUP BY state, year
)
SELECT
state,
year,
yearly_avg,
___ OVER (
___
___
ROWS BETWEEN ___)
AS running_yearly_avg
FROM yearly
ORDER BY state, year;