从315投诉到供应商：我用4步LEFT JOIN追到了跨仓链路

从315投诉到供应商：我用4步LEFT JOIN追到了跨仓链路

作者：数据豆的成长路

一个投诉引发的溯源需求

消费者投诉：收到一箱过期冷冻汤圆。客服拉出运单号，追到末仓出库包裹——batch_no 是空的。再查拣货记录，manufacture_date 也是空的。

这箱汤圆到底是哪个供应商发的、经过了几个仓库、什么时候从哪个仓出去的？不知道。

不是系统设计有问题，是数仓里没有一张表能回答"这个包裹是从哪来的、经过了几个仓"。

今天聊：出库包裹怎么向前追溯，跨仓链路怎么用迭代 LEFT JOIN 串起来。给你完整的 DDL、ETL SQL、匹配规则展开、踩坑实录。

一、跨仓链路的物理结构

先搞清楚跨仓场景长什么样。

货从 A 仓出库 → 运输 → B 仓入库 → B 仓出库 → 消费者签收。

在 A 仓出库时，系统生成out_shipment_order_code（出库单号）和out_waybill_code（运单号）。货到 B 仓入库时，这批货的in_erp_order_code对应的就是 A 仓的out_shipment_order_code，in_waybill_code对应的就是 A 仓的out_waybill_code。

到了 B 仓再出库，又会生成新的out_shipment_order_code和out_waybill_code。

如果货从 A 仓 → B 仓 → C 仓，链路就是：

C仓出库包裹 → C仓入库(in_erp_order_code = B仓out_shipment_order_code) → B仓出库包裹 → B仓入库(in_erp_order_code = A仓out_shipment_order_code) → A仓出库包裹 → 供应商/源头

关键点：每个仓库同时是下游的"入库方"和上游的"出库方"，一张表存在两个角色。

整条链路的关联，靠四个字段：

• out_shipment_order_code（当前仓出库单号）
• out_waybill_code（当前仓运单号）
• in_erp_order_code（入库时ERP单号 = 上游出库单号）
• in_waybill_code（入库时运单号 = 上游运单号）

二、4条匹配规则：每条怎么JOIN、什么时候用

匹配条件是跨仓追溯的灵魂。不是随便一个字段就能关联，必须保证追的是同一批货、不是别人的货。

规则1：ERP单号 + 生产日期 + SKU + 货主 + 不同仓（精确匹配）

匹配逻辑：

LEFTJOINroute_1 bONa.in_erp_order_code=b.out_erp_order_code-- 末仓入库ERP单号 = 上1仓出库ERP单号ANDa.out_manufacture_date=b.out_manufacture_date-- 同一批货，生产日期一致ANDa.out_sku_id=b.out_sku_id-- 同一个SKUANDa.out_owner_id=b.in_owner_id-- 末仓货主 = 上1仓入库货主ANDa.out_warehouse_id<>b.out_warehouse_id-- 不同仓，不能追到同仓去ANDb.out_erp_order_code<>''-- ERP单号非空

触发场景：采购入库、同货主调拨——生产日期明确、ERP单号在同一编码体系内。

优先级：最高。五个条件全命中，匹配精度最高。

规则2：运单号 + 生产日期 + SKU + 货主 + 不同仓（运单号匹配）

匹配逻辑：

LEFTJOINroute_1 cONa.in_waybill_code=c.out_waybill_code-- 末仓入库运单号 = 上1仓出库运单号ANDa.out_manufacture_date=c.out_manufacture_date-- 同生产日期ANDa.out_sku_id=c.out_sku_id-- 同SKUANDa.in_owner_id=c.out_owner_id-- 末仓入库货主 = 上1仓货主ANDa.out_warehouse_id<>c.out_warehouse_id-- 不同仓ANDc.out_waybill_code<>'1'-- 过滤占位数据！ANDc.out_waybill_code<>''-- 运单号非空

触发场景：跨货主调拨。A公司的ERP单号是 PO-20241201-001，B公司用的是 ALL-20241201-001，根本不是一套编码体系。但运单号是物流系统生成的，不管跨不跨货主都用同一个号。

