Re: SQL moving window averages/statistics

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
>