1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.22. Window Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-aggregate.html" title="9.21. Aggregate Functions" /><link rel="next" href="functions-subquery.html" title="9.23. Subquery Expressions" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.22. Window Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-aggregate.html" title="9.21. Aggregate Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-subquery.html" title="9.23. Subquery Expressions">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-WINDOW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.22. Window Functions</h2></div></div></div><a id="id-1.5.8.28.2" class="indexterm"></a><p>
<em class="firstterm">Window functions</em> provide the ability to perform
calculations across sets of rows that are related to the current query
row. See <a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a> for an introduction to this
feature, and <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a> for syntax
details.
</p><p>
The built-in window functions are listed in
<a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.62. General-Purpose Window Functions">Table 9.62</a>. Note that these functions
<span class="emphasis"><em>must</em></span> be invoked using window function syntax, i.e., an
<code class="literal">OVER</code> clause is required.
</p><p>
In addition to these functions, any built-in or user-defined
ordinary aggregate (i.e., not ordered-set or hypothetical-set aggregates)
can be used as a window function; see
<a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> for a list of the built-in aggregates.
Aggregate functions act as window functions only when an <code class="literal">OVER</code>
clause follows the call; otherwise they act as plain aggregates
and return a single row for the entire set.
</p><div class="table" id="FUNCTIONS-WINDOW-TABLE"><p class="title"><strong>Table 9.62. General-Purpose Window Functions</strong></p><div class="table-contents"><table class="table" summary="General-Purpose Window Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
Function
</p>
<p>
Description
</p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.1.1.1.1" class="indexterm"></a>
<code class="function">row_number</code> ()
→ <code class="returnvalue">bigint</code>
</p>
<p>
Returns the number of the current row within its partition, counting
from 1.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.2.1.1.1" class="indexterm"></a>
<code class="function">rank</code> ()
→ <code class="returnvalue">bigint</code>
</p>
<p>
Returns the rank of the current row, with gaps; that is,
the <code class="function">row_number</code> of the first row in its peer
group.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.3.1.1.1" class="indexterm"></a>
<code class="function">dense_rank</code> ()
→ <code class="returnvalue">bigint</code>
</p>
<p>
Returns the rank of the current row, without gaps; this function
effectively counts peer groups.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.4.1.1.1" class="indexterm"></a>
<code class="function">percent_rank</code> ()
→ <code class="returnvalue">double precision</code>
</p>
<p>
Returns the relative rank of the current row, that is
(<code class="function">rank</code> - 1) / (total partition rows - 1).
The value thus ranges from 0 to 1 inclusive.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.5.1.1.1" class="indexterm"></a>
<code class="function">cume_dist</code> ()
→ <code class="returnvalue">double precision</code>
</p>
<p>
Returns the cumulative distribution, that is (number of partition rows
preceding or peers with current row) / (total partition rows).
The value thus ranges from 1/<em class="parameter"><code>N</code></em> to 1.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.6.1.1.1" class="indexterm"></a>
<code class="function">ntile</code> ( <em class="parameter"><code>num_buckets</code></em> <code class="type">integer</code> )
→ <code class="returnvalue">integer</code>
</p>
<p>
Returns an integer ranging from 1 to the argument value, dividing the
partition as equally as possible.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.7.1.1.1" class="indexterm"></a>
<code class="function">lag</code> ( <em class="parameter"><code>value</code></em> <code class="type">anycompatible</code>
[<span class="optional">, <em class="parameter"><code>offset</code></em> <code class="type">integer</code>
[<span class="optional">, <em class="parameter"><code>default</code></em> <code class="type">anycompatible</code> </span>]</span>] )
→ <code class="returnvalue">anycompatible</code>
</p>
<p>
Returns <em class="parameter"><code>value</code></em> evaluated at
the row that is <em class="parameter"><code>offset</code></em>
rows before the current row within the partition; if there is no such
row, instead returns <em class="parameter"><code>default</code></em>
(which must be of a type compatible with
<em class="parameter"><code>value</code></em>).
Both <em class="parameter"><code>offset</code></em> and
<em class="parameter"><code>default</code></em> are evaluated
with respect to the current row. If omitted,
<em class="parameter"><code>offset</code></em> defaults to 1 and
<em class="parameter"><code>default</code></em> to <code class="literal">NULL</code>.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.8.1.1.1" class="indexterm"></a>
<code class="function">lead</code> ( <em class="parameter"><code>value</code></em> <code class="type">anycompatible</code>
[<span class="optional">, <em class="parameter"><code>offset</code></em> <code class="type">integer</code>
[<span class="optional">, <em class="parameter"><code>default</code></em> <code class="type">anycompatible</code> </span>]</span>] )
→ <code class="returnvalue">anycompatible</code>
</p>
<p>
Returns <em class="parameter"><code>value</code></em> evaluated at
the row that is <em class="parameter"><code>offset</code></em>
rows after the current row within the partition; if there is no such
row, instead returns <em class="parameter"><code>default</code></em>
(which must be of a type compatible with
<em class="parameter"><code>value</code></em>).
Both <em class="parameter"><code>offset</code></em> and
<em class="parameter"><code>default</code></em> are evaluated
with respect to the current row. If omitted,
<em class="parameter"><code>offset</code></em> defaults to 1 and
<em class="parameter"><code>default</code></em> to <code class="literal">NULL</code>.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.9.1.1.1" class="indexterm"></a>
<code class="function">first_value</code> ( <em class="parameter"><code>value</code></em> <code class="type">anyelement</code> )
→ <code class="returnvalue">anyelement</code>
</p>
<p>
Returns <em class="parameter"><code>value</code></em> evaluated
at the row that is the first row of the window frame.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.10.1.1.1" class="indexterm"></a>
<code class="function">last_value</code> ( <em class="parameter"><code>value</code></em> <code class="type">anyelement</code> )
→ <code class="returnvalue">anyelement</code>
</p>
<p>
Returns <em class="parameter"><code>value</code></em> evaluated
at the row that is the last row of the window frame.
</p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
<a id="id-1.5.8.28.6.2.2.11.1.1.1" class="indexterm"></a>
<code class="function">nth_value</code> ( <em class="parameter"><code>value</code></em> <code class="type">anyelement</code>, <em class="parameter"><code>n</code></em> <code class="type">integer</code> )
→ <code class="returnvalue">anyelement</code>
</p>
<p>
Returns <em class="parameter"><code>value</code></em> evaluated
at the row that is the <em class="parameter"><code>n</code></em>'th
row of the window frame (counting from 1);
returns <code class="literal">NULL</code> if there is no such row.
</p></td></tr></tbody></table></div></div><br class="table-break" /><p>
All of the functions listed in
<a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.62. General-Purpose Window Functions">Table 9.62</a> depend on the sort ordering
specified by the <code class="literal">ORDER BY</code> clause of the associated window
definition. Rows that are not distinct when considering only the
<code class="literal">ORDER BY</code> columns are said to be <em class="firstterm">peers</em>.
The four ranking functions (including <code class="function">cume_dist</code>) are
defined so that they give the same answer for all rows of a peer group.
</p><p>
Note that <code class="function">first_value</code>, <code class="function">last_value</code>, and
<code class="function">nth_value</code> consider only the rows within the <span class="quote">“<span class="quote">window
frame</span>”</span>, which by default contains the rows from the start of the
partition through the last peer of the current row. This is
likely to give unhelpful results for <code class="function">last_value</code> and
sometimes also <code class="function">nth_value</code>. You can redefine the frame by
adding a suitable frame specification (<code class="literal">RANGE</code>,
<code class="literal">ROWS</code> or <code class="literal">GROUPS</code>) to
the <code class="literal">OVER</code> clause.
See <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a> for more information
about frame specifications.
</p><p>
When an aggregate function is used as a window function, it aggregates
over the rows within the current row's window frame.
An aggregate used with <code class="literal">ORDER BY</code> and the default window frame
definition produces a <span class="quote">“<span class="quote">running sum</span>”</span> type of behavior, which may or
may not be what's wanted. To obtain
aggregation over the whole partition, omit <code class="literal">ORDER BY</code> or use
<code class="literal">ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code>.
Other frame specifications can be used to obtain other effects.
</p><div class="note"><h3 class="title">Note</h3><p>
The SQL standard defines a <code class="literal">RESPECT NULLS</code> or
<code class="literal">IGNORE NULLS</code> option for <code class="function">lead</code>, <code class="function">lag</code>,
<code class="function">first_value</code>, <code class="function">last_value</code>, and
<code class="function">nth_value</code>. This is not implemented in
<span class="productname">PostgreSQL</span>: the behavior is always the
same as the standard's default, namely <code class="literal">RESPECT NULLS</code>.
Likewise, the standard's <code class="literal">FROM FIRST</code> or <code class="literal">FROM LAST</code>
option for <code class="function">nth_value</code> is not implemented: only the
default <code class="literal">FROM FIRST</code> behavior is supported. (You can achieve
the result of <code class="literal">FROM LAST</code> by reversing the <code class="literal">ORDER BY</code>
ordering.)
</p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-aggregate.html" title="9.21. Aggregate Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-subquery.html" title="9.23. Subquery Expressions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.21. Aggregate Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.23. Subquery Expressions</td></tr></table></div></body></html>
|
