From: Kai Carter <kcarter@customweather.com>
Content-Type: text/plain; charset=us-ascii; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Subject: SQL moving window averages/statistics
Date: Wed, 23 Sep 2009 16:04:15 -0700
Message-Id: <35FD937C-FE22-4272-B3C2-AE4B3E58579A@customweather.com>
To: pgsql-sql@postgresql.org
Mime-Version: 1.0 (Apple Message framework v1075.2)

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