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@xxxxxxxxx > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Berend Tober > Sent: Monday, November 21, 2005 5:03 PM > To: pgsql-general@xxxxxxxxxxxxxx > 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 > > >
