Postgres – adding custom aggregates most()

22 05 2009

Postgres allows you to define custom aggregates. In most cases the stock aggregate functions (AVG, MIN, MAX, SUM etc.) will meet our needs.  But lets say we have a table of teachers joined to the classes they teach and we want to group by teacher and see what subject area the teacher teaches. So what results will you get if the teacher has 5 English classes and 1 Science class? If you use MAX() she’ll show up as a Science teacher. What we want is the subject area that shows up most often.

Enter the most() aggregate. We’ll use it to find the value that occurs most in our group. We need a few helper functions for this.

The first we need to gather every item of the group into an array using array_append().

Then we need to process that array and return the element that occurs most often.

CREATE OR REPLACE FUNCTION _final_most(anyarray)
  RETURNS anyelement AS
$BODY$
    SELECT a
    FROM unnest($1) a
    GROUP BY 1 ORDER BY COUNT(1) DESC
    LIMIT 1;
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

Note that this makes use of the unnest() function added in 8.4. If to run in on an earlier version you’ll need to add it.

CREATE OR REPLACE FUNCTION unnest(anyarray)
  RETURNS SETOF anyelement AS
$BODY$
SELECT $1[i] FROM
    generate_series(array_lower($1,1),
                    array_upper($1,1)) i;
$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT

Now lets glue these together for our custom aggregate.

CREATE AGGREGATE most(anyelement) (
  SFUNC=array_append,
  STYPE=anyarray,
  FINALFUNC=_final_most,
  INITCOND='{}'
);

Use it just like you would min() or max().

** Update **
Dave Fetter suggested that most() should be called mode() since that is the corresponding concept in mathematics and statistics. I was focusing on text when I wrote it, so it never occurred to me. So here are the final functions to define mode, median and range.

CREATE OR REPLACE FUNCTION _final_mode(anyarray)
  RETURNS anyelement AS
$BODY$
    SELECT a
    FROM unnest($1) a
    GROUP BY 1 ORDER BY COUNT(1) DESC
    LIMIT 1;
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION _final_median(_numeric)
   RETURNS numeric AS
$$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val
     ORDER BY 1
     LIMIT  2 - MOD(array_upper($1, 1), 2)
     OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
   ) sub;
$$
  LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION _final_range(_numeric)
   RETURNS numeric AS
$$
   SELECT MAX(val) - MIN(val) 
   FROM unnest($1) val;
$$
LANGUAGE 'sql' IMMUTABLE;
About these ads

Actions

Information

3 responses

19 06 2009
Simplify XML Parsing in Postgres « Flex and Specs()

[…] feature in 8.4 (which should have been there all along). If you need to add it to 8.3 or below, see this […]

16 08 2012
Evan Stanford

Hi Scott,
This DOESN’T work if given one or more NULL values.
Any ideas?
-Evan

Example code (v8.2):
>Import your 3 functions
>create table NULLZ as (select 7 as value);
>insert into NULLZ values(NULL);
>insert into NULLZ values(NULL);
>insert into NULLZ values(NULL);
>select mode(value) from NULLZ;
ERROR: null array element where not supported (arrayfuncs.c:872)

8 07 2013
jason

An error occurred when executing the SQL command:
CREATE OR REPLACE FUNCTION _final_range(_numeric)
RETURNS numeric AS
$$
SELECT MAX(val) – MIN(val)
FROM unnest($1) val

ERROR: unterminated dollar-quoted string at or near “$$
SELECT MAX(val) – MIN(val)
FROM unnest($1) val”
Position: 73 [SQL State=42601]

Execution time: 0.02s
(Statement 1 of 2 finished)

An error occurred when executing the SQL command:
$$

ERROR: unterminated dollar-quoted string at or near “$$”
Position: 1 [SQL State=42601]

Execution time: 0.02s
(Statement 2 of 2 finished)

2 statement(s) failed.
Script execution finished
Total script execution time: 0.04s

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

%d bloggers like this: