筆記:如何使用postgresql做順序扣減庫存

funnyZpC發表於2021-09-01

如何使用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 這一列即為最終求解哈，為了這一列費老多力了?]

最後

** 很多時候我們以為的似乎並不是那麼難，只是你很少去思考而已，當然吶，以上只是個人拙見，解決方法肯定還有很多，各位不妨試試看囖~ ? **

搶購庫存扣減
2022-01-17
Redis使用lua指令碼實現庫存扣減
2023-05-16
Redis指令碼
Redis 如何實現庫存扣減操作和防止被超賣？
2022-12-28
Redis
Go 庫存扣減的幾種實現方法
2022-01-17
Go
關於訂單庫存扣減的最佳實踐
2021-11-02
實戰來了，基於DDD實現庫存扣減~
2023-11-03
postgreSql 使用筆記
2018-10-24
SQL筆記
第2章順序表及其順序儲存
2020-09-25
秒殺系統中的扣減庫存和流量削峰
2020-09-17
佇列-順序儲存
2018-09-23
佇列
棧1: 順序儲存
2024-04-18
高併發業務下的庫存扣減技術方案設計
2024-08-26
列定義的順序和列儲存的順序
2009-07-08
電商庫存系統的防超賣和高併發扣減方案
2022-08-01
學習筆記：MySQL Cluster 的結果順序
2009-08-26
筆記MySql
PostgreSQL秒殺4種方法-增加批量流式加減庫存方法
2018-01-27
SQL
mysql儲存過程宣告順序
2016-04-05
MySql儲存過程
串的順序儲存結構
2015-02-14
併發減庫存
2018-06-04
Activiti學習筆記二：簡單順序流演示demo
2016-09-03
筆記
MYSQL學習筆記11: DQL查詢執行順序
2024-03-09
MySql筆記
如何做筆記/如何學習：
2015-02-26
筆記
PostgreSQL筆記
2023-03-02
SQL筆記
postgresql 筆記
2018-04-26
SQL筆記
PostgreSQL資料庫多列複合索引的欄位順序選擇原理
2018-04-18
SQL資料庫索引
佇列的順序儲存結構
2015-11-26
佇列
解決庫存扣減及訂單建立時防止併發死鎖的問題
2022-05-26
CMake和靜態庫順序
2018-12-07
讀書如何做筆記
2018-01-24
筆記
Erlang學習筆記(六)順序程式的錯誤處理
2017-08-28
筆記
線性表之順序儲存結構
2020-09-10
分享：大端小端-位元組儲存順序
2013-01-04
php與資料庫連線如何實現資料的順序和倒序
2020-11-05
PHP資料庫
隨筆：MySQL 普通3表join順序
2020-01-09
MySql
Solidity語言學習筆記————8、運算子優先順序
2018-06-22
Solid筆記
七牛儲存使用筆記
2018-04-16
筆記
11 線性表的順序儲存結構
2020-11-19
棧是按照順序且連續儲存值
2022-02-28