笔记:如何使用postgresql做顺序扣减库存

如何使用postgresql做顺序扣减库存

Ⅰ.废话在前面

首先这篇笔记源自于最近的一次需求，这个临时性需求是根据两份数据(库存数据以及出库数据）算出实际库存给到业务，至于库存为什么不等于剩余库存，这个一两句话也说不清(主要是我不懂。。。😓)，算出来的实际库存是以产品&批次为主展示实际库存(库存按日期分批次不求总)，所以给的出库数据（需要扣减的）一个按产品代码汇总的数据，顺带一提的是两张表是以产品代码连接的；最终，算出来的实际库存除了会有库存表日期和数量外还得有 扣减数量列 以及 扣减后数量（实际库存)，扣减顺序是按照批次的日期升序扣减，批次日期为空的首先扣减（需考虑到排序）；还有就是：没有任何扣减数量（没有出库的）的产品最终的 扣减后数量（批次库存数量-出库数量） 为库存数量，扣减数为零 ~
好了，我先给出测试的表数据以及最终结果的样子，各位思考思考哈~😄

Ⅱ.表数据及实际库存(结果)

库存数据
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	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的思考过程还是比较复杂滴（当然可以确定是我自跟儿写的），而整个过程几乎就是走一步看一步的解决问题的过程🤣，掉了多少头发可想而知了。。。

First.我们确定在sql中处理，那首先想到的是得有个连表吧，另外排序也会是最easy的吧😅，let me try ~

  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批次的做优先扣减 🤗]

Second. 我们已经通过连表做好出库的数据列，排序也做好了，现在。。。让我想想

觉得还是先回顾下需求吧，我们的需求是每个产品下每一个批次顺序扣减的最终结果（还有批次扣减的数），其中扣减数量应该就是=当前批次(库存)数量-出库数量，公式是确定的，看起来似乎简单，然而难点是如何算出这个”扣减数量（出库数量）“呢？？？😂😂 。。。想想，我们用当前产品出库总数按批次往下减，这样会出现一个问题是批次剩余数量=出库总数-当前批次数量，而且这个批次剩余数量并不能累加，只能用出库数量依次递减才是，。。。好了，这又是一个难点，继续思考下，目前我们是不是没法做(至少是没法简单的)获取到库存数量-出库数量；幸运的是。。。如果将产品库存数量依次递减，这样不就可以算出库存差异了（事实上这样也有各种各样的问题）。。。让我们试试看吧

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

[看,我们将各个产品库存数量按照批次的顺序依次递增累加了（入_递增这一列），注意窗口函数内需要排序！]

Third. 好了，让我们趁热将差异也算出来吧

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有缺陷😆]

Third+. 对于以上sql出现的缺陷我准备做个Plus版以修复它~😜

**首先要确定的是 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比较得来的，想想看是不是很妙} 😉]

Next. oh ~ 糟糕

[_虽然我们可能注意到了出库数超出的情况，但是你可能忽略了最后一个问题，如果某个产品最近根本就没有出库呢...不妨看看 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 这一列即为最终求解哈，为了这一列费老多力了😄]

最后

** 很多时候我们以为的似乎并不是那么难，只是你很少去思考而已，当然呐，以上只是个人拙见，解决方法肯定还有很多，各位不妨试试看囖~ 😘 **

posted @ 2021-09-01 20:30 funnyZpC 阅读(608) 评论(0) 编辑收藏举报

刷新页面返回顶部

funnyZpC