FDE Times 数据在招 FDE 职位 924▲33 近 7 天在招职位数的变化(同口径比较):不计入本周新开始追踪的公司的职位近 7 天新增 80在招公司 196支持远程 25%美国薪资中位数 $200k招聘最多 Databricks 107
ENVI

前线部署工程师(FDE)的专业媒体

技能指南 · 指南

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

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

分享微博

要点速览

  • 一对多 JOIN 会重复“一”端的行,于是 SUM 重复计算,这就是扇出:一笔含三件商品的 10 美元订单会变成 30 美元。
  • Kimball 认为,设计之初不声明粒度是最常见的设计错误;主键上的 unique 和 not_null 测试能让主键与粒度保持一致。
  • 稳妥的修法是先按度量的粒度聚合,再做 JOIN,而不是在最后用 DISTINCT 打补丁。
横向条形图。一笔真实订单价值 10 美元。与 order_items 表连接后,一笔含三件商品的订单出现了三次:一次原始记录加两份副本,因此 SUM 报出 30 美元。
扇出:orders 与 order_items 连接时,order_total 会按每件商品重复,SUM 因此虚增营收。来源:文中示例,依据 Holistics 和 Sigma 对扇出的定义。

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 个信源
路线图上的下一篇 · 阶段 2: 全栈工程能力测试 AI 集成,从成本最低的一层做起断言和契约便宜、跑得快,能尽早发现错误;评测和端到端测试可以往后放,数量也应更少。