# 为什么一次 JOIN 就让客户营收翻了倍

> 主键正确还不够：先把数据聚合到每个度量的粒度，再连接表，错误才不会出现。

原文: https://fdetimes.net/zh/guides/sql-join-fan-out-revenue-double/

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 美元。客户想看每个客户的营收和商品数量，很多人写出的第一个查询看起来人畜无害：

```sql
-- 错误：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 针对两张表都带度量的情况提出了同样的观点：先聚合到共同的粒度，再连接。

```sql
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 时，这层保护并不存在。

**要点:** 每次 JOIN 之前，先弄清每张表的一行代表什么。

## 带到每个客户现场的固定流程

第一步，把要用到的每张表的粒度写成一句话，再请客户的数据负责人确认。像“一行是一笔付款，包括已退款的付款”这样的句子，常常能暴露出被列名掩盖的误解。

第二步，用数据检验这句话。查询 `SELECT order_id, COUNT(*) FROM orders GROUP BY 1 HAVING COUNT(*) > 1` 必须返回空结果。重复键和空键都会导致计数出错，所以如果项目使用 dbt，就把这两个条件写成测试，下次交给机器检查：

```yaml
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 前后的行数。

**本周动手试试:**

- 挑出当前项目里最常连接的三张表，把每张表的粒度写成一句话，再运行一条 GROUP BY 键 HAVING COUNT(*) > 1 的查询来检验。
- 给每个缺少测试的 dbt 模型主键加上 unique 和 not_null 测试，从存放营收的表开始。
- 打开一个正在使用的营收看板，在它的源 SQL 中统计每次 JOIN 前后的行数，记下行数在哪里增加。

## 信源

- [What's a Primary Key and Why Do We Test Them?](https://docs.getdbt.com/blog/primary-key-testing)

- [Fan-out and how Holistics resolves it](https://docs.holistics.io/docs/joins/fan-out-resolution)

- [How Holistics handles joins](https://docs.holistics.io/docs/joins/how-joins-work)

- [Resolving The Fan Trap with Sigma and Snowflake UDF](https://quickstarts.sigmacomputing.com/guide/tables_fan_traps/index.html)

- [Grain (Kimball Dimensional Modeling Techniques)](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/grain/)

- [Declaring the Grain](https://www.kimballgroup.com/2003/03/declaring-the-grain/)

- [Handling fan out with symmetric aggregates](https://docs.omni.co/analyze-explore/sql/symmetric-aggregates.md)

- [Your JOIN is quietly doubling your numbers](https://buttondown.com/SystemCraftPress/archive/your-join-is-quietly-doubling-your-numbers/)

- [Why Your Totals Are Silently Wrong after a Join: Finding Fan-Out before the Number Ships](https://www.owox.com/blog/articles/join-fan-out-wrong-totals)
