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

Commit25a30bb

Browse files
committed
Add IGNORE NULLS/RESPECT NULLS option to Window functions.
Add IGNORE NULLS/RESPECT NULLS option (null treatment clause) to lead,lag, first_value, last_value and nth_value window functions. Ifunspecified, the default is RESPECT NULLS which includes NULL valuesin any result calculation. IGNORE NULLS ignores NULL values.Built-in window functions are modified to call new APIWinCheckAndInitializeNullTreatment() to indicate whether they acceptIGNORE NULLS/RESPECT NULLS option or not (the API can be called byuser defined window functions as well). If WinGetFuncArgInPartition'sallowNullTreatment argument is true and IGNORE NULLS option is given,WinGetFuncArgInPartition() or WinGetFuncArgInFrame() will returnevaluated function's argument expression on specified non NULL row (ifit exists) in the partition or the frame.When IGNORE NULLS option is given, window functions need to visit andevaluate same rows over and over again to look for non null rows. Tomitigate the issue, 2-bit not null information array is created whileexecuting window functions to remember whether the row has beenalready evaluated to NULL or NOT NULL. If already evaluated, we couldskip the evaluation work, thus we could get better performance.Author: Oliver Ford <ojford@gmail.com>Co-authored-by: Tatsuo Ishii <ishii@postgresql.org>Reviewed-by: Krasiyan Andreev <krasiyan@gmail.com>Reviewed-by: Andrew Gierth <andrew@tao11.riddles.org.uk>Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Reviewed-by: David Fetter <david@fetter.org>Reviewed-by: Vik Fearing <vik@postgresfriends.org>Reviewed-by: "David G. Johnston" <david.g.johnston@gmail.com>Reviewed-by: Chao Li <lic@highgo.com>Discussion:https://postgr.es/m/flat/CAGMVOdsbtRwE_4+v8zjH1d9xfovDeQAGLkP_B6k69_VoFEgX-A@mail.gmail.com
1 parent381f5cf commit25a30bb

File tree

15 files changed

+1092
-61
lines changed

15 files changed

+1092
-61
lines changed

‎doc/src/sgml/func/func-window.sgml‎

Lines changed: 23 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -140,7 +140,7 @@
140140
</indexterm>
141141
<function>lag</function> ( <parameter>value</parameter> <type>anycompatible</type>
142142
<optional>, <parameter>offset</parameter> <type>integer</type>
143-
<optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
143+
<optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> ) <optional> <parameter>null treatment</parameter> </optional>
144144
<returnvalue>anycompatible</returnvalue>
145145
</para>
146146
<para>
@@ -165,7 +165,7 @@
165165
</indexterm>
166166
<function>lead</function> ( <parameter>value</parameter> <type>anycompatible</type>
167167
<optional>, <parameter>offset</parameter> <type>integer</type>
168-
<optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
168+
<optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> ) <optional> <parameter>null treatment</parameter> </optional>
169169
<returnvalue>anycompatible</returnvalue>
170170
</para>
171171
<para>
@@ -188,7 +188,7 @@
188188
<indexterm>
189189
<primary>first_value</primary>
190190
</indexterm>
191-
<function>first_value</function> ( <parameter>value</parameter> <type>anyelement</type> )
191+
<function>first_value</function> ( <parameter>value</parameter> <type>anyelement</type> ) <optional> <parameter>null treatment</parameter> </optional>
192192
<returnvalue>anyelement</returnvalue>
193193
</para>
194194
<para>
@@ -202,7 +202,7 @@
202202
<indexterm>
203203
<primary>last_value</primary>
204204
</indexterm>
205-
<function>last_value</function> ( <parameter>value</parameter> <type>anyelement</type> )
205+
<function>last_value</function> ( <parameter>value</parameter> <type>anyelement</type> ) <optional> <parameter>null treatment</parameter> </optional>
206206
<returnvalue>anyelement</returnvalue>
207207
</para>
208208
<para>
@@ -216,7 +216,7 @@
216216
<indexterm>
217217
<primary>nth_value</primary>
218218
</indexterm>
219-
<function>nth_value</function> ( <parameter>value</parameter> <type>anyelement</type>, <parameter>n</parameter> <type>integer</type> )
219+
<function>nth_value</function> ( <parameter>value</parameter> <type>anyelement</type>, <parameter>n</parameter> <type>integer</type> ) <optional> <parameter>null treatment</parameter> </optional>
220220
<returnvalue>anyelement</returnvalue>
221221
</para>
222222
<para>
@@ -265,18 +265,26 @@
265265
Other frame specifications can be used to obtain other effects.
266266
</para>
267267

