begriffs open source - ai-pg/blob - full-docs/src/sgml/html/functions-window.html

   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-merge-support.html" title="9.23. Merge Support Functions" /></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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-merge-support.html" title="9.23. Merge Support Functions">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 perform
   4    calculations across sets of rows that are related to the current query
   5    row.  See <a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a> for an introduction to this
   6    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
   7    details.
   8   </p><p>
   9    The built-in window functions are listed in
  10    <a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.67. General-Purpose Window Functions">Table 9.67</a>.  Note that these functions
  11    <span class="emphasis"><em>must</em></span> be invoked using window function syntax, i.e., an
  12    <code class="literal">OVER</code> clause is required.
  13   </p><p>
  14    In addition to these functions, any built-in or user-defined
  15    ordinary aggregate (i.e., not ordered-set or hypothetical-set aggregates)
  16    can be used as a window function; see
  17    <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 aggregates
  20    and return a single row for the entire set.
  21   </p><div class="table" id="FUNCTIONS-WINDOW-TABLE"><p class="title"><strong>Table 9.67. 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         Function
  23        </p>
  24        <p>
  25         Description
  26        </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, counting
  33         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 peer
  42         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 function
  50         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 is
  58         (<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 rows
  67         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 the
  76         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 at
  86         the row that is <em class="parameter"><code>offset</code></em>
  87         rows before the current row within the partition; if there is no such
  88         row, instead returns <em class="parameter"><code>default</code></em>
  89         (which must be of a type compatible with
  90         <em class="parameter"><code>value</code></em>).
  91         Both <em class="parameter"><code>offset</code></em> and
  92         <em class="parameter"><code>default</code></em> are evaluated
  93         with respect to the current row.  If omitted,
  94         <em class="parameter"><code>offset</code></em> defaults to 1 and
  95         <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 at
 105         the row that is <em class="parameter"><code>offset</code></em>
 106         rows after the current row within the partition; if there is no such
 107         row, instead returns <em class="parameter"><code>default</code></em>
 108         (which must be of a type compatible with
 109         <em class="parameter"><code>value</code></em>).
 110         Both <em class="parameter"><code>offset</code></em> and
 111         <em class="parameter"><code>default</code></em> are evaluated
 112         with respect to the current row.  If omitted,
 113         <em class="parameter"><code>offset</code></em> defaults to 1 and
 114         <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> evaluated
 122         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> evaluated
 130         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> evaluated
 138         at the row that is the <em class="parameter"><code>n</code></em>'th
 139         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 in
 143    <a class="xref" href="functions-window.html#FUNCTIONS-WINDOW-TABLE" title="Table 9.67. General-Purpose Window Functions">Table 9.67</a> depend on the sort ordering
 144    specified by the <code class="literal">ORDER BY</code> clause of the associated window
 145    definition.  Rows that are not distinct when considering only the
 146    <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>) are
 148    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>, and
 151    <code class="function">nth_value</code> consider only the rows within the <span class="quote">“<span class="quote">window
 152    frame</span>”</span>, which by default contains the rows from the start of the
 153    partition through the last peer of the current row.  This is
 154    likely to give unhelpful results for <code class="function">last_value</code> and
 155    sometimes also <code class="function">nth_value</code>.  You can redefine the frame by
 156    adding a suitable frame specification (<code class="literal">RANGE</code>,
 157    <code class="literal">ROWS</code> or <code class="literal">GROUPS</code>) to
 158    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 information
 160    about frame specifications.
 161   </p><p>
 162    When an aggregate function is used as a window function, it aggregates
 163    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 frame
 165    definition produces a <span class="quote">“<span class="quote">running sum</span>”</span> type of behavior, which may or
 166    may not be what's wanted.  To obtain
 167    aggregation over the whole partition, omit <code class="literal">ORDER BY</code> or use
 168    <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> or
 172     <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>, and
 174     <code class="function">nth_value</code>.  This is not implemented in
 175     <span class="productname">PostgreSQL</span>: the behavior is always the
 176     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 the
 179     default <code class="literal">FROM FIRST</code> behavior is supported.  (You can achieve
 180     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-merge-support.html" title="9.23. Merge Support Functions">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 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.23. Merge Support Functions</td></tr></table></div></body></html>