百万行事实表如何快速生成所有维度完整组合并填充缺失度量为0

方案1：预去重维度交叉连接（全数据库兼容） #

原有写法性能差的核心原因是直接用全量事实表做三次交叉连接，产生了完全没必要的海量中间数据。优化逻辑是先单独提取三个维度的去重取值，再做交叉连接，大幅降低笛卡尔积的量级：

-- 先提取各维度的唯一取值
WITH dim_a AS (SELECT DISTINCT a FROM fact),
dim_b AS (SELECT DISTINCT b FROM fact),
dim_c AS (SELECT DISTINCT c FROM fact),
-- 预聚合原有维度组合的度量值
agg_res AS (SELECT a, b, c, SUM(m) AS m FROM fact GROUP BY a, b, c)
SELECT 
    da.a,
    db.b,
    dc.c,
    COALESCE(ar.m, 0) AS m -- 不存在的组合补0
FROM dim_a da
CROSS JOIN dim_b db
CROSS JOIN dim_c dc
LEFT JOIN agg_res ar 
    ON da.a = ar.a 
    AND db.b = ar.b 
    AND dc.c = ar.c

如果你的数据库不支持CTE语法，把子查询换成嵌套写法即可。

方案2：使用CUBE聚合语法（适配支持高阶聚合的数据库） #

如果你用的是Hive、Spark SQL、PostgreSQL、BigQuery等支持CUBE语法的引擎，可以直接用内置的多维聚合能力实现，写法更简洁，优化器也会做专门的执行加速：

SELECT 
    a,
    b,
    c,
    COALESCE(SUM(m), 0) AS m
FROM fact
GROUP BY CUBE(a, b, c)
-- 过滤掉仅聚合部分维度的结果，只保留三个维度都存在的全组合
HAVING a IS NOT NULL AND b IS NOT NULL AND c IS NOT NULL

额外优化建议 #

• 如果该查询是高频查询，可以提前将A、B、C三个维度的唯一值存储为独立的维度表，避免每次查询都扫描全表做去重
• 可以给事实表的a、b、c三个字段建立联合索引，进一步提升聚合和关联的效率

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