Aggregate Mode

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
m (correct intralink)
(restructuring)
Line 1: Line 1:
{{SnippetInfo|Aggregate Mode|lang=SQL}}
+
The [http://en.wikipedia.org/wiki/Mode_%28statistics%29 statistical mode] is the value that appears most often in a set of values.
[[Category:SQL]]
+
<!-- Sometime we need to find the value that occurs most often in a group. Then mathematical concept is mode. -->
  
by Scott Bailey 'Artacus'
+
== mode() ==
 +
{{SnippetInfo2|Aggregate Mode|lang=SQL}}
  
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.
+
PostgreSQL makes it easy to add custom aggregate functions. This snippet is also part of the [[ulib_agg|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.
+
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.
 
+
If you are on PostgreSQL 8.3 or below you will need to add the [[Array Unnest|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.
+
  
 
<source lang="sql">
 
<source lang="sql">
Line 33: Line 30:
 
</source>
 
</source>
  
Note: Returns error if the argument is a column of NULL values.
+
=== Usage ===
 +
<source lang="sql">SELECT mode(some_value) AS modal_value FROM t;</source>
 +
 
 +
=== 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 [[Array Unnest|unnest()]] function to convert an array to a set of rows.
 +
 
 
== See also ==
 
== See also ==
[[Aggregate Range]] [[Aggregate Median]]
+
* [[Aggregate Range]]  
 +
* [[Aggregate Median]]
 +
* <tt>most_common_vals()</tt> [http://www.postgresql.org/docs/9.0/static/view-pg-stats.html pg_stats] function
 +
[[Category:SQL]]
 +
 
 +
== External links ==
 +
* [http://www.databasesoup.com/2013/04/a-very-simple-custom-aggregate.html A very simple custom aggregate]

Revision as of 10:13, 25 April 2013

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

Contents

mode()

Snippets

Aggregate Mode

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

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
$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
);

Usage

SELECT mode(some_value) AS modal_value FROM t;

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.

See also

External links

Personal tools