PostgreSQL 19 preview - group by all

PostgreSQL 19 preview - group by all

PostgreSQL 19 将新增 group by all 的分组聚合语法alias, 在select子句中的所有不带聚合函数、窗口函数的选择列都会被作为group by子句. 方便简化SQL书写内容.

https://github.com/postgres/postgres/commit/ef38a4d9756db9ae1d20f40aa39f3cf76059b81a

Add GROUP BY ALL.  
GROUP BY ALL is a form of GROUP BY that adds any TargetExpr that does  
not contain an aggregate or window function into the groupClause of  
the query, making it exactly equivalent to specifying those same  
expressions in an explicit GROUP BY list.  

This feature is useful for certain kinds of data exploration.  It's  
already present in some other DBMSes, and the SQL committee recently  
accepted it into the standard, so we can be reasonably confident in  
the syntax being stable.  We do have to invent part of the semantics,  
as the standard doesn't allow for expressions in GROUP BY, so they  
haven't specified what to do with window functions.  We assume that  
those should be treated like aggregates, i.e., left out of the  
constructed GROUP BY list.  

In passing, wordsmith some existing documentation about GROUP BY,  
and update some neglected synopsis entries in select_into.sgml.  

Author: David Christensen <[email protected]>  
Reviewed-by: Tom Lane <[email protected]>  
Discussion: https://postgr.es/m/CAHM0NXjz0kDwtzoe-fnHAqPB1qA8_VJN0XAmCgUZ+iPnvP5LbA@mail.gmail.com  

例子

   <para>  
    PostgreSQL also supports the syntax <literal>GROUP BY ALL</literal>,  
which is equivalent to explicitly writing all select-list entries that  
do not contain either an aggregate function or a window function.  
    This can greatly simplify ad-hoc exploration of data.  
    As an example, these queries are equivalent:  
<screen>  
<prompt>=&gt;</prompt> <userinput>SELECT a, b, a + b, sum(c) FROM test1 GROUP BY ALL;</userinput>  
 a | b | ?column? | sum  
---+---+----------+----  
 1 | 4 |        5 |  9  
 2 | 5 |        7 | 12  
 3 | 6 |        9 | 15  
(3 rows)  
<prompt>=&gt;</prompt> <userinput>SELECT a, b, a + b, sum(c) FROM test1 GROUP BY a, b, a + b;</userinput>  
 a | b | ?column? | sum  
---+---+----------+----  
 1 | 4 |        5 |  9  
 2 | 5 |        7 | 12  
 3 | 6 |        9 | 15  
(3 rows)  
</screen>