From: Kai Carter <kcarter@customweather.com>
To: pgsql-sql@postgresql.org
Subject: SQL moving window averages/statistics
Date: Wed, 23 Sep 2009 16:04:15 -0700
Message-ID: <35FD937C-FE22-4272-B3C2-AE4B3E58579A@customweather.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kcarter@customweather.com
Subject: Re: SQL moving window averages/statistics
In-Reply-To: <35FD937C-FE22-4272-B3C2-AE4B3E58579A@customweather.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox