Database Reference
In-Depth Information
used in the SQL query in the same manner as any other window function with the
specification of the
OVER
clause.
11.3.3 Ordered Aggregates
Sometimes the value of an aggregate may depend on an ordered set of values.
For example, to determine the median of a set of values, it is common to first
sort the values from smallest to largest and identify the median from the center
of the sorted values. The sorting can be accomplished by using the function
array_agg()
. The following SQL query calculates the median of the weekly sales
data.
SELECT (d.ord_sales[ d.n/2 + 1 ] +
d.ord_sales[ (d.n + 1)/2 ]) / 2.0 as median
FROM (SELECT ARRAY_AGG(s.sales ORDER BY s.sales) AS
ord_sales,
COUNT(*) AS n
FROM sales_by_week s
WHERE s.year = 2014
AND s.week <= 26) d
median
1551923.5
In general, the function
ARRAY_AGG()
builds an array from a table column.
Executing the subquery from the previous SQL query for just the first five weeks
illustrates the creation of the array, denoted by the braces, and the sorted weekly
sales within the array.
SELECT ARRAY_AGG(s.sales ORDER BY s.sales) AS ord_sales,
COUNT(*) AS n
FROM sales_by_week s
WHERE s.year = 2014
AND s.week <= 5
ord_sales n
{1564539,1572128,1580146,1582331,1600769} 5