优先级：第二。生产日期还在，精度和规则1相当，只是关联键从ERP单号换成了运单号。

规则3：ERP单号 + SKU + 货主 + 不同仓（降级匹配，rn=1）

匹配逻辑：

LEFTJOINroute_1 b1ONa.in_erp_order_code=b1.out_erp_order_code-- ERP单号关联ANDa.out_sku_id=b1.out_sku_id-- 同SKUANDa.out_owner_id=b1.in_owner_id-- 同货主ANDa.out_warehouse_id<>b1.out_warehouse_id-- 不同仓ANDb1.out_erp_order_code<>''ANDb1.rn=1-- 降级：只取最新一条

触发场景：生产日期为空时降级。跨货主调拨、采购入库的货，有些场景 manufacture_date 字段是空的。没有生产日期约束，只能按 ERP单号 + SKU + 货主粗粒度匹配。

优先级：第三。比规则1少了生产日期约束，匹配范围变宽，用rn=1控制只取最新一条。

规则4：运单号 + SKU + 货主 + 不同仓（降级匹配，rn=1）

匹配逻辑：

LEFTJOINroute_1 c1ONa.in_waybill_code=c1.out_waybill_code-- 运单号关联ANDa.out_sku_id=c1.out_sku_id-- 同SKUANDa.in_owner_id=c1.out_owner_id-- 同货主ANDa.out_warehouse_id<>c1.out_warehouse_id-- 不同仓ANDc1.out_waybill_code<>'1'-- 过滤占位ANDc1.rn=1-- 降级：只取最新一条ANDc1.out_waybill_code<>''

触发场景：生产日期为空 + 跨货主。最弱匹配条件，作为兜底。

优先级：最低。规则1-3都匹配不上时才走这条路。

rn=1 到底解决什么问题

一条出库记录可能对应多条入库记录——同一批货分了3次入库，就会产生3条记录。如果不去重，一个末仓包裹会"追"出3个上1仓，全串了。

ROW_NUMBER()OVER(PARTITIONBYout_waybill_code,out_carrier_code,out_warehouse_id,in_warehouse_id,out_owner_id,in_owner_id,out_sku_id,out_erp_order_code,in_erp_order_codeORDERBYmanufacture_dateDESC)ASrn

按运单+仓+SKU+货主维度分组，按生产日期倒序——取最新那条，避免脏匹配。

如果manufacture_date为空，排序后 NULL 值排在最后，rn=1取到的是第一条非空生产日期的记录。全为空时取分区内第一条，至少保证不重复。

为什么过滤 out_waybill_code <> ‘1’

有些占位数据把运单号填成1或空字符串，不是真实运单。不过滤的话，几万条记录会全部脏匹配到同一个莫须有的上游仓。

三、4步ETL链路：种子表 → 上1仓 → 上2仓 → 首仓

主流数仓不支持递归 CTE 跨库自关联，用迭代 LEFT JOIN 模拟递归。核心思路：先拿到末仓数据作为种子，依次向左 JOIN 找上游。

Step 1：种子表——B2B和B2C合并拉平

输入表：

• dm.dm_cl_shipment_plan_trace_base_d（B2B出库计划维度）
• dm.dm_cl_shipment_package_trace_base_d（B2C出库包裹维度）
• dw.shipment_plan_consignee（收件城市维度）
• dw.shipment_package_consignee（收件城市维度）
• dw.owner（货主维度）
• dw.warehouse（仓库维度）
• dw.carrier_config（承运商维度）

输出表：tmp_shipment_package_trace_base_d0（种子临时表）

