Home Explore Blog CI



postgresql

25th chunk of `doc/src/sgml/syntax.sgml`
102058336a5d978997187606000e30f4b3328b156d8b0f230000000100000fa3
 partition.
   </para>

   <para>
    In <literal>RANGE</literal> or <literal>GROUPS</literal> mode,
    a <replaceable>frame_start</replaceable> of
    <literal>CURRENT ROW</literal> means the frame starts with the current
    row's first <firstterm>peer</firstterm> row (a row that the
    window's <literal>ORDER BY</literal> clause sorts as equivalent to the
    current row), while a <replaceable>frame_end</replaceable> of
    <literal>CURRENT ROW</literal> means the frame ends with the current
    row's last peer row.
    In <literal>ROWS</literal> mode, <literal>CURRENT ROW</literal> simply
    means the current row.
   </para>

   <para>
    In the <replaceable>offset</replaceable> <literal>PRECEDING</literal>
    and <replaceable>offset</replaceable> <literal>FOLLOWING</literal> frame
    options, the <replaceable>offset</replaceable> must be an expression not
    containing any variables, aggregate functions, or window functions.
    The meaning of the <replaceable>offset</replaceable> depends on the
    frame mode:
    <itemizedlist>
     <listitem>
      <para>
       In <literal>ROWS</literal> mode,
       the <replaceable>offset</replaceable> must yield a non-null,
       non-negative integer, and the option means that the frame starts or
       ends the specified number of rows before or after the current row.
      </para>
     </listitem>
     <listitem>
      <para>
       In <literal>GROUPS</literal> mode,
       the <replaceable>offset</replaceable> again must yield a non-null,
       non-negative integer, and the option means that the frame starts or
       ends the specified number of <firstterm>peer groups</firstterm>
       before or after the current row's peer group, where a peer group is a
       set of rows that are equivalent in the <literal>ORDER BY</literal>
       ordering.  (There must be an <literal>ORDER BY</literal> clause
       in the window definition to use <literal>GROUPS</literal> mode.)
      </para>
     </listitem>
     <listitem>
      <para>
       In <literal>RANGE</literal> mode, these options require that
       the <literal>ORDER BY</literal> clause specify exactly one column.
       The <replaceable>offset</replaceable> specifies the maximum
       difference between the value of that column in the current row and
       its value in preceding or following rows of the frame.  The data type
       of the <replaceable>offset</replaceable> expression varies depending
       on the data type of the ordering column.  For numeric ordering
       columns it is typically of the same type as the ordering column,
       but for datetime ordering columns it is an <type>interval</type>.
       For example, if the ordering column is of type <type>date</type>
       or <type>timestamp</type>, one could write <literal>RANGE BETWEEN
       '1 day' PRECEDING AND '10 days' FOLLOWING</literal>.
       The <replaceable>offset</replaceable> is still required to be
       non-null and non-negative, though the meaning
       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

Title: Window Function Frame Specification: Offset Meanings, Range Mode and Frame Exclusion
Summary
This section details the meaning of the 'offset' in the PRECEDING/FOLLOWING frame options within window functions. The offset must be an expression without variables, aggregates, or window functions. Its meaning varies based on the frame mode: in ROWS, it's a non-negative integer representing rows before/after; in GROUPS, it's the number of peer groups before/after (requiring an ORDER BY clause); in RANGE, it specifies the maximum difference in the ORDER BY column's value (requiring exactly one ORDER BY column), with the offset's data type depending on the ordering column. It also notes that 0 PRECEDING/FOLLOWING are equivalent to CURRENT ROW in ROWS/GROUPS and usually in RANGE mode. Finally, it introduces the frame_exclusion option, which allows excluding rows around the current row.