不能直接用 CROSS JOIN 补全数据，因其生成无业务约束的笛卡尔积，导致数据量爆炸、性能下降甚至内存溢出；必须先明确补全维度与粒度，再分步构造干净维度矩阵并加业务过滤。
为什么不能直接用 CROSS JOIN 补全数据？
很多人一想到“补全”，就立刻写 CROSS JOIN 生成所有组合，再 LEFT JOIN 原表——结果发现数据量爆炸、性能骤降，甚至内存溢出。根本原因是：笛卡尔积本身不带业务约束，CROSS JOIN 会把两个表每行两两配对，哪怕逻辑上根本不该存在这条记录（比如某商品在某门店从未上架，但你硬给它补了一条空销量）。真实补全必须先明确「补全维度」和「补全粒度」，比如“每个 store_id × 每个 product_id × 每个 date”是合理粒度；而“每个 store_id × 每个 user_id”可能就是错误粒度。
如何安全构造维度矩阵？用 SELECT DISTINCT + CROSS JOIN 分步做
别在一个 SQL 里堆完所有逻辑。先分别提取干净的维度值，再交叉——这样可控、可查、可加 WHERE 过滤。比如要补全「近7天各门店各商品的销售记录」：
SELECT s.store_id, p.product_id, d.date FROM (SELECT DISTINCT store_id FROM sales WHERE dt >= CURRENT_DATE - 6) s CROSS JOIN (SELECT DISTINCT product_id FROM product WHERE status = 'on_sale') p CROSS JOIN (SELECT CURRENT_DATE - n AS date FROM generate_series(0,6) n) d
关键点：
-
generate_series()是 PostgreSQL 函数；MySQL 要用递归 CTE 或日历表；BigQuery 用UNNEST(GENERATE_DATE_ARRAY(...)) - 每个子查询都加了业务过滤（如
status = 'on_sale'），避免把已下架商品也补进去 - 不从原事实表直接
CROSS JOIN，防止把脏数据（如NULL store_id）带入维度矩阵
左连接时为什么总出现重复或丢失？检查 ON 条件的等价性
维度矩阵和原表 LEFT JOIN 后，要么一行变多行（重复），要么该有的没出来（丢失），大概率是 ON 条件没对齐。常见陷阱：
- 原表里
date是TIMESTAMP类型，维度矩阵里是DATE，直接ON t.date = d.date可能因时区/精度不匹配失败 → 改成ON DATE(t.date) = d.date或统一转为日期类型 - 原表有复合主键但漏写字段，比如只写了
ON m.store_id = s.store_id AND m.product_id = s.product_id，却忘了date字段 → 结果是每个 (store, product) 组合把所有日期的数据都扫进来 - 原表存在多条相同维度的记录（比如同一天同一商品两笔退货+一笔销售），
LEFT JOIN会自然展开 → 如果只需要“是否存在”，就先GROUP BY聚合；如果要保留明细，这是预期行为
补全后 NULL 值怎么处理？优先用 COALESCE 而非 IS NULL 判断
补全后的事实字段（如 sales_amount）为空，代表“无记录”，不是“零”。但报表常需显示 0。此时别写 CASE WHEN sales_amount IS NULL THEN 0 ELSE sales_amount END——太啰嗦且易漏字段。直接：
SELECT dm.store_id, dm.product_id, dm.date, COALESCE(s.sales_amount, 0) AS sales_amount, COALESCE(s.order_count, 0) AS order_count FROM dim_matrix dm LEFT JOIN sales s ON dm.store_id = s.store_id AND dm.product_id = s.product_id AND dm.date = DATE(s.created_at)
注意：COALESCE 是标准 SQL，所有主流数据库都支持；如果字段类型是 DECIMAL，确保默认值 0 的类型一致（比如写 COALESCE(s.sales_amount, 0.00) 避免隐式转换警告）。
最易被忽略的是：补全逻辑一旦写进调度任务，后续维度表（如新增门店、新上架商品）不会自动纳入历史补全范围——每次变更维度源，都要重新评估是否需要重跑补全逻辑，而不是只依赖“每天增量跑”。
