Kai, Still not quite sure what you are doing here, but if you are using PostgreSQL 8.4 you can use the New windowing functions lead(), lag() will give you forward and backward values. I put in 1 and 1 for example, but if you wanted to count back say 5 records from your current you would replace with 5
http://www.postgresql.org/docs/8.4/interactive/functions-window.html So something like SELECT date, tmax As curr_tmax, lag(tmax,1) OVER(ORDER BY date) As previous_day_tmax, lead(tmax,+1) OVER(ORDER BY date) As next_day_tmax FROM daily_climate; Leo -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Kai Carter Sent: Wednesday, September 23, 2009 6:36 PM To: [email protected] Subject: [postgis-users] SQL moving window averages/statistics 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