WITHshipment_traceAS(-- B2B 出库计划维度（拣货视角）SELECTa.*,if(b.city='null',null,b.city)AScityFROMdm.dm_cl_shipment_plan_trace_base_d aLEFTJOINdw.shipment_plan_consignee bONa.package_id=b.shipment_plan_idANDb.ds='${dt}'WHEREa.ds='${dt}'UNIONALL-- B2C 出库包裹维度（包裹视角）SELECTa.*,if(b.city='null',null,b.city)AScityFROMdm.dm_cl_shipment_package_trace_base_d aLEFTJOINdw.shipment_package_consignee bONa.package_id=b.package_idANDb.dsBETWEEN'${dt-45d}'AND'${dt}'WHEREa.ds='${dt}')SELECTa.package_id,COALESCE(a.city,if(d.city='null',null,d.city),if(c.city='null',null,c.city))ASlast_ewb_city,node_codeASsend_city,out_waybill_code,out_carrier_code,e.nameASout_carrier_name,out_warehouse_id,d.nameASout_warehouse_name,out_owner_id,c.nameASout_owner_name,out_erp_order_code,out_shipment_order_code,enter_code,enter_complete_by,out_sku_id,out_sku_code,out_sku_name,out_request_qty,complete_pick_qty,manufacture_dateASout_manufacture_date,max_manufacture_date,in_erp_order_code,in_waybill_code,in_warehouse_id,in_owner_id,in_completed_time,in_create_time,in_arrival_date,package_create_time,ROW_NUMBER()OVER(PARTITIONBYout_waybill_code,out_carrier_code,out_warehouse_id,in_warehouse_id,out_owner_id,in_owner_id,out_sku_id,out_erp_order_code,in_erp_order_codeORDERBYmanufacture_dateDESC)ASrnFROMshipment_trace aLEFTJOINdw.owner cONa.out_owner_id=c.idLEFTJOINdw.warehouse dONa.out_warehouse_id=d.idLEFTJOINdw.carrier_config eONe.code=a.out_carrier_codeANDe.deleted=0

这段做了什么：

1. UNION ALL 合并 B2B 和 B2C 两个数据源
2. LEFT JOIN 补全货主、仓库、承运商维度名称
3. ROW_NUMBER() rn：按运单+仓+SKU维度分组，生产日期倒序去重

Step 2：LEFT JOIN 找上1仓

输入表：tmp_shipment_package_trace_base_d0（种子表）
输出表：tmp_shipment_package_trace_base_d1

4条匹配规则同时 LEFT JOIN，用 COALESCE 取优先级最高的那个：

SELECTa.*,COALESCE(b.out_shipment_order_code,-- 规则1：ERP单号+生产日期c.out_shipment_order_code,-- 规则2：运单号+生产日期b1.out_shipment_order_code,-- 规则3：ERP单号降级(rn=1)c1.out_shipment_order_code-- 规则4：运单号降级(rn=1))ASout_shipment_order_code1,-- 上1仓出库单号COALESCE(b.out_warehouse_id,c.out_warehouse_id,b1.out_warehouse_id,c1.out_warehouse_id)ASout_warehouse_id1,-- 上1仓仓库IDCOALESCE(b.out_manufacture_date,c.out_manufacture_date,b1.out_manufacture_date,c1.out_manufacture_date)ASout_manufacture_date1,-- 上1仓生产日期b.in_erp_order_codeASin_erp_order_code1,-- 上1仓入库ERP单号=找上2仓的钥匙b.in_waybill_codeASin_waybill_code1,b.package_idASpackage_id1FROMtmp_shipment_package_trace_base_d0 a-- 规则1：ERP单号 + 生产日期 + SKU + 货主 + 不同仓LEFTJOINroute_1 bONa.in_erp_order_code=b.out_erp_order_codeANDa.out_manufacture_date=b.out_manufacture_dateANDa.out_sku_id=b.out_sku_idANDa.out_owner_id=b.in_owner_idANDa.out_warehouse_id<>b.out_warehouse_idANDb.out_erp_order_code<>''-- 规则2：运单号 + 生产日期 + SKU + 货主 + 不同仓LEFTJOINroute_1 cONa.in_waybill_code=c.out_waybill_codeANDa.out_manufacture_date=c.out_manufacture_dateANDa.out_sku_id=c.out_sku_idANDa.in_owner_id=c.out_owner_idANDa.out_warehouse_id<>c.out_warehouse_idANDc.out_waybill_code<>'1'ANDc.out_waybill_code<>''-- 规则3：ERP单号 + SKU + 货主 + 不同仓（降级，rn=1）LEFTJOINroute_1 b1ONa.in_erp_order_code=b1.out_erp_order_codeANDa.out_sku_id=b1.out_sku_idANDa.out_owner_id=b1.in_owner_idANDa.out_warehouse_id<>b1.out_warehouse_idANDb1.out_erp_order_code<>''ANDb1.rn=1-- 规则4：运单号 + SKU + 货主 + 不同仓（降级，rn=1）LEFTJOINroute_1 c1ONa.in_waybill_code=c1.out_waybill_codeANDa.out_sku_id=c1.out_sku_idANDa.in_owner_id=c1.out_owner_idANDa.out_warehouse_id<>c1.out_warehouse_idANDc1.out_waybill_code<>'1'ANDc1.rn=1ANDc1.out_waybill_code<>''

