Skip to content

Commit b560ec1

Browse files
nmischnmisch
committed
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.
1 parent 7a8e9f2 commit b560ec1

35 files changed

+403
-51
lines changed

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1546,6 +1546,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
15461546
JumbleExpr(jstate, (Node *) expr->args);
15471547
JumbleExpr(jstate, (Node *) expr->aggorder);
15481548
JumbleExpr(jstate, (Node *) expr->aggdistinct);
1549+
JumbleExpr(jstate, (Node *) expr->aggfilter);
15491550
}
15501551
break;
15511552
case T_WindowFunc:
@@ -1555,6 +1556,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
15551556
APP_JUMB(expr->winfnoid);
15561557
APP_JUMB(expr->winref);
15571558
JumbleExpr(jstate, (Node *) expr->args);
1559+
JumbleExpr(jstate, (Node *) expr->aggfilter);
15581560
}
15591561
break;
15601562
case T_ArrayRef:

‎doc/src/sgml/keywords.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1786,7 +1786,7 @@
17861786
</row>
17871787
<row>
17881788
<entry><token>FILTER</token></entry>
1789-
<entry></entry>
1789+
<entry>non-reserved</entry>
17901790
<entry>reserved</entry>
17911791
<entry>reserved</entry>
17921792
<entry></entry>

‎doc/src/sgml/ref/select.sgml

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -598,6 +598,11 @@ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...]
598598
making up each group, producing a separate value for each group
599599
(whereas without <literal>GROUP BY</literal>, an aggregate
600600
produces a single value computed across all the selected rows).
601+
The set of rows fed to the aggregate function can be further filtered by
602+
attaching a <literal>FILTER</literal> clause to the aggregate function
603+
call; see <xref linkend="syntax-aggregates"> for more information. When
604+
a <literal>FILTER</literal> clause is present, only those rows matching it
605+
are included.
601606
When <literal>GROUP BY</literal> is present, it is not valid for
602607
the <command>SELECT</command> list expressions to refer to
603608
ungrouped columns except within aggregate functions or if the

‎doc/src/sgml/syntax.sgml

Lines changed: 35 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1554,6 +1554,10 @@ sqrt(2)
15541554
<secondary>invocation</secondary>
15551555
</indexterm>
15561556

1557+
<indexterm zone="syntax-aggregates">
1558+
<primary>filter</primary>
1559+
</indexterm>
1560+
15571561
<para>
15581562
An <firstterm>aggregate expression</firstterm> represents the
15591563
application of an aggregate function across the rows selected by a
@@ -1562,19 +1566,19 @@ sqrt(2)
15621566
syntax of an aggregate expression is one of the following:
15631567

15641568
<synopsis>
1565-
<replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] )
1566-
<replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] )
1567-
<replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] )
1568-
<replaceable>aggregate_name</replaceable> ( * )
1569+
<replaceable>aggregate_name</replaceable> (<replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ]
1570+
<replaceable>aggregate_name</replaceable> (ALL <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ]
1571+
<replaceable>aggregate_name</replaceable> (DISTINCT <replaceable>expression</replaceable> [ , ... ] [ <replaceable>order_by_clause</replaceable> ] ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ]
1572+
<replaceable>aggregate_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ]
15691573
</synopsis>
15701574

15711575
where <replaceable>aggregate_name</replaceable> is a previously
1572-
defined aggregate (possibly qualified with a schema name),
1576+
defined aggregate (possibly qualified with a schema name) and
15731577
<replaceable>expression</replaceable> is
15741578
any value expression that does not itself contain an aggregate
1575-
expression or a window function call, and
1576-
<replaceable>order_by_clause</replaceable> is a optional
1577-
<literal>ORDER BY</> clause as described below.
1579+
expression or a window function call. The optional
1580+
<replaceable>order_by_clause</replaceable> and
1581+
<replaceable>filter_clause</replaceable> are described below.
15781582
</para>
15791583

15801584
<para>
@@ -1606,6 +1610,23 @@ sqrt(2)
16061610
distinct non-null values of <literal>f1</literal>.
16071611
</para>
16081612

1613+
<para>
1614+
If <literal>FILTER</literal> is specified, then only the input
1615+
rows for which the <replaceable>filter_clause</replaceable>
1616+
evaluates to true are fed to the aggregate function; other rows
1617+
are discarded. For example:
1618+
<programlisting>
1619+
SELECT
1620+
count(*) AS unfiltered,
1621+
count(*) FILTER (WHERE i < 5) AS filtered
1622+
FROM generate_series(1,10) AS s(i);
1623+
unfiltered | filtered
1624+
------------+----------
1625+
10 | 4
1626+
(1 row)
1627+
</programlisting>
1628+
</para>
1629+
16091630
<para>
16101631
Ordinarily, the input rows are fed to the aggregate function in an
16111632
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
17091730
The syntax of a window function call is one of the following:
17101731

