Home • Explore • Blog • CI
☰



postgresql
18th chunk of `doc/src/sgml/queries.sgml`
0754d07c655dcbe9cb9e2a6a66e00ef1619557bc636098850000000100000fa2

    <literal>GROUPING SETS</literal> clause.  If one <literal>GROUPING SETS</literal> clause
    is nested inside another, the effect is the same as if all the elements of
    the inner clause had been written directly in the outer clause.
   </para>

   <para>
    If multiple grouping items are specified in a single <literal>GROUP BY</literal>
    clause, then the final list of grouping sets is the Cartesian product of the
    individual items.  For example:
<programlisting>
GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
</programlisting>
    is equivalent to
<programlisting>
GROUP BY GROUPING SETS (
    (a, b, c, d), (a, b, c, e),
    (a, b, d),    (a, b, e),
    (a, c, d),    (a, c, e),
    (a, d),       (a, e)
)
</programlisting>
   </para>

   <para>
    <indexterm zone="queries-grouping-sets">
     <primary>ALL</primary>
     <secondary>GROUP BY ALL</secondary>
    </indexterm>
    <indexterm zone="queries-grouping-sets">
     <primary>DISTINCT</primary>
     <secondary>GROUP BY DISTINCT</secondary>
    </indexterm>
    When specifying multiple grouping items together, the final set of grouping
    sets might contain duplicates. For example:
<programlisting>
GROUP BY ROLLUP (a, b), ROLLUP (a, c)
</programlisting>
    is equivalent to
<programlisting>
GROUP BY GROUPING SETS (
    (a, b, c),
    (a, b),
    (a, b),
    (a, c),
    (a),
    (a),
    (a, c),
    (a),
    ()
)
</programlisting>
    If these duplicates are undesirable, they can be removed using the
    <literal>DISTINCT</literal> clause directly on the <literal>GROUP BY</literal>.
    Therefore:
<programlisting>
GROUP BY <emphasis>DISTINCT</emphasis> ROLLUP (a, b), ROLLUP (a, c)
</programlisting>
    is equivalent to
<programlisting>
GROUP BY GROUPING SETS (
    (a, b, c),
    (a, b),
    (a, c),
    (a),
    ()
)
</programlisting>
    This is not the same as using <literal>SELECT DISTINCT</literal> because the output
    rows may still contain duplicates.  If any of the ungrouped columns contains NULL,
    it will be indistinguishable from the NULL used when that same column is grouped.
   </para>

  <note>
   <para>
    The construct <literal>(a, b)</literal> is normally recognized in expressions as
    a <link linkend="sql-syntax-row-constructors">row constructor</link>.
    Within the <literal>GROUP BY</literal> clause, this does not apply at the top
    levels of expressions, and <literal>(a, b)</literal> is parsed as a list of
    expressions as described above.  If for some reason you <emphasis>need</emphasis>
    a row constructor in a grouping expression, use <literal>ROW(a, b)</literal>.
   </para>
  </note>
  </sect2>

  <sect2 id="queries-window">
   <title>Window Function Processing</title>

   <indexterm zone="queries-window">
    <primary>window function</primary>
    <secondary>order of execution</secondary>
   </indexterm>

   <para>
    If the query contains any window functions (see
    <xref linkend="tutorial-window"/>,
    <xref linkend="functions-window"/> and
    <xref linkend="syntax-window-functions"/>), these functions are evaluated
    after any grouping, aggregation, and <literal>HAVING</literal> filtering is
    performed.  That is, if the query uses any aggregates, <literal>GROUP
    BY</literal>, or <literal>HAVING</literal>, then the rows seen by the window functions
    are the group rows instead of the original table rows from
    <literal>FROM</literal>/<literal>WHERE</literal>.
   </para>

   <para>
    When multiple window functions are used, all the window functions having
    equivalent <literal>PARTITION BY</literal> and <literal>ORDER BY</literal>
    clauses in their window definitions are guaranteed to see the same
    ordering of the input rows, even if the <literal>ORDER BY</literal> does
    not uniquely determine the ordering.
    However, no guarantees are made about the evaluation of functions having
    different <literal>PARTITION BY</literal> or <literal>ORDER BY</literal> specifications.
    (In such cases
Title: Advanced SQL Grouping and Window Functions
Summary
This section covers advanced SQL grouping techniques and window function processing. It explains how multiple grouping items in a GROUP BY clause result in a Cartesian product of grouping sets. The text demonstrates how to use DISTINCT with GROUP BY to remove duplicate groupings. It also clarifies the parsing of expressions within GROUP BY clauses. The section then transitions to window function processing, explaining that these functions are evaluated after grouping, aggregation, and HAVING filtering. It notes that window functions see group rows rather than original table rows when aggregates, GROUP BY, or HAVING are used. The text also discusses the ordering guarantees for multiple window functions with equivalent PARTITION BY and ORDER BY clauses, while noting that no guarantees are made for functions with different specifications.