COALESCE 的含义：规则1匹配到了就用规则1的结果，匹配不到就往规则2 fallback，依次类推。保证了匹配精度优先、兜底保障不丢数据。

Step 3：LEFT JOIN 找上2仓

输入表：tmp_shipment_package_trace_base_d1
输出表：tmp_shipment_package_trace_base_d2

逻辑和 Step 2 完全一样，区别是关联字段从当前仓变成了上1仓：

• Step 2 用a.in_erp_order_codeJOIN → Step 3 用a.in_erp_order_code1（上1仓的入库ERP单号）
• Step 2 用a.in_waybill_codeJOIN → Step 3 用a.in_waybill_code1（上1仓的入库运单号）
• 输出字段加_2后缀：out_shipment_order_code2、out_warehouse_id2

Step 4：LEFT JOIN 找首仓

输入表：tmp_shipment_package_trace_base_d2
输出表：tmp_shipment_package_trace_base_d3

同理，关联字段再往上一层：a.in_erp_order_code2、a.in_waybill_code2。输出字段加_last后缀：out_warehouse_name_last、out_erp_order_code_last。

首仓就是追溯终点——它的上游是供应商/源头，不再有出库记录。

Step 5：INSERT OVERWRITE 落表

INSERTOVERWRITETABLEdm.dws_cl_shipment_package_multiple_warehouse_base_dPARTITION(ds)SELECTpackage_id,last_ewb_city,send_city,out_waybill_code,out_carrier_code,out_carrier_name,out_warehouse_name,out_owner_name,out_erp_order_code,out_shipment_order_code,enter_code,enter_complete_by,out_sku_name,out_request_qty,complete_pick_qty,out_manufacture_date,max_manufacture_date,in_erp_order_code,in_waybill_code,in_warehouse_id,in_owner_id,in_completed_time,in_create_time,in_arrival_date,-- 上1仓字段（_1后缀）package_id1,last_ewb_city1,out_warehouse_name1,out_owner_name1,out_shipment_order_code1,out_manufacture_date1,in_erp_order_code1,-- 上2仓字段（_2后缀）package_id2,out_warehouse_name2,out_shipment_order_code2,-- 首仓字段（_last后缀，追溯终点）package_id_last,out_warehouse_name_last,out_erp_order_code_last,package_create_time,REGEXP_REPLACE(SUBSTR(package_create_time,1,10),'-','')ASdsFROMtmp_shipment_package_trace_base_d3 DISTRIBUTEBYds

为什么拆成4个临时表

四、完整DDL

