>-----Messaggio originale-----
>Da: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Per conto di Scara Maccai
>Inviato: lunedì 2 febbraio 2009 10.36
>A: Paolo Saudin; pgsql-general@postgresql.org
>Cc: pgsql-general
>Oggetto: Re: R: [GENERAL] complex custom aggregate function
> Paolo Saudin wrote:
> For that purpose, a sliding mean calculation I use the following
>
> CREATE TABLE tbl_ayas
> (
> fulldate timestamp without time zone NOT NULL,
> id_1 real, -- temperature
> id_2 real, -- pressure
> ..........
> CONSTRAINT tbl_ayas_pkey PRIMARY KEY (fulldate)
> ) WITH (OIDS=FALSE);
>
> [...]
> Select perl_sliding_mean(0,0,0,0,'f','t');
> SELECT perl_sliding_mean(0," id_1 ", 8, 6, 'f', 'f') AS numeric), 1) AS
> "ayas_temperature",
> perl_sliding_mean(1," id_2 ", 8, 6, 'f', 'f') AS numeric), 1) AS
> "ayas_pressure"
>I don't understand: how can you be sure that data is passed to the function ordered by "fulldate"?
>Thank you.
I use a master table with a "fulldate" field and filled with sequential dates to fill gaps when meteo data is missing.
CREATE TABLE master
(
fulldate timestamp without time zone NOT NULL,
CONSTRAINT master_pkey PRIMARY KEY (fulldate)
) WITH (OIDS=FALSE);
So the query will be:
SELECT
fulldate, id_3 AS "ayas_temperature" ,
round(cast(perl_sliding_mean(0,id_3, 8, 6, 'f', 'f') AS numeric), 3) AS "ayas_temperature_sliding"
FROM
_master LEFT JOIN tables_ar.tbl_ayas USING(fulldate)
WHERE
fulldate > '2009-01-01'
ORDER BY fulldate limit 16;
01/01/2009 1.00 -7
01/01/2009 2.00 -7,1
01/01/2009 3.00 -5,3
01/01/2009 4.00 -5,2
01/01/2009 5.00 -4,8
01/01/2009 6.00 -4
01/01/2009 7.00 -4,3
01/01/2009 8.00 -5,2 -5,363 ( mean from 01/01/2009 1.00 - 01/01/2009 8.00 )
01/01/2009 9.00 -5,4 -5,163 ...............................................
01/01/2009 10.00 -3 -4,65 ...............................................
01/01/2009 11.00 -0,4 -4,038 ...............................................
01/01/2009 12.00 0,4 -3,338 ...............................................
01/01/2009 13.00 -0,2 -2,763 ...............................................
01/01/2009 14.00 -1,8 -2,488 ...............................................
01/01/2009 15.00 -2,2 -2,225 ...............................................
01/01/2009 16.00 -2,6 -1,9 ( mean from 01/01/2009 9.00 - 01/01/2009 16.00 )
And all the sliding means are correct ( from the 8th value ahead)
Paolo Saudin