268+
<para>
269+
The <literal>null treatment</literal> option must be one of:
270+
<synopsis>
271+
RESPECT NULLS
272+
IGNORE NULLS
273+
</synopsis>
274+
If unspecified, the default is <literal>RESPECT NULLS</literal> which includes NULL
275+
values in any result calculation. <literal>IGNORE NULLS</literal> ignores NULL values.
276+
This option is only allowed for the following functions: <function>lag</function>,
277+
<function>lead</function>, <function>first_value</function>, <function>last_value</function>,
278+
<function>nth_value</function>.
279+
</para>
280+
268281
<note>
269282
<para>
270-
The SQL standard defines a <literal>RESPECT NULLS</literal> or
271-
<literal>IGNORE NULLS</literal> option for <function>lead</function>, <function>lag</function>,
272-
<function>first_value</function>, <function>last_value</function>, and
273-
<function>nth_value</function>. This is not implemented in
274-
<productname>PostgreSQL</productname>: the behavior is always the
275-
same as the standard's default, namely <literal>RESPECT NULLS</literal>.
276-
Likewise, the standard's <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
277-
option for <function>nth_value</function> is not implemented: only the
278-
default <literal>FROM FIRST</literal> behavior is supported. (You can achieve
279-
the result of <literal>FROM LAST</literal> by reversing the <literal>ORDER BY</literal>
283+
The SQL standard defines a <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
284+
option for <function>nth_value</function>. This is not implemented in
285+
<productname>PostgreSQL</productname>: only the default <literal>FROM FIRST</literal>
286+
behavior is supported. (You can achieve the result of <literal>FROM LAST</literal> by
287+
reversing the <literal>ORDER BY</literal>
280288
ordering.)
281289
</para>
282290
</note>

‎doc/src/sgml/syntax.sgml‎

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1834,8 +1834,8 @@ FROM generate_series(1,10) AS s(i);
18341834
The syntax of a window function call is one of the following:
18351835

18361836
<synopsis>
1837-
<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>
1838-
<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> )
1837+
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>)<optional>null treatment</optional>[ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
1838+
<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>)<optional>null treatment</optional>[ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
18391839
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
18401840
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
18411841
</synopsis>
@@ -1873,7 +1873,9 @@ EXCLUDE NO OTHERS
18731873

18741874
<para>
18751875
Here, <replaceable>expression</replaceable> represents any value
1876-
expression that does not itself contain window function calls.
1876+
expression that does not itself contain window function calls. Some
1877+
non-aggregate functions allow a <literal>null treatment</literal> clause,
1878+
described in <xref linkend="functions-window"/>.
18771879
</para>
18781880

18791881
<para>
@@ -2048,7 +2050,7 @@ EXCLUDE NO OTHERS
20482050

20492051
<para>
20502052
The built-in window functions are described in <xref
2051-
linkend="functions-window-table"/>.Other window functions can be added by
2053+
linkend="functions-window-table"/>. Other window functions can be added by
20522054
the user. Also, any built-in or user-defined general-purpose or
20532055
statistical aggregate can be used as a window function. (Ordered-set
20542056
and hypothetical-set aggregates cannot presently be used as window functions.)

‎src/backend/catalog/sql_features.txt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -518,7 +518,7 @@ T612Advanced OLAP operationsYES
518518
T613SamplingYES
519519
T614NTILE functionYES
520520
T615LEAD and LAG functionsYES
521-
T616Null treatment option for LEAD and LAG functionsNO
521+
T616Null treatment option for LEAD and LAG functionsYES
522522
T617FIRST_VALUE and LAST_VALUE functionsYES
523523
T618NTH_VALUE functionNOfunction exists, but some options missing
524524
T619Nested window functionsNO

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp