Home • Explore • Blog • CI
☰



postgresql
26th chunk of `doc/src/sgml/syntax.sgml`
a60409b4b329587d3b1b7dc5453705570ec2313511daac540000000100000fa0
  of <quote>non-negative</quote> depends on its data type.
      </para>
     </listitem>
    </itemizedlist>
    In any case, the distance to the end of the frame is limited by the
    distance to the end of the partition, so that for rows near the partition
    ends the frame might contain fewer rows than elsewhere.
   </para>

   <para>
    Notice that in both <literal>ROWS</literal> and <literal>GROUPS</literal>
    mode, <literal>0 PRECEDING</literal> and <literal>0 FOLLOWING</literal>
    are equivalent to <literal>CURRENT ROW</literal>.  This normally holds
    in <literal>RANGE</literal> mode as well, for an appropriate
    data-type-specific meaning of <quote>zero</quote>.
   </para>

   <para>
    The <replaceable>frame_exclusion</replaceable> option allows rows around
    the current row to be excluded from the frame, even if they would be
    included according to the frame start and frame end options.
    <literal>EXCLUDE CURRENT ROW</literal> excludes the current row from the
    frame.
    <literal>EXCLUDE GROUP</literal> excludes the current row and its
    ordering peers from the frame.
    <literal>EXCLUDE TIES</literal> excludes any peers of the current
    row from the frame, but not the current row itself.
    <literal>EXCLUDE NO OTHERS</literal> simply specifies explicitly the
    default behavior of not excluding the current row or its peers.
   </para>

   <para>
    The default framing option is <literal>RANGE UNBOUNDED PRECEDING</literal>,
    which is the same as <literal>RANGE BETWEEN UNBOUNDED PRECEDING AND
    CURRENT ROW</literal>.  With <literal>ORDER BY</literal>, this sets the frame to be
    all rows from the partition start up through the current row's last
    <literal>ORDER BY</literal> peer.  Without <literal>ORDER BY</literal>,
    this means all rows of the partition are included in the window frame,
    since all rows become peers of the current row.
   </para>

   <para>
    Restrictions are that
    <replaceable>frame_start</replaceable> cannot be <literal>UNBOUNDED FOLLOWING</literal>,
    <replaceable>frame_end</replaceable> cannot be <literal>UNBOUNDED PRECEDING</literal>,
    and the <replaceable>frame_end</replaceable> choice cannot appear earlier in the
    above list of <replaceable>frame_start</replaceable>
    and <replaceable>frame_end</replaceable> options than
    the <replaceable>frame_start</replaceable> choice does &mdash; for example
    <literal>RANGE BETWEEN CURRENT ROW AND <replaceable>offset</replaceable>
    PRECEDING</literal> is not allowed.
    But, for example, <literal>ROWS BETWEEN 7 PRECEDING AND 8
    PRECEDING</literal> is allowed, even though it would never select any
    rows.
   </para>

   <para>
    If <literal>FILTER</literal> is specified, then only the input
    rows for which the <replaceable>filter_clause</replaceable>
    evaluates to true are fed to the window function; other rows
    are discarded.  Only window functions that are aggregates accept
    a <literal>FILTER</literal> clause.
   </para>

   <para>
    The built-in window functions are described in <xref
    linkend="functions-window-table"/>.  Other window functions can be added by
    the user.  Also, any built-in or user-defined general-purpose or
    statistical aggregate can be used as a window function.  (Ordered-set
    and hypothetical-set aggregates cannot presently be used as window functions.)
   </para>

   <para>
    The syntaxes using <literal>*</literal> are used for calling parameter-less
    aggregate functions as window functions, for example
    <literal>count(*) OVER (PARTITION BY x ORDER BY y)</literal>.
    The asterisk (<literal>*</literal>) is customarily not used for
    window-specific functions.  Window-specific functions do not
    allow <literal>DISTINCT</literal> or <literal>ORDER BY</literal> to be used within the
    function argument list.
   </para>

   <para>
    Window function calls are permitted only in the <literal>SELECT</literal>
    list and
Title: Window Function Frame Exclusion, Defaults, Restrictions, and Aggregate Use
Summary
This section describes the frame_exclusion option for excluding rows from the frame, including EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, and EXCLUDE NO OTHERS. It explains that the default framing option is RANGE UNBOUNDED PRECEDING. It outlines restrictions on frame_start and frame_end, preventing UNBOUNDED FOLLOWING and PRECEDING, respectively, and ensuring frame_end options don't precede frame_start. The section also touches on the FILTER clause for window functions, allowing the passing of only rows that evaluate to true. It mentions that built-in and user-defined aggregates can be used as window functions, and that the * syntax is used for parameter-less aggregate functions as window functions. Finally, it specifies window function calls are only permitted in SELECT and ORDER BY lists.