从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_codein_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、什么时候用匹配条件是跨仓追溯的灵魂。不是随便一个字段就能关联必须保证追的是同一批货、不是别人的货。规则1ERP单号 生产日期 SKU 货主 不同仓精确匹配匹配逻辑LEFTJOINroute_1 bONa.in_erp_order_codeb.out_erp_order_code-- 末仓入库ERP单号 上1仓出库ERP单号ANDa.out_manufacture_dateb.out_manufacture_date-- 同一批货生产日期一致ANDa.out_sku_idb.out_sku_id-- 同一个SKUANDa.out_owner_idb.in_owner_id-- 末仓货主 上1仓入库货主ANDa.out_warehouse_idb.out_warehouse_id-- 不同仓不能追到同仓去ANDb.out_erp_order_code-- ERP单号非空触发场景采购入库、同货主调拨——生产日期明确、ERP单号在同一编码体系内。优先级最高。五个条件全命中匹配精度最高。规则2运单号 生产日期 SKU 货主 不同仓运单号匹配匹配逻辑LEFTJOINroute_1 cONa.in_waybill_codec.out_waybill_code-- 末仓入库运单号 上1仓出库运单号ANDa.out_manufacture_datec.out_manufacture_date-- 同生产日期ANDa.out_sku_idc.out_sku_id-- 同SKUANDa.in_owner_idc.out_owner_id-- 末仓入库货主 上1仓货主ANDa.out_warehouse_idc.out_warehouse_id-- 不同仓ANDc.out_waybill_code1-- 过滤占位数据ANDc.out_waybill_code-- 运单号非空触发场景跨货主调拨。A公司的ERP单号是 PO-20241201-001B公司用的是 ALL-20241201-001根本不是一套编码体系。但运单号是物流系统生成的不管跨不跨货主都用同一个号。优先级第二。生产日期还在精度和规则1相当只是关联键从ERP单号换成了运单号。规则3ERP单号 SKU 货主 不同仓降级匹配rn1匹配逻辑LEFTJOINroute_1 b1ONa.in_erp_order_codeb1.out_erp_order_code-- ERP单号关联ANDa.out_sku_idb1.out_sku_id-- 同SKUANDa.out_owner_idb1.in_owner_id-- 同货主ANDa.out_warehouse_idb1.out_warehouse_id-- 不同仓ANDb1.out_erp_order_codeANDb1.rn1-- 降级只取最新一条触发场景生产日期为空时降级。跨货主调拨、采购入库的货有些场景 manufacture_date 字段是空的。没有生产日期约束只能按 ERP单号 SKU 货主粗粒度匹配。优先级第三。比规则1少了生产日期约束匹配范围变宽用rn1控制只取最新一条。规则4运单号 SKU 货主 不同仓降级匹配rn1匹配逻辑LEFTJOINroute_1 c1ONa.in_waybill_codec1.out_waybill_code-- 运单号关联ANDa.out_sku_idc1.out_sku_id-- 同SKUANDa.in_owner_idc1.out_owner_id-- 同货主ANDa.out_warehouse_idc1.out_warehouse_id-- 不同仓ANDc1.out_waybill_code1-- 过滤占位ANDc1.rn1-- 降级只取最新一条ANDc1.out_waybill_code触发场景生产日期为空 跨货主。最弱匹配条件作为兜底。优先级最低。规则1-3都匹配不上时才走这条路。rn1 到底解决什么问题一条出库记录可能对应多条入库记录——同一批货分了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 值排在最后rn1取到的是第一条非空生产日期的记录。全为空时取分区内第一条至少保证不重复。为什么过滤 out_waybill_code ‘1’有些占位数据把运单号填成1或空字符串不是真实运单。不过滤的话几万条记录会全部脏匹配到同一个莫须有的上游仓。三、4步ETL链路种子表 → 上1仓 → 上2仓 → 首仓主流数仓不支持递归 CTE 跨库自关联用迭代 LEFT JOIN 模拟递归。核心思路先拿到末仓数据作为种子依次向左 JOIN 找上游。Step 1种子表——B2B和B2C合并拉平输入表dm.dm_cl_shipment_plan_trace_base_dB2B出库计划维度dm.dm_cl_shipment_package_trace_base_dB2C出库包裹维度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.citynull,null,b.city)AScityFROMdm.dm_cl_shipment_plan_trace_base_d aLEFTJOINdw.shipment_plan_consignee bONa.package_idb.shipment_plan_idANDb.ds${dt}WHEREa.ds${dt}UNIONALL-- B2C 出库包裹维度包裹视角SELECTa.*,if(b.citynull,null,b.city)AScityFROMdm.dm_cl_shipment_package_trace_base_d aLEFTJOINdw.shipment_package_consignee bONa.package_idb.package_idANDb.dsBETWEEN${dt-45d}AND${dt}WHEREa.ds${dt})SELECTa.package_id,COALESCE(a.city,if(d.citynull,null,d.city),if(c.citynull,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_idc.idLEFTJOINdw.warehouse dONa.out_warehouse_idd.idLEFTJOINdw.carrier_config eONe.codea.out_carrier_codeANDe.deleted0这段做了什么UNION ALL 合并 B2B 和 B2C 两个数据源LEFT JOIN 补全货主、仓库、承运商维度名称ROW_NUMBER() rn按运单仓SKU维度分组生产日期倒序去重Step 2LEFT JOIN 找上1仓输入表tmp_shipment_package_trace_base_d0种子表输出表tmp_shipment_package_trace_base_d14条匹配规则同时 LEFT JOIN用 COALESCE 取优先级最高的那个SELECTa.*,COALESCE(b.out_shipment_order_code,-- 规则1ERP单号生产日期c.out_shipment_order_code,-- 规则2运单号生产日期b1.out_shipment_order_code,-- 规则3ERP单号降级(rn1)c1.out_shipment_order_code-- 规则4运单号降级(rn1))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-- 规则1ERP单号 生产日期 SKU 货主 不同仓LEFTJOINroute_1 bONa.in_erp_order_codeb.out_erp_order_codeANDa.out_manufacture_dateb.out_manufacture_dateANDa.out_sku_idb.out_sku_idANDa.out_owner_idb.in_owner_idANDa.out_warehouse_idb.out_warehouse_idANDb.out_erp_order_code-- 规则2运单号 生产日期 SKU 货主 不同仓LEFTJOINroute_1 cONa.in_waybill_codec.out_waybill_codeANDa.out_manufacture_datec.out_manufacture_dateANDa.out_sku_idc.out_sku_idANDa.in_owner_idc.out_owner_idANDa.out_warehouse_idc.out_warehouse_idANDc.out_waybill_code1ANDc.out_waybill_code-- 规则3ERP单号 SKU 货主 不同仓降级rn1LEFTJOINroute_1 b1ONa.in_erp_order_codeb1.out_erp_order_codeANDa.out_sku_idb1.out_sku_idANDa.out_owner_idb1.in_owner_idANDa.out_warehouse_idb1.out_warehouse_idANDb1.out_erp_order_codeANDb1.rn1-- 规则4运单号 SKU 货主 不同仓降级rn1LEFTJOINroute_1 c1ONa.in_waybill_codec1.out_waybill_codeANDa.out_sku_idc1.out_sku_idANDa.in_owner_idc1.out_owner_idANDa.out_warehouse_idc1.out_warehouse_idANDc1.out_waybill_code1ANDc1.rn1ANDc1.out_waybill_codeCOALESCE 的含义规则1匹配到了就用规则1的结果匹配不到就往规则2 fallback依次类推。保证了匹配精度优先、兜底保障不丢数据。Step 3LEFT 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_id2Step 4LEFT 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 5INSERT 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个临时表原因说明数据量控制每次 JOIN 扫描全量种子表拆成4步控制中间结果量调试成本某一步匹配率低可以单独看中间结果定位问题血缘清晰哪一步找到的上一仓、哪一步为空一目了然血缘可追溯Step 3找不到时能判断是Step 2的问题还是数据本身的问题四、完整DDLCREATETABLEdm.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做层级区分读的时候按层级取对应后缀。五、踩坑实录坑1ERP单号匹配跨货主调拨时全挂最开始只用 ERP 单号匹配信心满满上线。结果跨货主调拨不同公司之间仓库调货一条都匹配不上。原因A 公司的 ERP 单号是 PO-20241201-001B 公司用的是 ALL-20241201-001根本不是一套编码体系。解决加运单号匹配。运单号是物流系统生成的不管跨不跨货主都用同一个号。坑2运单号匹配到1或空字符串加上运单号之后有些占位数据把运单号填成1或空字符串不是真实运单。一匹配就是几万条脏数据全串到同一个莫须有的上游仓去了。解决out_waybill_code 1 AND out_waybill_code 必须过滤。坑3一条出库对应多条入库差点背锅一条出库记录对应了3条入库记录——同一批货分了3次入库。不去重的话一个末仓包裹追出3个上1仓业务方问为什么这批货显示从3个仓库来的解决ROW_NUMBER() rn1按生产日期倒序取最新那条。六、食品召回场景查询已知消费者投诉的末仓运单号从末仓追到首仓、定位供应商-- 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.deleted0;两步走第一步从末仓运单追到首仓 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仓、首仓。rn1去重 占位数据过滤保证匹配干净。不是递归 CTE 写不了是用迭代 LEFT JOIN 模拟递归——本质一样实现方式更贴合数仓的执行引擎。上篇回顾入库篇讲了从入库单向后追踪到出库包裹inbound_flow_code 是唯一关联键。本篇讲了从出库包裹向前追溯到首仓ERP 单号/运单号 SKU 是跨仓关联键。两篇合在一起才是完整的入库→出库→跨仓全链路溯源。