How to create multi-column aggregates

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

How to create multi-column aggregates - Postgres OnLine Journal
      How to create multi-column aggregates

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