sql oracle merge 在庫更新

#まえがき
前回、在庫引当の動きを漸化的に表現しました。今回はその結果をもとに在庫更新処理を表現したいと思います。最初に在庫更新フローを提示し、そのあと前回のパターンに即して、フローの結果を記載します。

#参考文献
>[sql oracle recursive lesson 在庫引当@create_data.sql]
(https://qiita.com/ukijumotahaneniarukenia/items/7ebc4a18e6b1fdbc502e)

#在庫更新フロー

①在庫更新情報の作成
create_zaiko_update_info.sqlの実行
②引当前の在庫確認
conf_stock.sqlの実行
③残在庫数＆引当済数の算出
calc.sqlの実行
④在庫引当の反映
zaiko_prov_ref.sqlの実行
⑤引当後の在庫確認
conf_stock.sqlの実行
⑥在庫引落の反映
zaiko_withdrawal_ref.sqlの実行
⑦引落後の在庫確認
conf_stock.sqlの実行

##在庫テーブルの確認

```sql:conf_stock.sql

SELECT
	item
	, expiration_date
	, warehousing_date
	, stock_qty
	, reserved_stock_qty
FROM
	stock
ORDER BY
	item
	, expiration_date
	, warehousing_date
;

```

##在庫更新情報の作成

```sql:create_zaiko_update_info.sql

DROP TABLE zaiko_update_info;
CREATE TABLE zaiko_update_info AS 
WITH zaiko AS(
    SELECT
        row_number() OVER (ORDER BY item , expiration_date , warehousing_date) AS rn
        ,item
        ,expiration_date
        ,warehousing_date
        ,row_number() OVER (PARTITION BY item ORDER BY expiration_date , warehousing_date) AS PRIORITY
        ,stock_qty
        ,reserved_stock_qty
        ,stock_qty - reserved_stock_qty AS prov_enable_stock_qty
        ,LEAD(stock_qty - reserved_stock_qty,1,0) OVER (PARTITION BY item ORDER BY expiration_date , warehousing_date) AS next_prov_enable_stock_qty
    FROM
        stock
),sales AS (
    SELECT
        row_number() OVER (ORDER BY item, arrive_date, cust_id, seq) AS rn
        , cust_id
        , item
        , arrive_date
        , seq
        , prov_inst_qty
        , SUM(prov_inst_qty) OVER (PARTITION BY item) - SUM(prov_inst_qty) OVER (PARTITION BY item ORDER BY item, arrive_date, cust_id, seq) AS remain_stock_qty
    FROM
        sales_trn
),rec(
      rn
    , arrive_date
    , cust_id
    , item
    , prov_inst_qty
    , expiration_date
    , warehousing_date
    , PRIORITY
    , prov_enable_stock_qty
    , remain_stock_qty
    , next_prov_enable_stock_qty
    , carryover_prov_inst_qty
    , update_qty
    , flg
)AS(
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.cust_id
        , s1.item
        , s1.prov_inst_qty
        , nvl(s2.expiration_date,'********')--紐付く在庫がない場合、********を設定
        , nvl(s2.warehousing_date,'********')--紐付く在庫がない場合、********を設定
        , CASE
            WHEN(s2.expiration_date IS NULL AND s2.warehousing_date IS NULL) THEN nvl(s2.PRIORITY,0) + 1
            --紐付く在庫がない場合、引当優先順位に1を加算（マイナス在庫の場合でも在庫データをリニアに処理したいため）
            ELSE s2.PRIORITY
            --上記以外は結合条件により紐付く値を設定
        END AS PRIORITY
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
            ELSE nvl(s2.prov_enable_stock_qty,0)
            --上記以外の場合、引当可能在庫数を設定
        END AS prov_enable_stock_qty
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty <= 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0以下となる場合、残在庫数に0を設定
            ELSE nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty
            --上記以外は引当可能在庫数から引当指示数を差し引いた値を設定
        END AS remain_stock_qty
        ,nvl(s2.next_prov_enable_stock_qty,0)--次鮮度日の引当可能在庫数がない場合、0を設定
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN s1.prov_inst_qty
            --紐付く在庫がない場合、引当指示数を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0より多い場合、0を設定
            ELSE ABS(nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty)
            --上記以外は引当可能在庫数から引当指示数を差し引いたの絶対数を設定
        END AS carryover_prov_inst_qty
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN s1.prov_inst_qty
            --紐付く在庫がない場合、引当指示数を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN s1.prov_inst_qty
            --引当可能在庫数から引当指示数を差し引き、0より多い場合、引当指示数を設定
            ELSE nvl(s2.prov_enable_stock_qty,0)
            --上記以外は引当可能在庫数を設定
        END AS update_qty
        , CASE
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN -1
            --紐付く在庫がない場合、-1を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty < 0 THEN -1
            --引当可能在庫数から引当指示数を差し引き、0より少ない場合、-1を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty = 0 THEN 0
            --引当可能在庫数から引当指示数を差し引き、0の場合、0を設定
            WHEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN 1
            --引当可能在庫数から引当指示数を差し引き、0より多い場合、1を設定
        END AS flg
    FROM
        sales s1
            LEFT OUTER JOIN zaiko s2--引き当てる在庫データが存在しない場合を考慮
                ON
                    CASE
                        WHEN s2.rn IS NOT NULL THEN s2.rn
                        --紐付く在庫がある場合
                        ELSE s1.rn
                        --紐付く在庫がない場合
                    END = s1.rn
    WHERE
        s1.rn = 1
    UNION ALL
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.cust_id
        , s1.item
        , s1.prov_inst_qty
        , nvl(s2.expiration_date,'********')--紐付く在庫がない場合、********を設定
        , nvl(s2.warehousing_date,'********')--紐付く在庫がない場合、********を設定
        , CASE
            WHEN tar.item <> s1.item THEN 1
            --前回アイテムと今回アイテムが異なる場合、1を設定
            WHEN(s2.expiration_date IS NULL AND s2.warehousing_date IS NULL) THEN tar.PRIORITY + 1
            --紐付く在庫がない場合、前回の引当優先順位に1を加算（マイナス在庫の場合でも在庫データをリニアに処理したいため）
            ELSE s2.PRIORITY
            --上記以外は結合条件により紐付く値を設定
        END AS PRIORITY
        , CASE
            WHEN tar.item <> s1.item THEN nvl(s2.prov_enable_stock_qty,0)
            --前回アイテムと今回アイテムが異なる場合、今回アイテムの引当可能在庫数を設定
            WHEN tar.remain_stock_qty = 0 THEN tar.next_prov_enable_stock_qty
            --前回の残在庫数が0の場合、前回の次鮮度日の引当可能在庫数を設定
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
            ELSE tar.remain_stock_qty
            --上記以外の場合、前回の残在庫数を設定
        END AS prov_enable_stock_qty
        , CASE
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引き、0より多くなる場合、今回の引当可能在庫数から今回の引当指示数差し引いた値を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty <= 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引き、0以下となる場合、0を設定
            WHEN tar.carryover_prov_inst_qty >= 0 AND decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty) > 0 THEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty)
            --前回の繰越引当指示数が0以上で、かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引き、0より多くなる場合、前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引いた値を設定
            WHEN tar.carryover_prov_inst_qty >= 0 AND decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty) <= 0 THEN 0
            --前回の繰越引当指示数が0以上で、かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引き、0以下となる場合、0を設定
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN 0
            --紐付く在庫がない場合、0を設定
        END AS remain_stock_qty
        ,nvl(s2.next_prov_enable_stock_qty,0)--次鮮度日の引当可能在庫数がない場合、0を設定
        , CASE
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty >= 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より多い場合、0を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty < 0 THEN ABS(nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty)
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より少なくなる場合、今回の引当可能在庫数から今回の引当指示数を差し引いた値の絶対数を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) >= 0 THEN 0
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数と今回の引当指示数を差し引き、0以上場合、0を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) < 0 THEN ABS((decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty)) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty))
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数と今回の引当指示数を差し引き、0より少ない場合、前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引いた値の絶対数を設定
        END AS carryover_prov_inst_qty
        , CASE
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN s1.prov_inst_qty
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より多い場合、今回の引当指示数を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty <= 0 THEN nvl(s2.prov_enable_stock_qty,s1.prov_inst_qty)
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0以下の場合、今回の引当可能在庫数あるいは今回引当指示数を設定
            WHEN (s2.expiration_date IS NULL AND s2.warehousing_date IS NULL ) THEN s1.prov_inst_qty
            --紐付く在庫がない場合、今回の引当指示数を設定
            WHEN tar.carryover_prov_inst_qty >= 0 AND decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) > 0 THEN tar.carryover_prov_inst_qty + s1.prov_inst_qty
            --前回の繰越引当指示数が0以上かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数と今回の引当指示数を差し引き、0より多くなる場合、前回の繰越引当指示数に今回の引当指示数を加算したものを設定
            WHEN tar.carryover_prov_inst_qty >= 0 AND decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) <= 0 THEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty)
            --前回の繰越引当指示数が0以上かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数と今回の引当指示数を差し引き、0以下となる場合、今回の引当可能在庫数を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - s1.prov_inst_qty <= 0 THEN nvl(s2.prov_enable_stock_qty,ABS((tar.next_prov_enable_stock_qty + tar.remain_stock_qty) - (s1.prov_inst_qty + tar.carryover_prov_inst_qty)))
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数を差し引き、0以下となる場合、前回の残在庫数を設定
        END AS update_qty
        , CASE
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty < 0 THEN -1
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より少ない場合、-1を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty = 0 THEN 0
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0の場合、0を設定
            WHEN tar.item <> s1.item AND nvl(s2.prov_enable_stock_qty,0) - s1.prov_inst_qty > 0 THEN 1
            --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より多い場合、1を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) < 0 THEN -1
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引き、0より少ない場合、-1を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) = 0 THEN 0
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引き、0の場合、0を設定
            WHEN decode(tar.remain_stock_qty,0,tar.next_prov_enable_stock_qty,tar.remain_stock_qty) - (tar.carryover_prov_inst_qty + s1.prov_inst_qty) > 0 THEN 1
            --前回の残在庫数または前回の次鮮度日の引当可能在庫数から今回の引当指示数と前回の繰越引当指示数を差し引き、0より多い場合、1を設定
        END AS flg
    FROM
        rec tar
            INNER JOIN sales s1
                ON
                    tar.rn + 1 = s1.rn--リニアにトランデータ処理
            LEFT OUTER JOIN zaiko s2
                ON
                    s1.item = s2.item--トランに紐付くアイテムで
                AND CASE
                        WHEN tar.item <> s1.item THEN 1
                        --前回アイテムと今回アイテムが異なる場合、紐付く在庫があるかチェックするため、1を設定
                        WHEN tar.flg > 0 AND tar.item = s1.item THEN tar.PRIORITY
                        --残在庫数が0より大きく、かつ前回アイテムと今回アイテムが同じの場合、次鮮度日の在庫から引当開始しないため、前回アイテムの引当優先順位を設定
                        WHEN tar.flg <= 0 AND tar.item = s1.item THEN tar.PRIORITY + 1
                        --残在庫数が0以下で、かつ前回アイテムと今回アイテムが同じの場合、次鮮度日の在庫から引当開始するため、前回アイテムの引当優先順位に1を加算して設定
                        WHEN tar.flg <= 0 AND tar.next_prov_enable_stock_qty = 0 THEN tar.PRIORITY + 1
                        --残在庫数が0以下で、前回の次鮮度日の引当可能在庫数が0の場合、同一商品在庫データ内の最終レコードとなるので、商品が切り替わるまで前回アイテムの引当優先順位に1を加算して設定
                        ELSE NULL
                    END = s2.PRIORITY
)
SELECT * FROM rec;


```

##残在庫数＆引当済数の算出

```sql:calc.sql

SELECT
    item
    , expiration_date
    , warehousing_date
    , MIN(remain_stock_qty) as remain_stock_qty
    , SUM(update_qty) as reserved_stock_qty 
FROM
    zaiko_update_info
GROUP BY
    item
    , expiration_date
    , warehousing_date
ORDER BY
	item
	, expiration_date
	, warehousing_date
;

```

##在庫引当の反映

引当済数を反映します。

```sql:zaiko_prov_ref.sql

MERGE INTO stock tar
USING
	(
	SELECT
		item
		, expiration_date
		, warehousing_date
		, MIN(remain_stock_qty) AS remain_stock_qty
		, SUM(update_qty) AS reserved_stock_qty 
	FROM
		zaiko_update_info
	GROUP BY
		item
		, expiration_date
		, warehousing_date
	)src
ON (
		tar.item = src.item
	AND tar.expiration_date = src.expiration_date
	AND tar.warehousing_date = src.warehousing_date
	)
WHEN MATCHED THEN UPDATE
	SET tar.reserved_stock_qty = tar.reserved_stock_qty + src.reserved_stock_qty
WHEN NOT MATCHED THEN
INSERT VALUES (
				src.item
				, src.expiration_date
				, src.warehousing_date
				, src.remain_stock_qty
				, src.reserved_stock_qty 
				)
;

COMMIT;

```

##在庫引落の反映

在庫数と引当済数が同じ場合、マイナス在庫以外お掃除します。マイナス在庫は後で補充対象の在庫と識別するために残すようにします。

```sql:zaiko_withdrawal_ref.sql

MERGE INTO stock tar
USING
	(
	SELECT
		item
		, expiration_date
		, warehousing_date
		, MIN(remain_stock_qty) AS remain_stock_qty
		, SUM(update_qty) AS reserved_stock_qty 
	FROM
		zaiko_update_info
	GROUP BY
		item
		, expiration_date
		, warehousing_date
	)src
ON (
		tar.item = src.item
	AND tar.expiration_date = src.expiration_date
	AND tar.warehousing_date = src.warehousing_date
	)
WHEN MATCHED THEN UPDATE
	SET
		tar.stock_qty = case when tar.stock_qty > tar.reserved_stock_qty then tar.stock_qty - tar.reserved_stock_qty else tar.stock_qty - src.reserved_stock_qty end
		,tar.reserved_stock_qty = case when tar.stock_qty > tar.reserved_stock_qty then 0 else tar.reserved_stock_qty - src.reserved_stock_qty end
DELETE
WHERE
	tar.stock_qty = tar.reserved_stock_qty
WHEN NOT MATCHED THEN
INSERT VALUES (
				src.item
				, src.expiration_date
				, src.warehousing_date
				, src.remain_stock_qty
				, src.reserved_stock_qty 
				)
;

COMMIT;

```

#在庫もりもりある場合の在庫更新結果
ここからは上記に示した在庫更新フローに従い、前回のパターンごとに実行した結果を記載します。

##引当前の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/f565745e-e5ad-f900-9d90-9f7d027dfa35.png)

##更新内容
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/a6892540-2422-480f-197a-1f873c12072b.png)

