PostgreSQL 8.2 and above has this pretty neat feature of allowing you to define aggregate functions
that take more than one column as an input. First we'll start off with a rather pointless but easy to relate to example and then we'll follow up with something
a bit more interesting.
For more examples of creating aggregates in PostgreSQL, check out our other articles:
Product Sum
We have all seen aggregate functions that take one column as argument, but what about ones that take
two or three or four or more. Why would you ever want to do this? The first example we will show is one that
puts me to sleep, but demonstrates the point.
We'll call it the prodsum. What it does is this.
prodsum(a,b) = a1*b1 + a2*b2 + a3*b3 ....an*bn
Any astitute observer will observe that this is just a lame use of something as powerful as multicolumn aggregates
because you can easily simulate it with the below. Its only nice because it allows us to validate our result.
prodsum(a,b) = SUM(a*b)
You would form the aggregate product sum by doing this
CREATE OR REPLACE FUNCTION prod_state(prev numeric, e1 numeric, e2 numeric)
RETURNS numeric AS
$$
SELECT COALESCE($1,0) + COALESCE($2*$3,0);
$$
LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE prodsum(numeric, numeric)(
SFUNC=prod_state,
STYPE=numeric,
INITCOND=0
);
Now to validate that it is doing the right thing..
SELECT k,
prodsum(i,j + k) As prodsum ,
sum(i*(j + k)) As regsum
FROM generate_series(1,4) As i
CROSS JOIN generate_series(1,2) As j
CROSS JOIN generate_series(0,1) As k
GROUP BY k
ORDER BY k;
k | prodsum | regsum
---+---------+--------
0 | 27 | 27
1 | 45 | 45
Something different
Now this example is a little trickier to simulate without an aggregate function that takes more than one argument, so we won't bother
funky_agg(a,b) = a10*b1 + a21*b2 ...ann-1*bn
--Funky agg
CREATE OR REPLACE FUNCTION funky_state(prev numeric[2], e1 numeric, e2 numeric)
RETURNS numeric[2] AS
$$
SELECT ARRAY[CASE WHEN $1[2] = 0 THEN 1*$3 ELSE $1[1] + POWER($2,$1[2])*$3 END, $1[2] + 1] ;
$$
LANGUAGE 'sql' IMMUTABLE;
CREATE OR REPLACE FUNCTION funky_final(last numeric[2])
RETURNS numeric AS
$$
SELECT $1[1];
$$
LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE funky_agg(numeric, numeric) (
SFUNC=funky_state,
STYPE=numeric[2],
FINALFUNC=funky_final,
INITCOND = '{1,0}'
);
--The foos
SELECT i,j,k FROM generate_series(0,2) As i
CROSS JOIN generate_series(1,2) As j
CROSS JOIN generate_series(0,1) As k
ORDER BY k,i,j;
i | j | k
---+---+---
0 | 1 | 0
0 | 2 | 0
1 | 1 | 0
1 | 2 | 0
2 | 1 | 0
2 | 2 | 0
0 | 1 | 1
0 | 2 | 1
1 | 1 | 1
1 | 2 | 1
2 | 1 | 1
2 | 2 | 1
(12 rows)
SELECT k ,
funky_agg(foo.i,foo.j + foo.k) As what
FROM (SELECT i,j,k FROM generate_series(0,2) As i
CROSS JOIN generate_series(1,2) As j
CROSS JOIN generate_series(0,1) As k
ORDER BY k,i,j) As foo
GROUP BY k
ORDER BY k;
k | what
---+----------------------
0 | 84.0000000000000000
1 | 135.0000000000000000
Checking our math by expanding by hand:
k = 0: SELECT 1*1 + POWER(0,1)*2 + POWER(1,2)*1 + POWER(1,3)*2
+ POWER(2,4)*1 + POWER(2,5)*2 ; --84
k = 1: SELECT 1*(1+1) + POWER(0,1)*(2 + 1) + POWER(1,2)*(1 + 1)
+ POWER(1,3)*(2+1) + POWER(2,4)*(1+1) + POWER(2,5)*(2+1) ; -- 135
PostGIS in Action
About the Authors
Consulting
Tracked: Jul 10, 19:39