如何让GROUP BY无结果的维度组合返回计数为0的结果？

初始数据表 #

原查询及结果 #

执行以下SQL：

select service, frequency, count(membership_id) from your_table group by service, frequency

得到结果：

期望结果 #

需要返回service和frequency的所有可能组合，无匹配数据的计数填0：

实现方案 #

核心思路是先生成两个维度的全量组合，再与原表的聚合结果左连接，以此保留无数据的组合并填充0。

方案1：通用动态生成全组合（适配任意维度值）

-- 生成所有service和frequency的可能组合
WITH all_combinations AS (
    SELECT DISTINCT t1.service, t2.frequency
    FROM your_table t1
    CROSS JOIN your_table t2
),
-- 原表的聚合结果
aggregated_data AS (
    SELECT service, frequency, COUNT(membership_id) AS count
    FROM your_table
    GROUP BY service, frequency
)
-- 左连接并将NULL替换为0
SELECT 
    ac.service, 
    ac.frequency, 
    COALESCE(ad.count, 0) AS count
FROM all_combinations ac
LEFT JOIN aggregated_data ad 
    ON ac.service = ad.service 
    AND ac.frequency = ad.frequency
ORDER BY ac.service, ac.frequency;

方案2：固定枚举频率值（如果频率是已知固定集合）

如果frequency的可选值是固定的（比如仅monthly/quarterly/yearly），可以直接枚举避免冗余计算：

WITH all_services AS (
    SELECT DISTINCT service FROM your_table
),
all_frequencies AS (
    SELECT 'monthly' AS frequency UNION ALL
    SELECT 'quarterly' UNION ALL
    SELECT 'yearly'
),
all_combinations AS (
    SELECT service, frequency FROM all_services CROSS JOIN all_frequencies
),
aggregated_data AS (
    SELECT service, frequency, COUNT(membership_id) AS count
    FROM your_table
    GROUP BY service, frequency
)
SELECT 
    ac.service, 
    ac.frequency, 
    COALESCE(ad.count, 0) AS count
FROM all_combinations ac
LEFT JOIN aggregated_data ad 
    ON ac.service = ad.service 
    AND ac.frequency = ad.frequency
ORDER BY ac.service, ac.frequency;

关键说明 #

• CROSS JOIN：用于生成两个维度的所有配对，确保没有遗漏任何组合。
• COALESCE：将左连接后无匹配数据产生的NULL值替换为0，符合计数要求。
• 以上写法兼容MySQL、PostgreSQL、SQL Server等主流数据库，可根据实际数据库特性微调。

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