Running average and moving average yearly temperature
As a data scientist at a meteorological research center, you wish to get a time-smoothed average of the yearly temperature in each state as each state's temperature evolves over the years. Then, compare the current year's average temperature with the moving average of yearly temperatures over the previous five years.
A common table expression gives the yearly average of the monthly average temperature for each state. Because there is only one weather station per state, grouping by state is equivalent to grouping by station.
Este exercício faz parte do curso
Time Series Analysis in PostgreSQL
Exercício interativo prático
Experimente este exercício completando este código de exemplo.
-- 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;