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:
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
How to create multi-column aggregates - Postgres OnLine Journal
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:
- Aug/Sept 2008: Build Median Aggregate Function in SQL
- Aug/Sept 2008: More Aggregate Fun: Who's on First and Who's on Last
- Feb/March 2009: PLPython Part 4: PLPython meets aggregates
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