如何使用postgresql做顺序扣减库存
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:06a7824d-a9c1-4bfb-a4e4-0d833270b905
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:851c51b6-0371-473a-b3ea-8f59c19ad84c
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:b8790823-13f1-47dc-93e3-7ebe24740c69
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:69498efe-7c46-4e21-b715-6e2ebae93098
- 库存数据
select * from t_product_inventory;id type 产品代码 日期 数量 7 in 99999279 2018-11-01 24480 8 in 99999279 2018-11-03 20832 9 in 99999279 2018-12-02 21360 10 in 99999279 2019-06-14 18768 11 in 99999279 2019-06-16 9552 12 in 99999279 2019-07-12 2304 13 in 99999279 2019-09-05 3696 14 in 99999279 2019-09-06 16 15 in 99999279 2019-10-22 48 16 in 99999279 2019-11-03 14112 17 in 99999279 2019-12-02 2160 18 in 99999279 2019-12-04 720 19 in 99999279 2019-12-12 12960 20 in 99999290 2019-12-23 6336 21 in 99999290 2019-12-26 50 29 in 99999777 2021-04-08 10011 - 出库数据
select * from t_product_out;id type 产品代码 数量 1 out 99999279 77777 2 out 99999290 10000 - 实际库存（结果）id– –产品代码– –日期(库存批次日期)– –数量(库存)– –数量_出– –出_入差异（出-库存) 7 99999279 24480 24480 0 8 99999279 2018-11-03 20832 20832 0 9 99999279 2018-12-02 21360 21360 0 10 99999279 2019-06-14 18768 11105 7663 11 99999279 2019-06-16 9552 0 9552 12 99999279 2019-07-12 2304 0 2304 13 99999279 2019-09-05 3696 0 3696 14 99999279 2019-09-06 16 0 16 15 99999279 2019-10-22 48 0 48 16 99999279 2019-11-03 14112 0 14112 17 99999279 2019-12-02 2160 0 2160 18 99999279 2019-12-04 720 0 720 19 99999279 2019-12-12 12960 0 12960 20 99999290 2019-12-23 6336 6336 0 21 99999290 2019-12-26 50 50 3614 29 99999777 2021-04-08 10011 0 10011
首先要说sql的思考过程还是比较复杂滴（当然可以确定是我自跟儿写的），而整个过程几乎就是走一步看一步的解决问题的过程🤣，掉了多少头发可想而知了。。。
SELECT
i.id,
i.type,
i."产品代码",
i."日期",
i."数量",
o."出_汇总"
FROM t_product_inventory i
LEFT JOIN (
SELECT t_product_out."产品代码",
sum(t_product_out."数量") AS "出_汇总"
FROM t_product_out
GROUP BY t_product_out."产品代码"
) o ON i."产品代码" = o."产品代码"
ORDER BY i."产品代码", i."日期" NULLS FIRST;
id type 产品代码 日期 数量 出_汇总 7 in 99999279 24480 77777 8 in 99999279 2018-11-03 20832 77777 9 in 99999279 2018-12-02 21360 77777 10 in 99999279 2019-06-14 18768 77777 11 in 99999279 2019-06-16 9552 77777 12 in 99999279 2019-07-12 2304 77777 13 in 99999279 2019-09-05 3696 77777 14 in 99999279 2019-09-06 16 77777 15 in 99999279 2019-10-22 48 77777 16 in 99999279 2019-11-03 14112 77777 17 in 99999279 2019-12-02 2160 77777 18 in 99999279 2019-12-04 720 77777 19 in 99999279 2019-12-12 12960 77777 20 in 99999290 2019-12-23 6336 6386 21 in 99999290 2019-12-26 50 6386 29 in 99999777 2021-04-08 10011
[注意： 因为所给的出库数据是没有重复的，以上是可以略去 sum 聚合这个操作的，因为两张表是按产品代码做关联的（很显然)，另外就是日期是可以降序排列的，但是在日期有null值的情况下null所在的记录默认是降序排在最后的，所以要 order by 要指定 NULLS FIRST 这样才能为后面null批次的做优先扣减 🤗]
觉得还是先回顾下需求吧，我们的需求是每个产品下每一个批次顺序扣减的最终结果（还有批次扣减的数），其中扣减数量应该就是=当前批次(库存)数量-出库数量，公式是确定的，看起来似乎简单，然而难点是如何算出这个”扣减数量（出库数量）”呢？？？😂😂 。。。 想想，我们用当前产品出库总数按批次往下减，这样会出现一个问题是批次剩余数量=出库总数-当前批次数量，而且这个批次剩余数量并不能累加，只能用出库数量依次递减才是，。。。好了，这又是一个难点，继续思考下，目前我们是不是没法做(至少是没法简单的)获取到 库存数量-出库数量；幸运的是。。。如果将产品库存数量依次递减，这样不就可以算出库存差异了（事实上这样也有各种各样的问题）。。。让我们试试看吧
SELECT
i.id,
i.type,
i."产品代码",
i."日期",
i."数量",
o."出_汇总",
sum(i."数量") OVER (PARTITION BY i."产品代码" ORDER BY i."日期" NULLS FIRST, i."数量") AS "入_递增"
FROM (t_product_inventory i
LEFT JOIN ( SELECT t_product_out."产品代码",
sum(t_product_out."数量") AS "出_汇总"
FROM t_product_out
GROUP BY t_product_out."产品代码") o
ON (((i."产品代码")::text = (o."产品代码")::text)))
ORDER BY i."产品代码", i."日期" NULLS FIRST;
id type 产品代码 日期 数量 出_汇总 入_递增 7 in 99999279 24480 77777 24480 8 in 99999279 2018-11-03 20832 77777 45312 9 in 99999279 2018-12-02 21360 77777 66672 10 in 99999279 2019-06-14 18768 77777 85440 11 in 99999279 2019-06-16 9552 77777 94992 12 in 99999279 2019-07-12 2304 77777 97296 13 in 99999279 2019-09-05 3696 77777 100992 14 in 99999279 2019-09-06 16 77777 101008 15 in 99999279 2019-10-22 48 77777 101056 16 in 99999279 2019-11-03 14112 77777 115168 17 in 99999279 2019-12-02 2160 77777 117328 18 in 99999279 2019-12-04 720 77777 118048 19 in 99999279 2019-12-12 12960 77777 131008 20 in 99999290 2019-12-23 6336 10000 6336 21 in 99999290 2019-12-26 50 10000 6386 29 in 99999777 2021-04-08 10011 10011
[看,我们将各个产品库存数量按照批次的顺序依次递增累加了（入_递增这一列），注意窗口函数内需要排序！]
SELECT t1.id,
t1.type,
t1."产品代码",
t1."日期",
t1."数量",
t1."出_汇总",
t1."入_递增",
CASE
WHEN (((t1."出_汇总" - t1."入_递增") > (0)::numeric) /*AND (t1.rk <> t1.rk_ct)*/) THEN (0)::numeric
ELSE (t1."入_递增" - t1."出_汇总")
END AS "出_入差异"
FROM (
SELECT i.id,
i.type,
i."产品代码",
i."日期",
i."数量",
o."出_汇总",
sum(i."数量") OVER (PARTITION BY i."产品代码" ORDER BY i."日期" NULLS FIRST, i."数量") AS "入_递增"
FROM (t_product_inventory i
LEFT JOIN ( SELECT t_product_out."产品代码",
sum(t_product_out."数量") AS "出_汇总"
FROM t_product_out
GROUP BY t_product_out."产品代码") o ON (((i."产品代码")::text = (o."产品代码")::text)))
ORDER BY i."产品代码", i."日期" NULLS FIRST
) t1
id type 产品代码 日期 数量 出_汇总 入_递增 出_入差异 7 in 99999279 24480 77777 24480 0 8 in 99999279 2018-11-03 20832 77777 45312 0 9 in 99999279 2018-12-02 21360 77777 66672 0 10 in 99999279 2019-06-14 18768 77777 85440 7663 11 in 99999279 2019-06-16 9552 77777 94992 17215 12 in 99999279 2019-07-12 2304 77777 97296 19519 13 in 99999279 2019-09-05 3696 77777 100992 23215 14 in 99999279 2019-09-06 16 77777 101008 23231 15 in 99999279 2019-10-22 48 77777 101056 23279 16 in 99999279 2019-11-03 14112 77777 115168 37391 17 in 99999279 2019-12-02 2160 77777 117328 39551 18 in 99999279 2019-12-04 720 77777 118048 40271 19 in 99999279 2019-12-12 12960 77777 131008 53231 20 in 99999290 2019-12-23 6336 10000 6336 0 21 in 99999290 2019-12-26 50 10000 6386 0 29 in 99999777 2021-04-08 10011 10011
[看似一切都没有问题，所以中间我特意将 99999290 这款产品临时改为10000，这样你就会看到2019-12-26这个 出_入差异 值为零，零，怎么可能为零呢。。。不要计较了一定是sql有缺陷😆]
首先要确定的是 99999290 -> 2019-12-26 这个批次的差异应该是3614，造成这样的原因无非就是(最后一个批次的)出库数大于库存数~，看出问题了就不能无视缺陷的存在😎，所以对于最后一个批次如果出库数量仍然大于当前批次的数量，他的差异（出_入差异）应该就是负数；等等，那我如何确定每个产品的最后一个批次呢，让我们试着用sql找找看😂
SELECT t1.id,
t1.type,
t1."产品代码",
t1."日期",
t1."数量",
t1."出_汇总",
t1.rk,
t1.rk_ct,
t1."入_递增",
CASE
WHEN (((t1."出_汇总" - t1."入_递增") > (0)::numeric) AND (t1.rk <> t1.rk_ct)) THEN (0)::numeric
ELSE (t1."入_递增" - t1."出_汇总")
END AS "出_入差异"
FROM (
SELECT i.id,
i.type,
i."产品代码",
i."日期",
i."数量",
o."出_汇总",
row_number() OVER (PARTITION BY i."产品代码" ORDER BY i."日期" NULLS FIRST, i."数量") AS rk,
count(1) OVER (PARTITION BY i."产品代码") AS rk_ct,
sum(i."数量") OVER (PARTITION BY i."产品代码" ORDER BY i."日期" NULLS FIRST, i."数量") AS "入_递增"
FROM (t_product_inventory i
LEFT JOIN ( SELECT t_product_out."产品代码",
sum(t_product_out."数量") AS "出_汇总"
FROM t_product_out
GROUP BY t_product_out."产品代码") o ON (((i."产品代码")::text = (o."产品代码")::text)))
ORDER BY i."产品代码", i."日期" NULLS FIRST
) t1;
id type 产品代码 日期 数量 出_汇总 rk rk_ct 入_递增 出_入差异 7 in 99999279 24480 77777 1 13 24480 0 8 in 99999279 2018-11-03 20832 77777 2 13 45312 0 9 in 99999279 2018-12-02 21360 77777 3 13 66672 0 10 in 99999279 2019-06-14 18768 77777 4 13 85440 7663 11 in 99999279 2019-06-16 9552 77777 5 13 94992 17215 12 in 99999279 2019-07-12 2304 77777 6 13 97296 19519 13 in 99999279 2019-09-05 3696 77777 7 13 100992 23215 14 in 99999279 2019-09-06 16 77777 8 13 101008 23231 15 in 99999279 2019-10-22 48 77777 9 13 101056 23279 16 in 99999279 2019-11-03 14112 77777 10 13 115168 37391 17 in 99999279 2019-12-02 2160 77777 11 13 117328 39551 18 in 99999279 2019-12-04 720 77777 12 13 118048 40271 19 in 99999279 2019-12-12 12960 77777 13 13 131008 53231 20 in 99999290 2019-12-23 6336 10000 1 2 6336 0 21 in 99999290 2019-12-26 50 10000 2 2 6386 -3614 29 in 99999777 2021-04-08 10011 1 1 10011
[看，以上处理方式是不是赞👍，前面的缺陷完美滴解决，总结重点就是：通过窗口函数算出最后一列，这一列通过rk以及rk_ct比较得来的，想想看是不是很妙 😉]
[虽然我们可能注意到了出库数超出的情况，但是你可能忽略了最后一个问题，如果某个产品最近根本就没有出库呢…不妨看看 99999777 这款产品 是不是…是不是。。😥 ，当然对于出库数不存在的解决办法就相当easy了，当然如果你认真揣度过上面的sql的话。。。应该就不存在困难，如果不看以下sql，试试看~（相信你可以哟😎） ]
SELECT
tt1.id,
tt1.type,
tt1."产品代码",
tt1."日期",
tt1."数量",
tt1."出_汇总",
tt1.rk,
tt1.rk_ct,
tt1."入_递增",
tt1."出_入差异",
case when tt1."出_汇总" is null then 0 else
((tt1."数量")::numeric - COALESCE((tt1."出_入差异" - lag(tt1."出_入差异", 1, (0)::numeric) OVER (PARTITION BY tt1."产品代码" ORDER BY tt1."日期" NULLS FIRST, tt1."数量")),0)) end AS "数量_出",
case when tt1."出_汇总" is null then tt1."数量" else
(COALESCE(tt1."出_入差异",0) - lag(tt1."出_入差异", 1, (0)::numeric) OVER (PARTITION BY tt1."产品代码" ORDER BY tt1."日期" NULLS FIRST, tt1."数量")) end AS "出_入差异_result"
FROM (
SELECT t1.id,
t1.type,
t1."产品代码",
t1."日期",
t1."数量",
t1."出_汇总",
t1.rk,
t1.rk_ct,
t1."入_递增",
CASE
WHEN (((t1."出_汇总" - t1."入_递增") > (0)::numeric) AND (t1.rk <> t1.rk_ct)) THEN (0)::numeric
ELSE (t1."入_递增" - t1."出_汇总")
END AS "出_入差异"
FROM (
SELECT
i.id,
i.type,
i."产品代码",
i."日期",
i."数量",
o."出_汇总",
row_number() OVER (PARTITION BY i."产品代码" ORDER BY i."日期" NULLS FIRST, i."数量") AS rk,
count(1) OVER (PARTITION BY i."产品代码") AS rk_ct,
sum(i."数量") OVER (PARTITION BY i."产品代码" ORDER BY i."日期" NULLS FIRST, i."数量") AS "入_递增"
FROM (t_product_inventory i
LEFT JOIN ( SELECT t_product_out."产品代码",
sum(t_product_out."数量") AS "出_汇总"
FROM t_product_out
GROUP BY t_product_out."产品代码") o ON (((i."产品代码")::text = (o."产品代码")::text)))
ORDER BY i."产品代码", i."日期" NULLS FIRST
) t1
) tt1
ORDER BY tt1."产品代码", tt1."日期" NULLS FIRST;
id type 产品代码 日期 数量 出_汇总 rk rk_ct 入_递增 出_入差异 数量_出 出_入差异_result 7 in 99999279 24480 77777 1 13 24480 0 24480 0 8 in 99999279 2018-11-03 20832 77777 2 13 45312 0 20832 0 9 in 99999279 2018-12-02 21360 77777 3 13 66672 0 21360 0 10 in 99999279 2019-06-14 18768 77777 4 13 85440 7663 11105 7663 11 in 99999279 2019-06-16 9552 77777 5 13 94992 17215 0 9552 12 in 99999279 2019-07-12 2304 77777 6 13 97296 19519 0 2304 13 in 99999279 2019-09-05 3696 77777 7 13 100992 23215 0 3696 14 in 99999279 2019-09-06 16 77777 8 13 101008 23231 0 16 15 in 99999279 2019-10-22 48 77777 9 13 101056 23279 0 48 16 in 99999279 2019-11-03 14112 77777 10 13 115168 37391 0 14112 17 in 99999279 2019-12-02 2160 77777 11 13 117328 39551 0 2160 18 in 99999279 2019-12-04 720 77777 12 13 118048 40271 0 720 19 in 99999279 2019-12-12 12960 77777 13 13 131008 53231 0 12960 20 in 99999290 2019-12-23 6336 10000 1 2 6336 0 6336 0 21 in 99999290 2019-12-26 50 10000 2 2 6386 -3614 3664 -3614 29 in 99999777 2021-04-08 10011 1 1 10011 0 10011
[注意: 以上 出_入差异_result 这一列即为最终求解哈，为了这一列费老多力了😄]
很多时候我们以为的似乎并不是那么难，只是你很少去思考而已，当然呐，以上只是个人拙见，解决方法肯定还有很多，各位不妨试试看囖~ 😘
Original: https://www.cnblogs.com/funnyzpc/p/15207267.html
Author: funnyZpC
Title: 笔记:如何使用postgresql做顺序扣减库存
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/562390/
转载文章受原作者版权保护。转载请注明原作者出处！