PostgreSQL Grouping Sets：显示零计数分组或命名分组

原查询与问题 #

原查询语句：

select count(*)
from "my_table"
where field1 = true and field2 = 'delivered'
group by grouping sets((field3), (field4), (field5))
having field3 = true or field4 = true or field5

当前存在两个问题：

• 无法区分每个计数对应的分组字段（比如不知道结果里的1、2分别对应field3还是field4）
• 没有符合条件的分组（比如field5）会被直接排除，得不到计数为0的结果

当前输出结果（缺失field5的0值）：

count
_____
1
2

期望结果 #

两种可行的输出形式：
形式一（每行对应一个分组，包含0值）：

count
-----
1
2
0

形式二（一行展示所有分组的计数）：

count_field3 | count_field4 | count_field5
------------------------------------------
1            | 2            | 0

解决方案 #

形式一实现：每行对应分组+保留0值 #

通过构造全部分组标签，左连接统计结果，将缺失值转为0：

WITH grouped_counts AS (
    SELECT
        -- 用grouping函数判断当前分组对应的字段
        CASE
            WHEN grouping(field3) = 0 THEN 'field3'
            WHEN grouping(field4) = 0 THEN 'field4'
            WHEN grouping(field5) = 0 THEN 'field5'
        END AS group_label,
        count(*) AS cnt
    FROM "my_table"
    WHERE field1 = true AND field2 = 'delivered'
    GROUP BY grouping sets((field3), (field4), (field5))
    HAVING (field3 = true) OR (field4 = true) OR (field5)
)
SELECT
    COALESCE(g.cnt, 0) AS count
FROM (
    -- 生成所有需要统计的分组标签
    VALUES ('field3'), ('field4'), ('field5')
) AS all_groups(group_label)
LEFT JOIN grouped_counts g ON all_groups.group_label = g.group_label;

形式二实现：一行展示所有分组计数 #

用条件聚合直接统计每个字段的符合条件数量，天然支持返回0值：

SELECT
    COUNT(CASE WHEN field3 = true THEN 1 END) AS count_field3,
    COUNT(CASE WHEN field4 = true THEN 1 END) AS count_field4,
    COUNT(CASE WHEN field5 THEN 1 END) AS count_field5
FROM "my_table"
WHERE field1 = true AND field2 = 'delivered';

这种方式不需要使用grouping sets，通过CASE语句筛选每个字段的符合条件行，COUNT会自动忽略不符合条件的NULL值，没有匹配行时直接返回0。

内容的提问来源于stack exchange，提问作者chishiki