From 8d65da1f01c6a4c84fe9c59aeb6b7e3adf870145 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 23 Dec 2013 16:11:35 -0500 Subject: Support ordered-set (WITHIN GROUP) aggregates. This patch introduces generic support for ordered-set and hypothetical-set aggregate functions, as well as implementations of the instances defined in SQL:2008 (percentile_cont(), percentile_disc(), rank(), dense_rank(), percent_rank(), cume_dist()). We also added mode() though it is not in the spec, as well as versions of percentile_cont() and percentile_disc() that can compute multiple percentile values in one pass over the data. Unlike the original submission, this patch puts full control of the sorting process in the hands of the aggregate's support functions. To allow the support functions to find out how they're supposed to sort, a new API function AggGetAggref() is added to nodeAgg.c. This allows retrieval of the aggregate call's Aggref node, which may have other uses beyond the immediate need. There is also support for ordered-set aggregates to install cleanup callback functions, so that they can be sure that infrastructure such as tuplesort objects gets cleaned up. In passing, make some fixes in the recently-added support for variadic aggregates, and make some editorial adjustments in the recent FILTER additions for aggregates. Also, simplify use of IsBinaryCoercible() by allowing it to succeed whenever the target type is ANY or ANYELEMENT. It was inconsistent that it dealt with other polymorphic target types but not these. Atri Sharma and Andrew Gierth; reviewed by Pavel Stehule and Vik Fearing, and rather heavily editorialized upon by Tom Lane --- doc/src/sgml/catalogs.sgml | 21 +++ doc/src/sgml/func.sgml | 296 ++++++++++++++++++++++++++++++++- doc/src/sgml/ref/alter_aggregate.sgml | 51 ++++-- doc/src/sgml/ref/alter_extension.sgml | 29 ++-- doc/src/sgml/ref/comment.sgml | 29 ++-- doc/src/sgml/ref/create_aggregate.sgml | 98 ++++++++++- doc/src/sgml/ref/drop_aggregate.sgml | 38 ++++- doc/src/sgml/ref/security_label.sgml | 29 ++-- doc/src/sgml/syntax.sgml | 109 +++++++++--- doc/src/sgml/xaggr.sgml | 159 +++++++++++++++--- 10 files changed, 735 insertions(+), 124 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index acc261ca516..558b41b9bc5 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -348,6 +348,27 @@ pg_proc.oid pg_proc OID of the aggregate function + + aggkind + char + + Aggregate kind: + n for normal aggregates, + o for ordered-set aggregates, or + h for hypothetical-set aggregates + + + + aggnumdirectargs + int2 + + Number of direct (non-aggregated) arguments of an ordered-set or + hypothetical-set aggregate, counting a variadic array as one argument. + If equal to pronargs, the aggregate must be variadic + and the variadic array describes the aggregated arguments as well as + the final direct arguments. + Always zero for normal aggregates. + aggtransfn regproc diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index a411e3a0cc9..8579bdd9480 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -11560,10 +11560,13 @@ NULL baz(3 rows) Aggregate functions compute a single result - from a set of input values. The built-in aggregate functions + from a set of input values. The built-in normal aggregate functions are listed in and . + The built-in ordered-set aggregate functions + are listed in and + . The special syntax considerations for aggregate functions are explained in . Consult for additional introductory @@ -12307,6 +12310,290 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + shows some + aggregate functions that use the ordered-set aggregate + syntax. These functions are sometimes referred to as inverse + distribution functions. + + + + ordered-set aggregate + built-in + + + inverse distribution + + + + Ordered-Set Aggregate Functions + + + + + Function + Direct Argument Type(s) + Aggregated Argument Type(s) + Return Type + Description + + + + + + + + + mode + statistical + + mode() WITHIN GROUP (ORDER BY sort_expression) + + + + + any sortable type + + + same as sort expression + + + returns the most frequent input value (arbitrarily choosing the first + one if there are multiple equally-frequent results) + + + + + + + percentile + continuous + + + median + + percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) + + + double precision + + + double precision or interval + + + same as sort expression + + + continuous percentile: returns a value corresponding to the specified + fraction in the ordering, interpolating between adjacent input items if + needed + + + + + + percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression) + + + double precision[] + + + double precision or interval + + + array of sort expression's type + + + multiple continuous percentile: returns an array of results matching + the shape of the fractions parameter, with each + non-null element replaced by the value corresponding to that percentile + + + + + + + percentile + discrete + + percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression) + + + double precision + + + any sortable type + + + same as sort expression + + + discrete percentile: returns the first input value whose position in + the ordering equals or exceeds the specified fraction + + + + + + percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression) + + + double precision[] + + + any sortable type + + + array of sort expression's type + + + multiple discrete percentile: returns an array of results matching the + shape of the fractions parameter, with each non-null + element replaced by the input value corresponding to that percentile + + + + + +
+ + + All the aggregates listed in + ignore null values in their sorted input. For those that take + a fraction parameter, the fraction value must be + between 0 and 1; an error is thrown if not. However, a null fraction value + simply produces a null result. + + + + Each of the aggregates listed in + is associated with a + window function of the same name defined in + . In each case, the aggregate result + is the value that the associated window function would have + returned for the hypothetical row constructed from + args, if such a row had been added to the sorted + group of rows computed from the sorted_args. + + + + Hypothetical-Set Aggregate Functions + + + + + Function + Direct Argument Type(s) + Aggregated Argument Type(s) + Return Type + Description + + + + + + + + + rank + hypothetical + + rank(args) WITHIN GROUP (ORDER BY sorted_args) + + + VARIADIC "any" + + + VARIADIC "any" + + + bigint + + + rank of the hypothetical row, with gaps for duplicate rows + + + + + + + dense_rank + hypothetical + + dense_rank(args) WITHIN GROUP (ORDER BY sorted_args) + + + VARIADIC "any" + + + VARIADIC "any" + + + bigint + + + rank of the hypothetical row, without gaps + + + + + + + percent_rank + hypothetical + + percent_rank(args) WITHIN GROUP (ORDER BY sorted_args) + + + VARIADIC "any" + + + VARIADIC "any" + + + double precision + + + relative rank of the hypothetical row, ranging from 0 to 1 + + + + + + + cume_dist + hypothetical + + cume_dist(args) WITHIN GROUP (ORDER BY sorted_args) + + + VARIADIC "any" + + + VARIADIC "any" + + + double precision + + + relative rank of the hypothetical row, ranging from + 1/N to 1 + + + + + +
+ + + For each of these hypothetical-set aggregates, the list of direct arguments + given in args must match the number and types of + the aggregated arguments given in sorted_args. + Unlike most built-in aggregates, these aggregates are not strict, that is + they do not drop input rows containing nulls. Null values sort according + to the rule specified in the ORDER BY clause. + + @@ -12332,9 +12619,10 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; - In addition to these functions, any built-in or user-defined aggregate - function can be used as a window function (see - for a list of the built-in aggregates). + In addition to these functions, any built-in or user-defined normal + aggregate function (but not ordered-set or hypothetical-set aggregates) + can be used as a window function; see + for a list of the built-in aggregates. Aggregate functions act as window functions only when an OVER clause follows the call; otherwise they act as regular aggregates. diff --git a/doc/src/sgml/ref/alter_aggregate.sgml b/doc/src/sgml/ref/alter_aggregate.sgml index aab5b2b695e..641d19ccebe 100644 --- a/doc/src/sgml/ref/alter_aggregate.sgml +++ b/doc/src/sgml/ref/alter_aggregate.sgml @@ -21,12 +21,15 @@ PostgreSQL documentation -ALTER AGGREGATE name ( [ argmode ] [ arg_name ] arg_data_type [ , ... ] ) - RENAME TO new_name -ALTER AGGREGATE name ( [ argmode ] [ arg_name ] arg_data_type [ , ... ] ) - OWNER TO new_owner -ALTER AGGREGATE name ( [ argmode ] [ arg_name ] arg_data_type [ , ... ] ) - SET SCHEMA new_schema +ALTER AGGREGATE name ( aggregate_signature ) RENAME TO new_name +ALTER AGGREGATE name ( aggregate_signature ) OWNER TO new_owner +ALTER AGGREGATE name ( aggregate_signature ) SET SCHEMA new_schema + +where aggregate_signature is: + +* | +[ argmode ] [ argname ] argtype [ , ... ] | +[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ] @@ -76,7 +79,7 @@ ALTER AGGREGATE name ( [ argmode - arg_name + argname @@ -89,12 +92,15 @@ ALTER AGGREGATE name ( [ argmode - arg_data_type + argtype An input data type on which the aggregate function operates. To reference a zero-argument aggregate function, write * in place of the list of argument specifications. + To reference an ordered-set aggregate function, write + ORDER BY between the direct and aggregated argument + specifications. @@ -128,6 +134,21 @@ ALTER AGGREGATE name ( [ argmode + + Notes + + + The recommended syntax for referencing an ordered-set aggregate + is to write ORDER BY between the direct and aggregated + argument specifications, in the same style as in + . However, it will also work to + omit ORDER BY and just run the direct and aggregated + argument specifications into a single list. In this abbreviated form, + if VARIADIC "any" was used in both the direct and + aggregated argument lists, write VARIADIC "any" only once. + + + Examples @@ -148,11 +169,17 @@ ALTER AGGREGATE myavg(integer) OWNER TO joe; - To move the aggregate function myavg for type - integer into schema myschema: + To move the ordered-set aggregate mypercentile with + direct argument of type float8 and aggregated argument + of type integer into schema myschema: -ALTER AGGREGATE myavg(integer) SET SCHEMA myschema; - +ALTER AGGREGATE mypercentile(float8 ORDER BY integer) SET SCHEMA myschema; + + This will work too: + +ALTER AGGREGATE mypercentile(float8, integer) SET SCHEMA myschema; + + diff --git a/doc/src/sgml/ref/alter_extension.sgml b/doc/src/sgml/ref/alter_extension.sgml index a14fcb48683..56fa30d2d3a 100644 --- a/doc/src/sgml/ref/alter_extension.sgml +++ b/doc/src/sgml/ref/alter_extension.sgml @@ -30,7 +30,7 @@ ALTER EXTENSION name DROP where member_object is: - AGGREGATE agg_name ( [ argmode ] [ argname ] agg_type [, ...] ) | + AGGREGATE aggregate_name ( aggregate_signature ) | CAST (source_type AS target_type) | COLLATION object_name | CONVERSION object_name | @@ -54,6 +54,12 @@ ALTER EXTENSION name DROP object_name | TYPE object_name | VIEW object_name + +and aggregate_signature is: + +* | +[ argmode ] [ argname ] argtype [ , ... ] | +[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ] @@ -159,7 +165,7 @@ ALTER EXTENSION name DROP object_name - agg_name + aggregate_name function_name operator_name @@ -173,17 +179,6 @@ ALTER EXTENSION name DROP - - agg_type - - - An input data type on which the aggregate function operates. - To reference a zero-argument aggregate function, write * - in place of the list of argument specifications. - - - - source_type @@ -207,7 +202,8 @@ ALTER EXTENSION name DROP - The mode of a function argument: IN, OUT, + The mode of a function or aggregate + argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. Note that ALTER EXTENSION does not actually pay @@ -224,7 +220,7 @@ ALTER EXTENSION name DROP - The name of a function argument. + The name of a function or aggregate argument. Note that ALTER EXTENSION does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity. @@ -237,8 +233,7 @@ ALTER EXTENSION name DROP - The data type(s) of the function's arguments (optionally - schema-qualified), if any. + The data type of a function or aggregate argument. diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index e55050042a8..1b2949f64f1 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -23,7 +23,7 @@ PostgreSQL documentation COMMENT ON { - AGGREGATE agg_name ( [ argmode ] [ argname ] agg_type [, ...] ) | + AGGREGATE aggregate_name ( aggregate_signature ) | CAST (source_type AS target_type) | COLLATION object_name | COLUMN relation_name.column_name | @@ -58,6 +58,12 @@ COMMENT ON TYPE object_name | VIEW object_name } IS 'text' + +where aggregate_signature is: + +* | +[ argmode ] [ argname ] argtype [ , ... ] | +[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ] @@ -101,7 +107,7 @@ COMMENT ON object_name relation_name.column_name - agg_name + aggregate_name constraint_name function_name operator_name @@ -120,17 +126,6 @@ COMMENT ON - - agg_type - - - An input data type on which the aggregate function operates. - To reference a zero-argument aggregate function, write * - in place of the list of argument specifications. - - - - source_type @@ -153,7 +148,8 @@ COMMENT ON argmode - The mode of a function argument: IN, OUT, + The mode of a function or aggregate + argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. Note that COMMENT does not actually pay @@ -169,7 +165,7 @@ COMMENT ON argname - The name of a function argument. + The name of a function or aggregate argument. Note that COMMENT does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity. @@ -181,8 +177,7 @@ COMMENT ON argtype - The data type(s) of the function's arguments (optionally - schema-qualified), if any. + The data type of a function or aggregate argument. diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml index 17819dd1a8e..d15fcbae959 100644 --- a/doc/src/sgml/ref/create_aggregate.sgml +++ b/doc/src/sgml/ref/create_aggregate.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -CREATE AGGREGATE name ( [ argmode ] [ arg_name ] arg_data_type [ , ... ] ) ( +CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) ( SFUNC = sfunc, STYPE = state_data_type [ , SSPACE = state_data_size ] @@ -30,6 +30,16 @@ CREATE AGGREGATE name ( [ sort_operator ] ) +CREATE AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ] + ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) ( + SFUNC = sfunc, + STYPE = state_data_type + [ , SSPACE = state_data_size ] + [ , FINALFUNC = ffunc ] + [ , INITCOND = initial_condition ] + [ , HYPOTHETICAL ] +) + or the old syntax CREATE AGGREGATE name ( @@ -69,6 +79,8 @@ CREATE AGGREGATE name ( name and input data type(s) of an aggregate must also be distinct from the name and input data type(s) of every ordinary function in the same schema. + This behavior is identical to overloading of ordinary function names + (see ). @@ -128,7 +140,7 @@ CREATE AGGREGATE name ( If the state transition function is not strict, then it will be called unconditionally at each input row, and must deal with null inputs - and null transition values for itself. This allows the aggregate + and null state values for itself. This allows the aggregate author to have full control over the aggregate's handling of null values. @@ -142,6 +154,22 @@ CREATE AGGREGATE name ( input rows. + + The syntax with ORDER BY in the parameter list creates + a special type of aggregate called an ordered-set + aggregate; or if HYPOTHETICAL is specified, then + a hypothetical-set aggregate is created. These + aggregates operate over groups of sorted values in order-dependent ways, + so that specification of an input sort order is an essential part of a + call. Also, they can have direct arguments, which are + arguments that are evaluated only once per aggregation rather than once + per input row. Hypothetical-set aggregates are a subclass of ordered-set + aggregates in which some of the direct arguments are required to match, + in number and datatypes, the aggregated argument columns. This allows + the values of those direct arguments to be added to the collection of + aggregate-input rows as an additional hypothetical row. + + Aggregates that behave like MIN or MAX can sometimes be optimized by looking into an index instead of scanning every @@ -202,7 +230,7 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; - arg_name + argname @@ -234,6 +262,7 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; only one input parameter. To define a zero-argument aggregate function with this syntax, specify the basetype as "ANY" (not *). + Ordered-set aggregates cannot be defined with the old syntax. @@ -243,7 +272,7 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; The name of the state transition function to be called for each - input row. For an N-argument + input row. For a normal N-argument aggregate function, the sfunc must take N+1 arguments, the first being of type + + + For ordered-set (including hypothetical-set) aggregates, the state + transition function receives only the current state value and the + aggregated arguments, not the direct arguments. Otherwise it is the + same. + @@ -287,7 +323,8 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; The name of the final function called to compute the aggregate's - result after all input rows have been traversed. The function + result after all input rows have been traversed. + For a normal aggregate, this function must take a single argument of type state_data_type. The return data type of the aggregate is defined as the return type of this @@ -296,6 +333,17 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; aggregate's result, and the return type is state_data_type. + + + For ordered-set (including hypothetical-set) aggregates, the + final function receives not only the final state value, + but also the values of all the direct arguments, followed by + null values corresponding to each aggregated argument. + (The reason for including the aggregated arguments in the function + signature is that this may be necessary to allow correct resolution + of the aggregate result type, when a polymorphic aggregate is + being defined.) + @@ -319,7 +367,22 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; MAX-like aggregate. This is just an operator name (possibly schema-qualified). The operator is assumed to have the same input data types as - the aggregate (which must be a single-argument aggregate). + the aggregate (which must be a single-argument normal aggregate). + + + + + + HYPOTHETICAL + + + For ordered-set aggregates only, this flag specifies that the aggregate + arguments are to be processed according to the requirements for + hypothetical-set aggregates: that is, the last few direct arguments must + match the data types of the aggregated (WITHIN GROUP) + arguments. The HYPOTHETICAL flag has no effect on + run-time behavior, only on parse-time resolution of the data types and + collations of the aggregate's arguments. @@ -331,6 +394,29 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1; + + Notes + + + The syntax for ordered-set aggregates allows VARIADIC + to be specified for both the last direct parameter and the last + aggregated (WITHIN GROUP) parameter. However, the + current implementation restricts use of VARIADIC + in two ways. First, ordered-set aggregates can only use + VARIADIC "any", not other variadic array types. + Second, if the last direct parameter is VARIADIC "any", + then there can be only one aggregated parameter and it must also + be VARIADIC "any". (In the representation used in the + system catalogs, these two parameters are merged into a single + VARIADIC "any" item, since pg_proc cannot + represent functions with more than one VARIADIC parameter.) + If the aggregate is a hypothetical-set aggregate, the direct arguments + that match the VARIADIC "any" parameter are the hypothetical + ones; any preceding parameters represent additional direct arguments + that are not constrained to match the aggregated arguments. + + + Examples diff --git a/doc/src/sgml/ref/drop_aggregate.sgml b/doc/src/sgml/ref/drop_aggregate.sgml index 06060fb4f7b..e89e76268fe 100644 --- a/doc/src/sgml/ref/drop_aggregate.sgml +++ b/doc/src/sgml/ref/drop_aggregate.sgml @@ -21,9 +21,13 @@ PostgreSQL documentation -DROP AGGREGATE [ IF EXISTS ] - name ( [ argmode ] [ arg_name ] arg_data_type [ , ... ] ) - [ CASCADE | RESTRICT ] +DROP AGGREGATE [ IF EXISTS ] name ( aggregate_signature ) [ CASCADE | RESTRICT ] + +where aggregate_signature is: + +* | +[ argmode ] [ argname ] argtype [ , ... ] | +[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ] @@ -73,7 +77,7 @@ DROP AGGREGATE [ IF EXISTS ] - arg_name + argname @@ -86,12 +90,15 @@ DROP AGGREGATE [ IF EXISTS ] - arg_data_type + argtype An input data type on which the aggregate function operates. To reference a zero-argument aggregate function, write * in place of the list of argument specifications. + To reference an ordered-set aggregate function, write + ORDER BY between the direct and aggregated argument + specifications. @@ -117,6 +124,15 @@ DROP AGGREGATE [ IF EXISTS ] + + Notes + + + Alternative syntaxes for referencing ordered-set aggregates + are described under . + + + Examples @@ -125,7 +141,17 @@ DROP AGGREGATE [ IF EXISTS ] integer: DROP AGGREGATE myavg(integer); - + + + + + To remove the hypothetical-set aggregate function myrank, + which takes an arbitrary list of ordering columns and a matching list + of direct arguments: + +DROP AGGREGATE myrank(VARIADIC "any" ORDER BY VARIADIC "any"); + + diff --git a/doc/src/sgml/ref/security_label.sgml b/doc/src/sgml/ref/security_label.sgml index 76c131f94ee..d7595654e4d 100644 --- a/doc/src/sgml/ref/security_label.sgml +++ b/doc/src/sgml/ref/security_label.sgml @@ -25,7 +25,7 @@ SECURITY LABEL [ FOR provider ] ON { TABLE object_name | COLUMN table_name.column_name | - AGGREGATE agg_name ( [ argmode ] [ argname ] agg_type [, ...] ) | + AGGREGATE aggregate_name ( aggregate_signature ) | DATABASE object_name | DOMAIN object_name | EVENT TRIGGER object_name | @@ -41,6 +41,12 @@ SECURITY LABEL [ FOR provider ] ON TYPE object_name | VIEW object_name } IS 'label' + +where aggregate_signature is: + +* | +[ argmode ] [ argname ] argtype [ , ... ] | +[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ] @@ -83,7 +89,7 @@ SECURITY LABEL [ FOR provider ] ON object_name table_name.column_name - agg_name + aggregate_name function_name @@ -106,23 +112,13 @@ SECURITY LABEL [ FOR provider ] ON - - agg_type - - - An input data type on which the aggregate function operates. - To reference a zero-argument aggregate function, write * - in place of the list of argument specifications. - - - - argmode - The mode of a function argument: IN, OUT, + The mode of a function or aggregate + argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN. Note that SECURITY LABEL does not actually @@ -139,7 +135,7 @@ SECURITY LABEL [ FOR provider ] ON - The name of a function argument. + The name of a function or aggregate argument. Note that SECURITY LABEL does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity. @@ -152,8 +148,7 @@ SECURITY LABEL [ FOR provider ] ON - The data type(s) of the function's arguments (optionally - schema-qualified), if any. + The data type of a function or aggregate argument. diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 4f50f431475..434a8941577 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1555,7 +1555,15 @@ sqrt(2) - filter + ordered-set aggregate + + + + WITHIN GROUP + + + + FILTER @@ -1570,6 +1578,7 @@ sqrt(2) aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ] +aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ] where aggregate_name is a previously @@ -1589,9 +1598,11 @@ sqrt(2) The third form invokes the aggregate once for each distinct value of the expression (or distinct set of values, for multiple expressions) found in the input rows. - The last form invokes the aggregate once for each input row; since no + The fourth form invokes the aggregate once for each input row; since no particular input value is specified, it is generally only useful for the count(*) aggregate function. + The last form is used with ordered-set aggregate + functions, which are described below. @@ -1610,23 +1621,6 @@ sqrt(2) distinct non-null values of f1. - - If FILTER is specified, then only the input - rows for which the filter_clause - evaluates to true are fed to the aggregate function; other rows - are discarded. For example: - -SELECT - count(*) AS unfiltered, - count(*) FILTER (WHERE i < 5) AS filtered -FROM generate_series(1,10) AS s(i); - unfiltered | filtered -------------+---------- - 10 | 4 -(1 row) - - - Ordinarily, the input rows are fed to the aggregate function in an unspecified order. In many cases this does not matter; for example, @@ -1676,6 +1670,71 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect + + Placing ORDER BY within the aggregate's regular argument + list, as described so far, is used when ordering the input rows for + a normal aggregate for which ordering is optional. There is a + subclass of aggregate functions called ordered-set + aggregates for which an order_by_clause + is required, usually because the aggregate's computation is + only sensible in terms of a specific ordering of its input rows. + Typical examples of ordered-set aggregates include rank and percentile + calculations. For an ordered-set aggregate, + the order_by_clause is written + inside WITHIN GROUP (...), as shown in the final syntax + alternative above. The expressions in + the order_by_clause are evaluated once per + input row just like normal aggregate arguments, sorted as per + the order_by_clause's requirements, and fed + to the aggregate function as input arguments. (This is unlike the case + for a non-WITHIN GROUP order_by_clause, + which is not treated as argument(s) to the aggregate function.) The + argument expressions preceding WITHIN GROUP, if any, are + called direct arguments to distinguish them from + the aggregated arguments listed in + the order_by_clause. Unlike normal aggregate + arguments, direct arguments are evaluated only once per aggregate call, + not once per input row. This means that they can contain variables only + if those variables are grouped by GROUP BY; this restriction + is the same as if the direct arguments were not inside an aggregate + expression at all. Direct arguments are typically used for things like + percentile fractions, which only make sense as a single value per + aggregation calculation. The direct argument list can be empty; in this + case, write just () not (*). + (PostgreSQL will actually accept either spelling, but + only the first way conforms to the SQL standard.) + An example of an ordered-set aggregate call is: + + +SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households; + percentile_disc +----------------- + 50489 + + + which obtains the 50th percentile, or median, value of + the income column from table households. + Here, 0.5 is a direct argument; it would make no sense + for the percentile fraction to be a value varying across rows. + + + + If FILTER is specified, then only the input + rows for which the filter_clause + evaluates to true are fed to the aggregate function; other rows + are discarded. For example: + +SELECT + count(*) AS unfiltered, + count(*) FILTER (WHERE i < 5) AS filtered +FROM generate_series(1,10) AS s(i); + unfiltered | filtered +------------+---------- + 10 | 4 +(1 row) + + + The predefined aggregate functions are described in . Other aggregate functions can be added @@ -1695,7 +1754,8 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect and ), the aggregate is normally evaluated over the rows of the subquery. But an exception occurs - if the aggregate's arguments contain only outer-level variables: + if the aggregate's arguments (and filter_clause + if any) contain only outer-level variables: the aggregate then belongs to the nearest such outer level, and is evaluated over the rows of that query. The aggregate expression as a whole is then an outer reference for the subquery it appears in, @@ -1856,15 +1916,16 @@ UNBOUNDED FOLLOWING If FILTER is specified, then only the input rows for which the filter_clause evaluates to true are fed to the window function; other rows - are discarded. Only aggregate window functions accept + are discarded. Only window functions that are aggregates accept a FILTER clause. The built-in window functions are described in . Other window functions can be added by - the user. Also, any built-in or user-defined aggregate function can be - used as a window function. + the user. Also, any built-in or user-defined normal aggregate function + can be used as a window function. Ordered-set aggregates presently + cannot be used as window functions, however. @@ -1885,7 +1946,7 @@ UNBOUNDED FOLLOWING More information about window functions can be found in , - , + , and . diff --git a/doc/src/sgml/xaggr.sgml b/doc/src/sgml/xaggr.sgml index 9ed7d99f7c0..e77ef12e5c3 100644 --- a/doc/src/sgml/xaggr.sgml +++ b/doc/src/sgml/xaggr.sgml @@ -9,20 +9,27 @@ - Aggregate functions in PostgreSQL - are expressed in terms of state values + Aggregate functions in PostgreSQL + are defined in terms of state values and state transition functions. That is, an aggregate operates using a state value that is updated as each successive input row is processed. To define a new aggregate function, one selects a data type for the state value, an initial value for the state, and a state transition - function. The state transition function is just an - ordinary function that could also be used outside the - context of the aggregate. A final function + function. The state transition function takes the previous state + value and the aggregate's input value(s) for the current row, and + returns a new state value. + A final function can also be specified, in case the desired result of the aggregate is different from the data that needs to be kept in the running - state value. + state value. The final function takes the last state value + and returns whatever is wanted as the aggregate result. + In principle, the transition and final functions are just ordinary + functions that could also be used outside the context of the + aggregate. (In practice, it's often helpful for performance reasons + to create specialized transition functions that can only work when + called as part of an aggregate.) @@ -42,20 +49,24 @@ we only need the addition function for that data type. The aggregate definition would be: - + CREATE AGGREGATE sum (complex) ( sfunc = complex_add, stype = complex, initcond = '(0,0)' ); + + + which we might use like this: + SELECT sum(a) FROM test_complex; sum ----------- (34,53.9) - + (Notice that we are relying on function overloading: there is more than one aggregate named sum, but @@ -64,19 +75,19 @@ SELECT sum(a) FROM test_complex; - The above definition of sum will return zero (the initial - state condition) if there are no nonnull input values. + The above definition of sum will return zero + (the initial state value) if there are no nonnull input values. Perhaps we want to return null in that case instead — the SQL standard expects sum to behave that way. We can do this simply by omitting the initcond phrase, so that the initial state - condition is null. Ordinarily this would mean that the sfunc - would need to check for a null state-condition input. But for + value is null. Ordinarily this would mean that the sfunc + would need to check for a null state-value input. But for sum and some other simple aggregates like max and min, it is sufficient to insert the first nonnull input value into the state variable and then start applying the transition function at the second nonnull input value. PostgreSQL - will do that automatically if the initial condition is null and + will do that automatically if the initial state value is null and the transition function is marked strict (i.e., not to be called for null inputs). @@ -109,12 +120,16 @@ CREATE AGGREGATE avg (float8) initcond = '{0,0,0}' ); + - (float8_accum requires a three-element array, not just + + + float8_accum requires a three-element array, not just two elements, because it accumulates the sum of squares as well as the sum and count of the inputs. This is so that it can be used for - some other aggregates besides avg.) - + some other aggregates besides avg. + + Aggregate functions can use polymorphic @@ -136,7 +151,7 @@ CREATE AGGREGATE array_accum (anyelement) ); - Here, the actual state type for any aggregate call is the array type + Here, the actual state type for any given aggregate call is the array type having the actual input type as elements. The behavior of the aggregate is to concatenate all the inputs into an array of that type. (Note: the built-in aggregate array_agg provides similar @@ -205,6 +220,97 @@ SELECT myaggregate(a, b, c ORDER BY a) FROM ... + + Aggregate function calls in SQL allow DISTINCT + and ORDER BY options that control which rows are fed + to the aggregate's transition function and in what order. These + options are implemented behind the scenes and are not the concern + of the aggregate's support functions. + + + + The aggregates we have been describing so far are normal + aggregates. PostgreSQL also + supports ordered-set aggregates, which differ from + normal aggregates in two key ways. First, in addition to ordinary + aggregated arguments that are evaluated once per input row, an + ordered-set aggregate can have direct arguments that are + evaluated only once per aggregation operation. Second, the syntax + for the ordinary aggregated arguments specifies a sort ordering + for them explicitly. An ordered-set aggregate is usually + used to implement a computation that depends on a specific row + ordering, for instance rank or percentile, so that the sort ordering + is a required aspect of any call. For example, the built-in + definition of percentile_disc is equivalent to: + + +CREATE AGGREGATE percentile_disc (float8 ORDER BY anyelement) +( + sfunc = ordered_set_transition, + stype = internal, + finalfunc = percentile_disc_final +); + + + which could be used to obtain a median household income like this: + + +SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households; + percentile_disc +----------------- + 50489 + + + Here, 0.5 is a direct argument; it would make no sense + for the percentile fraction to be a value varying across rows. + + + + Unlike the case for normal aggregates, the sorting of input rows for + an ordered-set aggregate is not done behind the scenes, + but is the responsibility of the aggregate's support functions. + The typical implementation approach is to keep a reference to + a tuplesort object in the aggregate's state value, feed the + incoming rows into that object, and then complete the sorting and + read out the data in the final function. This design allows the + final function to perform special operations such as injecting + additional hypothetical rows into the data to be sorted. + While normal aggregates can often be implemented with support + functions written in PL/pgSQL or another + PL language, ordered-set aggregates generally have to be written in + C, since their state values aren't definable as any SQL datatype. + (In the above example, notice that the state value is declared as + type internal — this is typical.) + + + + The state transition function for an ordered-set aggregate receives + the current state value plus the aggregated input values for + each row, and returns the updated state value. This is the + same definition as for normal aggregates, but note that the direct + arguments (if any) are not provided. The final function receives + the last state value, the values of the direct arguments if any, + and null values corresponding to the aggregated input(s). While the + null values seem useless at first sight, they are important because + they make it possible to include the data types of the aggregated + input(s) in the final function's signature, which may be necessary + to resolve the output type of a polymorphic aggregate. For example, + the built-in mode() ordered-set aggregate takes a + single aggregated column of any sortable data type and returns a + value of that same type. This is possible because the final function + is declared as mode_final(internal, anyelement) returns + anyelement, with the anyelement parameter corresponding + to the dummy null argument that represents the aggregated column. + The actual data is conveyed in the internal-type state + value, but type resolution needs a parse-time indication of what the + result data type will be, and the dummy argument provides that. + In the example of percentile_disc, the support functions + are respectively declared as + ordered_set_transition(internal, "any") returns internal + and percentile_disc_final(internal, float8, anyelement) + returns anyelement. + + A function written in C can detect that it is being called as an aggregate transition or final function by calling @@ -214,14 +320,25 @@ if (AggCheckCallContext(fcinfo, NULL)) One reason for checking this is that when it is true for a transition function, the first input - must be a temporary transition value and can therefore safely be modified + must be a temporary state value and can therefore safely be modified in-place rather than allocating a new copy. See int8inc() for an example. (This is the only case where it is safe for a function to modify a pass-by-reference input. - In particular, aggregate final functions should not modify their inputs in - any case, because in some cases they will be re-executed on the same - final transition value.) + In particular, final functions for normal aggregates must not + modify their inputs in any case, because in some cases they will be + re-executed on the same final state value.) + + + + Another support routine available to aggregate functions written in C + is AggGetAggref, which returns the Aggref + parse node that defines the aggregate call. This is mainly useful + for ordered-set aggregates, which can inspect the substructure of + the Aggref node to find out what sort ordering they are + supposed to implement. Examples can be found + in orderedsetaggs.c in the PostgreSQL + source code. -- cgit v1.2.3