SQL如何实现多维度分组全组合展开并补全缺失值为0

核心逻辑是先生成每个分组下所有维度的全量组合，再关联原表补全数值，不存在的组合补0，不需要写复杂的循环或者自定义函数，标准SQL即可实现，兼容绝大多数主流数据库（MySQL 8.0+、PostgreSQL、Hive、Spark SQL、BigQuery等）。

实现步骤 #

• 先提取源表中organisation、individual、month三个分组维度的去重值，作为维度展开的基准组
• 分别构造mode、professional、setting三个维度的枚举值集合
• 将基准组和三个维度枚举集合做交叉连接，生成每个基准组下所有可能的维度组合骨架
• 用维度组合骨架左连接原表，匹配到的记录保留原number_consultations值，未匹配到的记录该字段填充为0

完整SQL代码 #

WITH
-- 提取分组维度的唯一组合
base_dim AS (
    SELECT DISTINCT organisation, individual, month
    FROM 替换为你的源表名
),
-- 构造mode维度枚举
dim_mode AS (
    SELECT mode FROM (
        VALUES ('face-to-face'), ('telephone'), ('homevisit'), ('digital')
    ) AS t(mode)
),
-- 构造professional维度枚举
dim_professional AS (
    SELECT professional FROM (
        VALUES ('nurse'), ('doctor'), ('otherdirectcare'), ('other')
    ) AS t(professional)
),
-- 构造setting维度枚举
dim_setting AS (
    SELECT setting FROM (
        VALUES ('group1'), ('group2'), ('group3'), ('group4')
    ) AS t(setting)
),
-- 生成全量维度组合骨架
all_combinations AS (
    SELECT
        b.organisation,
        b.individual,
        b.month,
        m.mode,
        p.professional,
        s.setting
    FROM base_dim b
    CROSS JOIN dim_mode m
    CROSS JOIN dim_professional p
    CROSS JOIN dim_setting s
)
-- 关联原表补全咨询量
SELECT
    a.organisation,
    a.individual,
    a.month,
    COALESCE(t.number_consultations, 0) AS number_consultations,
    a.mode,
    a.professional,
    a.setting
FROM all_combinations a
LEFT JOIN 替换为你的源表名 t
    ON a.organisation = t.organisation
    AND a.individual = t.individual
    AND a.month = t.month
    AND a.mode = t.mode
    AND a.professional = t.professional
    AND a.setting = t.setting
-- 排序规则可根据实际需求调整，默认和示例结果顺序一致
ORDER BY a.organisation, a.individual, a.month, a.setting, a.professional, a.mode;

兼容性说明 #

如果你使用的数据库不支持VALUES子句直接构造行（比如MySQL 5.x版本），可以把维度构造部分替换为UNION ALL写法，所有SQL引擎都兼容该写法，以dim_mode为例：

dim_mode AS (
    SELECT 'face-to-face' AS mode UNION ALL
    SELECT 'telephone' UNION ALL
    SELECT 'homevisit' UNION ALL
    SELECT 'digital'
)

其余两个维度的构造逻辑同理替换即可。
如果业务中这三个维度的枚举值经常变动，建议单独建维度表存储枚举值，使用时直接查询维度表即可，不需要每次修改SQL里的硬编码枚举。

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