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);

0 commit comments

Comments
 (0)