1、背景
为了保证集团成品/物料的跌价计提统一正确,集团子公司内部交易场景中,由A公司转卖成品/物料到B公司,需要实现账龄继承(即:物料在A公司账龄为36,转卖到B公司后,该物料账龄为36,不能从0开始计算)。(备注:账龄、库龄的含义,请参考:账龄VS库龄)
数仓实现过程中,需要用SQL实现以下FIFO库存分配(库存配额/数量分配)逻辑:
(1)A公司卖方库存表(假设表名为:transfer_before)
物料ID | 物料编码 | 卖方组织 | 批次 | 卖方入库数量 | 库龄 | 入库日期 | 卖方出库数量 |
945068 | 004.038.0050220 | 514 | 740 | 14 | 2022/11/15 | 174 | |
945068 | 004.038.0050220 | 514 | 528 | 13 | 2022/11/16 | 245 |
(2) B公司买方库存表(假设表名为:transfer_after)
物料ID | 物料编码 | 卖方组织 | 买方组织 | 批次 | 入库时间 | 买方入库数量 |
945068 | 004.038.0050220 | 514 | 1754 | 2022/11/28 | 168 | |
945068 | 004.038.0050220 | 514 | 2334 | 2022/11/28 | 1 | |
945068 | 004.038.0050220 | 514 | 337 | 2022/11/28 | 250 |
现需要把B公司买方库存数量均摊给A公司卖方组织,得到如下的结果
买方组织 | 卖方组织 | 物料编码 | 物料ID | 批次 | 入库日期 | 库龄 | 分配数量 |
1754 | 514 | 004.038.0050220 | 945068 | 2022/11/15 | 14 | 168 | |
2334 | 514 | 004.038.0050220 | 945068 | 2022/11/15 | 14 | 1 | |
337 | 514 | 004.038.0050220 | 945068 | 2022/11/15 | 14 | 5 | |
337 | 514 | 004.038.0050220 | 945068 | 2022/11/16 | 13 | 245 |
问改SQL如何实现?
2、问题分析
先上参考SQL:
with transfer_before(from_org_id,i_date,entr_qty,item_id) as ( select '514','20221115',174,'945068' union all select '514','20221116',245,'945068' ),transfer_after(to_org_id,i_date,entr_qty,item_id) as( select '1754','20221128',168,'945068' union all select '2334','20221128',1,'945068' union all select '337','20221128',250,'945068' ),running_transfer_before AS ( SELECT from_org_id, i_date, entr_qty, item_id, SUM(entr_qty) OVER (PARTITION BY item_id ORDER BY i_date, from_org_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM transfer_before ), running_transfer_after AS ( SELECT to_org_id, i_date, entr_qty, item_id, SUM(entr_qty) OVER (PARTITION BY item_id ORDER BY i_date, to_org_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM transfer_after ) SELECT s.to_org_id, p.from_org_id, p.item_id, LEAST(p.running_total, s.running_total) - GREATEST(s.running_total - s.entr_qty, p.running_total - p.entr_qty) AS entr_qty FROM running_transfer_before AS p JOIN running_transfer_after AS s ON p.item_id = s.item_id AND s.running_total - s.entr_qty < p.running_total AND p.running_total - p.entr_qty < s.running_total ORDER BY p.item_id, p.i_date, p.from_org_id ;
3、类似案例
该问题是典型的FIFO配额问题,在数仓统计中经常遇到,例如如下案例:
案例1、销售采购FIFO配额分析,来源:SQL如何实现先进先出:
销售表:
时间 | 产品 | 数量 | 单价 |
20220101 | 001 | 5 | 100 |
20220201 | 001 | 15 | 90 |
20220301 | 001 | 55 | 110 |
采购表
时间 | 产品 | 数量 | 单价 |
20220101 | 001 | 10 | 50 |
20220110 | 001 | 15 | 55 |
20220130 | 001 | 100 | 40 |
问:在销售表中,带出对应的采购价格
案例2、库存转卖FIFO佣金计算,来源:库存转卖FIFO佣金分析
成品/物料从A账户转卖到B账户,根据成品/物料在A/B账户上的交易时间,统计在不同账户下的佣金/跌价计提?
A账户(100)库存信息表
Account | trxid | transacted_units | transactiontype | transferfrom | transferto | date |
100 | 1 | 100 | buy | NULL | NULL | 1/1/2020 |
100 | 2 | 50 | transfer in | 200 | NULL | 1/2/2020 |
A账户(200)库存信息表
Account | trxid | transacted_units | transactiontype | transferfrom | transferto | date |
200 | 3 | 40 | buy | NULL | NULL | 12/1/2019 |
200 | 4 | 30 | buy | NULL | NULL | 12/2/2019 |
200 | 5 | 7 | sell | NULL | NULL | 12/3/2019 |
200 | 6 | 50 | trandfer out | NULL | 100 | 1/2/2020 |
期望输出,统计账户间所有交易明细:
level | Account | trxid | parenttrxid | transacted_units | transactiontype | transferfrom | transferto | date | units_balance |
0 | 100 | 1 | NULL | 100 | buy | NULL | NULL | 1/1/2020 | 100 |
0 | 100 | 2 | NULL | 50 | tranfer in | 200 | NULL | 1/2/2020 | NULL |
1 | 200 | 3 | 2 | 40 | buy | NULL | NULL | 12/1/2019 | 33 |
1 | 200 | 4 | 2 | 30 | buy | NULL | NULL | 12/2/2019 | 17 |
1 | 200 | 5 | 2 | 7 | sell | NULL | NULL | 12/3/2019 | 0 |
1 | 200 | 6 | 2 | 50 | tranfer out | NULL | 100 | 1/2/2020 | 0 |
案例3、库存消耗FIFO统计分析,来源库存消耗:
Here's my current table Item transaction code qty price apple IN 5 10.00 apple IN 3 20.00 apple OUT 6 Manual computation for the OUT transaction price (FIFO) QTY price total price 5 10.00 50.00 1 20.00 20.00 TOTAL:6 70.00 Output of the script: Item CurrentItems CurrentValue apple 2 40.00 What I need: Item transaction code qty price CurrentItems CurrentValue apple IN 5 10.00 5 50.00 apple IN 3 20.00 8 110.00 apple OUT 6 2 40.00 This too will be OK Item transaction code qty price CurrentItems apple IN 5 10.00 0 apple IN 3 20.00 0 apple OUT 6 70
案例4、订单数据分配分析,来源:订单数据分配:
两张表 table_1 A (料号) B(总交货量) 111 100 112 50 table_2 D(订单) M(料号) E(未交数量) F(预交货日) OD001 111 70 20151101 OD002 111 20 20151102 OD003 112 40 20151102 OD004 112 30 20151101 需按料号,交货日期排序,然后将table_1中料号对应的总交货量分配给table_2中未交货的订单 查询出来的结果如下: D(订单) M(料号) E(未交数量) F(预交货日) G(本次交货) OD001 111 70 20151101 70 OD002 111 20 20151102 20 OD003 112 40 20151102 20 OD004 112 30 20151101 30
案例5、配额计算,来源:FIFO配额计算
采购表(Purchase):
| PO | Date | Quantity | Item | |-------|--------------|----------|------| | PO001 | 01-Jan-2016 | 3 | AO21 | | PO002 | 10-Jan-2016 | 7 | AO21 | | PO003 | 01-Feb-2016 | 3 | AO21 |
库存表(Strock):
| SO | Date | Quantity | Item | |-------|-------------|----------|------| | SO001 | 02-Jan-2016 | 2 | AO21 | | SO002 | 11-Feb-2016 | 8 | AO21 | | SO003 | 12-Feb-2016 | 6 | AO23 |
期望结果表
| SO | PO | Quantity | |-------|-------|----------| | SO001 | PO001 | 2 | | SO002 | PO001 | 1 | | SO002 | PO003 | 7 |
4、问题总结
本文中提及问题及案例,都是基于不同约束条件下FIFO/LIFO算法使用SQL实现。关键点都在于如何保存中间结果,供下一次计算迭代时存取。SQL中存储中间结果的技巧有使用存储过程,或者使用开窗函数+聚合统计函数实现,例如移动平均值、移动累加、移动计数等。