SQL实现FIFO算法:库龄继承、配额分析
admin
2024-03-18 00:26:23
0次

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 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如何实现先进先出:

销售表:

时间

产品

数量

单价

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 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中存储中间结果的技巧有使用存储过程,或者使用开窗函数+聚合统计函数实现,例如移动平均值、移动累加、移动计数等。

相关内容

热门资讯

蜻蜓订购APP贵金属订购非法期...   蜻蜓订购APP以"投资平台"名义对外宣传,主打贵金属期货(订购)交易。该平台一方面强调自身"正规...
悟空订购APP非法期货交易欺骗...   近期,有投资者反映,“悟空订购APP”等平台以白银、贵金属订购为名,宣传所谓“高收益”“低门槛”...
小赢淘金APP期货投资骗局,平...   “小赢淘金”之流,最擅长的就是用“注册送现金”、“充值返券”的糖衣炮弹来引流。别天真地以为那是福...
八方淘金APP非法期货交易欺骗...   警惕!八方生财、每日淘金、小赢淘金、悟空订购、蜻蜓订购、斗金订购等APP均涉嫌非法期货交易!  ...
智易购APP贵金属订购交易骗局...   很多人看到“3元起投”“充值送福利”“操作简单”“随时提现”这类宣传,很容易把智易购APP当成普...
喜玛银庄APP商品订购投资骗局...   喜玛银庄APP白银、铂金“现货订购”骗局警示:无资质平台涉嫌非法期货交易,投资者爆仓后如何维权?...
吉鑫乐享APP炒贵金属订购交易...   近期,多名投资者反映在“吉鑫乐享APP”进行贵金属投资时遭遇严重亏损,甚至血本无归。看似正规的“...
易掘金APP虚假期货投资骗局,...   “易掘金”的APP它打着“高收益、灵活投资”的旗号,宣称通过“商品订购”就能轻松赚钱,吸引了不少...
在大吉订购APP订购交易亏损被...   近期,不少投资者反映在“大吉订购App”上投资白银、铂金等商品遭遇严重亏损。该平台宣称提供现货订...
铂银壹号APP商品订购交易亏损...   许多中小投资者误以为网上的“商品现货订购”是合规合法的理财渠道,殊不知这背后隐藏着巨大的合规风险...
兴鑫淘金APP贵金属订购交易亏...   白银、贵金属或其他商品类投资,除国家批准的正规期货交易场所及持牌期货公司外,市面上大量第三方交易...
铂银掘金APP无资质的违规平台...   铂银掘金等平台的投资者,往往是被“注册送现金”“充值返现券”“首单免亏”等广告话术吸引,进而下载...
八方淘金APP订购交易不合规,...   “八方淘金APP”“铂银掘金APP”“铂银壹号APP”等软件被指以“现货订购”为名,实际采用高杠...
在指尖订购APP订购商品亏损被...   指尖订购APP这类平台,表面是“订购交易”,实则是披着电商外衣的非法期货陷阱。绝大多数投资者被蒙...
在犀贝易购APP做商品订购交易...   市面上打着“现货投资”“贵金属交易”旗号的APP并不少,但真正合规、受监管的平台并不多。犀贝易购...
易达淘金APP虚假商品订购交易...   易达淘金APP存在严重的虚假宣传与违规交易行为。该平台打着“商品订购”的旗号,实则通过不合规的交...
大浪淘金APP白银订购亏损被骗...   大浪淘金APP最隐蔽的套路,在于其完全封闭的内盘交易系统。表面上,它宣称与国际贵金属市场接轨;实...
一品好购APP上订购白银出现亏...   一品好购APP原名“一品订购”,运营方为上海宝智未来信息技术有限公司。近期不少投资者反映在该平台...
九州订购APP贵金属订购投资骗...   九州订购APP正通过抖.音、快手、今日头条等平台大量投放投资广告,用“5元起投”“充值返现”“0...
坚锅淘金APP交易软件并不正规...   当前,市面上大量所谓的“现货商城订购”交易软件,早已偏离了正常的实物商品买卖,演变为高杠杆、高手...