##引当後の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/978516d5-ee59-fcae-f6a7-696d5deb86f1.png)

##引落後の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/1e8491db-e992-51af-d569-bbacd4ce244a.png)

#在庫そこそこない場合の在庫更新結果

##引当前の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/2d4d8a35-be96-f30b-509a-1a68248bb728.png)

##更新内容
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/af0d7838-380e-45d2-2da7-b8ed3394e3c0.png)

##引当後の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/8e358585-9523-326d-9c8e-563f5e795a03.png)

##引落後の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/72033c58-fd3e-4b2f-caa4-8819cd63c65b.png)

#在庫ぶっ飛ばした場合の在庫更新結果

##引当前の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/1f63fe29-f956-a645-a9b0-80781dc8ccc9.png)

##更新内容
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/5cd1be5d-3ed8-4fa7-20fe-dc1a4a164e31.png)

##引当後の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/e78bf531-774d-c01e-f959-e497e8d548cd.png)

##引落後の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/176f6a38-7e5e-05e2-8080-d382887887ce.png)

#引当済数がすでにある場合の在庫更新結果

##引当前の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/38c494f3-f560-7f6c-1da9-881ff32dba39.png)

##更新内容
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/1ce6dcaf-1073-fd46-39ea-6fadc6d7d3fa.png)

##引当後の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/daae1ce8-6c57-0cf7-5cea-c5d3d2b9d3e4.png)

##引落後の在庫
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/4e0ad6a4-62d7-359a-4ac8-6e3fe4d80db2.png)

#あとがき
merge文活躍したなー。

以上、ありがとうございました。