GMV凭空翻了3倍
报表上线第一天,GMV 凭空翻了三倍。SQL 没报错,只是多 Join 了一张商品明细表。面试官问:一对多 Join 为什么会让指标膨胀?你答:加个 DISTINCT 就行。这个回答不够。今天把一对多 Join 讲透。
一句话回答:一对多 Join,会把一端的每条记录复制成多行,结果粒度变细。再对一端的指标做 SUM,就会按匹配行数重复计算。解法不是无脑去重,而是先声明目标粒度,再让每个指标,只在自己的粒度上计算。
先看最小案例。订单头表,一行一张订单:O1001,GMV 300。订单明细表,一行一个商品:A 100、B 80、C 120。按 order_id Join,订单头这一行被复制到三条明细上,每行都带着300。再 SUM 订单 GMV,300 就变成了 900。数据库没算错,它忠实地把三个300相加。写错的,是指标口径。
根因:Join 改变了粒度。粒度,就是一行代表什么。Join 前,订单头一行是一张订单;Join 后,一行变成订单里的一个商品。结果粒度由更细的多端决定。明细上的 item_amount,可以照常 SUM。复制过来的订单 GMV,只是让每行字段完整,不能再相加。再看基数:某个 Key 左边 m 行、右边 n 行,最多产出 m 乘 n 行。一对多只复制一侧,多对多是乘法膨胀。所以判断风险,不看表多大,要看每个 Key 在两侧各出现几次。
最常见的膨胀有三种。第一种,订单头 Join 商品明细。运费、整单优惠这类订单级指标,不能跟着明细重复累加。第二种,支付明细 Join 退款明细。一笔订单两次支付、三次退款,直接 Join 就是六行。支付被重复三次,退款被重复两次。正确做法:两边先各自按订单聚合,再 Join。第三种,事实表 Join 非唯一维度:重复数据、多版本、多标签,都让维表不唯一。比如只按 user_id 关联第7期的拉链表,一笔订单会命中北京、上海、杭州三个版本。加上支付时间落在有效期内,才只匹配当时那一个版本。
怎么避免?方案一:先声明目标粒度,再决定要不要 Join。全站日 GMV,粒度是日期,直接从订单头聚合,不需要明细。商品日粒度,用明细的 item_amount 和件数;用户日粒度,先按用户归并事件。最便宜、最稳的 Join 优化,就是不做不必要的 Join。
方案二:多端先聚合到公共粒度,再 Join。明细先按 order_id 做 GROUP BY,汇总 GMV 和件数,每个订单只剩一行。再和订单头 Join,就成了一对一,订单 GMV 不会被复制。注意,聚合粒度由需求定:还要看退款明细,就保留明细表,另建订单级汇总。
方案三:关联表本该唯一,就先治理唯一性。先按 sku_id 做 GROUP BY,HAVING COUNT 大于1,查出重复的 Key。重复来自同步重放,就用 ROW_NUMBER 按更新时间取最新一条。但它不是万能胶带。SCD2 的历史版本,要按时间匹配;用户多标签是合法的多对多,要用桥接表和权重。脏数据要治理,合法多值要建模,不能都用去重掩盖。
方案四:只想判断有没有,就用半连接。比如统计活动订单的 GMV,一个订单买了两件活动商品,Join 后订单 GMV 就算了两次。改用 EXISTS 或 LEFT SEMI JOIN,只判断至少有一件;或者先 DISTINCT 出订单集合,再 Join。只需要知道有没有,就别把有几条一起 Join 进来。
方案五:必须下沉到多端时,先定义分摊规则。比如整单优惠30,要分到三个商品上。不能复制,也不能随便挑一条。可以按商品原始占比、按件数平均,或指定商品优先承担。按占比分下去:A 10、B 8、C 12,最后一行承接舍入尾差,加起来正好30。
前面几招都从根上避免复制;而 DISTINCT 类写法,只是补丁。补丁一,SELECT DISTINCT。三行的 sku_id 不同,整行不重复,一行也删不掉。补丁二,SUM DISTINCT。O1001 和 O1002 都是300,只算一次,得到300,正确是600。它按数值去重,不认订单身份。补丁三,Join 后 GROUP BY order_id,还写 SUM,照样累加。改成 MAX 能取回,但前提是复制值完全一致。补丁四,COUNT DISTINCT。订单数修好了,GMV、件数照样错,还多了 Shuffle 成本。共同问题:只在结果上压数字,没修粒度和基数。
上线前怎么发现膨胀?四个检查。一,查 Join Key 唯一性:左右两边,各查一遍重复的 Key。二,对比 Join 前后行数和唯一键数:订单数不变,行数100万变500万,每单平均展开5行。三,守恒校验:Join 前后各算一次总 GMV,明显变大,就去定位多次匹配的 Key。四,统计匹配倍数分布:匹配1行、10行、上千行的各有多少单,异常 Key 一眼看出。
再看三个追问。一:一对多 Join,一定会出错吗?不一定。目标就是商品明细时,多行是对的;错的是把订单级指标,当明细再加一遍。二:SCD2 加了有效期,就一定安全吗?还要校验版本区间不重叠,否则照样多匹配。三:合法的多对多怎么建模?用桥接表,归因时定义权重。比如一个商品属于两个活动,各分50%,每条事实的权重之和为1。
最后,面试回答模板。根因一句:Join 后粒度变细,一端指标被复制。解法四步:先说粒度,再查基数,让指标回到所属粒度,最后做行数、唯一键、GMV 守恒校验。写 Join 之前,先说清左右表一行是什么,Join 后一行又是什么。