pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL moving window averages/statistics
2+ messages / 2 participants
[nested] [flat]
* SQL moving window averages/statistics
@ 2009-09-23 23:04 Kai Carter <kcarter@customweather.com>
2009-10-02 03:52 ` Re: SQL moving window averages/statistics Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Kai Carter @ 2009-09-23 23:04 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SQL moving window averages/statistics
2009-09-23 23:04 SQL moving window averages/statistics Kai Carter <kcarter@customweather.com>
@ 2009-10-02 03:52 ` Andreas <maps.on@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: Andreas @ 2009-10-02 03:52 UTC (permalink / raw)
To: Kai Carter <kcarter@customweather.com>; +Cc: pgsql-sql
Just 3 points ...
1) don't use "date" as a column name because it's a data type.
2) to_char(current_date, 'MM')||to_char(current_date, 'DD')
is equivalent to
to_char(current_date, 'MMDD')
3) you should get the same result with
...
where icao='KSFO'
and (EXTRACT (MONTH from date) = 9)
and (EXTRACT (DAY from date) BETWEEN 23 AND 29))
group by
...
Then you lost me with your 3 day idea =8-}
It might be depressingly slow but depending how time critical the report
is, you could do something like
select distinct (date)
date,
(select max(dc1.tmax) from daily_climate as dc1 where dc1.date
between (dc0.date - interval '1 day') and (dc0.date + interval '1 day'))
as max_tmax,
(select min(dc1.tmax) from daily_climate as dc1 where dc1.date
between (dc0.date - interval '1 day') and (dc0.date + interval '1 day'))
as min_tmax,
.................
from daily_climate as dc0
............
That's just something that might get you a result.
I didn't try it out.
Kai Carter schrieb:
> 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
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-10-02 03:52 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-23 23:04 SQL moving window averages/statistics Kai Carter <kcarter@customweather.com>
2009-10-02 03:52 ` Andreas <maps.on@gmx.net>
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