codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 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 16.3 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 /></div><div class="sect1" id="FUNCTIONS-WINDOW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.22. Window Functions <a href="#FUNCTIONS-WINDOW" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.28.2" class="indexterm"></a><p>3 <em class="firstterm">Window functions</em> provide the ability to perform4 calculations across sets of rows that are related to the current query5 row. See <a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a> for an introduction to this6 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 syntax7 details.8 </p><p>9 The built-in window functions are listed in10 <a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.64. General-Purpose Window Functions">Table 9.64</a>. Note that these functions11 <span class="emphasis"><em>must</em></span> be invoked using window function syntax, i.e., an12 <code class="literal">OVER</code> clause is required.13 </p><p>14 In addition to these functions, any built-in or user-defined15 ordinary aggregate (i.e., not ordered-set or hypothetical-set aggregates)16 can be used as a window function; see17 <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a> for a list of the built-in aggregates.18 Aggregate functions act as window functions only when an <code class="literal">OVER</code>19 clause follows the call; otherwise they act as plain aggregates20 and return a single row for the entire set.21 </p><div class="table" id="FUNCTIONS-WINDOW-TABLE"><p class="title"><strong>Table 9.64. 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">22 Function23 </p>24 <p>25 Description26 </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">27 <a id="id-1.5.8.28.6.2.2.1.1.1.1" class="indexterm"></a>28 <code class="function">row_number</code> ()29 → <code class="returnvalue">bigint</code>30 </p>31 <p>32 Returns the number of the current row within its partition, counting33 from 1.34 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">35 <a id="id-1.5.8.28.6.2.2.2.1.1.1" class="indexterm"></a>36 <code class="function">rank</code> ()37 → <code class="returnvalue">bigint</code>38 </p>39 <p>40 Returns the rank of the current row, with gaps; that is,41 the <code class="function">row_number</code> of the first row in its peer42 group.43 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">44 <a id="id-1.5.8.28.6.2.2.3.1.1.1" class="indexterm"></a>45 <code class="function">dense_rank</code> ()46 → <code class="returnvalue">bigint</code>47 </p>48 <p>49 Returns the rank of the current row, without gaps; this function50 effectively counts peer groups.51 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">52 <a id="id-1.5.8.28.6.2.2.4.1.1.1" class="indexterm"></a>53 <code class="function">percent_rank</code> ()54 → <code class="returnvalue">double precision</code>55 </p>56 <p>57 Returns the relative rank of the current row, that is58 (<code class="function">rank</code> - 1) / (total partition rows - 1).59 The value thus ranges from 0 to 1 inclusive.60 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">61 <a id="id-1.5.8.28.6.2.2.5.1.1.1" class="indexterm"></a>62 <code class="function">cume_dist</code> ()63 → <code class="returnvalue">double precision</code>64 </p>65 <p>66 Returns the cumulative distribution, that is (number of partition rows67 preceding or peers with current row) / (total partition rows).68 The value thus ranges from 1/<em class="parameter"><code>N</code></em> to 1.69 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">70 <a id="id-1.5.8.28.6.2.2.6.1.1.1" class="indexterm"></a>71 <code class="function">ntile</code> ( <em class="parameter"><code>num_buckets</code></em> <code class="type">integer</code> )72 → <code class="returnvalue">integer</code>73 </p>74 <p>75 Returns an integer ranging from 1 to the argument value, dividing the76 partition as equally as possible.77 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">78 <a id="id-1.5.8.28.6.2.2.7.1.1.1" class="indexterm"></a>79 <code class="function">lag</code> ( <em class="parameter"><code>value</code></em> <code class="type">anycompatible</code>80 [<span class="optional">, <em class="parameter"><code>offset</code></em> <code class="type">integer</code>81 [<span class="optional">, <em class="parameter"><code>default</code></em> <code class="type">anycompatible</code> </span>]</span>] )82 → <code class="returnvalue">anycompatible</code>83 </p>84 <p>85 Returns <em class="parameter"><code>value</code></em> evaluated at86 the row that is <em class="parameter"><code>offset</code></em>87 rows before the current row within the partition; if there is no such88 row, instead returns <em class="parameter"><code>default</code></em>89 (which must be of a type compatible with90 <em class="parameter"><code>value</code></em>).91 Both <em class="parameter"><code>offset</code></em> and92 <em class="parameter"><code>default</code></em> are evaluated93 with respect to the current row. If omitted,94 <em class="parameter"><code>offset</code></em> defaults to 1 and95 <em class="parameter"><code>default</code></em> to <code class="literal">NULL</code>.96 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">97 <a id="id-1.5.8.28.6.2.2.8.1.1.1" class="indexterm"></a>98 <code class="function">lead</code> ( <em class="parameter"><code>value</code></em> <code class="type">anycompatible</code>99 [<span class="optional">, <em class="parameter"><code>offset</code></em> <code class="type">integer</code>100 [<span class="optional">, <em class="parameter"><code>default</code></em> <code class="type">anycompatible</code> </span>]</span>] )101 → <code class="returnvalue">anycompatible</code>102 </p>103 <p>104 Returns <em class="parameter"><code>value</code></em> evaluated at105 the row that is <em class="parameter"><code>offset</code></em>106 rows after the current row within the partition; if there is no such107 row, instead returns <em class="parameter"><code>default</code></em>108 (which must be of a type compatible with109 <em class="parameter"><code>value</code></em>).110 Both <em class="parameter"><code>offset</code></em> and111 <em class="parameter"><code>default</code></em> are evaluated112 with respect to the current row. If omitted,113 <em class="parameter"><code>offset</code></em> defaults to 1 and114 <em class="parameter"><code>default</code></em> to <code class="literal">NULL</code>.115 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">116 <a id="id-1.5.8.28.6.2.2.9.1.1.1" class="indexterm"></a>117 <code class="function">first_value</code> ( <em class="parameter"><code>value</code></em> <code class="type">anyelement</code> )118 → <code class="returnvalue">anyelement</code>119 </p>120 <p>121 Returns <em class="parameter"><code>value</code></em> evaluated122 at the row that is the first row of the window frame.123 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">124 <a id="id-1.5.8.28.6.2.2.10.1.1.1" class="indexterm"></a>125 <code class="function">last_value</code> ( <em class="parameter"><code>value</code></em> <code class="type">anyelement</code> )126 → <code class="returnvalue">anyelement</code>127 </p>128 <p>129 Returns <em class="parameter"><code>value</code></em> evaluated130 at the row that is the last row of the window frame.131 </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">132 <a id="id-1.5.8.28.6.2.2.11.1.1.1" class="indexterm"></a>133 <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> )134 → <code class="returnvalue">anyelement</code>135 </p>136 <p>137 Returns <em class="parameter"><code>value</code></em> evaluated138 at the row that is the <em class="parameter"><code>n</code></em>'th139 row of the window frame (counting from 1);140 returns <code class="literal">NULL</code> if there is no such row.141 </p></td></tr></tbody></table></div></div><br class="table-break" /><p>142 All of the functions listed in143 <a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.64. General-Purpose Window Functions">Table 9.64</a> depend on the sort ordering144 specified by the <code class="literal">ORDER BY</code> clause of the associated window145 definition. Rows that are not distinct when considering only the146 <code class="literal">ORDER BY</code> columns are said to be <em class="firstterm">peers</em>.147 The four ranking functions (including <code class="function">cume_dist</code>) are148 defined so that they give the same answer for all rows of a peer group.149 </p><p>150 Note that <code class="function">first_value</code>, <code class="function">last_value</code>, and151 <code class="function">nth_value</code> consider only the rows within the <span class="quote">“<span class="quote">window152 frame</span>”</span>, which by default contains the rows from the start of the153 partition through the last peer of the current row. This is154 likely to give unhelpful results for <code class="function">last_value</code> and155 sometimes also <code class="function">nth_value</code>. You can redefine the frame by156 adding a suitable frame specification (<code class="literal">RANGE</code>,157 <code class="literal">ROWS</code> or <code class="literal">GROUPS</code>) to158 the <code class="literal">OVER</code> clause.159 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 information160 about frame specifications.161 </p><p>162 When an aggregate function is used as a window function, it aggregates163 over the rows within the current row's window frame.164 An aggregate used with <code class="literal">ORDER BY</code> and the default window frame165 definition produces a <span class="quote">“<span class="quote">running sum</span>”</span> type of behavior, which may or166 may not be what's wanted. To obtain167 aggregation over the whole partition, omit <code class="literal">ORDER BY</code> or use168 <code class="literal">ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code>.169 Other frame specifications can be used to obtain other effects.170 </p><div class="note"><h3 class="title">Note</h3><p>171 The SQL standard defines a <code class="literal">RESPECT NULLS</code> or172 <code class="literal">IGNORE NULLS</code> option for <code class="function">lead</code>, <code class="function">lag</code>,173 <code class="function">first_value</code>, <code class="function">last_value</code>, and174 <code class="function">nth_value</code>. This is not implemented in175 <span class="productname">PostgreSQL</span>: the behavior is always the176 same as the standard's default, namely <code class="literal">RESPECT NULLS</code>.177 Likewise, the standard's <code class="literal">FROM FIRST</code> or <code class="literal">FROM LAST</code>178 option for <code class="function">nth_value</code> is not implemented: only the179 default <code class="literal">FROM FIRST</code> behavior is supported. (You can achieve180 the result of <code class="literal">FROM LAST</code> by reversing the <code class="literal">ORDER BY</code>181 ordering.)182 </p></div></div><div class="navfooter"><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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.23. Subquery Expressions</td></tr></table></div></body></html>