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

   1
   2 9.22. Window Functions #
   3
   4    Window functions provide the ability to perform calculations across
   5    sets of rows that are related to the current query row. See Section 3.5
   6    for an introduction to this feature, and Section 4.2.8 for syntax
   7    details.
   8
   9    The built-in window functions are listed in Table 9.67. Note that these
  10    functions must be invoked using window function syntax, i.e., an OVER
  11    clause is required.
  12
  13    In addition to these functions, any built-in or user-defined ordinary
  14    aggregate (i.e., not ordered-set or hypothetical-set aggregates) can be
  15    used as a window function; see Section 9.21 for a list of the built-in
  16    aggregates. Aggregate functions act as window functions only when an
  17    OVER clause follows the call; otherwise they act as plain aggregates
  18    and return a single row for the entire set.
  19
  20    Table 9.67. General-Purpose Window Functions
  21
  22    Function
  23
  24    Description
  25
  26    row_number () → bigint
  27
  28    Returns the number of the current row within its partition, counting
  29    from 1.
  30
  31    rank () → bigint
  32
  33    Returns the rank of the current row, with gaps; that is, the row_number
  34    of the first row in its peer group.
  35
  36    dense_rank () → bigint
  37
  38    Returns the rank of the current row, without gaps; this function
  39    effectively counts peer groups.
  40
  41    percent_rank () → double precision
  42
  43    Returns the relative rank of the current row, that is (rank - 1) /
  44    (total partition rows - 1). The value thus ranges from 0 to 1
  45    inclusive.
  46
  47    cume_dist () → double precision
  48
  49    Returns the cumulative distribution, that is (number of partition rows
  50    preceding or peers with current row) / (total partition rows). The
  51    value thus ranges from 1/N to 1.
  52
  53    ntile ( num_buckets integer ) → integer
  54
  55    Returns an integer ranging from 1 to the argument value, dividing the
  56    partition as equally as possible.
  57
  58    lag ( value anycompatible [, offset integer [, default anycompatible ]]
  59    ) → anycompatible
  60
  61    Returns value evaluated at the row that is offset rows before the
  62    current row within the partition; if there is no such row, instead
  63    returns default (which must be of a type compatible with value). Both
  64    offset and default are evaluated with respect to the current row. If
  65    omitted, offset defaults to 1 and default to NULL.
  66
  67    lead ( value anycompatible [, offset integer [, default anycompatible
  68    ]] ) → anycompatible
  69
  70    Returns value evaluated at the row that is offset rows after the
  71    current row within the partition; if there is no such row, instead
  72    returns default (which must be of a type compatible with value). Both
  73    offset and default are evaluated with respect to the current row. If
  74    omitted, offset defaults to 1 and default to NULL.
  75
  76    first_value ( value anyelement ) → anyelement
  77
  78    Returns value evaluated at the row that is the first row of the window
  79    frame.
  80
  81    last_value ( value anyelement ) → anyelement
  82
  83    Returns value evaluated at the row that is the last row of the window
  84    frame.
  85
  86    nth_value ( value anyelement, n integer ) → anyelement
  87
  88    Returns value evaluated at the row that is the n'th row of the window
  89    frame (counting from 1); returns NULL if there is no such row.
  90
  91    All of the functions listed in Table 9.67 depend on the sort ordering
  92    specified by the ORDER BY clause of the associated window definition.
  93    Rows that are not distinct when considering only the ORDER BY columns
  94    are said to be peers. The four ranking functions (including cume_dist)
  95    are defined so that they give the same answer for all rows of a peer
  96    group.
  97
  98    Note that first_value, last_value, and nth_value consider only the rows
  99    within the “window frame”, which by default contains the rows from the
 100    start of the partition through the last peer of the current row. This
 101    is likely to give unhelpful results for last_value and sometimes also
 102    nth_value. You can redefine the frame by adding a suitable frame
 103    specification (RANGE, ROWS or GROUPS) to the OVER clause. See
 104    Section 4.2.8 for more information about frame specifications.
 105
 106    When an aggregate function is used as a window function, it aggregates
 107    over the rows within the current row's window frame. An aggregate used
 108    with ORDER BY and the default window frame definition produces a
 109    “running sum” type of behavior, which may or may not be what's wanted.
 110    To obtain aggregation over the whole partition, omit ORDER BY or use
 111    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame
 112    specifications can be used to obtain other effects.
 113
 114 Note
 115
 116    The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for
 117    lead, lag, first_value, last_value, and nth_value. This is not
 118    implemented in PostgreSQL: the behavior is always the same as the
 119    standard's default, namely RESPECT NULLS. Likewise, the standard's FROM
 120    FIRST or FROM LAST option for nth_value is not implemented: only the
 121    default FROM FIRST behavior is supported. (You can achieve the result
 122    of FROM LAST by reversing the ORDER BY ordering.)