只付100,GMV却是300?
一笔订单只付了 100,你一个 JOIN,报表上的 GMV 变成了 300。原因是这笔订单有两笔成功支付、三条退款记录,直接关联,两两配对成了六行。很多人会说:订单表里加上支付数值和退款数值两个字段就行。可一笔订单可能支付失败后重试、分多次支付,也可能只退一部分、分多次退款。面试官真正想听的是:不同业务过程的粒度怎么定,支付和退款的明细怎么存,状态变化要不要保留,数值怎么对账,以及多表 Join 之后,怎么避免把数值放大。
一句话回答:不要把多次支付和多次退款,硬塞进一行订单表。而是按业务过程,拆成订单事实、支付事实和退款事实三张表。订单对支付一对多、对退款一对多,分别存储。统计时先在各自粒度上聚合,再按订单或公共维度关联,避免支付和退款发生乘法膨胀。
先看痛点一:支付不是订单的一个字段,而是一组交易记录。订单 O1001,下单数值 100。第一次支付 100,失败了;第二次支付 60,成功;第三次支付 40,成功。这时订单表里一个固定的支付字段,表达不了这三次交易。失败的尝试、成功的流水、支付渠道和支付时间,都需要保留。而且支付尝试和支付成功,本来就是两个不同的分析口径。痛点二:退款也不是订单表里的一个 refund_amount。同一笔订单,可能先退 20,过两天又退 10;还有一笔 70 的退款申请,最后失败了。退款还会经历申请、审核、成功、失败这些状态。订单表里只留一个退款字段,要么丢掉每次退款的明细,要么每次更新都覆盖历史。这笔订单真正成功的退款一共是 30,失败的申请不计入退款数值,但这段售后过程,仍然要保留下来。痛点三:支付表和退款表直接 Join,数值会被重复放大。这笔订单有 2 笔成功支付、3 条退款记录,直接按 order_id 关联,会形成 2 乘 3 等于 6 行。每笔支付重复了 3 次,支付合计从 100 变成 300;每条退款重复了 2 次,合计变成 200,还混进了失败的 70。打个比方:两张支付小票、三张退款小票,两两配对后再求和,每张小票都被算了好几遍。前面专题讲过一对多 Join 的指标膨胀;这里是两个「多」撞在一起,放大变成了乘法。结果就是,GMV 和退款数值,全都算错。
所以第一步,先定清楚每张事实表的粒度。Kimball 的说法是:声明粒度,就是说清楚事实表的一行到底代表什么;不同粒度,不能混在同一张事实表里。前面专题讲过事实表的三种类型:事务事实表、周期快照和累积快照。支付流水和退款单,本质上都是事务事实:一行对应一次真实发生的业务事件。订单事实表先选粒度:看订单总额、订单状态和下单用户,用订单头,一行一张订单,主键 order_id。看商品销量、SKU 维度的数值和类目,就要用订单明细,一行是订单里的一个商品行。它的主键是 order_id 加 order_item_id。这两种粒度,不能混在一张表里。这期讲支付和退款,先以订单头事实表为主。字段有订单号、用户、order_amount、订单状态、下单时间和城市,按下单日期分区。
再看支付事实表:一行代表一笔支付交易。主键是支付流水号 payment_id,而不是 order_id;同一个 order_id,可以出现很多行。字段有第几次尝试、支付渠道、pay_amount、支付状态、支付时间和源端更新时间,按 pay_date 分区。这里要先说清楚:「支付事实」到底是什么口径。用来算支付成功 GMV,可以保留每笔流水,统计时只过滤 pay_status 等于 SUCCESS。用来算支付成功率和重试,就要保留失败、取消和超时的支付尝试。如果源系统的同一笔支付,会产生多条状态更新,就要把状态变更日志和支付最新状态分开建模,不能把每条 CDC 更新,都当成一笔新支付。
退款事实表:一行代表一笔退款单,主键是 refund_id。它关联订单,也可以进一步关联到具体的支付流水和商品明细。字段有 refund_amount、退款状态、退款原因、申请时间和成功时间,按 refund_date 分区。统计退款时,通常只纳入成功的退款。有些资料把它写成「SUM 括号里接 WHERE」,这只是示意,不是合法的 SQL。通用写法是 SUM 里面套 CASE WHEN;Spark SQL 还支持在聚合函数后面加 FILTER 子句。申请失败的退款照样保留,可以分析退款失败率和售后链路,但不能计入已退款的数值。补充一点:同一笔退款从申请到成功,这一行会被反复更新,形态上更接近累积快照。只追加、不修改的状态流水,才是事务事实。
三张表怎么关联?订单对支付一对多,对退款也是一对多,都靠 order_id。退款还可以通过 payment_id,可选地关联到支付流水。三张表可以共享用户、商品、渠道、地区和时间这些一致性维度,但不能因为都有 order_id,就把明细行全部 Join 在一起。正确的路线是:各自先按订单级聚合,再计算净支付数值。Kimball 管这叫「跨事实表钻取」:分别查询每张事实表,按相同的一致性维度对齐结果,再合并。用一张表,把三类事实表放在一起对比。业务过程:订单是下单,支付是支付或支付尝试,退款就是退款。一行代表:一张订单,一笔支付流水,一笔退款单。业务主键分别是 order_id、payment_id 和 refund_id;支付和退款对订单,都是多对一。数值含义:下单数值、支付流水数值、退款流水数值。成功条件:订单有效或已创建,pay_status 等于 SUCCESS,refund_status 等于 SUCCESS。时间字段:order_time、pay_time 和 refund_success_time。适合的指标:订单数和下单规模,支付 GMV 和成功率,退款规模和退款率。最后一行最关键:三张明细都不建议直接互相 Join,要先按公共粒度汇总。
先看错误写法:订单表 LEFT JOIN 支付表,再 LEFT JOIN 退款表,最后按 order_id 分组求和。两个 JOIN 都把 SUCCESS 条件写在了 ON 里。常见说法是 2 笔支付、3 笔退款会产生 6 行,前提是这 3 笔退款都成功。在我们的例子里,R003 失败,被 ON 条件挡掉,剩下 2 乘 2 等于 4 行。支付合计算成 200,退款算成 60,两边都正好翻倍。只要支付和退款同时有多行,这种写法就一定会互相展开。
正确写法:先把支付和退款,各自聚合到订单粒度,再关联。第一个 CTE 叫 pay,只取成功支付,按 order_id 求和;第二个 CTE 叫 refund,只取成功退款,按 order_id 求和。这时每个订单在两边都只剩一行,再分别 LEFT JOIN 回订单头。用 COALESCE 把没有支付或退款的订单补成 0,再相减,得到净支付数值:100 减 30,等于 70。记住这条规则:多张一对多的事实表,先聚合到共同粒度,再 Join。
日报统计还有一个坑:订单创建日、支付成功日和退款成功日,可能不是同一天。比如 7 月 30 日下单,7 月 31 日支付成功 100,8 月 1 日退款 20,8 月 3 日再退 10。统计「每天发生的支付和退款」,要分别按各自的事件日期,不能统一用订单日期。支付按支付成功日汇总,退款按退款成功日汇总:100 算在 7 月,30 算在 8 月。所以某一天退款多、支付少,按事件日期算出来的净值可以是负数,这是口径,不是数据错了。问「7 月订单最终的净 GMV」,就按下单日期筛订单,再关联截至统计时点成功的支付和退款,结果是 70。很多资料把它叫「净收入」,但收入是财务口径,平台收入不等于 GMV,这里更准确的叫法是净 GMV。如果问「7 月发生了多少退款」,就按退款成功日期,筛选退款事实。一句话:指标名称和统计时间,必须一起定义。
支付重试和状态变化怎么存?方案一:支付明细表存当前结果,事件表存过程。比如 P001:创建、待支付、失败;P002:创建、待支付、成功。dwd_payment_detail 一行一个 payment_id,保存当前或最终状态,方便算成功支付数值。dwd_payment_event 一行一个状态变化,用来算失败率、重试次数和支付耗时。同一个 payment_id 的 PENDING 和 SUCCESS 两条 CDC 记录,不能都加进支付 GMV,否则一笔支付会被算两次。事件表保留变化;明细表按主键和版本,只取当前状态。方案二:支付尝试和成功支付,使用不同的粒度。有的系统一次支付尝试对应一条支付流水;有的系统一次支付下面,还会有多个渠道分别扣款。这时要把「支付尝试」「支付成功交易」「支付渠道分摊」分别定义清楚,不能只根据字段名去猜。
再看一种情况:一笔支付覆盖多笔订单,也就是合并付款。支付 P9001 支付总额 150,其中订单 O1001 分摊 100,订单 O1002 分摊 50。这时 dwd_payment_detail 只能记录支付总额,订单级分析,要再加一张支付分摊表。它的粒度是一笔支付对一个订单的分摊。字段有 payment_id、order_id、allocated_amount、分摊比例和分摊时间。Kimball 处理订单头和订单行,也是这个思路:头上的数值按业务规则分摊到行,才能和明细一起切片汇总。校验要分开说:同一支付流水的分摊之和,必须等于支付总额。有些资料写成「不能大于」,这里统一成等于。退款也可以按订单和支付流水分摊,校验是:同一订单累计的成功退款,不能超过可退额度。
把这些串成一棵决策树。起点:订单存在多次支付和退款。先问:当前这条记录,描述的是什么业务过程?订单创建,进订单事实,粒度是 order_id,或 order_id 加 item_id。支付流水,进支付事实,粒度是 payment_id。退款交易进退款事实,refund_id 粒度;状态变化进支付或退款的事件表,一行一个状态事件。支付这一支再问:一笔支付是否覆盖多笔订单?是,就加支付订单分摊表。然后所有分支,都先按 order_id 分别聚合。再问:需不需要订单级的净数值?需要,就用成功支付减成功退款;不需要,就按各自的事件日期和粒度统计。最后都要落到三类校验:数值守恒、状态正确、没有重复。
面试怎么答?可以这样说。我会先按业务过程和粒度拆表,不把支付和退款的数值直接塞进订单表。订单事实一行一张订单或一个商品明细;支付事实一行一笔流水,主键 payment_id,同一订单允许多行;退款事实一行一笔退款单,主键 refund_id,支持部分退款和多次退款。支付失败重试,保留支付尝试或状态事件表;成功 GMV 只算成功支付,退款只算成功退款。一笔支付覆盖多笔订单,再加支付订单分摊表。查询时不把支付明细和退款明细直接 Join,先分别聚合到订单或公共维度,再算净数值,最后做数值守恒和重复校验。
追问一:支付失败的记录,要不要放进支付事实表?看用途。要分析支付成功率、失败原因和重试次数,失败记录必须保留;只服务支付 GMV 的表,可以只留成功结果,但最好仍保留来源流水和状态日志,方便追溯。实际项目常把当前状态和状态事件分开:前者算数值,后者做过程分析。无论怎么拆,失败记录都不能混进成功支付。追问二:支付状态从失败变成成功,会不会产生两条支付?如果源头是 CDC,状态每变一次就有一条消息,但它们通常属于同一个业务支付流水。而且像 Debezium 这类 CDC 工具,在故障恢复后可能重放消息,下游必须预期会收到重复事件。所以要用 payment_id 加源版本、更新时间或 CDC 位点去重,生成一行一个流水的当前状态表,原始变化进事件表。算成功 GMV 只取最终有效状态,不能把 FAILED 和 SUCCESS 两条变更都累加。追问三:退款数值为什么不能直接在订单表上更新?订单表里一个退款字段,只能表达当前汇总结果,保留不了多次退款的流水、时间、原因和状态。遇到冲正或部分失败,直接更新还可能覆盖历史事实。订单最新状态表可以冗余一个当前累计退款,但它是派生状态,不能代替退款明细。追问四:支付和退款,按订单日期还是各自的事件日期分区?通常按各自的业务事件日期:订单按下单日,支付按支付日,退款按退款日。因为一笔订单,完全可能 7 月下单、8 月支付、9 月退款。补一个边界:退款从申请到成功可能跨天,失败的退款根本没有成功日期。所以分区字段要写清楚用的是哪一天;按成功日统计时,用成功时间过滤,别默认它等于分区日期。统计 8 月发生的支付,查支付日期。统计 7 月订单的最终退款,先筛订单范围,再关联截至统计时点的成功退款。追问五:怎么校验支付和退款没有算错?做三层校验。第一层,支付事实按订单汇总后,和支付系统的成功流水对账。第二层,同一订单的成功支付、成功退款和可退额度,做范围校验。第三层,分摊表里同一个 payment_id 的分摊之和,要等于支付总额。再结合明细行数、唯一支付流水数和重复订单数一起检查,才能发现「合计刚好对上,但明细重复」的问题。
最后总结:多次支付和多次退款,不是订单表的两个字段,而是两组独立的业务过程。先定粒度:订单一行一单,支付一行一笔流水,退款一行一笔退款单。再定口径:只算成功,按事件日期统计,指标名称和统计时间一起定义。最后记住:两张一对多的事实表,永远先聚合,再 Join。