From b560ec1b0d7b910ce13edc51ffaafaca72136e3b Mon Sep 17 00:00:00 2001 From: Noah Misch Date: Tue, 16 Jul 2013 20:15:36 -0400 Subject: Implement the FILTER clause for aggregate function calls. This is SQL-standard with a few extensions, namely support for subqueries and outer references in clause expressions. catversion bump due to change in Aggref and WindowFunc. David Fetter, reviewed by Dean Rasheed. --- doc/src/sgml/keywords.sgml | 2 +- doc/src/sgml/ref/select.sgml | 5 +++++ doc/src/sgml/syntax.sgml | 48 ++++++++++++++++++++++++++++++++------------ 3 files changed, 41 insertions(+), 14 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml index 059c8e468f4..ecfde993da3 100644 --- a/doc/src/sgml/keywords.sgml +++ b/doc/src/sgml/keywords.sgml @@ -1786,7 +1786,7 @@ FILTER - + non-reserved reserved reserved diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 68309ba94da..b0cec1421ca 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -598,6 +598,11 @@ GROUP BY expression [, ...] making up each group, producing a separate value for each group (whereas without GROUP BY, an aggregate produces a single value computed across all the selected rows). + The set of rows fed to the aggregate function can be further filtered by + attaching a FILTER clause to the aggregate function + call; see for more information. When + a FILTER clause is present, only those rows matching it + are included. When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or if the diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index b1392124a9e..803ed855c82 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1554,6 +1554,10 @@ sqrt(2) invocation + + filter + + An aggregate expression represents the application of an aggregate function across the rows selected by a @@ -1562,19 +1566,19 @@ sqrt(2) syntax of an aggregate expression is one of the following: -aggregate_name (expression [ , ... ] [ order_by_clause ] ) -aggregate_name (ALL expression [ , ... ] [ order_by_clause ] ) -aggregate_name (DISTINCT expression [ , ... ] [ order_by_clause ] ) -aggregate_name ( * ) +aggregate_name (expression [ , ... ] [ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ] +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 ) ] where aggregate_name is a previously - defined aggregate (possibly qualified with a schema name), + defined aggregate (possibly qualified with a schema name) and expression is any value expression that does not itself contain an aggregate - expression or a window function call, and - order_by_clause is a optional - ORDER BY clause as described below. + expression or a window function call. The optional + order_by_clause and + filter_clause are described below. @@ -1606,6 +1610,23 @@ 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, @@ -1709,10 +1730,10 @@ SELECT string_agg(a ORDER BY a, ',') FROM table; -- incorrect The syntax of a window function call is one of the following: -function_name (expression , expression ... ) OVER ( window_definition ) -function_name (expression , expression ... ) OVER window_name -function_name ( * ) OVER ( window_definition ) -function_name ( * ) OVER window_name +function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) +function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER window_name +function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition ) +function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name where window_definition has the syntax @@ -1836,7 +1857,8 @@ UNBOUNDED FOLLOWING 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. + used as a window function. Only aggregate window functions accept + a FILTER clause. -- cgit v1.2.3