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