>-----Messaggio originale----- >Da: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] Per conto di Scara Maccai >Inviato: lunedì 2 febbraio 2009 10.36 >A: Paolo Saudin; pgsql-general@xxxxxxxxxxxxxx >Cc: pgsql-general >Oggetto: Re: R: 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