CREATETABLEdm.dws_cl_shipment_package_multiple_warehouse_base_d(-- === 末仓字段（核心关联字段）===package_idBIGINTCOMMENT'末仓出库包裹主表id',last_ewb_city STRINGCOMMENT'末仓收件城市',out_waybill_code STRINGCOMMENT'末仓运单号',out_carrier_code STRINGCOMMENT'末仓承运公司编码',out_carrier_name STRINGCOMMENT'末仓承运公司名称',out_warehouse_idBIGINTCOMMENT'末仓仓库id',out_warehouse_name STRINGCOMMENT'末仓仓库名称',out_owner_idBIGINTCOMMENT'末仓货主id',out_owner_name STRINGCOMMENT'末仓货主名称',out_shipment_order_code STRINGCOMMENT'末仓出库单号',out_erp_order_code STRINGCOMMENT'末仓出库ERP单号',enter_code STRINGCOMMENT'末仓入库订单号',enter_complete_by STRINGCOMMENT'末仓入库完成人',out_sku_idBIGINTCOMMENT'末仓货品ID',out_sku_code STRINGCOMMENT'末仓sku编码',out_sku_name STRINGCOMMENT'末仓sku名称',out_request_qtyDECIMAL(14,3)COMMENT'末仓请求数量',complete_pick_qtyDECIMAL(14,3)COMMENT'末仓已拣数量',out_manufacture_date STRINGCOMMENT'末仓生产日期',max_manufacture_date STRINGCOMMENT'末仓最晚生产日期',-- 入库字段 = 末仓入库信息 = 上1仓出库信息in_erp_order_code STRINGCOMMENT'末仓入库ERP单号(=上1仓out_shipment_order_code)',in_waybill_code STRINGCOMMENT'末仓入库运单号(=上1仓out_waybill_code)',in_warehouse_idBIGINTCOMMENT'末仓入库仓库ID',in_owner_idBIGINTCOMMENT'末仓入库货主ID',in_completed_time STRINGCOMMENT'末仓入库完成时间',in_create_time STRINGCOMMENT'末仓入库创建时间',in_arrival_date STRINGCOMMENT'末仓到货日期',-- === 上1仓字段（_1后缀）===package_id1BIGINTCOMMENT'上1仓出库包裹主表id',last_ewb_city1 STRINGCOMMENT'上1仓收件城市',out_warehouse_name1 STRINGCOMMENT'上1仓仓库名称',out_owner_name1 STRINGCOMMENT'上1仓货主名称',out_shipment_order_code1 STRINGCOMMENT'上1仓出库单号',out_erp_order_code1 STRINGCOMMENT'上1仓出库ERP单号',out_waybill_code1 STRINGCOMMENT'上1仓运单号',enter_code1 STRINGCOMMENT'上1仓入库订单号',out_manufacture_date1 STRINGCOMMENT'上1仓生产日期',in_erp_order_code1 STRINGCOMMENT'上1仓入库ERP单号(=上2仓out_shipment_order_code)',in_warehouse_id1BIGINTCOMMENT'上1仓入库仓库ID',-- === 上2仓字段（_2后缀）===package_id2BIGINTCOMMENT'上2仓出库包裹主表id',out_warehouse_name2 STRINGCOMMENT'上2仓仓库名称',out_shipment_order_code2 STRINGCOMMENT'上2仓出库单号',-- === 首仓字段（_last后缀，追溯终点）===package_id_lastBIGINTCOMMENT'首仓出库包裹主表id',out_warehouse_name_last STRINGCOMMENT'首仓名称=货物源头',out_shipment_order_code_last STRINGCOMMENT'首仓出库单号',out_erp_order_code_last STRINGCOMMENT'首仓出库ERP单号=追溯供应商入口',out_waybill_code_last STRINGCOMMENT'首仓运单号',out_manufacture_date_last STRINGCOMMENT'首仓生产日期',in_completed_time_last STRINGCOMMENT'首仓完成时间',package_create_time STRINGCOMMENT'包裹创建时间')COMMENT'溯源基础信息-多仓横标'PARTITIONEDBY(ds STRING)STOREDASORC;

每行 = 一个末仓出库包裹的完整跨仓链路。字段名用_1、_2、_last做层级区分，读的时候按层级取对应后缀。

五、踩坑实录

坑1：ERP单号匹配，跨货主调拨时全挂

最开始只用 ERP 单号匹配，信心满满上线。结果跨货主调拨（不同公司之间仓库调货）一条都匹配不上。

原因：A 公司的 ERP 单号是 PO-20241201-001，B 公司用的是 ALL-20241201-001，根本不是一套编码体系。

