SQL分组统计实现全维度组合零计数结果查询方法

现有名为services的服务表，包含ACT_MONTH（激活月份）、S_TYPE（服务类型）两个字段，需编写SQL返回所有月份与所有服务类型的全量组合，以及每个组合对应的当月激活服务数量。
常规GROUP BY分组统计仅会返回存在对应服务记录的分组，无法展示无服务激活记录的月份/服务类型组合的零值结果，要求这类无对应数据的组合也需返回QTY字段值为0的记录，预期返回结果结构如下：

ACT_MONTH | S_TYPE | QTY
==========+========+=====
2022M01   | A      |  20
2022M01   | B      |  33
2022M02   | A      |   6
2022M02   | B      |   0
2022M03   | A      |  12
2022M03   | B      |   4
2022M04   | A      |   0
2022M04   | B      |   0
2022M05   | A      |   0
2022M05   | B      |   0
...
2022M12   | A      |   0
2022M12   | B      |   0

核心逻辑是先通过笛卡尔积生成两个维度的全量组合，再左关联实际统计值，空值补0：

• 先分别取出去重的激活月份集合、去重的服务类型集合
• 对两个集合做笛卡尔积，得到所有月份+服务类型的完整组合
• 对原表做常规分组聚合，得到有记录的组合的激活数量
• 用全量组合左关联聚合结果，关联不到的组合数量统一赋值为0

通用写法（兼容MySQL8.0+、PostgreSQL、SQL Server等主流数据库） #

WITH all_months AS (
    -- 提取所有出现过的去重激活月份
    SELECT DISTINCT ACT_MONTH FROM services
),
all_types AS (
    -- 提取所有出现过的去重服务类型
    SELECT DISTINCT S_TYPE FROM services
),
full_comb AS (
    -- 笛卡尔积生成全量维度组合
    SELECT m.ACT_MONTH, t.S_TYPE
    FROM all_months m
    CROSS JOIN all_types t
),
stat AS (
    -- 统计有实际记录的组合激活量
    SELECT ACT_MONTH, S_TYPE, COUNT(*) AS QTY
    FROM services
    GROUP BY ACT_MONTH, S_TYPE
)
-- 左连补0
SELECT
    c.ACT_MONTH,
    c.S_TYPE,
    COALESCE(s.QTY, 0) AS QTY
FROM full_comb c
LEFT JOIN stat s
    ON c.ACT_MONTH = s.ACT_MONTH
    AND c.S_TYPE = s.S_TYPE
ORDER BY c.ACT_MONTH, c.S_TYPE;

固定连续月份场景扩展 #

如果需要展示指定范围内的完整连续月份（比如示例中2022年全年12个月，哪怕整月无任何服务记录也要展示），只需要把上面SQL中all_months的CTE替换为对应数据库生成连续序列的逻辑即可，以MySQL8.0+为例：

WITH RECURSIVE all_months AS (
    -- 递归生成2022年1-12月的连续月份值
    SELECT '2022M01' AS ACT_MONTH
    UNION ALL
    SELECT CONCAT('2022M', LPAD(CAST(SUBSTRING(ACT_MONTH, 6) AS UNSIGNED) + 1, 2, '0'))
    FROM all_months
    WHERE ACT_MONTH < '2022M12'
)
-- 后续全量组合生成、关联统计的逻辑和通用写法完全一致

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