[postgis-users] SQL moving window averages/statistics

[Kai Carter]

Hi there,

I'm currently have an sql statement that selects a week of descriptive  
statistics for various historical weather variables, sorted by date.

SELECT to_char(date, 'MM')||to_char(date, 'DD') as date, max(tmax) as  
max_tmax, min(tmax) as min_tmax, avg(tmax) as mean_tmax, stddev(tmax)  
as std_tmax, count(tmax) as count_tmax, sum(tmax) as sum_tmax, variance 
(tmax) as var_tmax  FROM daily_climate where icao='KSFO' and (EXTRACT 
(MONTH from date) =9 and EXTRACT(DAY from date) = 23) or  (EXTRACT 
(MONTH from date) = 9 and EXTRACT(DAY from date) = 24) or  (EXTRACT 
(MONTH from date) = 9 and EXTRACT(DAY from date) = 25) or  (EXTRACT 
(MONTH from date) = 9 and EXTRACT(DAY from date) = 26) or  (EXTRACT 
(MONTH from date) =9 and EXTRACT(DAY from date) = 27) or  (EXTRACT 
(MONTH from date) = 9 and EXTRACT(DAY from date) = 28) or  (EXTRACT 
(MONTH from date) =  and EXTRACT(DAY from date) = 29) group by date  
order by date;

The problem is that I only have 36 years of data to work with, and I  
would prefer to have a sample of ~100 rather than 30.  So the idea  
would be to have a sample statistics for each day made up of 3 days:  
the current day, the day previous and the day after.

Is it possible to get this sort of a result with one select statement?

Thanks in advance for your responses,

Kai Carter