R: complex custom aggregate function

["Paolo Saudin" <paolo@xxxxxxxxxxx>]

>-----Messaggio originale-----
>Da: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] Per conto di Scara Maccai
>Inviato: venerdì 30 gennaio 2009 9.36
>A: pgsql-general@xxxxxxxxxxxxxx
>Oggetto:  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@xxxxxxxxxxxxxx)
>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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general