Aggregate Mode

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
m (ops, categ)
Line 42: Line 42:
* [[Aggregate Median]]
* [[Aggregate Median]]
* <tt>most_common_vals()</tt> [ pg_stats] function  
* <tt>most_common_vals()</tt> [ pg_stats] function  
== External links ==
== External links ==
* [ A very simple custom aggregate]
* [ A very simple custom aggregate]
[[Category:{{{category|}}} Snippets]]

Latest revision as of 10:36, 25 April 2013

The statistical mode is the value that appears most often in a set of values.


[edit] mode()


Aggregate Mode

Works with PostgreSQL

Any version

Written in


Depends on


PostgreSQL makes it easy to add custom aggregate functions. This snippet is also part of the ulib_agg user-defined library.

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

[edit] Usage

SELECT mode(some_value) AS modal_value FROM t;

[edit] Caution

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

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.

[edit] See also

[edit] External links

Personal tools