begriffs open source - ai-pg/blob - full-docs/src/sgml/html/tutorial-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>3.5. 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="tutorial-transactions.html" title="3.4. Transactions" /><link rel="next" href="tutorial-inheritance.html" title="3.6. Inheritance" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">3.5. Window Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="tutorial-transactions.html" title="3.4. Transactions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="tutorial-advanced.html" title="Chapter 3. Advanced Features">Up</a></td><th width="60%" align="center">Chapter 3. Advanced Features</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="tutorial-inheritance.html" title="3.6. Inheritance">Next</a></td></tr></table><hr /></div><div class="sect1" id="TUTORIAL-WINDOW"><div class="titlepage"><div><div><h2 class="title" style="clear: both">3.5. Window Functions <a href="#TUTORIAL-WINDOW" class="id_link">#</a></h2></div></div></div><a id="id-1.4.5.6.2" class="indexterm"></a><p>
   3     A <em class="firstterm">window function</em> performs a calculation across a set of
   4     table rows that are somehow related to the current row.  This is comparable
   5     to the type of calculation that can be done with an aggregate function.
   6     However, window functions do not cause rows to become grouped into a single
   7     output row like non-window aggregate calls would.  Instead, the
   8     rows retain their separate identities.  Behind the scenes, the window
   9     function is able to access more than just the current row of the query
  10     result.
  11    </p><p>
  12     Here is an example that shows how to compare each employee's salary
  13     with the average salary in his or her department:
  14
  15 </p><pre class="programlisting">
  16 SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
  17 </pre><p>
  18
  19 </p><pre class="screen">
  20   depname  | empno | salary |          avg
  21 -----------+-------+--------+-----------------------
  22  develop   |    11 |   5200 | 5020.0000000000000000
  23  develop   |     7 |   4200 | 5020.0000000000000000
  24  develop   |     9 |   4500 | 5020.0000000000000000
  25  develop   |     8 |   6000 | 5020.0000000000000000
  26  develop   |    10 |   5200 | 5020.0000000000000000
  27  personnel |     5 |   3500 | 3700.0000000000000000
  28  personnel |     2 |   3900 | 3700.0000000000000000
  29  sales     |     3 |   4800 | 4866.6666666666666667
  30  sales     |     1 |   5000 | 4866.6666666666666667
  31  sales     |     4 |   4800 | 4866.6666666666666667
  32 (10 rows)
  33 </pre><p>
  34
  35     The first three output columns come directly from the table
  36     <code class="structname">empsalary</code>, and there is one output row for each row in the
  37     table.  The fourth column represents an average taken across all the table
  38     rows that have the same <code class="structfield">depname</code> value as the current row.
  39     (This actually is the same function as the non-window <code class="function">avg</code>
  40     aggregate, but the <code class="literal">OVER</code> clause causes it to be
  41     treated as a window function and computed across the window frame.)
  42    </p><p>
  43     A window function call always contains an <code class="literal">OVER</code> clause
  44     directly following the window function's name and argument(s).  This is what
  45     syntactically distinguishes it from a normal function or non-window
  46     aggregate.  The <code class="literal">OVER</code> clause determines exactly how the
  47     rows of the query are split up for processing by the window function.
  48     The <code class="literal">PARTITION BY</code> clause within <code class="literal">OVER</code>
  49     divides the rows into groups, or partitions, that share the same
  50     values of the <code class="literal">PARTITION BY</code> expression(s).  For each row,
  51     the window function is computed across the rows that fall into the
  52     same partition as the current row.
  53    </p><p>
  54     You can also control the order in which rows are processed by
  55     window functions using <code class="literal">ORDER BY</code> within <code class="literal">OVER</code>.
  56     (The window <code class="literal">ORDER BY</code> does not even have to match the
  57     order in which the rows are output.)  Here is an example:
  58
  59 </p><pre class="programlisting">
  60 SELECT depname, empno, salary,
  61        row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
  62 FROM empsalary;
  63 </pre><p>
  64
  65 </p><pre class="screen">
  66   depname  | empno | salary | row_number
  67 -----------+-------+--------+------------
  68  develop   |     8 |   6000 |          1
  69  develop   |    10 |   5200 |          2
  70  develop   |    11 |   5200 |          3
  71  develop   |     9 |   4500 |          4
  72  develop   |     7 |   4200 |          5
  73  personnel |     2 |   3900 |          1
  74  personnel |     5 |   3500 |          2
  75  sales     |     1 |   5000 |          1
  76  sales     |     4 |   4800 |          2
  77  sales     |     3 |   4800 |          3
  78 (10 rows)
  79 </pre><p>
  80
  81     As shown here, the <code class="function">row_number</code> window function
  82     assigns sequential numbers to the rows within each partition,
  83     in the order defined by the <code class="literal">ORDER BY</code> clause
  84     (with tied rows numbered in an unspecified order).
  85     <code class="function">row_number</code> needs no explicit parameter,
  86     because its behavior
  87     is entirely determined by the <code class="literal">OVER</code> clause.
  88    </p><p>
  89     The rows considered by a window function are those of the <span class="quote">“<span class="quote">virtual
  90     table</span>”</span> produced by the query's <code class="literal">FROM</code> clause as filtered by its
  91     <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>, and <code class="literal">HAVING</code> clauses
  92     if any.  For example, a row removed because it does not meet the
  93     <code class="literal">WHERE</code> condition is not seen by any window function.
  94     A query can contain multiple window functions that slice up the data
  95     in different ways using different <code class="literal">OVER</code> clauses, but
  96     they all act on the same collection of rows defined by this virtual table.
  97    </p><p>
  98     We already saw that <code class="literal">ORDER BY</code> can be omitted if the ordering
  99     of rows is not important.  It is also possible to omit <code class="literal">PARTITION
 100     BY</code>, in which case there is a single partition containing all rows.
 101    </p><p>
 102     There is another important concept associated with window functions:
 103     for each row, there is a set of rows within its partition called its
 104     <em class="firstterm">window frame</em>.  Some window functions act only
 105     on the rows of the window frame, rather than of the whole partition.
 106     By default, if <code class="literal">ORDER BY</code> is supplied then the frame consists of
 107     all rows from the start of the partition up through the current row, plus
 108     any following rows that are equal to the current row according to the
 109     <code class="literal">ORDER BY</code> clause.  When <code class="literal">ORDER BY</code> is omitted the
 110     default frame consists of all rows in the partition.
 111      <a href="#ftn.id-1.4.5.6.9.5" class="footnote"><sup class="footnote" id="id-1.4.5.6.9.5">[5]</sup></a>
 112     Here is an example using <code class="function">sum</code>:
 113    </p><pre class="programlisting">
 114 SELECT salary, sum(salary) OVER () FROM empsalary;
 115 </pre><pre class="screen">
 116  salary |  sum
 117 --------+-------
 118    5200 | 47100
 119    5000 | 47100
 120    3500 | 47100
 121    4800 | 47100
 122    3900 | 47100
 123    4200 | 47100
 124    4500 | 47100
 125    4800 | 47100
 126    6000 | 47100
 127    5200 | 47100
 128 (10 rows)
 129 </pre><p>
 130     Above, since there is no <code class="literal">ORDER BY</code> in the <code class="literal">OVER</code>
 131     clause, the window frame is the same as the partition, which for lack of
 132     <code class="literal">PARTITION BY</code> is the whole table; in other words each sum is
 133     taken over the whole table and so we get the same result for each output
 134     row.  But if we add an <code class="literal">ORDER BY</code> clause, we get very different
 135     results:
 136    </p><pre class="programlisting">
 137 SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 138 </pre><pre class="screen">
 139  salary |  sum
 140 --------+-------
 141    3500 |  3500
 142    3900 |  7400
 143    4200 | 11600
 144    4500 | 16100
 145    4800 | 25700
 146    4800 | 25700
 147    5000 | 30700
 148    5200 | 41100
 149    5200 | 41100
 150    6000 | 47100
 151 (10 rows)
 152 </pre><p>
 153     Here the sum is taken from the first (lowest) salary up through the
 154     current one, including any duplicates of the current one (notice the
 155     results for the duplicated salaries).
 156    </p><p>
 157     Window functions are permitted only in the <code class="literal">SELECT</code> list
 158     and the <code class="literal">ORDER BY</code> clause of the query. They are forbidden
 159     elsewhere, such as in <code class="literal">GROUP BY</code>, <code class="literal">HAVING</code>
 160     and <code class="literal">WHERE</code> clauses.  This is because they logically
 161     execute after the processing of those clauses.  Also, window functions
 162     execute after non-window aggregate functions.  This means it is valid to
 163     include an aggregate function call in the arguments of a window function,
 164     but not vice versa.
 165    </p><p>
 166     If there is a need to filter or group rows after the window calculations
 167     are performed, you can use a sub-select.  For example:
 168
 169 </p><pre class="programlisting">
 170 SELECT depname, empno, salary, enroll_date
 171 FROM
 172   (SELECT depname, empno, salary, enroll_date,
 173      row_number() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
 174      FROM empsalary
 175   ) AS ss
 176 WHERE pos &lt; 3;
 177 </pre><p>
 178
 179     The above query only shows the rows from the inner query having
 180     <code class="literal">row_number</code> less than 3 (that is, the first
 181     two rows for each department).
 182    </p><p>
 183     When a query involves multiple window functions, it is possible to write
 184     out each one with a separate <code class="literal">OVER</code> clause, but this is
 185     duplicative and error-prone if the same windowing behavior is wanted
 186     for several functions.  Instead, each windowing behavior can be named
 187     in a <code class="literal">WINDOW</code> clause and then referenced in <code class="literal">OVER</code>.
 188     For example:
 189
 190 </p><pre class="programlisting">
 191 SELECT sum(salary) OVER w, avg(salary) OVER w
 192   FROM empsalary
 193   WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
 194 </pre><p>
 195    </p><p>
 196     More details about window functions can be found in
 197     <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a>,
 198     <a class="xref" href="functions-window.html" title="9.22. Window Functions">Section 9.22</a>,
 199     <a class="xref" href="queries-table-expressions.html#QUERIES-WINDOW" title="7.2.5. Window Function Processing">Section 7.2.5</a>, and the
 200     <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a> reference page.
 201    </p><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.id-1.4.5.6.9.5" class="footnote"><p><a href="#id-1.4.5.6.9.5" class="para"><sup class="para">[5] </sup></a>
 202        There are options to define the window frame in other ways, but
 203        this tutorial does not cover them.  See
 204        <a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a> for details.
 205       </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="tutorial-transactions.html" title="3.4. Transactions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="tutorial-advanced.html" title="Chapter 3. Advanced Features">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="tutorial-inheritance.html" title="3.6. Inheritance">Next</a></td></tr><tr><td width="40%" align="left" valign="top">3.4. Transactions </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"> 3.6. Inheritance</td></tr></table></div></body></html>