Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb560ec1

Browse files
committed
Implement the FILTER clause for aggregate function calls.
This is SQL-standard with a few extensions, namely support forsubqueries and outer references in clause expressions.catversion bump due to change in Aggref and WindowFunc.David Fetter, reviewed by Dean Rasheed.
1 parent7a8e9f2 commitb560ec1

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
caseT_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
caseT_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 argumentsor filtersof Aggrefs or WindowFuncs,
653+
*because those donot represent expressions to be evaluated within the
654+
*overalltargetlist'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
AggStatePerAggperaggstate=&aggstate->peragg[aggno];
486486
AggStatePerGrouppergroupstate=&pergroup[aggno];
487+
ExprState*filter=peraggstate->aggrefstate->aggfilter;
487488
intnargs=peraggstate->numArguments;
488489
inti;
489490
TupleTableSlot*slot;
490491

492+
/* Skip anything FILTERed out */
493+
if (filter)
494+
{
495+
boolisnull;
496+
Datumres;
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
inti;
228228
MemoryContextoldContext;
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+
boolisnull;
238+
Datumres=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
caseT_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
caseT_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)
[8]ページ先頭
©2009-2025 Movatter.jp