1、背景

为了保证集团成品/物料的跌价计提统一正确，集团子公司内部交易场景中，由A公司转卖成品/物料到B公司，需要实现账龄继承（即：物料在A公司账龄为36，转卖到B公司后，该物料账龄为36，不能从0开始计算）。（备注：账龄、库龄的含义，请参考：账龄VS库龄）

数仓实现过程中，需要用SQL实现以下FIFO库存分配（库存配额/数量分配）逻辑：

（1）A公司卖方库存表（假设表名为：transfer_before）

（2） B公司买方库存表（假设表名为：transfer_after）

现需要把B公司买方库存数量均摊给A公司卖方组织，得到如下的结果

问改SQL如何实现？

2、问题分析

先上参考SQL：

with transfer_before(from_org_id,i_date,entr_qty,item_id) as (select '514','20221115',174,'945068'union allselect '514','20221116',245,'945068'
),transfer_after(to_org_id,i_date,entr_qty,item_id) as(select '1754','20221128',168,'945068'union allselect '2334','20221128',1,'945068'union allselect '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_idORDER BY i_date, from_org_idROWS BETWEEN UNBOUNDED PRECEDINGAND CURRENT ROW)AS running_totalFROM transfer_before),
running_transfer_after AS
( SELECT to_org_id, i_date, entr_qty, item_id,SUM(entr_qty) OVER (PARTITION BY item_idORDER BY i_date, to_org_idROWS BETWEEN UNBOUNDED PRECEDINGAND CURRENT ROW)AS running_totalFROM 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如何实现先进先出:

销售表：

采购表

问：在销售表中，带出对应的采购价格

案例2、库存转卖FIFO佣金计算，来源：库存转卖FIFO佣金分析

成品/物料从A账户转卖到B账户，根据成品/物料在A/B账户上的交易时间，统计在不同账户下的佣金/跌价计提？

A账户（100）库存信息表

A账户（200）库存信息表

期望输出，统计账户间所有交易明细：

案例3、库存消耗FIFO统计分析，来源库存消耗：

Here's my current tableItem    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   CurrentValueapple   2            40.00What 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 OKItem    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中存储中间结果的技巧有使用存储过程，或者使用开窗函数+聚合统计函数实现，例如移动平均值、移动累加、移动计数等。