PGSQL16 新功能：GROUP BY 不用列出全部出現的 non-aggregation 欄位

MySQL/MariaDB 雖然超熱門，但有不少的 SQL 功能聽說高其他款的行為有點差異；例如 GROUP BY 的用法～

• GROUP BY list 的欄位，物以類聚
• SELECT list 套用 aggregation 的欄位，依照 GROUP BY 分類回傳結果並壓縮成一筆；window function 則是依照分類回傳但保持筆數（重複資料）
• SELECT list 中，既非 aggregation 欄位，又不是 GROUP BY list，卻又出現在 SQL 的欄位，資料會隨便亂回

對照之下，PGSQL 跟其他大品牌的關聯式資料庫，SQL 行為的規格會比較相近。

例如 GROUP BY 就比較標準：

• GROUP BY list 的欄位，物以類聚
• SELECT list 套用 aggregation 的欄位，依照 GROUP BY 分類回傳結果並壓縮成一筆；window function 則是依照分類回傳但保持筆數（重複資料）
• 「不允許」SELECT list 中，既非 aggregation 欄位，又不是 GROUP BY list，卻又出現在 SQL 的欄位

雖然有時令人稍微不習慣，但有時候又很需要的感覺：有偶爾的資料欄位之間有人為知道的關係（對 A 欄位做某種過濾或 JOIN 之後，B 欄位的值也會一樣均勻），但又不需要特地讓資料庫一直算（GROUP BY list 也要花力氣跑的），只想要某些欄位顯示出「一位代表」就好（因為預期都會長得一樣～）而不用寫落落長的 GROUP BY list。

到了 PGSQL16 ，就出一個很重要的功能～滿足這種不用每個欄位都放到 group by 的用途。

PGSQL 支援的同時又不打破舊有功能的方式，是新增一個 ANY_VALUE() 的 aggregation function。

先單純看一下這功能有點白痴

postgres=# select any_value(tablename) from pg_stats ;
   any_value   
---------------
 pg_tablespace
(1 row)

postgres=# 

這相當於 GROUP BY 的切割集合內，對這個欄位隨機挑一個。（不過實際上並不是隨機挑的，可以看原始 patch 的敘述。）

再來看一下對比用途：

原本不是 aggregation function 的一定強制列進去 group by list，不然就出錯。

這邊範例從pg_stats這個系統表，裡面預期一個表格會有多筆紀錄，每一筆紀錄都是欄位的抽樣統計資訊。

以下範例為了讓功能與效果明確一點，特地放一個 where 條件，用以代表實際 any_value() 使用上預期的條件。（不論是由於 JOIN 導致，還是因為 where 條件過濾的結果，或是資料本身就只有這樣的裝飾欄位）。

若限定 where 條件限定一個 schema（這邊列系統schema pg_catalog），原則上只要 group by 列出表格名稱就足夠，而 schema 名稱就算不列進去 group by 也是應該只有一種。

但普通 SQL 仍然強制要求我們要把 schema 欄位也列進去 group by 裡面。

這種狀況在「很大量」資料的表格上，就相當於要額外花 CPU 的力氣去檢視一輪「我們知道但資料庫不知道」只有一種資料的欄位。

postgres=# select schemaname, tablename, count(*)
from pg_stats
where schemaname = 'pg_catalog'
group by tablename;
ERROR:  column "pg_stats.schemaname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select schemaname, tablename, count(*) from pg_stats where s...
               ^
postgres=# 

postgres=# select schemaname, tablename, count(*)
from pg_stats
where schemaname = 'pg_catalog'
group by schemaname, tablename;
 schemaname |    tablename     | count 
------------+------------------+-------
 pg_catalog | pg_aggregate     |    22
 pg_catalog | pg_am            |     4
 pg_catalog | pg_amop          |     9
 pg_catalog | pg_amproc        |     6
 pg_catalog | pg_attribute     |    26
 pg_catalog | pg_auth_members  |     7
 pg_catalog | pg_authid        |    12
 pg_catalog | pg_cast          |     6
 pg_catalog | pg_class         |    33
 pg_catalog | pg_collation     |    12
 pg_catalog | pg_constraint    |    26
 pg_catalog | pg_conversion    |     8
 pg_catalog | pg_database      |    17
 pg_catalog | pg_depend        |     7
 pg_catalog | pg_description   |     4
 pg_catalog | pg_extension     |     8
 pg_catalog | pg_index         |    21
 pg_catalog | pg_init_privs    |     5
 pg_catalog | pg_language      |     9
 pg_catalog | pg_namespace     |     4
 pg_catalog | pg_opclass       |     9
 pg_catalog | pg_operator      |    15
 pg_catalog | pg_opfamily      |     5
 pg_catalog | pg_proc          |    30
 pg_catalog | pg_range         |     7
 pg_catalog | pg_rewrite       |     8
 pg_catalog | pg_shdescription |     3
 pg_catalog | pg_tablespace    |     5
 pg_catalog | pg_ts_config     |     5
...略
postgres=# 

避免讓已經知道不用浪費力氣去跑，只要取一個值出來的方式，就是把不需要額外算 non-aggregation 欄位套用 ANY_VALUE()。

postgres=# select
 any_value(schemaname) as schemaname,
 tablename,
 count(*)
from pg_stats
where schemaname = 'pg_catalog'
group by tablename;
 schemaname |    tablename     | count 
------------+------------------+-------
 pg_catalog | pg_aggregate     |    22
 pg_catalog | pg_am            |     4
 pg_catalog | pg_amop          |     9
 pg_catalog | pg_amproc        |     6
 pg_catalog | pg_attribute     |    26
 pg_catalog | pg_auth_members  |     7
 pg_catalog | pg_authid        |    12
 pg_catalog | pg_cast          |     6
 pg_catalog | pg_class         |    33
 pg_catalog | pg_collation     |    12
 pg_catalog | pg_constraint    |    26
 pg_catalog | pg_conversion    |     8
 pg_catalog | pg_database      |    17
 pg_catalog | pg_depend        |     7
 pg_catalog | pg_description   |     4
 pg_catalog | pg_extension     |     8
 pg_catalog | pg_index         |    21
 pg_catalog | pg_init_privs    |     5
 pg_catalog | pg_language      |     9
 pg_catalog | pg_namespace     |     4
 pg_catalog | pg_opclass       |     9
 pg_catalog | pg_operator      |    15
 pg_catalog | pg_opfamily      |     5
 pg_catalog | pg_proc          |    30
 pg_catalog | pg_range         |     7
 pg_catalog | pg_rewrite       |     8
 pg_catalog | pg_shdescription |     3
 pg_catalog | pg_tablespace    |     5
 pg_catalog | pg_ts_config     |     5
...略
postgres=# 

掛這個 function 就可以不用每個欄位都放到 group by 了～對於部份的用途變得更加方便～尤其是很多固定資訊的欄位列起來很費工，或是group by list太大量時（group by list 太多，在經驗上也是可能有效能上的狀況。。）

不過要是發現自己寫到需要這樣，可能要檢視一下這是不是真的需要（有時可能是自己寫程式已經有點頭昏腦脹了）。

參考資料：
MySQL GROUP BY behavior - Stack Overflow
Guide to ANY_VALUE() Functionality in PostgreSQL 16 | EDB Blog
PostgreSQL Hackers: ANY_VALUE aggregate
ANY_VALUE aggregate - Commitfest 2023-03 - PostgreSQL.org
Table 9.59. General-Purpose Aggregate Functions: any_value ( anyelement ) - PostgreSQL: Documentation: 16: 9.21. Aggregate Functions