From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Berend Tober <btober(at)seaworthysys(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Multi-parameter aggregates. |
Date: | 2005-11-21 17:06:27 |
Message-ID: | 20051121170626.GA91161@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Nov 21, 2005 at 11:03:22AM -0500, Berend Tober wrote:
> 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;
I think aggregates must take a single value, so the above won't
work as written. However, in PostgreSQL 8.0 or later you could
define the aggregate's base type to be a composite type and do
something like
SELECT company, COVAR(ROW(year, sales)) FROM annual_sales GROUP BY company;
You'd create the aggregate like this:
CREATE TYPE covar_state AS (...);
CREATE TYPE xypair AS (x numeric, y numeric);
CREATE FUNCTION covar_accum(covar_state, xypair) RETURNS covar_state AS ...
CREATE FUNCTION covar_final(covar_state) RETURNS numeric AS ...
CREATE AGGREGATE covar (
BASETYPE = xypair,
SFUNC = covar_accum,
FINALFUNC = covar_final,
STYPE = covar_state,
INITCOND = '(...)'
);
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-11-21 17:06:40 | Re: Multi-parameter aggregates. |
Previous Message | Hakan Kocaman | 2005-11-21 16:23:44 | Re: Multi-parameter aggregates. |