解决：加运单号匹配。运单号是物流系统生成的，不管跨不跨货主都用同一个号。

坑2：运单号匹配到"1"或空字符串

加上运单号之后，有些占位数据把运单号填成1或空字符串，不是真实运单。一匹配就是几万条脏数据，全串到同一个莫须有的上游仓去了。

解决：out_waybill_code <> '1' AND out_waybill_code <> ''，必须过滤。

坑3：一条出库对应多条入库，差点背锅

一条出库记录对应了3条入库记录——同一批货分了3次入库。不去重的话，一个末仓包裹"追"出3个上1仓，业务方问"为什么这批货显示从3个仓库来的？"

解决：ROW_NUMBER() rn=1，按生产日期倒序取最新那条。

六、食品召回场景查询

已知消费者投诉的末仓运单号，从末仓追到首仓、定位供应商：

-- Step 1：已知末仓运单，查跨仓链路SELECTt.out_waybill_codeAS末仓运单号,t.out_warehouse_nameAS末仓名称,t.out_sku_nameAS货品名称,t.out_manufacture_dateAS末仓生产日期,t.complete_pick_qtyAS末仓已拣数量,t.out_request_qtyAS末仓请求数量,t.out_warehouse_name1AS上1仓名称,t.out_warehouse_name_lastAS首仓名称,t.out_erp_order_code_lastAS首仓出库ERP单号,t.in_completed_time_lastAS首仓完成时间,CASEWHENt.package_id_lastISNOTNULLTHEN3WHENt.package_id2ISNOTNULLTHEN2WHENt.package_id1ISNOTNULLTHEN1ELSE0ENDAS经过仓数FROMdm.dws_cl_shipment_package_multiple_warehouse_base_d tWHEREt.ds='${dt}'ANDt.out_waybill_code='${complaint_waybill}';-- Step 2：用首仓出库ERP单号追溯到供应商SELECTe.erp_order_codeAS首仓出库ERP单号,e.supplierAS供应商名称,e.order_typeAS单据类型,e.manufacture_dateAS生产日期,e.batchAS批次号,e.expiration_dateAS失效日期FROMdw.entry_order eWHEREe.erp_order_code='${首仓出库ERP单号}'ANDe.deleted=0;

两步走：第一步从末仓运单追到首仓 ERP 单号，第二步用 ERP 单号追到供应商和批次。不用逐仓翻台账。

七、业务价值

1. 食品召回：末仓运单 → 首仓入库单 → 供应商，一条 SQL 搞定。

2. 货损定责：complete_pick_qty（已拣数量）和out_request_qty（请求数量）对不上，看是在哪一仓开始出现差异——末仓差值大，问题在末仓；上1仓差值大，问题在上1仓。

3. 跨仓时效：in_completed_time_last（首仓出库时间）到in_completed_time（末仓入库时间）= 跨仓运输时效。in_completed_time到package_create_time= 末仓作业时效。

4. 批次审计：同一条manufacture_date的货，经过了几个仓、在每个仓的complete_pick_qty是多少、最终去向哪些城市——全链路可查。

总结

跨仓追溯的核心：每个仓库同时扮演出库方和入库方两个角色。出库时产生out_shipment_order_code和out_waybill_code，入库时这两个字段变成in_erp_order_code和in_waybill_code。

用 ERP 单号或运单号 + SKU + 货主 + 不同仓四个条件关联上下层，4条规则按优先级 COALESCE fallback。迭代 LEFT JOIN 三次，找到上1仓、上2仓、首仓。rn=1去重 + 占位数据过滤保证匹配干净。

不是递归 CTE 写不了，是用迭代 LEFT JOIN 模拟递归——本质一样，实现方式更贴合数仓的执行引擎。

上篇回顾：入库篇讲了从入库单向后追踪到出库包裹，inbound_flow_code 是唯一关联键。本篇讲了从出库包裹向前追溯到首仓，ERP 单号/运单号 + SKU 是跨仓关联键。两篇合在一起，才是完整的"入库→出库→跨仓"全链路溯源。