17111732
<synopsis>
1712-
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER ( <replaceable class="parameter">window_definition</replaceable> )
1713-
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) OVER <replaceable>window_name</replaceable>
1714-
<replaceable>function_name</replaceable> ( * ) OVER ( <replaceable class="parameter">window_definition</replaceable> )
1715-
<replaceable>function_name</replaceable> ( * ) OVER <replaceable>window_name</replaceable>
1733+
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
1734+
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
1735+
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
1736+
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
17161737
</synopsis>
17171738
where <replaceable class="parameter">window_definition</replaceable>
17181739
has the syntax
@@ -1836,7 +1857,8 @@ UNBOUNDED FOLLOWING
18361857
The built-in window functions are described in <xref
18371858
linkend="functions-window-table">. Other window functions can be added by
18381859
the user. Also, any built-in or user-defined aggregate function can be
1839-
used as a window function.
1860+
used as a window function. Only aggregate window functions accept
1861+
a <literal>FILTER</literal> clause.
18401862
</para>
18411863

18421864
<para>

‎src/backend/executor/execQual.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4410,6 +4410,8 @@ ExecInitExpr(Expr *node, PlanState *parent)
44104410

44114411
astate->args = (List *) ExecInitExpr((Expr *) aggref->args,
44124412
parent);
4413+
astate->aggfilter = ExecInitExpr(aggref->aggfilter,
4414+
parent);
44134415

44144416
/*
44154417
* Complain if the aggregate's arguments contain any
@@ -4448,6 +4450,8 @@ ExecInitExpr(Expr *node, PlanState *parent)
44484450

44494451
wfstate->args = (List *) ExecInitExpr((Expr *) wfunc->args,
44504452
parent);
4453+
wfstate->aggfilter = ExecInitExpr(wfunc->aggfilter,
4454+
parent);
44514455

44524456
/*
44534457
* Complain if the windowfunc's arguments contain any

‎src/backend/executor/execUtils.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -649,9 +649,9 @@ get_last_attnums(Node *node, ProjectionInfo *projInfo)
649649
}
650650

651651
/*
652-
* Don't examine the arguments of Aggrefs or WindowFuncs, because those do
653-
* not represent expressions to be evaluated within the overall
654-
* targetlist's econtext.
652+
* Don't examine the arguments or filters of Aggrefs or WindowFuncs,
653+
* because those do not represent expressions to be evaluated within the
654+
* overall targetlist's econtext.
655655
*/
656656
if (IsA(node, Aggref))
657657
return false;

‎src/backend/executor/functions.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -380,7 +380,7 @@ sql_fn_post_column_ref(ParseState *pstate, ColumnRef *cref, Node *var)
380380
param = ParseFuncOrColumn(pstate,
381381
list_make1(subfield),
382382
list_make1(param),
383-
NIL, false, false, false,
383+
NIL, NULL, false, false, false,
384384
NULL, true, cref->location);
385385
}
386386

