跨过程事实的关联口径
不同业务过程的事实表不能按明细直接 join 后求和。订单事实是每个商品行一行，支付事实是每笔支付一行，直接 join 会形成多对多关系，金额被成倍放大。正确做法是先按一致性维度分别聚合，再按维度对齐合并，用全外连接保留双方独有数据。前提是两张事实表共享至少一个一致性维度；完全没有公共维度时，两个过程的数字无法在任何粒度上对齐，指标口径必然分叉。
基本信息
| 核心问题 | 明细 join 明细产生多对多放大 |
|---|---|
| 规范做法 | 先按一致性维度分别聚合，再对齐合并 |
| 连接方式 | 全外连接 + coalesce 取维度键，保留单边数据 |
| 前置条件 | 至少共享一个一致性维度以及对齐的时间口径 |
典型使用场景
- 订单与支付对账：按渠道与日期聚合后对齐，差异即未支付或未成功订单
- 跨过程漏斗：下单量与支付量按日对齐，观察转化率变化
- 流量与订单结合：会话维度的渠道与订单渠道对齐后算转化效率
- 履约与退款分析：发货与退款分别聚合，按商品与日期对比
- 指标交叉验证：两个独立口径的同一指标对齐后互相校验
先聚合再对齐
先聚合再对齐
-- 错误写法：明细直接 join，多对多放大金额
-- FROM fact_order o JOIN fact_pay p ON o.order_no = p.order_no
-- 正确写法：两侧先按一致性维度聚合，再全外连接
SELECT coalesce(a.dt, b.dt) AS dt, -- 统一日期维度
coalesce(a.channel_sk, b.channel_sk) AS channel_sk,
a.order_amt, -- 下单金额
b.pay_amt -- 支付金额
FROM (SELECT dt, channel_sk, sum(amt) AS order_amt
FROM fact_order GROUP BY dt, channel_sk) a
FULL JOIN (SELECT dt, channel_sk, sum(pay_amt) AS pay_amt
FROM fact_pay WHERE status = 'success'
GROUP BY dt, channel_sk) b
ON a.dt = b.dt AND a.channel_sk = b.channel_sk; -- 维度对齐即口径对齐关联方式对照
| 方式 | 行数影响 | 适用情况 |
|---|---|---|
| 明细 join 明细 | 多对多放大，金额虚增 | 仅用于逐单核对，且需先去重 |
| 先聚合再对齐 | 行数可控，口径清晰 | 跨过程对账与指标对比的首选 |
| 按主键一对一关联 | 不放大，但覆盖率低 | 两侧都有唯一业务主键时可用 |
| 无公共维度硬拼 | 结果无意义 | 应先补一致性维度 |
判断是否存在公共维度很简单：列出两张事实表各自的维度外键，看是否有同源且口径一致的维度。没有的话，任何跨过程对比都只是在比较两个不相干的口径。
金额类指标一旦被多对多放大，数值通常只是偏大而不是异常到无法解释，因此这类错误容易长期存在，直到某次对账才被追溯到关联写法。