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.
This exercise is part of the course
Time Series Analysis in PostgreSQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- 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;