Aggregate Mode

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(See also)
m (correct intralink)
Line 35: Line 35:
 
Note: Returns error if the argument is a column of NULL values.
 
Note: Returns error if the argument is a column of NULL values.
 
== See also ==
 
== See also ==
[[Array Range]] [[Array Median]]
+
[[Aggregate Range]] [[Aggregate Median]]

Revision as of 09:32, 25 April 2013

Snippets

Aggregate Mode

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

by Scott Bailey 'Artacus'

Sometime we need to find the value that occurs most often in a group. Then mathematical concept is mode. PostgreSQL makes it easy to add custom aggregate functions.

Conceptually the process will be to gather each value into an array. Then once all values are in the array, we will run a function to find the most common value in our array.

If you are on PostgreSQL 8.3 or below you will need to add the unnest() function to convert an array to a set of rows.

Then you need to create a function to find the most common value in the array.

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, 1
    LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
 
-- Tell Postgres how to use our aggregate
CREATE AGGREGATE mode(anyelement) (
  SFUNC=array_append, --Function to call for each row. Just builds the array
  STYPE=anyarray,
  FINALFUNC=_final_mode, --Function to call after everything has been added to array
  INITCOND='{}' --Initialize an empty array when starting
);

Note: Returns error if the argument is a column of NULL values.

See also

Aggregate Range Aggregate Median

Personal tools