‎src/backend/executor/nodeAgg.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -484,10 +484,23 @@ advance_aggregates(AggState *aggstate, AggStatePerGroup pergroup)
484484
{
485485
AggStatePerAgg peraggstate = &aggstate->peragg[aggno];
486486
AggStatePerGroup pergroupstate = &pergroup[aggno];
487+
ExprState *filter = peraggstate->aggrefstate->aggfilter;
487488
int nargs = peraggstate->numArguments;
488489
int i;
489490
TupleTableSlot *slot;
490491

492+
/* Skip anything FILTERed out */
493+
if (filter)
494+
{
495+
bool isnull;
496+
Datum res;
497+
498+
res = ExecEvalExprSwitchContext(filter, aggstate->tmpcontext,
499+
&isnull, NULL);
500+
if (isnull || !DatumGetBool(res))
501+
continue;
502+
}
503+
491504
/* Evaluate the current input expressions for this aggregate */
492505
slot = ExecProject(peraggstate->evalproj, NULL);
493506

‎src/backend/executor/nodeWindowAgg.c

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -227,9 +227,23 @@ advance_windowaggregate(WindowAggState *winstate,
227227
int i;
228228
MemoryContext oldContext;
229229
ExprContext *econtext = winstate->tmpcontext;
230+
ExprState *filter = wfuncstate->aggfilter;
230231

231232
oldContext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
232233

234+
/* Skip anything FILTERed out */
235+
if (filter)
236+
{
237+
bool isnull;
238+
Datum res = ExecEvalExpr(filter, econtext, &isnull, NULL);
239+
240+
if (isnull || !DatumGetBool(res))
241+
{
242+
MemoryContextSwitchTo(oldContext);
243+
return;
244+
}
245+
}
246+
233247
/* We start from 1, since the 0th arg will be the transition value */
234248
i = 1;
235249
foreach(arg, wfuncstate->args)

‎src/backend/nodes/copyfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1137,6 +1137,7 @@ _copyAggref(const Aggref *from)
11371137
COPY_NODE_FIELD(args);
11381138
COPY_NODE_FIELD(aggorder);
11391139
COPY_NODE_FIELD(aggdistinct);
1140+
COPY_NODE_FIELD(aggfilter);
11401141
COPY_SCALAR_FIELD(aggstar);
11411142
COPY_SCALAR_FIELD(agglevelsup);
11421143
COPY_LOCATION_FIELD(location);
@@ -1157,6 +1158,7 @@ _copyWindowFunc(const WindowFunc *from)
11571158
COPY_SCALAR_FIELD(wincollid);
11581159
COPY_SCALAR_FIELD(inputcollid);
11591160
COPY_NODE_FIELD(args);
1161+
COPY_NODE_FIELD(aggfilter);
11601162
COPY_SCALAR_FIELD(winref);
11611163
COPY_SCALAR_FIELD(winstar);
11621164
COPY_SCALAR_FIELD(winagg);
@@ -2152,6 +2154,7 @@ _copyFuncCall(const FuncCall *from)
21522154
COPY_NODE_FIELD(funcname);
21532155
COPY_NODE_FIELD(args);
21542156
COPY_NODE_FIELD(agg_order);
2157+
COPY_NODE_FIELD(agg_filter);
21552158
COPY_SCALAR_FIELD(agg_star);
21562159
COPY_SCALAR_FIELD(agg_distinct);
21572160
COPY_SCALAR_FIELD(func_variadic);

‎src/backend/nodes/equalfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -196,6 +196,7 @@ _equalAggref(const Aggref *a, const Aggref *b)
196196
COMPARE_NODE_FIELD(args);
197197
COMPARE_NODE_FIELD(aggorder);
198198
COMPARE_NODE_FIELD(aggdistinct);
199+
COMPARE_NODE_FIELD(aggfilter);
199200
COMPARE_SCALAR_FIELD(aggstar);
200201
COMPARE_SCALAR_FIELD(agglevelsup);
201202
COMPARE_LOCATION_FIELD(location);
@@ -211,6 +212,7 @@ _equalWindowFunc(const WindowFunc *a, const WindowFunc *b)
211212
COMPARE_SCALAR_FIELD(wincollid);
212213
COMPARE_SCALAR_FIELD(inputcollid);
213214
COMPARE_NODE_FIELD(args);
215+
COMPARE_NODE_FIELD(aggfilter);
214216
COMPARE_SCALAR_FIELD(winref);
215217
COMPARE_SCALAR_FIELD(winstar);
216218
COMPARE_SCALAR_FIELD(winagg);
@@ -1993,6 +1995,7 @@ _equalFuncCall(const FuncCall *a, const FuncCall *b)
19931995
COMPARE_NODE_FIELD(funcname);
19941996
COMPARE_NODE_FIELD(args);
19951997
COMPARE_NODE_FIELD(agg_order);
1998+
COMPARE_NODE_FIELD(agg_filter);
19961999
COMPARE_SCALAR_FIELD(agg_star);
19972000
COMPARE_SCALAR_FIELD(agg_distinct);
19982001
COMPARE_SCALAR_FIELD(func_variadic);

‎src/backend/nodes/makefuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -526,6 +526,7 @@ makeFuncCall(List *name, List *args, int location)
526526
n->args = args;
527527
n->location = location;
528528
n->agg_order = NIL;
529+
n->agg_filter = NULL;
529530
n->agg_star = FALSE;
530531
n->agg_distinct = FALSE;
531532
n->func_variadic = FALSE;

‎src/backend/nodes/nodeFuncs.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1570,6 +1570,8 @@ expression_tree_walker(Node *node,
15701570
if (expression_tree_walker((Node *) expr->aggdistinct,
15711571
walker, context))
15721572
return true;
1573+
if (walker((Node *) expr->aggfilter, context))
1574+
return true;
15731575
}
15741576
break;
15751577
case T_WindowFunc:
@@ -1580,6 +1582,8 @@ expression_tree_walker(Node *node,
15801582
if (expression_tree_walker((Node *) expr->args,
15811583
walker, context))
15821584
return true;
1585+
if (walker((Node *) expr->aggfilter, context))
1586+
return true;
15831587
}
15841588
break;
15851589
case T_ArrayRef:
@@ -2079,6 +2083,7 @@ expression_tree_mutator(Node *node,
20792083
MUTATE(newnode->args, aggref->args, List *);
20802084
MUTATE(newnode->aggorder, aggref->aggorder, List *);
20812085
MUTATE(newnode->aggdistinct, aggref->aggdistinct, List *);
2086+
MUTATE(newnode->aggfilter, aggref->aggfilter, Expr *);
20822087
return (Node *) newnode;
20832088
}
20842089
break;
@@ -2089,6 +2094,7 @@ expression_tree_mutator(Node *node,
20892094

20902095
FLATCOPY(newnode, wfunc, WindowFunc);
20912096
MUTATE(newnode->args, wfunc->args, List *);
2097+
MUTATE(newnode->aggfilter, wfunc->aggfilter, Expr *);
20922098
return (Node *) newnode;
20932099
}
20942100
break;
@@ -2951,6 +2957,8 @@ raw_expression_tree_walker(Node *node,
29512957
return true;
29522958
if (walker(fcall->agg_order, context))
29532959
return true;
2960+
if (walker(fcall->agg_filter, context))
2961+
return true;
29542962
if (walker(fcall->over, context))
29552963
return true;
29562964
/* function name is deemed uninteresting */

‎src/backend/nodes/outfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -958,6 +958,7 @@ _outAggref(StringInfo str, const Aggref *node)
958958
WRITE_NODE_FIELD(args);
959959
WRITE_NODE_FIELD(aggorder);
960960
WRITE_NODE_FIELD(aggdistinct);
961+
WRITE_NODE_FIELD(aggfilter);
961962
WRITE_BOOL_FIELD(aggstar);
962963
WRITE_UINT_FIELD(agglevelsup);
963964
WRITE_LOCATION_FIELD(location);
@@ -973,6 +974,7 @@ _outWindowFunc(StringInfo str, const WindowFunc *node)
973974
WRITE_OID_FIELD(wincollid);
974975
WRITE_OID_FIELD(inputcollid);
975976
WRITE_NODE_FIELD(args);
977+
WRITE_NODE_FIELD(aggfilter);
976978
WRITE_UINT_FIELD(winref);
977979
WRITE_BOOL_FIELD(winstar);
978980
WRITE_BOOL_FIELD(winagg);
@@ -2080,6 +2082,7 @@ _outFuncCall(StringInfo str, const FuncCall *node)
20802082
WRITE_NODE_FIELD(funcname);
20812083
WRITE_NODE_FIELD(args);
20822084
WRITE_NODE_FIELD(agg_order);
2085+
WRITE_NODE_FIELD(agg_filter);
20832086
WRITE_BOOL_FIELD(agg_star);
20842087
WRITE_BOOL_FIELD(agg_distinct);
20852088
WRITE_BOOL_FIELD(func_variadic);

‎src/backend/nodes/readfuncs.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -479,6 +479,7 @@ _readAggref(void)
479479
READ_NODE_FIELD(args);
480480
READ_NODE_FIELD(aggorder);
481481
READ_NODE_FIELD(aggdistinct);
482+
READ_NODE_FIELD(aggfilter);
482483
READ_BOOL_FIELD(aggstar);
483484
READ_UINT_FIELD(agglevelsup);
484485
READ_LOCATION_FIELD(location);
@@ -499,6 +500,7 @@ _readWindowFunc(void)
499500
READ_OID_FIELD(wincollid);
500501
READ_OID_FIELD(inputcollid);
501502
READ_NODE_FIELD(args);
503+
READ_NODE_FIELD(aggfilter);
502504
READ_UINT_FIELD(winref);
503505
READ_BOOL_FIELD(winstar);
504506
READ_BOOL_FIELD(winagg);

‎src/backend/optimizer/path/costsize.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1590,6 +1590,14 @@ cost_windowagg(Path *path, PlannerInfo *root,
15901590
startup_cost += argcosts.startup;
15911591
wfunccost += argcosts.per_tuple;
15921592

1593+
/*
1594+
* Add the filter's cost to per-input-row costs. XXX We should reduce
1595+
* input expression costs according to filter selectivity.
1596+
*/
1597+
cost_qual_eval_node(&argcosts, (Node *) wfunc->aggfilter, root);
1598+
startup_cost += argcosts.startup;
1599+
wfunccost += argcosts.per_tuple;
1600+
15931601
total_cost += wfunccost * input_tuples;
15941602
}
15951603

‎src/backend/optimizer/plan/planagg.c

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -329,6 +329,12 @@ find_minmax_aggs_walker(Node *node, List **context)
329329
*/
330330
if (aggref->aggorder != NIL)
331331
return true;
332+
/*
333+
* We might implement the optimization when a FILTER clause is present
334+
* by adding the filter to the quals of the generated subquery.
335+
*/
336+
if (aggref->aggfilter != NULL)
337+
return true;
332338
/* note: we do not care if DISTINCT is mentioned ... */
333339

334340
aggsortop = fetch_agg_sort_op(aggref->aggfnoid);

0 commit comments

Comments
 (0)