From: | Kai Carter <kcarter(at)customweather(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | SQL moving window averages/statistics |
Date: | 2009-09-23 23:04:15 |
Message-ID: | 35FD937C-FE22-4272-B3C2-AE4B3E58579A@customweather.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark J Camilleri | 2009-09-24 07:43:41 | Re: SQL Subqueries on each result row |
Previous Message | Jim | 2009-09-23 16:33:39 | Re: SQL Subqueries on each result row |