筆記:如何使用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-- --產品程式碼-- --日期(庫存批次日期)-- --數量(庫存)-- --數量_出-- --出_入差異(出-庫存)
    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 這一列即為最終求解哈,為了這一列費老多力了?]

最後

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

相關文章