begriffs open source - ai-pg/blob - full-docs/txt/tutorial-window.txt

   1
   2 3.5. Window Functions #
   3
   4    A window function performs a calculation across a set of table rows
   5    that are somehow related to the current row. This is comparable to the
   6    type of calculation that can be done with an aggregate function.
   7    However, window functions do not cause rows to become grouped into a
   8    single output row like non-window aggregate calls would. Instead, the
   9    rows retain their separate identities. Behind the scenes, the window
  10    function is able to access more than just the current row of the query
  11    result.
  12
  13    Here is an example that shows how to compare each employee's salary
  14    with the average salary in his or her department:
  15 SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM emps
  16 alary;
  17
  18   depname  | empno | salary |          avg
  19 -----------+-------+--------+-----------------------
  20  develop   |    11 |   5200 | 5020.0000000000000000
  21  develop   |     7 |   4200 | 5020.0000000000000000
  22  develop   |     9 |   4500 | 5020.0000000000000000
  23  develop   |     8 |   6000 | 5020.0000000000000000
  24  develop   |    10 |   5200 | 5020.0000000000000000
  25  personnel |     5 |   3500 | 3700.0000000000000000
  26  personnel |     2 |   3900 | 3700.0000000000000000
  27  sales     |     3 |   4800 | 4866.6666666666666667
  28  sales     |     1 |   5000 | 4866.6666666666666667
  29  sales     |     4 |   4800 | 4866.6666666666666667
  30 (10 rows)
  31
  32    The first three output columns come directly from the table empsalary,
  33    and there is one output row for each row in the table. The fourth
  34    column represents an average taken across all the table rows that have
  35    the same depname value as the current row. (This actually is the same
  36    function as the non-window avg aggregate, but the OVER clause causes it
  37    to be treated as a window function and computed across the window
  38    frame.)
  39
  40    A window function call always contains an OVER clause directly
  41    following the window function's name and argument(s). This is what
  42    syntactically distinguishes it from a normal function or non-window
  43    aggregate. The OVER clause determines exactly how the rows of the query
  44    are split up for processing by the window function. The PARTITION BY
  45    clause within OVER divides the rows into groups, or partitions, that
  46    share the same values of the PARTITION BY expression(s). For each row,
  47    the window function is computed across the rows that fall into the same
  48    partition as the current row.
  49
  50    You can also control the order in which rows are processed by window
  51    functions using ORDER BY within OVER. (The window ORDER BY does not
  52    even have to match the order in which the rows are output.) Here is an
  53    example:
  54 SELECT depname, empno, salary,
  55        row_number() OVER (PARTITION BY depname ORDER BY salary DESC)
  56 FROM empsalary;
  57
  58   depname  | empno | salary | row_number
  59 -----------+-------+--------+------------
  60  develop   |     8 |   6000 |          1
  61  develop   |    10 |   5200 |          2
  62  develop   |    11 |   5200 |          3
  63  develop   |     9 |   4500 |          4
  64  develop   |     7 |   4200 |          5
  65  personnel |     2 |   3900 |          1
  66  personnel |     5 |   3500 |          2
  67  sales     |     1 |   5000 |          1
  68  sales     |     4 |   4800 |          2
  69  sales     |     3 |   4800 |          3
  70 (10 rows)
  71
  72    As shown here, the row_number window function assigns sequential
  73    numbers to the rows within each partition, in the order defined by the
  74    ORDER BY clause (with tied rows numbered in an unspecified order).
  75    row_number needs no explicit parameter, because its behavior is
  76    entirely determined by the OVER clause.
  77
  78    The rows considered by a window function are those of the “virtual
  79    table” produced by the query's FROM clause as filtered by its WHERE,
  80    GROUP BY, and HAVING clauses if any. For example, a row removed because
  81    it does not meet the WHERE condition is not seen by any window
  82    function. A query can contain multiple window functions that slice up
  83    the data in different ways using different OVER clauses, but they all
  84    act on the same collection of rows defined by this virtual table.
  85
  86    We already saw that ORDER BY can be omitted if the ordering of rows is
  87    not important. It is also possible to omit PARTITION BY, in which case
  88    there is a single partition containing all rows.
  89
  90    There is another important concept associated with window functions:
  91    for each row, there is a set of rows within its partition called its
  92    window frame. Some window functions act only on the rows of the window
  93    frame, rather than of the whole partition. By default, if ORDER BY is
  94    supplied then the frame consists of all rows from the start of the
  95    partition up through the current row, plus any following rows that are
  96    equal to the current row according to the ORDER BY clause. When ORDER
  97    BY is omitted the default frame consists of all rows in the partition.
  98    ^[5] Here is an example using sum:
  99 SELECT salary, sum(salary) OVER () FROM empsalary;
 100
 101  salary |  sum
 102 --------+-------
 103    5200 | 47100
 104    5000 | 47100
 105    3500 | 47100
 106    4800 | 47100
 107    3900 | 47100
 108    4200 | 47100
 109    4500 | 47100
 110    4800 | 47100
 111    6000 | 47100
 112    5200 | 47100
 113 (10 rows)
 114
 115    Above, since there is no ORDER BY in the OVER clause, the window frame
 116    is the same as the partition, which for lack of PARTITION BY is the
 117    whole table; in other words each sum is taken over the whole table and
 118    so we get the same result for each output row. But if we add an ORDER
 119    BY clause, we get very different results:
 120 SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 121
 122  salary |  sum
 123 --------+-------
 124    3500 |  3500
 125    3900 |  7400
 126    4200 | 11600
 127    4500 | 16100
 128    4800 | 25700
 129    4800 | 25700
 130    5000 | 30700
 131    5200 | 41100
 132    5200 | 41100
 133    6000 | 47100
 134 (10 rows)
 135
 136    Here the sum is taken from the first (lowest) salary up through the
 137    current one, including any duplicates of the current one (notice the
 138    results for the duplicated salaries).
 139
 140    Window functions are permitted only in the SELECT list and the ORDER BY
 141    clause of the query. They are forbidden elsewhere, such as in GROUP BY,
 142    HAVING and WHERE clauses. This is because they logically execute after
 143    the processing of those clauses. Also, window functions execute after
 144    non-window aggregate functions. This means it is valid to include an
 145    aggregate function call in the arguments of a window function, but not
 146    vice versa.
 147
 148    If there is a need to filter or group rows after the window
 149    calculations are performed, you can use a sub-select. For example:
 150 SELECT depname, empno, salary, enroll_date
 151 FROM
 152   (SELECT depname, empno, salary, enroll_date,
 153      row_number() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
 154      FROM empsalary
 155   ) AS ss
 156 WHERE pos < 3;
 157
 158    The above query only shows the rows from the inner query having
 159    row_number less than 3 (that is, the first two rows for each
 160    department).
 161
 162    When a query involves multiple window functions, it is possible to
 163    write out each one with a separate OVER clause, but this is duplicative
 164    and error-prone if the same windowing behavior is wanted for several
 165    functions. Instead, each windowing behavior can be named in a WINDOW
 166    clause and then referenced in OVER. For example:
 167 SELECT sum(salary) OVER w, avg(salary) OVER w
 168   FROM empsalary
 169   WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
 170
 171    More details about window functions can be found in Section 4.2.8,
 172    Section 9.22, Section 7.2.5, and the SELECT reference page.
 173
 174    ^[5] There are options to define the window frame in other ways, but
 175    this tutorial does not cover them. See Section 4.2.8 for details.