| 问题 | 明细 join 多对多放大 |
|---|---|
| 规范 | 先聚合再对齐 |
| 场景 | 订单与支付对账 |
先聚合后对齐
先聚合后对齐
SELECT coalesce(a.dt,b.dt) dt, coalesce(a.channel,b.channel) channel,
a.order_amt, b.pay_amt
FROM (SELECT dt, channel, sum(amt) order_amt FROM fact_order GROUP BY dt, channel) a
FULL JOIN (SELECT dt, channel, sum(amt) pay_amt FROM fact_pay GROUP BY dt, channel) b
ON a.dt=b.dt AND a.channel=b.channel;缺少公共维度时，两套事实无法在任何粒度上对齐，指标口径必然分叉。