Re: Multi-parameter aggregates. - Mailing list pgsql-general

Hello Berend,

have you considered using pl/r.
http://www.joeconway.com/plr/

I think R got a covariance-function.
http://www.r-project.org/

Best regards

Hakan Kocaman

Software-Developer
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98

Email: hakan.kocaman@digame.de



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Berend Tober
> Sent: Monday, November 21, 2005 5:03 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Multi-parameter aggregates.
>
>
> I'm interested in defining a covariance aggregate function. (As a
> refresher, remember that covariance is a little bit like
> variance, but
> is between two variables:
>
> cov(X,Y)    =  <XY> - <X><Y>,
>
> where the angular brackets in this case denote taking the averag.
> Variance is a special case when X and Y are the same.)
>
> But the whole user-defined aggregate thing is tough to get a
> handle on.
> I'm not even sure if the direction I'm heading in below will actually
> work, but as far as I got, I'm stuck on not knowing how to define a
> aggregate that takes more that one variable as its argument,
> so its use
> in SQL would look like, e.g.,
>
> SELECT company, COVAR(year, sales) FROM annual_sales GROUP BY company;
>
> Here is what I tried, and I'm wondering if the team here can help me
> make this work (or tell me that the whole approach makes no sense,
> etc.). All the DDL executes without syntactical errors until the last
> function definition, and the problem is with the "  BASETYPE=numeric"
> line, i.e., "ERROR:  AggregateCreate: function
> covariance_accum(numeric[], numeric) does not exist"
>
> CREATE TYPE public._covariance AS
>     (n integer, x numeric, y numeric, xy numeric);
>
>
> CREATE OR REPLACE FUNCTION
> public.covariance_accum(_covariance, numeric,
> numeric)
>     RETURNS _covariance AS '
>     BEGIN
>     _covariance.n := _covariance.n+1;
>     _covariance.x := _covariance.x+$2;
>     _covariance.y := _covariance.x+$3;
>     _covariance.xy:= _covariance.xy+($1*$2);
>     END;
>     'LANGUAGE 'plpgsql' IMMUTABLE STRICT;
> GRANT EXECUTE ON FUNCTION
> public.covariance_accum(_covariance, numeric,
> numeric) TO public;
> COMMENT ON FUNCTION public.covariance_accum(_covariance, numeric,
> numeric) IS 'covariance aggregate transition function';
>
>
> -- Need to include a check for N equal zero data points
>
> CREATE OR REPLACE FUNCTION public.numeric_covariance(_covariance)
>     RETURNS numeric AS '
>     BEGIN
>     (_covariance.xy/_covariance.n) -
> (_covariance.x/_covariance.n)*(_covariance.y/_covariance.n);
>     END;
>     'LANGUAGE 'plpgsql' IMMUTABLE STRICT;
> GRANT EXECUTE ON FUNCTION
> public.numeric_covariance(_covariance) TO public;
> COMMENT ON FUNCTION public.numeric_covariance(_covariance) IS
> 'covariance aggregate final function';
>
>
>
> CREATE AGGREGATE public.covariance(
>   BASETYPE=numeric,
>   SFUNC=covariance_accum,
>   STYPE=numeric[],
>   FINALFUNC=numeric_covariance,
>   INITCOND='{0,0,0,0}'
> );
>
>
> /*
> --I also tried this:
>
> CREATE AGGREGATE covariance(
>   BASETYPE='numeric, numeric',
>   SFUNC=covariance_accum,
>   STYPE=numeric[],
>   FINALFUNC=numeric_covariance,
>   INITCOND='{0,0,0,0}'
> );
>
> -- to no avail.
> */
>
> Regards,
> Berend
>
>
>