Aggregate Range

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
m
 
Line 1: Line 1:
{{SnippetInfo|Aggregate Range|lang=SQL}}
+
...?? ... Same as
[[Category:SQL]]
+
  
by Scott Bailey 'Artacus'
+
<source lang="sql">SELECT MAX(num_value)-MIN(num_value) AS range_val FROM t;</source>
 +
 
 +
== range() ==
 +
{{SnippetInfo2|Aggregate Range|lang=SQL}}
  
 
<source lang="sql">
 
<source lang="sql">
 
CREATE OR REPLACE FUNCTION _final_range(numeric[])
 
CREATE OR REPLACE FUNCTION _final_range(numeric[])
   RETURNS numeric AS
+
   RETURNS numeric AS $$
$$
+
 
   SELECT MAX(val) - MIN(val)
 
   SELECT MAX(val) - MIN(val)
 
   FROM unnest($1) val;
 
   FROM unnest($1) val;
$$
+
$$ LANGUAGE 'sql' IMMUTABLE;
LANGUAGE 'sql' IMMUTABLE;
+
  
 
-- Add aggregate
 
-- Add aggregate
Line 22: Line 22:
 
</source>
 
</source>
  
 +
=== Usage ===
 +
<source lang="sql">SELECT range(num_value) AS range_val FROM t;</source>
 +
 +
=== Caution ===
 
If you are on PostgreSQL 8.3 or earlier, you will need to add the [[Array Unnest]] function.
 
If you are on PostgreSQL 8.3 or earlier, you will need to add the [[Array Unnest]] function.
  
 
=== See Also ===
 
=== See Also ===
 +
* [[Aggregate Mode]]
 +
* [[Aggregate Median]]
  
[[Aggregate Mode]]
+
[[Category:SQL]]
[[Aggregate Median]]
+
[[Category:{{{category|}}} Snippets]]

Latest revision as of 11:15, 25 April 2013

...?? ... Same as

SELECT MAX(num_value)-MIN(num_value) AS range_val FROM t;

Contents

[edit] range()

Snippets

Aggregate Range

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

CREATE OR REPLACE FUNCTION _final_range(numeric[])
   RETURNS numeric AS $$
   SELECT MAX(val) - MIN(val)
   FROM unnest($1) val;
$$ LANGUAGE 'sql' IMMUTABLE;
 
-- Add aggregate
CREATE AGGREGATE range(numeric) (
  SFUNC=array_append, --Function to call for each row. Just builds the array
  STYPE=numeric[],
  FINALFUNC=_final_range, --Function to call after everything has been added to array
  INITCOND='{}' --Initialize an empty array when starting
);

[edit] Usage

SELECT range(num_value) AS range_val FROM t;

[edit] Caution

If you are on PostgreSQL 8.3 or earlier, you will need to add the Array Unnest function.

[edit] See Also

Personal tools