如何使用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 這一列即為最終求解哈,為了這一列費老多力了?]
最後
** 很多時候我們以為的似乎並不是那麼難,只是你很少去思考而已,當然吶,以上只是個人拙見,解決方法肯定還有很多,各位不妨試試看囖~ ? **