Aggregate Random

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(Undo revision 17436 by Boshomi (Talk))
(restructuring)
Line 1: Line 1:
{{SnippetInfo|Aggregate Random|lang=SQL}}
+
A random value is obtained by the [http://www.postgresql.org/docs/9.0/static/functions-math.html random()] buildin function, but sometimes, you want a random element from a grouping. 
[[Category:SQL]]
+
  
By Daniel Kahn Gillmor (dkg)
+
== random() ==
 +
{{SnippetInfo2|Aggregate Random|lang=SQL}}
  
Sometimes, you want a random element from a grouping.  This snippet allows you to use RANDOM() as an aggregate function. It should distribute the choices uniformly over each row in the grouping, whether the value selected is NULL or not.  (you could modify SFUNC if you wanted to have it select a random non-NULL element)
+
This snippet allows you to use <tt>random()</tt> as an aggregate function. It is also part of the [[ulib_agg|ulib_agg user-defined library]].
 +
 
 +
It should distribute the choices uniformly over each row in the grouping, whether the value selected is NULL or not.  (you could modify SFUNC if you wanted to have it select a random non-NULL element)
  
 
<source lang="sql">
 
<source lang="sql">
Line 21: Line 23:
 
);
 
);
 
</source>
 
</source>
 +
 +
[[Category:SQL]]
 +
[[Category:{{{category|}}} Snippets]]

Revision as of 10:34, 25 April 2013

A random value is obtained by the random() buildin function, but sometimes, you want a random element from a grouping.

random()

Snippets

Aggregate Random

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

This snippet allows you to use random() as an aggregate function. It is also part of the ulib_agg user-defined library.

It should distribute the choices uniformly over each row in the grouping, whether the value selected is NULL or not. (you could modify SFUNC if you wanted to have it select a random non-NULL element)

CREATE OR REPLACE FUNCTION _final_random(anyarray)           
 RETURNS anyelement AS
$BODY$
 SELECT $1[array_lower($1,1) + floor((1 + array_upper($1, 1) - array_lower($1, 1))*random())];
$BODY$
LANGUAGE 'sql' IMMUTABLE;
 
CREATE AGGREGATE random(anyelement) (
  SFUNC=array_append, --Function to call for each row. Just builds the array
  STYPE=anyarray,
  FINALFUNC=_final_random, --Function to call after everything has been added to array
  INITCOND='{}' --Initialize an empty array when starting
);
Personal tools