R: complex custom aggregate function - Mailing list pgsql-general

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