R: complex custom aggregate function

>-----Messaggio originale-----
>Da: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] Per conto di Scara Maccai
>Inviato: venerdì 30 gennaio 2009 9.36
>A: pgsql-general(at)postgresql(dot)org
>Oggetto: [GENERAL] complex custom aggregate function
>
>Hi all,
>
>I have a table like:
>
>value int,
>quarter timestamp
>
>I need an aggregate function that gives back the maximum "value" using
>this algorithm:
>
>AVG of the first hour (first 4 quarters) (AVG0)
>same as above, but 1 quarter later (AVG1)
>....
>same as above, but n quarters later (AVGn)
>
>result: the quarter where AVGn was MAX.
>
>Example:
>
>quarter value AVGn
>
>2008-01-01 00:00 10
>2008-01-01 00:15 15
>2008-01-01 00:30 5
>2008-01-01 00:45 20 -> 12.5 ((10+15+5+20)/4)
>2008-01-01 01:15 2 -> 21 ((15+5+20+2)/4)
>2008-01-01 01:30 30 -> 14.25 ((5+20+2+30)/4))
>
>the result should be ('2008-01-01 00:15', 21)
>
>
>
>It would be very easy if the input to the custom aggregate function was
>ordered (because I would keep 4 internal counters), but I guess there's
>no way of "forcing" the ordering of the input to the function, right?
>
>So I have to cache all the (quarter,value) couples and give back a
>result at the end, right?
>
>--
>Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

For that purpose, a sliding mean calculation I use the following

--
-- Sample table definition
--
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);

--
-- Function
--
CREATE OR REPLACE FUNCTION perl_sliding_mean(integer, real, integer, integer, bpchar, bpchar)
RETURNS real AS
$BODY$
#BEGIN { strict->import(); }

# get values
my ($myid, $myval, $mycount, $myvalid, $myslidesum, $myreset) = @_;

# reset the arry if requested
if ( $myreset eq 't' ) {
@stored_sl_val=();
@stored_arr=();
return 0;
}

# restore the array of array
@temp_sl_val = $stored_arr[$myid];
@stored_sl_val = @{$temp_sl_val[0]};

# check if the value is null
if ( ! defined $myval ) {
# log log log log log log
elog(NOTICE, "perl_sliding_mean => push null value [undef]" );
# sum does not change
push(@stored_sl_val, undef);
} else {
# log log log log log log
elog(NOTICE, "perl_sliding_mean => push value $myval" );
# assign the new value
push(@stored_sl_val, $myval);
}

# log log log log log log
elog(NOTICE, "perl_sliding_mean => scalar array " . scalar @stored_sl_val );
if ( ( scalar @stored_sl_val ) > $mycount ) {
# log log log log log log
elog(NOTICE, "perl_sliding_mean => pop element" );
# Remove one element from the beginning of the array.
shift(@stored_sl_val);
}

# getting mean
# log log log log log log
elog(NOTICE, "perl_sliding_mean => getting mean" );
my $good_values;
my $result;
foreach (@stored_sl_val) {
# log log log log log log
elog(NOTICE, "arr : " . $_ );
if ( defined $_ ) {
$result += $_;
$good_values ++;
}
}

# log log log log log log
elog(NOTICE, "perl_sliding_mean => sum : $result, good values : $good_values" );
my $mean;
if ( $good_values >= $myvalid ) {
# reset the arry if requested
if ( $myslidesum eq 't' ) {
$mean = $result; # sum
} else {
$mean = $result / $good_values; # average
}
} else {
# log log log log log log
elog(NOTICE, "perl_sliding_mean => good_values < myvalid" );
$mean = -99999999; # skip later and return null
}

# save back the array of array
elog(NOTICE, "perl_sliding_mean => scalar stored_sl_val " . scalar @stored_sl_val );
$stored_arr[$myid] = [ @stored_sl_val ];

# return calculated sliding mean or null
if ( $mean == -99999999 ) { return; }
return $mean;

$BODY$
LANGUAGE 'plperlu' VOLATILE;
COMMENT ON FUNCTION perl_sliding_mean(integer, real, integer, integer, bpchar, bpchar) IS 'Calculate sliding means/sums';

--
-- query
--
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"
.....

Regards,
Paolo Saudin