为什么一次 JOIN 就让客户营收翻了倍
主键正确还不够:先把数据聚合到每个度量的粒度,再连接表,错误才不会出现。
要点速览
- 一对多 JOIN 会重复“一”端的行,于是 SUM 重复计算,这就是扇出:一笔含三件商品的 10 美元订单会变成 30 美元。
- Kimball 认为,设计之初不声明粒度是最常见的设计错误;主键上的 unique 和 not_null 测试能让主键与粒度保持一致。
- 稳妥的修法是先按度量的粒度聚合,再做 JOIN,而不是在最后用 DISTINCT 打补丁。
dbt Labs 曾讲过一起所有数据从业者都怕遇到的事故:某个周一,历史营收因为扇出(fan-out)突然翻了一倍。重复或为空的主键会导致行被丢弃或计错,而且用户常常在测试还没来得及运行时,就先发现了报表出错。
对前线部署工程师(FDE)来说,这种情况比对公司内部的数据工程师更危险。要连接的表不是自己设计的,schema 上周才第一次见到,交付的第一个看板还会被客户财务团队拿去和账本逐项核对。一处对不上,就可能让整个项目失去信任。
好消息是,这个错误的机制很清楚,预防它也可以练成条件反射。一切从每次 JOIN 之前要回答的一个问题开始:这张表的一行代表什么?
先声明粒度,再做其他事
维度建模的代表人物 Kimball 把粒度(grain)定义为事实表中一行的确切含义。粒度必须在选择维度或事实之前声明。
Ralph Kimball 还写道,最常见的设计错误,而且远超其他错误,是在设计过程一开始没有声明事实表的粒度。在客户现场,很少有机会从零开始设计,但写第一行 SQL 之前,仍然可以先声明每张表的粒度。
主键把这种声明变成机器可以检查的东西。如果 order_id 确实唯一且从不为空,orders 表就真的是“一行一个订单”。否则,下游的每一个计数都值得怀疑。
10 美元的订单如何变成 30 美元
Holistics 的文档把扇出定义为:在一对多关系中,JOIN 复制了“一”端的行,之后聚合函数把这些副本当作真实数据。Sigma 把这类错误中的一种称为扇形陷阱(fan trap):订单头上的金额与明细表连接后,会在订单的每件商品上重复一次。
以一个常见的 schema 为例。orders 表的粒度是“一行是一个订单”,带有 order_total 列;order_items 表的粒度是“一行是订单中的一件商品”。
一笔含三件商品的 10 美元订单,JOIN 之后会出现三次,SUM 报出的是 30 美元。客户想看每个客户的营收和商品数量,很多人写出的第一个查询看起来人畜无害:
-- 错误:order_total 会随每一行 order_items 重复
SELECT o.customer_id,
SUM(o.order_total) AS revenue,
COUNT(i.product_id) AS n_products
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.customer_id;
n_products 是对的,因为它位于 order_items 的粒度。revenue 是错的,因为 order_total 位于 orders 的粒度,却被拉到了更细的粒度上。
每个订单都会被乘以它自己的商品数,所以购物篮越大的客户,数字膨胀得越厉害。误差不是一个固定倍数,只看总数很难发现。
修法:JOIN 之前先聚合到正确的粒度
SystemCraftPress 上的一篇独立分析给出了一个简洁的修法:在 JOIN 之前单独聚合“多”端,让乘法永远碰不到主表的总数。OWOX 针对两张表都带度量的情况提出了同样的观点:先聚合到共同的粒度,再连接。
WITH items_per_order AS (
SELECT order_id,
COUNT(product_id) AS n_products
FROM order_items
GROUP BY order_id -- 现在粒度是一个订单
)
SELECT o.customer_id,
SUM(o.order_total) AS revenue,
SUM(i.n_products) AS n_products
FROM orders o
LEFT JOIN items_per_order i ON i.order_id = o.order_id
GROUP BY o.customer_id;
经过 CTE 之后,JOIN 两侧的粒度相同,关系变成一对一,没有任何行被复制。n_products 仍然是商品行数,只是提前在订单层面算好了。LEFT JOIN 会保留还没有商品的订单,普通的 JOIN 会把它们悄悄丢掉。
注意,这个例子里两张表的主键都有效,却仍然出现了扇出。OWOX 这样概括:键是对的,粒度不对。主键测试保证每张表符合声明的粒度;而阻止乘法发生的,是 JOIN 之前的聚合。
当 JOIN 链变长,比如在一条语句里从 customers 连到 orders 再连到 order_items,每个客户级别的度量可能会被连乘两次。原则不变:每一层在连接之前,都先聚合到上一层的粒度。
BI 工具各有对策。Holistics 会重写查询结构,让扇出根本不会发生,而不是用算术补救;Omni 则使用对称聚合(symmetric aggregates),保证聚合结果始终正确。但在客户的数据仓库里直接写 SQL 时,这层保护并不存在。
带到每个客户现场的固定流程
第一步,把要用到的每张表的粒度写成一句话,再请客户的数据负责人确认。像“一行是一笔付款,包括已退款的付款”这样的句子,常常能暴露出被列名掩盖的误解。
第二步,用数据检验这句话。查询 SELECT order_id, COUNT(*) FROM orders GROUP BY 1 HAVING COUNT(*) > 1 必须返回空结果。重复键和空键都会导致计数出错,所以如果项目使用 dbt,就把这两个条件写成测试,下次交给机器检查:
models:
- name: orders
columns:
- name: order_id
tests: [unique, not_null]
第三步,每次 JOIN 之前,记下关系的方向,以及每个打算求和的度量的粒度。“一”端的任何度量都必须先聚合,或者经过对称聚合这类机制处理。
最后一步是对账。SystemCraftPress 提到一点容易被忽视:要核对行数,而不只是总数。在每次 JOIN 前后统计行数,然后在任何人看到看板之前,把结果与客户已经信任的数字比对,比如会计系统里某个月的总营收。
让错误更难被发现的补丁
最常见的补丁是 SUM(DISTINCT o.order_total)。它在测试样本上能给出正确数字,但只要两个不同订单的总额相同,就会悄悄少算营收,因为 DISTINCT 按值去重,而不是按键去重。
另一个陷阱是相信列名。客户表里一个叫 id 的列不一定唯一,被反复重新加载的表最容易混进重复行。只有测试能告诉你答案。
也不要以为这个错误只影响营收。Holistics 的例子用的是同样的 orders 和 order_items:SUM(orders.delivery_attempts) 会把一个订单算好几次,返回一个虚高的数字。从配送费到配送尝试次数,任何订单级别的列被拉到商品粒度时,下场都一样。
如何回答“找出这个查询的 bug”
如果你瞄准的 FDE 岗位招聘要求里提到数据建模或 dbt,可以用模拟面试来演练。请朋友把上面那个错误的查询交给你,附上一句抱怨“每个客户的营收比会计账本高,bug 在哪?”,然后按这个顺序大声作答:粒度、关系、修法、检验。
一个参考答案是这样的:
“在
orders里,一行是一个订单;在order_items里,一行是订单中的一件商品。按order_id连接是一对多,所以order_total会按每件商品重复一次:一笔含三件商品的 10 美元订单被算成了 30。COUNT 仍然是对的,只有 SUM 错了。
修法是先按
order_id聚合order_items,再做 LEFT JOIN。不要用 SUM DISTINCT,因为两个总额相同的订单会被合并成一个。然后统计 JOIN 前后的行数,再把某个月的总额和会计数字比对。”
答案的后半段,才是区分真正做过这件事的人的地方。等你能流畅地说出来,就在简历上写一次你用真实数据做过这件事的经历,并附上对账结果。
下次营收数字好得出奇时,先别急着庆祝。数一数 JOIN 前后的行数。
这篇文章对你有帮助吗?
感谢你的反馈!
9 个信源
- What's a Primary Key and Why Do We Test Them? · 2021-11-29
- Fan-out and how Holistics resolves it
- How Holistics handles joins
- Resolving The Fan Trap with Sigma and Snowflake UDF
- Grain (Kimball Dimensional Modeling Techniques)
- Declaring the Grain · 2003-03-01
- Handling fan out with symmetric aggregates
- Your JOIN is quietly doubling your numbers · 2026-09-25
- Why Your Totals Are Silently Wrong after a Join: Finding Fan-Out before the Number Ships · 2026-11-10