#まえがき
そして、つい先日、sql oracle Advent Calendar 2018にて@tlokwengさんが投稿したlateral句とunion allを使った以下の記事を拝見してsqlの表現の幅が一気に広がったように見えました。sqlの可能性しか感じなかった。。「うあぁ、、すげぇー」と思わず声が出た。。

単一SQLクエリで穴掘り法や壁伸ばし法を使って迷路をちゃんと作ってみる

それと、以下の記事のコメントも目に留まった。@youmil_rainさん、@tlokwengさんがコメントしています。

シクシク素数列 Advent Calendar 2018 SQL編

そんなこんなで、lateral句どんどん使ってみたいなーと思った次第であります。

#左相関のイメージ

まずは、lateral句で意識する左相関のイメージについて把握してみた。左相関に関しては以下を参考にしました。

第37回新しいSQLについて

左側に位置しているテーブルの列を参照しながら相関していくイメージだから、簡単な例だとこんな感じだと思う。

lateral sql（開く）

WITH sub AS (
SELECT
	LEVEL AS rn
FROM
	dual
CONNECT BY
	LEVEL <= 4
)
SELECT
	s1.*
	,s2.*
	,s3.*
FROM
	sub s1
	,LATERAL(SELECT s2.* FROM sub s2 WHERE s1.rn <= s2.rn) s2
	,LATERAL(SELECT s3.* FROM sub s3 WHERE s2.rn <= s3.rn) s3
;

取得結果も参照している左側のテーブルのカラム値以上での紐付きが確認できます。

lateral sql 実行結果（開く）

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/b213df15-b5af-20c2-1c6e-fee824df452b.png)

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |     8 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D66F0_11C2E8C |       |       |            |          |
|   3 |    CONNECT BY WITHOUT FILTERING          |                            |       |       |            |          |
|   4 |     FAST DUAL                            |                            |     1 |       |     2   (0)| 00:00:01 |
|   5 |   NESTED LOOPS                           |                            |     1 |    39 |     6   (0)| 00:00:01 |
|   6 |    NESTED LOOPS                          |                            |     1 |    26 |     4   (0)| 00:00:01 |
|   7 |     VIEW                                 |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   8 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D66F0_11C2E8C |     1 |    13 |     2   (0)| 00:00:01 |
|*  9 |     VIEW                                 |                            |     1 |    13 |     2   (0)| 00:00:01 |
|  10 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D66F0_11C2E8C |     1 |    13 |     2   (0)| 00:00:01 |
|* 11 |    VIEW                                  |                            |     1 |    13 |     2   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D66F0_11C2E8C |     1 |    13 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

#左相関パターン

なんとなく雰囲気味わったので、以下のパターンで基本的な動きを見てみた。参考にしたのは以下です。

LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1 (12.1)

##左相関（内部結合）lateral

左相関（内部結合）sql（開く）

WITH item_mst AS (
SELECT
	LEVEL AS rn
	,chr(level + 66) || '0001' as item
FROM
	dual
CONNECT BY
	LEVEL <= 4
),sales_trn as (
select
	level as seq
	,chr(level + 64) || '0001' as item
from
	dual
CONNECT BY
	LEVEL <= 4
)
SELECT
	s1.*
	,s2.*
FROM
	sales_trn s1
	,LATERAL(SELECT s2.* FROM item_mst s2 WHERE s1.item = s2.item) s2
;

左相関（内部結合）sql 実行結果（開く）

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/b8811049-1085-df35-fecb-e4a86bd236e9.png)

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |       |       |     4 (100)|          |
|*  1 |  HASH JOIN                     |      |     1 |    38 |     4   (0)| 00:00:01 |
|   2 |   VIEW                         |      |     1 |    19 |     2   (0)| 00:00:01 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |   VIEW                         |      |     1 |    19 |     2   (0)| 00:00:01 |
|   6 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   7 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

##左相関（外部結合）lateral

一回インラインにしないと外部結合できないぽい。めんどい。

左相関（外部結合）sql（開く）

WITH item_mst AS (
SELECT
	LEVEL AS rn
	,CHR(LEVEL + 66) || '0001' AS item
FROM
	dual
CONNECT BY
	LEVEL <= 4
),sales_trn AS (
SELECT
	LEVEL AS seq
	,CHR(LEVEL + 64) || '0001' AS item
FROM
	dual
CONNECT BY
	LEVEL <= 4
)
SELECT
	s1.*
	,s2.*
FROM
	sales_trn s1
	,LATERAL(SELECT * FROM (SELECT s2.* FROM item_mst s2 WHERE s1.item = s2.item))(+) s2
;

左相関（外部結合）sql 実行結果（開く）

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/30c9dead-07dd-b247-2b31-b7eea9de99cb.png)

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |       |       |     4 (100)|          |
|*  1 |  HASH JOIN OUTER               |      |     1 |    38 |     4   (0)| 00:00:01 |
|   2 |   VIEW                         |      |     1 |    19 |     2   (0)| 00:00:01 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |   VIEW                         |      |     1 |    19 |     2   (0)| 00:00:01 |
|   6 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   7 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

##左相関（内部結合）cross apply

左相関（内部結合）sql（開く）

WITH item_mst AS (
SELECT
    LEVEL AS rn
    ,CHR(LEVEL + 66) || '0001' AS item
FROM
    dual
CONNECT BY
    LEVEL <= 4
),sales_trn AS (
SELECT
    LEVEL AS seq
    ,CHR(LEVEL + 64) || '0001' AS item
FROM
    dual
CONNECT BY
    LEVEL <= 4
)
SELECT
    s1.*
    ,s2.*
FROM
    sales_trn s1
    CROSS APPLY(SELECT s2.* FROM item_mst s2 WHERE s1.item = s2.item) s2
;

左相関（内部結合）sql 実行結果（開く）

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/68f378d9-45db-a603-ab79-f643f5537682.png)

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |       |       |     4 (100)|          |
|*  1 |  HASH JOIN                     |      |     1 |    38 |     4   (0)| 00:00:01 |
|   2 |   VIEW                         |      |     1 |    19 |     2   (0)| 00:00:01 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |   VIEW                         |      |     1 |    19 |     2   (0)| 00:00:01 |
|   6 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   7 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

##左相関（外部結合）outer apply

インラインにしなくてすんだ！

左相関（外部結合）sql（開く）

WITH item_mst AS (
SELECT
    LEVEL AS rn
    ,CHR(LEVEL + 66) || '0001' AS item
FROM
    dual
CONNECT BY
    LEVEL <= 4
),sales_trn AS (
SELECT
    LEVEL AS seq
    ,CHR(LEVEL + 64) || '0001' AS item
FROM
    dual
CONNECT BY
    LEVEL <= 4
)
SELECT
    s1.*
    ,s2.*
FROM
    sales_trn s1
    OUTER APPLY(SELECT s2.* FROM item_mst s2 WHERE s1.item = s2.item) s2
;

左相関（外部結合）sql 実行結果（開く）

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |       |       |     4 (100)|          |
|*  1 |  HASH JOIN OUTER               |      |     1 |    38 |     4   (0)| 00:00:01 |
|   2 |   VIEW                         |      |     1 |    19 |     2   (0)| 00:00:01 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |   VIEW                         |      |     1 |    19 |     2   (0)| 00:00:01 |
|   6 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   7 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

##左相関 pivotあり

apply句だとpivot句とかそのまま展開できるからいい。where句の部分はランダムなので、1~9のどれかに変える。

左相関 pivotあり sql（開く）

WITH item_mst AS (
SELECT
    LEVEL AS rn
    ,CHR(LEVEL + 64) || '0001' AS item
    ,to_char(TRUNC(ABS(dbms_random.VALUE(1,10)),0)) AS item_kbn
FROM
    dual
CONNECT BY
    LEVEL <= 5
),sales_trn AS (
SELECT
    ROWNUM AS seq
	,LEVEL AS rn
    ,item
	,TO_DATE(to_char(sysdate + LEVEL, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS sale_date
FROM
    item_mst
CONNECT BY
    LEVEL <= 2
)
SELECT
	*
FROM
    (SELECT item,sale_date,seq FROM sales_trn)s1
    CROSS APPLY(SELECT s2.item_kbn FROM item_mst s2 WHERE s1.item = s2.item)
	PIVOT(COUNT(s1.seq) FOR sale_date IN (TO_DATE('18-12-09') AS d1,TO_DATE('18-12-10') AS d2)) s2
WHERE
	s2.item_kbn = '3'
;

左相関 pivotあり sql 実行結果（開く）

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |     7 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D670B_11C2E8C |       |       |            |          |
|   3 |    CONNECT BY WITHOUT FILTERING          |                            |       |       |            |          |
|   4 |     FAST DUAL                            |                            |     1 |       |     2   (0)| 00:00:01 |
|   5 |   HASH GROUP BY PIVOT                    |                            |     1 |    53 |     5  (20)| 00:00:01 |
|*  6 |    HASH JOIN                             |                            |     1 |    53 |     4   (0)| 00:00:01 |
|   7 |     VIEW                                 |                            |     1 |    25 |     2   (0)| 00:00:01 |
|   8 |      COUNT                               |                            |       |       |            |          |
|   9 |       CONNECT BY WITHOUT FILTERING       |                            |       |       |            |          |
|  10 |        VIEW                              |                            |     1 |     6 |     2   (0)| 00:00:01 |
|  11 |         TABLE ACCESS FULL                | SYS_TEMP_0FD9D670B_11C2E8C |     1 |    41 |     2   (0)| 00:00:01 |
|* 12 |     VIEW                                 |                            |     1 |    28 |     2   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D670B_11C2E8C |     1 |    41 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

##左相関 table function outer apply

cross apply句、outer apply句の引数にtable functionの取得結果を渡すとちゃんとうごく。

左相関 table function sql（開く）

CREATE OR REPLACE TYPE item_liz AS TABLE OF VARCHAR2(100);
/
DROP TABLE test_tbl;
/
CREATE TABLE test_tbl (rn,item) AS (
SELECT 1 ,'A0001' FROM dual UNION ALL
SELECT 2 ,'A0002' FROM dual UNION ALL
SELECT 3 ,'A0003' FROM dual UNION ALL
SELECT 4 ,'A0004' FROM dual UNION ALL
SELECT 5 ,'A0005' FROM dual UNION ALL
SELECT 6 ,'A0006' FROM dual UNION ALL
SELECT 7 ,'A0007' FROM dual UNION ALL
SELECT 8 ,'A0008' FROM dual UNION ALL
SELECT 9 ,'A0009' FROM dual
)
;
/

outer感でるまでぐりぐりしてたら、こうなった。

WITH sub AS (
SELECT
	ROWNUM AS rn
	,item_liz(s1.item) MULTISET UNION item_liz(s2.item) AS liz
--	,item_liz(s1.item) MULTISET UNION ALL item_liz(s2.item) AS lizz
--	,item_liz(s1.item) MULTISET UNION DISTINCT item_liz(s2.item) AS lizzz
--	,item_liz(s1.item) MULTISET INTERSECT item_liz(s2.item) AS lizzzz
--	,item_liz(s1.item) MULTISET INTERSECT DISTINCT item_liz(s2.item) AS lizzzzz
--	,item_liz(s1.item) MULTISET EXCEPT item_liz(s2.item) AS lizzzzzz
	,item_liz(s1.item) MULTISET EXCEPT DISTINCT item_liz(s2.item) AS lizzzzzzz
FROM
	test_tbl s1
	,LATERAL(SELECT src.* FROM test_tbl src WHERE s1.rn > src.rn) s2
	,LATERAL(SELECT src.* FROM test_tbl src WHERE s2.rn = MOD(src.rn,2)) s3
)
SELECT
	*
FROM
	(SELECT * FROM sub) s1
	OUTER APPLY (
				TABLE(s1.liz) s2
					OUTER APPLY (
						SELECT s3.* FROM TABLE(s1.lizzzzzzz) s3 WHERE s2.COLUMN_VALUE = s3.COLUMN_VALUE
					)
				);

outerでた！

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       |  8015K(100)|          |
|   1 |  MERGE JOIN OUTER                     |                 |    24M|  4237M|  8015K  (1)| 00:05:14 |
|   2 |   VIEW                                |                 |    36 |  2916 |    11  (19)| 00:00:01 |
|   3 |    COUNT                              |                 |       |       |            |          |
|   4 |     MERGE JOIN                        |                 |    36 |   756 |    11  (19)| 00:00:01 |
|   5 |      SORT JOIN                        |                 |     9 |   108 |     7  (15)| 00:00:01 |
|*  6 |       HASH JOIN                       |                 |     9 |   108 |     6   (0)| 00:00:01 |
|   7 |        TABLE ACCESS FULL              | TEST_TBL        |     9 |    81 |     3   (0)| 00:00:01 |
|   8 |        TABLE ACCESS FULL              | TEST_TBL        |     9 |    27 |     3   (0)| 00:00:01 |
|*  9 |      SORT JOIN                        |                 |     9 |    81 |     4  (25)| 00:00:01 |
|  10 |       TABLE ACCESS FULL               | TEST_TBL        |     9 |    81 |     3   (0)| 00:00:01 |
|  11 |   BUFFER SORT                         |                 |   667K|    66M|  8015K  (1)| 00:05:14 |
|  12 |    VIEW                               | VW_LAT_946460BC |   667K|    66M|   222K  (1)| 00:00:09 |
|  13 |     NESTED LOOPS OUTER                |                 |   667K|  2606K|   222K  (1)| 00:00:09 |
|  14 |      COLLECTION ITERATOR PICKLER FETCH|                 |  8168 | 16336 |    29   (0)| 00:00:01 |
|* 15 |      COLLECTION ITERATOR PICKLER FETCH|                 |    82 |   164 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

2つ目のouter applyをcross applyに変えてみる。

WITH sub AS (
SELECT
	ROWNUM AS rn
	,item_liz(s1.item) MULTISET UNION item_liz(s2.item) AS liz
--	,item_liz(s1.item) MULTISET UNION ALL item_liz(s2.item) AS lizz
--	,item_liz(s1.item) MULTISET UNION DISTINCT item_liz(s2.item) AS lizzz
--	,item_liz(s1.item) MULTISET INTERSECT item_liz(s2.item) AS lizzzz
--	,item_liz(s1.item) MULTISET INTERSECT DISTINCT item_liz(s2.item) AS lizzzzz
--	,item_liz(s1.item) MULTISET EXCEPT item_liz(s2.item) AS lizzzzzz
	,item_liz(s1.item) MULTISET EXCEPT DISTINCT item_liz(s2.item) AS lizzzzzzz
FROM
	test_tbl s1
	,LATERAL(SELECT src.* FROM test_tbl src WHERE s1.rn > src.rn) s2
	,LATERAL(SELECT src.* FROM test_tbl src WHERE s2.rn = MOD(src.rn,2)) s3
)
SELECT
	*
FROM
	(SELECT * FROM sub) s1
	OUTER APPLY (
				TABLE(s1.liz) s2
					CROSS APPLY (
						SELECT s3.* FROM TABLE(s1.lizzzzzzz) s3 WHERE s2.COLUMN_VALUE = s3.COLUMN_VALUE
					)
				);

id=13のOperationがNESTED LOOPS OUTERからHASH JOINになった。outer感なくなった。

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       |  2168 (100)|          |
|   1 |  MERGE JOIN OUTER                     |                 |    24M|  4237M|  2168   (4)| 00:00:01 |
|   2 |   VIEW                                |                 |    36 |  2916 |    11  (19)| 00:00:01 |
|   3 |    COUNT                              |                 |       |       |            |          |
|   4 |     MERGE JOIN                        |                 |    36 |   756 |    11  (19)| 00:00:01 |
|   5 |      SORT JOIN                        |                 |     9 |   108 |     7  (15)| 00:00:01 |
|*  6 |       HASH JOIN                       |                 |     9 |   108 |     6   (0)| 00:00:01 |
|   7 |        TABLE ACCESS FULL              | TEST_TBL        |     9 |    81 |     3   (0)| 00:00:01 |
|   8 |        TABLE ACCESS FULL              | TEST_TBL        |     9 |    27 |     3   (0)| 00:00:01 |
|*  9 |      SORT JOIN                        |                 |     9 |    81 |     4  (25)| 00:00:01 |
|  10 |       TABLE ACCESS FULL               | TEST_TBL        |     9 |    81 |     3   (0)| 00:00:01 |
|  11 |   BUFFER SORT                         |                 |   667K|    66M|  2168   (4)| 00:00:01 |
|  12 |    VIEW                               | VW_LAT_66E25303 |   667K|    66M|    60   (4)| 00:00:01 |
|* 13 |     HASH JOIN                         |                 |   667K|  2606K|    60   (4)| 00:00:01 |
|  14 |      COLLECTION ITERATOR PICKLER FETCH|                 |  8168 | 16336 |    29   (0)| 00:00:01 |
|  15 |      COLLECTION ITERATOR PICKLER FETCH|                 |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

#組み合わせ

左相関パターンをさらっとためしたところで、lateral句とunion allをあわせたsqlで何かできるか考えた。そういえば、以前、組み合わせを求めるSQLを@youmil_rainさん、@tlokwengさんからいろんな書き方を共有してもらいました。Qiita初めての投稿でいろいろアドバイスいただけたのがすごいうれしかったのを覚えています。

sql oracle 組み合わせ再帰with

そこで、lateral句とunion allで組み合わせsqlを再現できないか考えてみた。

##テストデータ
なつかしいテストデータだ。

テストデータ（開く）

DROP TABLE test_tbl;

CREATE TABLE test_tbl (rn,item) AS (
SELECT 1 ,'A0001' FROM dual UNION ALL
SELECT 2 ,'A0002' FROM dual UNION ALL
SELECT 3 ,'A0003' FROM dual UNION ALL
SELECT 4 ,'A0004' FROM dual UNION ALL
SELECT 5 ,'A0005' FROM dual UNION ALL
SELECT 6 ,'A0006' FROM dual UNION ALL
SELECT 7 ,'A0007' FROM dual UNION ALL
SELECT 8 ,'A0008' FROM dual UNION ALL
SELECT 9 ,'A0009' FROM dual
)
;

##組み合わせsql

lateralの引数にはunion allでつながれているprl1（parallel1）とprl2（parallel2）が渡されています。prl1では直前の左相関対象テーブルs1と同じrn分、nullの行を生成しています。prl2では直前の左相関対象テーブルs1のrnより大きいrn分、行を生成しています。同じ要領で直前の左相関対象テーブルと比較しながらlateral結合しています。s3以降、prl2のfilter条件にis not nullを付与しているのは、直前のlateral結合の結果により生成されたitem列がnullの場合を除外するためです。order byは見栄えを前回と比較するために、調節しています。

組み合わせsql（開く）

SELECT
	s1.rn
	,s1.item
	,s2.item
	,s3.item
	,s4.item
	,s5.item
	,s6.item
	,s7.item
	,s8.item
	,s9.item
FROM
	test_tbl s1
	,LATERAL(
			SELECT prl1.rn,NULL AS item FROM test_tbl prl1 WHERE s1.rn = prl1.rn
			UNION ALL
			SELECT prl2.* FROM test_tbl prl2 WHERE s1.rn < prl2.rn
			) s2
	,LATERAL(
			SELECT prl1.rn,NULL AS item FROM test_tbl prl1 WHERE s2.rn = prl1.rn
			UNION ALL
			SELECT prl2.* FROM test_tbl prl2 WHERE s2.rn < prl2.rn AND s2.item IS NOT NULL
			) s3
	,LATERAL(
			SELECT prl1.rn,NULL AS item FROM test_tbl prl1 WHERE s3.rn = prl1.rn
			UNION ALL
			SELECT prl2.* FROM test_tbl prl2 WHERE s3.rn < prl2.rn AND s3.item IS NOT NULL
			) s4
	,LATERAL(
			SELECT prl1.rn,NULL AS item FROM test_tbl prl1 WHERE s4.rn = prl1.rn
			UNION ALL
			SELECT prl2.* FROM test_tbl prl2 WHERE s4.rn < prl2.rn AND s4.item IS NOT NULL
			) s5
	,LATERAL(
			SELECT prl1.rn,NULL AS item FROM test_tbl prl1 WHERE s5.rn = prl1.rn
			UNION ALL
			SELECT prl2.* FROM test_tbl prl2 WHERE s5.rn < prl2.rn AND s5.item IS NOT NULL
			) s6
	,LATERAL(
			SELECT prl1.rn,NULL AS item FROM test_tbl prl1 WHERE s6.rn = prl1.rn
			UNION ALL
			SELECT prl2.* FROM test_tbl prl2 WHERE s6.rn < prl2.rn AND s6.item IS NOT NULL
			) s7
	,LATERAL(
			SELECT prl1.rn,NULL AS item FROM test_tbl prl1 WHERE s7.rn = prl1.rn
			UNION ALL
			SELECT prl2.* FROM test_tbl prl2 WHERE s7.rn < prl2.rn AND s7.item IS NOT NULL
			) s8
	,LATERAL(
			SELECT prl1.rn,NULL AS item FROM test_tbl prl1 WHERE s8.rn = prl1.rn
			UNION ALL
			SELECT prl2.* FROM test_tbl prl2 WHERE s8.rn < prl2.rn AND s8.item IS NOT NULL
			) s9
ORDER BY
	LENGTH(s1.item||s2.item||s3.item||s4.item||s5.item||s6.item||s7.item||s8.item||s9.item)
	,s1.item
	,s2.item
	,s3.item
	,s4.item
	,s5.item
	,s6.item
	,s7.item
	,s8.item
	,s9.item
;

##実行結果

実行計画もびしっとNL連段になっています。is not nullのfilter述語をつけると必ずフルスキャン（TABLE ACCESS FULL）が発生していることも確認できます。

lateral_sql_exec_plan（開く）

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |       |       | 34356 (100)|          |
|   1 |  NESTED LOOPS               |                 |  5760 |   742K| 34356   (1)| 00:00:02 |
|   2 |   NESTED LOOPS              |                 |  2880 |   360K| 17072   (1)| 00:00:01 |
|   3 |    NESTED LOOPS             |                 |  1440 |   156K|  8429   (1)| 00:00:01 |
|   4 |     NESTED LOOPS            |                 |   720 | 67680 |  4108   (1)| 00:00:01 |
|   5 |      NESTED LOOPS           |                 |   360 | 27720 |  1948   (1)| 00:00:01 |
|   6 |       NESTED LOOPS          |                 |   180 | 10800 |   867   (0)| 00:00:01 |
|   7 |        NESTED LOOPS         |                 |    90 |  3870 |   327   (0)| 00:00:01 |
|   8 |         NESTED LOOPS        |                 |    45 |  1170 |    57   (0)| 00:00:01 |
|   9 |          TABLE ACCESS FULL  | TEST_TBL        |     9 |    81 |     3   (0)| 00:00:01 |
|  10 |          VIEW               | VW_LAT_A18161FF |     5 |    85 |     6   (0)| 00:00:01 |
|  11 |           UNION-ALL         |                 |       |       |            |          |
|* 12 |            TABLE ACCESS FULL| TEST_TBL        |     1 |     3 |     3   (0)| 00:00:01 |
|* 13 |            TABLE ACCESS FULL| TEST_TBL        |     4 |    36 |     3   (0)| 00:00:01 |
|  14 |         VIEW                | VW_LAT_A18161FF |     2 |    34 |     6   (0)| 00:00:01 |
|  15 |          UNION-ALL          |                 |       |       |            |          |
|* 16 |           TABLE ACCESS FULL | TEST_TBL        |     1 |     3 |     3   (0)| 00:00:01 |
|* 17 |           FILTER            |                 |       |       |            |          |
|* 18 |            TABLE ACCESS FULL| TEST_TBL        |     1 |     9 |     3   (0)| 00:00:01 |
|  19 |        VIEW                 | VW_LAT_A18161FF |     2 |    34 |     6   (0)| 00:00:01 |
|  20 |         UNION-ALL           |                 |       |       |            |          |
|* 21 |          TABLE ACCESS FULL  | TEST_TBL        |     1 |     3 |     3   (0)| 00:00:01 |
|* 22 |          FILTER             |                 |       |       |            |          |
|* 23 |           TABLE ACCESS FULL | TEST_TBL        |     1 |     9 |     3   (0)| 00:00:01 |
|  24 |       VIEW                  | VW_LAT_A18161FF |     2 |    34 |     6   (0)| 00:00:01 |
|  25 |        UNION-ALL            |                 |       |       |            |          |
|* 26 |         TABLE ACCESS FULL   | TEST_TBL        |     1 |     3 |     3   (0)| 00:00:01 |
|* 27 |         FILTER              |                 |       |       |            |          |
|* 28 |          TABLE ACCESS FULL  | TEST_TBL        |     1 |     9 |     3   (0)| 00:00:01 |
|  29 |      VIEW                   | VW_LAT_A18161FF |     2 |    34 |     6   (0)| 00:00:01 |
|  30 |       UNION-ALL             |                 |       |       |            |          |
|* 31 |        TABLE ACCESS FULL    | TEST_TBL        |     1 |     3 |     3   (0)| 00:00:01 |
|* 32 |        FILTER               |                 |       |       |            |          |
|* 33 |         TABLE ACCESS FULL   | TEST_TBL        |     1 |     9 |     3   (0)| 00:00:01 |
|  34 |     VIEW                    | VW_LAT_A18161FF |     2 |    34 |     6   (0)| 00:00:01 |
|  35 |      UNION-ALL              |                 |       |       |            |          |
|* 36 |       TABLE ACCESS FULL     | TEST_TBL        |     1 |     3 |     3   (0)| 00:00:01 |
|* 37 |       FILTER                |                 |       |       |            |          |
|* 38 |        TABLE ACCESS FULL    | TEST_TBL        |     1 |     9 |     3   (0)| 00:00:01 |
|  39 |    VIEW                     | VW_LAT_A18161FF |     2 |    34 |     6   (0)| 00:00:01 |
|  40 |     UNION-ALL               |                 |       |       |            |          |
|* 41 |      TABLE ACCESS FULL      | TEST_TBL        |     1 |     3 |     3   (0)| 00:00:01 |
|* 42 |      FILTER                 |                 |       |       |            |          |
|* 43 |       TABLE ACCESS FULL     | TEST_TBL        |     1 |     9 |     3   (0)| 00:00:01 |
|  44 |   VIEW                      | VW_LAT_A18161FF |     2 |     8 |     6   (0)| 00:00:01 |
|  45 |    UNION-ALL                |                 |       |       |            |          |
|* 46 |     TABLE ACCESS FULL       | TEST_TBL        |     1 |     3 |     3   (0)| 00:00:01 |
|* 47 |     FILTER                  |                 |       |       |            |          |
|* 48 |      TABLE ACCESS FULL      | TEST_TBL        |     1 |     9 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1 
   9 - SEL$1  / S1@SEL$1
  10 - SET$1  / S2@SEL$1
  11 - SET$1 
  12 - SEL$2  / PRL1@SEL$2
  13 - SEL$3  / PRL2@SEL$3
  14 - SET$2  / S3@SEL$1
  15 - SET$2 
  16 - SEL$4  / PRL1@SEL$4
  17 - SEL$5 
  18 - SEL$5  / PRL2@SEL$5
  19 - SET$3  / S4@SEL$1
  20 - SET$3 
  21 - SEL$6  / PRL1@SEL$6
  22 - SEL$7 
  23 - SEL$7  / PRL2@SEL$7
  24 - SET$4  / S5@SEL$1
  25 - SET$4 
  26 - SEL$8  / PRL1@SEL$8
  27 - SEL$9 
  28 - SEL$9  / PRL2@SEL$9
  29 - SET$5  / S6@SEL$1
  30 - SET$5 
  31 - SEL$10 / PRL1@SEL$10
  32 - SEL$11
  33 - SEL$11 / PRL2@SEL$11
  34 - SET$6  / S7@SEL$1
  35 - SET$6 
  36 - SEL$12 / PRL1@SEL$12
  37 - SEL$13
  38 - SEL$13 / PRL2@SEL$13
  39 - SET$7  / S8@SEL$1
  40 - SET$7 
  41 - SEL$14 / PRL1@SEL$14
  42 - SEL$15
  43 - SEL$15 / PRL2@SEL$15
  44 - SET$8  / S9@SEL$1
  45 - SET$8 
  46 - SEL$16 / PRL1@SEL$16
  47 - SEL$17
  48 - SEL$17 / PRL2@SEL$17
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  12 - filter("S1"."RN"="PRL1"."RN")
  13 - filter("S1"."RN"<"PRL2"."RN")
  16 - filter("S2"."RN"="PRL1"."RN")
  17 - filter("S2"."ITEM" IS NOT NULL)
  18 - filter("S2"."RN"<"PRL2"."RN")
  21 - filter("S3"."RN"="PRL1"."RN")
  22 - filter("S3"."ITEM" IS NOT NULL)
  23 - filter("S3"."RN"<"PRL2"."RN")
  26 - filter("S4"."RN"="PRL1"."RN")
  27 - filter("S4"."ITEM" IS NOT NULL)
  28 - filter("S4"."RN"<"PRL2"."RN")
  31 - filter("S5"."RN"="PRL1"."RN")
  32 - filter("S5"."ITEM" IS NOT NULL)
  33 - filter("S5"."RN"<"PRL2"."RN")
  36 - filter("S6"."RN"="PRL1"."RN")
  37 - filter("S6"."ITEM" IS NOT NULL)
  38 - filter("S6"."RN"<"PRL2"."RN")
  41 - filter("S7"."RN"="PRL1"."RN")
  42 - filter("S7"."ITEM" IS NOT NULL)
  43 - filter("S7"."RN"<"PRL2"."RN")
  46 - filter("S8"."RN"="PRL1"."RN")
  47 - filter("S8"."ITEM" IS NOT NULL)
  48 - filter("S8"."RN"<"PRL2"."RN")

#在庫引当

再帰がないとlateral、union allのコンボは活きないような感じがあるので、以前作った、在庫引当sqlでいい感じに適用できないものかと思った。特に、結合条件のところを。イメージではなんとなくいけそう。case文で結合条件書き換えているものはいけそうな予感。。久しぶりに見返してみると、むずっってなったので、functionに切り出してみた。12cからwith句のinline functionができるようになったぽい。その場さくっと的な感じで使うのであろう。

PL/SQL Inline Functions/Procedures
Oracle Database 12c: In Line PL/SQL Functions in SQL queries

##テストデータ
stock とsales_trnのみ用意。

テストデータ（開く）

DROP TABLE stock PURGE;
CREATE TABLE stock AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    s0.item
	,s1.warehousing_date AS warehousing_date
    , s2.expiration_date AS expiration_date
    , TRUNC(ABS(dbms_random.VALUE(100, 300)), 0) AS stock_qty
    , TRUNC(ABS(dbms_random.VALUE(10, 90)), 0) AS reserved_stock_qty
FROM
    (SELECT CHR(LEVEL + 64) || '0001' AS item FROM dual CONNECT BY LEVEL <= 2) s0
	,(SELECT s1.std_date,to_char(TO_DATE(s1.std_date) - INTERVAL '10' DAY,'YYYYMMDD') AS warehousing_date FROM make_date s1) s1
    , LATERAL(SELECT s2.std_date,to_char(TO_DATE(s2.std_date) + INTERVAL '30' DAY,'YYYYMMDD') AS expiration_date FROM make_date s2 WHERE s1.std_date <= s2.std_date) s2
CONNECT BY
	LEVEL <= 1
;

ALTER TABLE stock ADD  CONSTRAINT stock_pk PRIMARY KEY(item,warehousing_date,expiration_date);

DROP TABLE sales_trn PURGE;
CREATE TABLE sales_trn(seq CONSTRAINT sales_trn_pk PRIMARY KEY, item, prov_inst_qty, cust_id, arrive_date) AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS std_date
    FROM
        (
            SELECT
                TO_DATE(to_char(sysdate, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_from
                , TO_DATE(to_char(sysdate + 2, 'YYYY/MM/DD'), 'YYYY/MM/DD') AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)
SELECT
    ROWNUM AS seq
    ,s0.item
    ,TRUNC(ABS(dbms_random.VALUE(10,100)),0) AS prov_inst_qty
    ,s1.cust_id
    ,s2.std_date AS arrive_date
FROM
	(SELECT CHR(LEVEL + 64) || '0001' AS item FROM dual CONNECT BY LEVEL <= 2) s0
	,(SELECT 'c_000' || LEVEL AS cust_id FROM dual CONNECT BY LEVEL <= 3) s1
	,make_date s2
;

##plsql function

functionたち。本当にそのまま移植しただけ。

使用するfunctionたち（開く）

DROP FUNCTION get_remain_prov_inst_qty_flg;
/
CREATE OR REPLACE FUNCTION get_remain_prov_inst_qty_flg (
    p_rn NUMBER--非再帰項判定（1：非再帰項、1以外：再帰項）
    ,p_pre_item VARCHAR2--前回のアイテム
    ,p_cur_item VARCHAR2--今回のアイテム
    ,p_cur_prov_enable_stock_qty NUMBER--今回の引当可能在庫数
    ,p_cur_prov_inst_qty  NUMBER--今回の引当指示数
    ,p_pre_remain_stock_qty NUMBER--前回の残在庫数
    ,p_pre_next_prov_enable_stock_qty NUMBER--前回の次鮮度日の引当可能在庫数
    ,p_pre_carryover_prov_inst_qty  NUMBER--前回の繰越引当指示数
    ,p_cur_warehousing_date  VARCHAR2--今回の入庫日
    ,p_cur_expiration_date  VARCHAR2--今回の賞味期限日
    ,p_pre_remain_prov_inst_qty_flg NUMBER--前回の残引当指示数有無フラグ（1:あり、0:なし、9:未使用）
)
RETURN NUMBER IS v_remain_prov_inst_qty_flg NUMBER;
BEGIN
    SELECT
        CASE
            WHEN p_rn = 1 THEN
                CASE
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN 0
                    --紐付く在庫がない場合、0を設定
                    WHEN p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty < 0 THEN 1
                    --引当可能在庫数から引当指示数を差し引き、0より少なくなる場合、1を設定
                    WHEN p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty >= 0 THEN 0
                    --引当可能在庫数から引当指示数を差し引き、0以上となる場合、0を設定
                END
            ELSE
                CASE
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL) THEN 0
                    --紐付く在庫がない場合、0を設定
                    WHEN p_pre_item <> p_cur_item AND p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) < 0 THEN 1
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0より少なくなる場合、1を設定
                    WHEN p_pre_item <> p_cur_item AND p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) >= 0 THEN 0
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0以上となる場合、0を設定
                    WHEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) < 0 THEN 1
                    --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より少なくなる場合、1を設定
                    WHEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) >= 0 THEN 0
                    --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以上となる場合、0を設定
                END
        END
        INTO v_remain_prov_inst_qty_flg
    FROM dual;
RETURN v_remain_prov_inst_qty_flg;
END;
/
DROP FUNCTION get_flg;
/
CREATE OR REPLACE FUNCTION get_flg (
    p_rn NUMBER--非再帰項判定（1：非再帰項、1以外：再帰項）
    ,p_pre_item VARCHAR2--前回のアイテム
    ,p_cur_item VARCHAR2--今回のアイテム
    ,p_cur_prov_enable_stock_qty NUMBER--今回の引当可能在庫数
    ,p_cur_prov_inst_qty  NUMBER--今回の引当指示数
    ,p_pre_remain_stock_qty NUMBER--前回の残在庫数
    ,p_pre_next_prov_enable_stock_qty NUMBER--前回の次鮮度日の引当可能在庫数
    ,p_pre_carryover_prov_inst_qty  NUMBER--前回の繰越引当指示数
    ,p_cur_warehousing_date  VARCHAR2--今回の入庫日
    ,p_cur_expiration_date  VARCHAR2--今回の賞味期限日
    ,p_pre_remain_prov_inst_qty_flg number--前回の残引当指示数有無フラグ（1:あり、0:なし、9:未使用）
)
RETURN NUMBER IS v_flg NUMBER;
BEGIN
    SELECT
        CASE
            WHEN p_rn = 1 THEN
                CASE
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN -1
                    --紐付く在庫がない場合、-1を設定
                    WHEN p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty < 0 THEN -1
                    --引当可能在庫数から引当指示数を差し引き、0より少ない場合、-1を設定
                    WHEN p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty = 0 THEN 0
                    --引当可能在庫数から引当指示数を差し引き、0の場合、0を設定
                    WHEN p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty > 0 THEN 1
                    --引当可能在庫数から引当指示数を差し引き、0より多い場合、1を設定
                END
            ELSE
                CASE
                    WHEN (p_pre_item <> p_cur_item AND p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) < 0) OR (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL) THEN -1
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0より少ないまたは紐付く在庫がない場合、-1を設定
                    WHEN p_pre_item <> p_cur_item AND p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) = 0 THEN 0
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0の場合、0を設定
                    WHEN p_pre_item <> p_cur_item AND p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) > 0 THEN 1
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から今回の引当指示数を差し引いた値が0より多い場合、1を設定
                    WHEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) < 0 THEN -1
                    --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より少なくなる場合、-1を設定
                    WHEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) = 0 THEN 0
                    --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0の場合、0を設定
                    WHEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) > 0 THEN 1
                    --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より多くなる場合、1を設定
                END
        END
        INTO v_flg
    FROM dual;
RETURN v_flg;
END;
/
DROP FUNCTION get_update_qty;
/
CREATE OR REPLACE FUNCTION get_update_qty (
    p_rn NUMBER--非再帰項判定（1：非再帰項、1以外：再帰項）
    ,p_pre_item VARCHAR2--前回のアイテム
    ,p_cur_item VARCHAR2--今回のアイテム
    ,p_cur_prov_enable_stock_qty NUMBER--今回の引当可能在庫数
    ,p_cur_prov_inst_qty  NUMBER--今回の引当指示数
    ,p_pre_remain_stock_qty NUMBER--前回の残在庫数
    ,p_pre_next_prov_enable_stock_qty NUMBER--前回の次鮮度日の引当可能在庫数
    ,p_pre_carryover_prov_inst_qty  NUMBER--前回の繰越引当指示数
    ,p_cur_warehousing_date  VARCHAR2--今回の入庫日
    ,p_cur_expiration_date  VARCHAR2--今回の賞味期限日
    ,p_pre_remain_prov_inst_qty_flg NUMBER--前回の残引当指示数有無フラグ（1:あり、0:なし、9:未使用）
)
RETURN NUMBER IS v_update_qty NUMBER;
BEGIN
    SELECT
        CASE
            WHEN p_rn = 1 THEN
                CASE
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN p_cur_prov_inst_qty
                    --紐付く在庫がない場合、引当指示数を設定
                    WHEN p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty > 0 THEN p_cur_prov_inst_qty
                    --引当可能在庫数から引当指示数を差し引き、0より多い場合、引当指示数を設定
                    ELSE p_cur_prov_enable_stock_qty
                    --上記以外は引当可能在庫数を設定
                END
            ELSE
                CASE
                    WHEN p_pre_item <> p_cur_item AND p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) > 0 THEN decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty)
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0より多い場合、前回の繰越引当指示数または今回の引当指示数を設定
                    WHEN p_pre_item <> p_cur_item AND p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) <= 0 THEN p_cur_prov_enable_stock_qty
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0以下の場合、今回の引当可能在庫数を設定
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty)
                    --紐付く在庫がない場合、今回の引当指示数を設定
                    WHEN p_pre_carryover_prov_inst_qty >= 0 AND decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) > 0 THEN decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty)
                    --前回の繰越引当指示数が0以上かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より多くなる場合、前回の繰越引当指示数または今回の引当指示数を設定
                    WHEN p_pre_carryover_prov_inst_qty >= 0 AND decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) <= 0 THEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty)
                    --前回の繰越引当指示数が0以上かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以下となる場合、前回の次鮮度日の引当可能在庫数または前回の残在庫数を設定
                    WHEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) <= 0 THEN nvl(p_cur_prov_enable_stock_qty,ABS((p_pre_next_prov_enable_stock_qty + p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty)))
                    --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以下となる場合、今回の引当可能在庫数または前回の残在庫数に前回の次鮮度日の引当可能在庫数を加算した値から前回の繰越引当指示数または今回の引当指示数を差し引いた値の絶対数を設定
                END
        END
        INTO v_update_qty
    FROM dual;
RETURN v_update_qty;
END;
/
DROP FUNCTION get_carryover_prov_inst_qty;
/
CREATE OR REPLACE FUNCTION get_carryover_prov_inst_qty (
    p_rn NUMBER--非再帰項判定（1：非再帰項、1以外：再帰項）
    ,p_pre_item VARCHAR2--前回のアイテム
    ,p_cur_item VARCHAR2--今回のアイテム
    ,p_cur_prov_enable_stock_qty NUMBER--今回の引当可能在庫数
    ,p_cur_prov_inst_qty  NUMBER--今回の引当指示数
    ,p_pre_remain_stock_qty NUMBER--前回の残在庫数
    ,p_pre_next_prov_enable_stock_qty NUMBER--前回の次鮮度日の引当可能在庫数
    ,p_pre_carryover_prov_inst_qty  NUMBER--前回の繰越引当指示数
    ,p_cur_warehousing_date  VARCHAR2--今回の入庫日
    ,p_cur_expiration_date  VARCHAR2--今回の賞味期限日
    ,p_pre_remain_prov_inst_qty_flg NUMBER--前回の残引当指示数有無フラグ（1:あり、0:なし、9:未使用）
)
RETURN NUMBER IS v_carryover_prov_inst_qty NUMBER;
BEGIN
    SELECT
        CASE
            WHEN p_rn = 1 THEN
                CASE
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN p_cur_prov_inst_qty
                    --紐付く在庫がない場合、引当指示数を設定
                    WHEN p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty > 0 THEN 0
                    --引当可能在庫数から引当指示数を差し引き、0より多い場合、0を設定
                    ELSE ABS(p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty)
                    --上記以外は引当可能在庫数から引当指示数を差し引いたの絶対数を設定
                END
            ELSE
                CASE
                    WHEN p_pre_item <> p_cur_item AND nvl(p_cur_prov_enable_stock_qty,0) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) >= 0 THEN 0
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0以上の場合、0を設定
                    WHEN p_pre_item <> p_cur_item AND nvl(p_cur_prov_enable_stock_qty,0) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) < 0 THEN ABS(nvl(p_cur_prov_enable_stock_qty,0) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty))
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値が0より少なくなる場合、今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値の絶対数を設定
                    WHEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) >= 0 THEN 0
                    --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以上場合、0を設定
                    WHEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) < 0 THEN ABS((decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty)) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty))
                    --前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より少なくなる場合、前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値の絶対数を設定
                END
        END
    INTO v_carryover_prov_inst_qty
    FROM dual;
RETURN v_carryover_prov_inst_qty;
END;
/
DROP FUNCTION get_remain_stock_qty;
/
CREATE OR REPLACE FUNCTION get_remain_stock_qty (
    p_rn NUMBER--非再帰項判定（1：非再帰項、1以外：再帰項）
    ,p_pre_item VARCHAR2--前回のアイテム
    ,p_cur_item VARCHAR2--今回のアイテム
    ,p_cur_prov_enable_stock_qty NUMBER--今回の引当可能在庫数
    ,p_cur_prov_inst_qty  NUMBER--今回の引当指示数
    ,p_pre_remain_stock_qty NUMBER--前回の残在庫数
    ,p_pre_next_prov_enable_stock_qty NUMBER--前回の次鮮度日の引当可能在庫数
    ,p_pre_carryover_prov_inst_qty  NUMBER--前回の繰越引当指示数
    ,p_cur_warehousing_date  VARCHAR2--今回の入庫日
    ,p_cur_expiration_date  VARCHAR2--今回の賞味期限日
    ,p_pre_remain_prov_inst_qty_flg NUMBER--前回の残引当指示数有無フラグ（1:あり、0:なし、9:未使用）
)
RETURN NUMBER IS v_remain_stock_qty NUMBER;
BEGIN
    SELECT
        CASE
            WHEN p_rn = 1 THEN
                CASE
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN 0
                    --紐付く在庫がない場合、0を設定
                    WHEN p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty <= 0 THEN 0
                    --引当可能在庫数から引当指示数を差し引き、0以下となる場合、残在庫数に0を設定
                    ELSE p_cur_prov_enable_stock_qty - p_cur_prov_inst_qty
                    --上記以外は引当可能在庫数から引当指示数を差し引いた値を設定
                END
            ELSE
                CASE
                    WHEN p_pre_item <> p_cur_item AND (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN 0
                    --前回アイテムと今回アイテムが異なり、紐付く在庫がない場合、0を設定
                    WHEN p_pre_item <> p_cur_item AND p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) > 0 THEN p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty)
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より多くなる場合、今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値を設定
                    WHEN p_pre_item <> p_cur_item AND p_cur_prov_enable_stock_qty - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) <= 0 THEN 0
                    --前回アイテムと今回アイテムが異なり、かつ今回の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以下となる場合、0を設定
                    WHEN p_pre_carryover_prov_inst_qty >= 0 AND decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) > 0 THEN decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty)
                    --前回の繰越引当指示数が0以上で、かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0より多くなる場合、前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引いた値を設定
                    WHEN p_pre_carryover_prov_inst_qty >= 0 AND decode(p_pre_remain_stock_qty,0,p_pre_next_prov_enable_stock_qty,p_pre_remain_stock_qty) - decode(p_pre_remain_prov_inst_qty_flg,1,p_pre_carryover_prov_inst_qty,p_cur_prov_inst_qty) <= 0 THEN 0
                    --前回の繰越引当指示数が0以上で、かつ前回の残在庫数または前回の次鮮度日の引当可能在庫数から前回の繰越引当指示数または今回の引当指示数を差し引き、0以下となる場合、0を設定
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN 0
                    --紐付く在庫がない場合、0を設定
                END
        END
        INTO v_remain_stock_qty
    FROM dual;
RETURN v_remain_stock_qty;
END;
/
DROP FUNCTION get_pre_prov_inst_qty;
/
CREATE OR REPLACE FUNCTION get_pre_prov_inst_qty (
    p_rn NUMBER--非再帰項判定（1：非再帰項、1以外：再帰項）
    ,p_pre_carryover_prov_inst_qty  NUMBER--前回の繰越引当指示数
    ,p_pre_warehousing_date  VARCHAR2--前回の入庫日
    ,p_pre_expiration_date  VARCHAR2--前回の賞味期限日
)
RETURN NUMBER IS v_pre_prov_inst_qty NUMBER;
BEGIN
    SELECT
        CASE
            WHEN p_rn = 1 THEN
                0
            ELSE
                CASE
                    WHEN p_pre_carryover_prov_inst_qty > 0 AND (p_pre_expiration_date <> '********' AND p_pre_warehousing_date <> '********' ) THEN p_pre_carryover_prov_inst_qty
                    --前回の繰越引当指示数が0より多い場合かつ前回の鮮度日がアスタリスクでない場合は前回の繰越引当指示数を設定
                    ELSE 0
                    --上記以外の場合、0を設定
                END
        END
        INTO v_pre_prov_inst_qty
    FROM dual;
RETURN v_pre_prov_inst_qty;
END;
/
DROP FUNCTION get_prov_enable_stock_qty;
/
CREATE OR REPLACE FUNCTION get_prov_enable_stock_qty (
    p_rn NUMBER--非再帰項判定（1：非再帰項、1以外：再帰項）
    ,p_pre_item VARCHAR2--前回のアイテム
    ,p_cur_item VARCHAR2--今回のアイテム
    ,p_cur_prov_enable_stock_qty NUMBER--今回の引当可能在庫数
    ,p_pre_remain_stock_qty NUMBER--前回の残在庫数
    ,p_pre_next_prov_enable_stock_qty NUMBER--前回の次鮮度日の引当可能在庫数
    ,p_cur_warehousing_date  VARCHAR2--今回の入庫日
    ,p_cur_expiration_date  VARCHAR2--今回の賞味期限日
)
RETURN NUMBER IS v_prov_enable_stock_qty NUMBER;
BEGIN
    SELECT
        CASE
            WHEN p_rn = 1 THEN
                CASE
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN 0
                    --紐付く在庫がない場合、0を設定
                    ELSE p_cur_prov_enable_stock_qty
                    --上記以外の場合、引当可能在庫数を設定
                END
            ELSE
                CASE
                    WHEN p_pre_item <> p_cur_item THEN nvl(p_cur_prov_enable_stock_qty,0)
                    --前回アイテムと今回アイテムが異なる場合、今回アイテムの引当可能在庫数を設定
                    WHEN p_pre_remain_stock_qty = 0 THEN p_pre_next_prov_enable_stock_qty
                    --前回の残在庫数が0の場合、前回の次鮮度日の引当可能在庫数を設定
                    WHEN (p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL ) THEN 0
                    --紐付く在庫がない場合、0を設定
                    ELSE p_pre_remain_stock_qty
                    --上記以外の場合、前回の残在庫数を設定
                END
            END
        INTO v_prov_enable_stock_qty
    FROM dual;
RETURN v_prov_enable_stock_qty;
END;
/
DROP FUNCTION get_priority;
/
CREATE OR REPLACE FUNCTION get_priority (
    p_rn NUMBER--非再帰項判定（1：非再帰項、1以外：再帰項）
    ,p_pre_item VARCHAR2--前回のアイテム
    ,p_cur_item VARCHAR2--今回のアイテム
    ,p_cur_warehousing_date  VARCHAR2--今回の入庫日
    ,p_cur_expiration_date  VARCHAR2--今回の賞味期限日
    ,p_pre_PRIORITY  NUMBER--前回の優先度
    ,p_cur_PRIORITY  NUMBER--今回の優先度
)
RETURN NUMBER IS v_priority NUMBER;
BEGIN
    SELECT
        CASE
            WHEN p_rn = 1 THEN
                CASE
                    WHEN(p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL) THEN nvl(p_cur_PRIORITY,0) + 1
                    --紐付く在庫がない場合、引当優先順位に1を加算（マイナス在庫の場合でも在庫データをリニアに処理したいため）
                    ELSE p_cur_PRIORITY
                    --上記以外は結合条件により紐付く値を設定
                END
            ELSE
                CASE
                    WHEN p_pre_item <> p_cur_item THEN 1
                    --前回アイテムと今回アイテムが異なる場合、1を設定
                    WHEN(p_cur_expiration_date IS NULL AND p_cur_warehousing_date IS NULL) THEN p_pre_PRIORITY + 1
                    --紐付く在庫がない場合、前回の引当優先順位に1を加算（マイナス在庫の場合でも在庫データをリニアに処理したいため）
                    ELSE p_cur_PRIORITY
                    --上記以外は結合条件により紐付く値を設定
                END
            END
        INTO v_priority
    FROM dual;
RETURN v_priority;
END;
/

##在庫引当sql（non lateral union all）

切り出したfunctionを使うように修正した在庫引当sql。はじめよりはだいぶマシになった気がする。問題は結合条件をlateralとunion allでいいかんじに修正できないか。。

在庫引当sql（non lateral union all）（開く）

WITH zaiko AS(
    SELECT
        row_number() OVER (ORDER BY s1.item, s1.expiration_date, s1.warehousing_date) AS rn
        ,s1.item
        ,s1.expiration_date
        ,s1.warehousing_date
        ,row_number() OVER (PARTITION BY s1.item ORDER BY s1.expiration_date , s1.warehousing_date) AS PRIORITY
        ,s1.stock_qty
        ,s1.reserved_stock_qty
        ,s1.stock_qty - s1.reserved_stock_qty AS prov_enable_stock_qty
        ,LEAD(s1.stock_qty - s1.reserved_stock_qty,1,0) OVER (PARTITION BY s1.item ORDER BY s1.expiration_date, s1.warehousing_date) AS next_prov_enable_stock_qty
    FROM
        stock s1
),sales AS (
    SELECT
        row_number() OVER (ORDER BY s1.item, s1.arrive_date, s1.cust_id, s1.seq) AS rn
        , s1.cust_id
        , s1.item
        , s1.arrive_date
        , s1.seq
        , s1.prov_inst_qty
    FROM
        sales_trn s1
),rec(
     rn
    , arrive_date
	, seq
    , cust_id
    , item
    , prov_inst_qty
    , expiration_date
    , warehousing_date
    , PRIORITY
    , prov_enable_stock_qty
    , pre_prov_inst_qty
    , remain_stock_qty
    , next_prov_enable_stock_qty
    , carryover_prov_inst_qty
    , update_qty
    , flg
    , remain_prov_inst_qty_flg
    , cnt
)AS(
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.seq
        , s1.cust_id
        , s1.item
        , s1.prov_inst_qty
        , nvl(s2.expiration_date,'********')--紐付く在庫がない場合、********を設定
        , nvl(s2.warehousing_date,'********')--紐付く在庫がない場合、********を設定
		, get_priority(s1.rn,'dummy_item','dummy_item',s2.warehousing_date,s2.expiration_date,0,s2.PRIORITY) AS PRIORITY
		, get_prov_enable_stock_qty(s1.rn,'dummy_item','dummy_item',s2.prov_enable_stock_qty,0,0,s2.warehousing_date,s2.expiration_date) AS prov_enable_stock_qty
        , get_pre_prov_inst_qty(s1.rn,0,'99999999','99999999') AS pre_prov_inst_qty
        , get_remain_stock_qty(s1.rn,'dummy_item','dummy_item',s2.prov_enable_stock_qty,s1.prov_inst_qty,0,0,0,s2.warehousing_date,s2.expiration_date,9) AS remain_stock_qty
        , nvl(s2.next_prov_enable_stock_qty,0)--次鮮度日の引当可能在庫数がない場合、0を設定
		, get_carryover_prov_inst_qty(s1.rn,'dummy_item','dummy_item',s2.prov_enable_stock_qty,s1.prov_inst_qty,0,0,0,s2.warehousing_date,s2.expiration_date,9) as carryover_prov_inst_qty
		, get_update_qty(s1.rn,'dummy_item','dummy_item',s2.prov_enable_stock_qty,s1.prov_inst_qty,0,0,0,s2.warehousing_date,s2.expiration_date,9) AS update_qty
		, get_flg(s1.rn,'dummy_item','dummy_item',s2.prov_enable_stock_qty,s1.prov_inst_qty,0,0,0,s2.warehousing_date,s2.expiration_date,9) AS flg
		, get_remain_prov_inst_qty_flg(s1.rn,'dummy_item','dummy_item',s2.prov_enable_stock_qty,s1.prov_inst_qty,0,0,0,s2.warehousing_date,s2.expiration_date,9) AS remain_prov_inst_qty_flg
        , count(*) over() as cnt
    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
                AND s1.item = s2.item
    WHERE
        s1.rn = 1
    UNION ALL
    SELECT
          s1.rn
        , s1.arrive_date
        , s1.seq
        , s1.cust_id
        , s1.item
        , s1.prov_inst_qty
        , nvl(s2.expiration_date,'********')--紐付く在庫がない場合、********を設定
        , nvl(s2.warehousing_date,'********')--紐付く在庫がない場合、********を設定
		, get_priority(s1.rn,tar.item,s1.item,s2.warehousing_date,s2.expiration_date,tar.PRIORITY,s2.PRIORITY) AS PRIORITY
		, get_prov_enable_stock_qty(s1.rn,tar.item,s1.item,s2.prov_enable_stock_qty,tar.remain_stock_qty,tar.next_prov_enable_stock_qty,s2.warehousing_date,s2.expiration_date) AS prov_enable_stock_qty
        , get_pre_prov_inst_qty(s1.rn,tar.carryover_prov_inst_qty,tar.expiration_date,tar.warehousing_date) AS pre_prov_inst_qty
        , get_remain_stock_qty(s1.rn,tar.item,s1.item,s2.prov_enable_stock_qty,s1.prov_inst_qty,tar.remain_stock_qty,tar.next_prov_enable_stock_qty,tar.carryover_prov_inst_qty,s2.warehousing_date,s2.expiration_date,tar.remain_prov_inst_qty_flg) AS remain_stock_qty
        , nvl(s2.next_prov_enable_stock_qty,0)--次鮮度日の引当可能在庫数がない場合、0を設定
		, get_carryover_prov_inst_qty(s1.rn,tar.item,s1.item,s2.prov_enable_stock_qty,s1.prov_inst_qty,tar.remain_stock_qty,tar.next_prov_enable_stock_qty,tar.carryover_prov_inst_qty,s2.warehousing_date,s2.expiration_date,tar.remain_prov_inst_qty_flg) as carryover_prov_inst_qty
		, get_update_qty(s1.rn,tar.item,s1.item,s2.prov_enable_stock_qty,s1.prov_inst_qty,tar.remain_stock_qty,tar.next_prov_enable_stock_qty,tar.carryover_prov_inst_qty,s2.warehousing_date,s2.expiration_date,tar.remain_prov_inst_qty_flg) AS update_qty
		, get_flg(s1.rn,tar.item,s1.item,s2.prov_enable_stock_qty,s1.prov_inst_qty,tar.remain_stock_qty,tar.next_prov_enable_stock_qty,tar.carryover_prov_inst_qty,s2.warehousing_date,s2.expiration_date,tar.remain_prov_inst_qty_flg) AS flg
		, get_remain_prov_inst_qty_flg(s1.rn,tar.item,s1.item,s2.prov_enable_stock_qty,s1.prov_inst_qty,tar.remain_stock_qty,tar.next_prov_enable_stock_qty,tar.carryover_prov_inst_qty,s2.warehousing_date,s2.expiration_date,tar.remain_prov_inst_qty_flg) AS remain_prov_inst_qty_flg
        , tar.cnt + count(*)over() as cnt
    FROM
        rec tar
            INNER JOIN sales s1
                ON
                    CASE
                        WHEN tar.remain_prov_inst_qty_flg = 1 THEN tar.rn
                        --前回の引当指示数が残っている場合は、同一のトランデータで処理する
                        ELSE tar.rn + 1
                        --上記以外は次のトランデータで処理する
                    END = s1.rn
                AND tar.cnt <= 1000000--ダミー値で終了条件を記載し、パーサをだましにいく（のつもり）。
            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
;

##実行結果（non lateral union all）

在庫引当処理はいい感じにできていると思う。

在庫引当sql（non lateral union all）実行結果（開く）

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |           |       |       |    72 (100)|          |
|   1 |  VIEW                                     |           |     2 |   434 |    72  (25)| 00:00:01 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|           |       |       |            |          |
|   3 |    WINDOW BUFFER                          |           |     1 |   145 |     8  (25)| 00:00:01 |
|*  4 |     HASH JOIN OUTER                       |           |     1 |   145 |     8  (25)| 00:00:01 |
|*  5 |      VIEW                                 |           |     1 |    75 |     4  (25)| 00:00:01 |
|*  6 |       WINDOW SORT PUSHED RANK             |           |    18 |   504 |     4  (25)| 00:00:01 |
|   7 |        TABLE ACCESS FULL                  | SALES_TRN |    18 |   504 |     3   (0)| 00:00:01 |
|   8 |      VIEW                                 |           |    12 |   840 |     4  (25)| 00:00:01 |
|   9 |       WINDOW SORT                         |           |    12 |   372 |     4  (25)| 00:00:01 |
|  10 |        TABLE ACCESS FULL                  | STOCK     |    12 |   372 |     3   (0)| 00:00:01 |
|  11 |    WINDOW BUFFER                          |           |     1 |   254 |    64  (25)| 00:00:01 |
|* 12 |     HASH JOIN OUTER                       |           |     1 |   254 |    64  (25)| 00:00:01 |
|* 13 |      HASH JOIN                            |           |     1 |   197 |    60  (25)| 00:00:01 |
|  14 |       RECURSIVE WITH PUMP                 |           |       |       |            |          |
|  15 |       BUFFER SORT (REUSE)                 |           |       |       |            |          |
|  16 |        VIEW                               |           |    18 |  1350 |     4  (25)| 00:00:01 |
|  17 |         WINDOW SORT                       |           |    18 |   504 |     4  (25)| 00:00:01 |
|  18 |          TABLE ACCESS FULL                | SALES_TRN |    18 |   504 |     3   (0)| 00:00:01 |
|  19 |      BUFFER SORT (REUSE)                  |           |       |       |            |          |
|  20 |       VIEW                                |           |    12 |   684 |     4  (25)| 00:00:01 |
|  21 |        WINDOW SORT                        |           |    12 |   372 |     4  (25)| 00:00:01 |
|  22 |         TABLE ACCESS FULL                 | STOCK     |    12 |   372 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

##在庫引当sql（lateral union all）

結合条件のcase文のlateral、union all変換はビビットきたら追記しよう。

在庫引当sql（lateral union all）（開く）

TODO

##実行結果（lateral union all）

在庫引当sql（lateral union all）実行結果（開く）

TODO

#あとがき
おもむろにsql oracle Advent Calendar 2018を作成したけど、ひとりぼっちにならなくてよかった。
@tlokwengさん、投稿ありがとうございます。

sql oracle Advent Calendar 2018

24日目はヒント句で遊んでいた内容をそのまま投稿します。

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

20191211追記

11gでラテラルはtable関数で実現できるとのこと。

以下サンプル。


create or replace type grp is object(n number,s varchar2(4000));
/
create or replace type grp_liz is table of grp;
/

with nats as(
    select level as n from dual connect by level<=3
)select s1.n,chr(s1.n+64),s2.n,s2.s
from nats s1,table( select collect(grp(n,chr(s2.n+64))) from nats s2 where s1.n<=s2.n) s2
order by s1.n,s2.n;

これをふまえてテストデータ作成

drop table stock purge;
create table stock as
with sub as(
select s1.n,column_value as nn
from(select level as n from dual connect by level<=3) s1
,table(select collect(level) from dual connect by level<=s1.n)
)
select
    s0.item
    ,s1.warehousing_date as warehousing_date
    , s1.expiration_date as expiration_date
    , trunc(abs(dbms_random.value(100, 300)), 0) as stock_qty
    , trunc(abs(dbms_random.value(10, 90)), 0) as reserved_stock_qty
from
    (select column_value as item from table(split('apple,banana'))) s0
    ,(select to_date(sysdate-n)as warehousing_date ,to_date(sysdate-nn+30) as expiration_date from sub ) s1
order by item,warehousing_date,expiration_date
;

split関数も織り交ぜた。

create or replace type liz is table of varchar2(4000);
/

create or replace function split(rsv_args varchar2)
return liz
as
rt liz;
begin
    select
        cast(collect(substr(rsv_args,decode(level-1,0,0,instr(rsv_args,',',1,level-1))+1,decode(instr(rsv_args,',',1,level),0,4000,instr(rsv_args,',',1,level))-decode(level-1,0,0,instr(rsv_args,',',1,level-1))-1)) as liz)
    into rt
    from
        dual
    connect by
        level <=length(rsv_args) - length(replace(rsv_args,',',''))+1;
    return rt;
end;
/

20191212追記

データ作成スクリプト追記

drop table test_order purge;
create table test_order as
select
    rownum as seq
    ,s0.item
    ,trunc(abs(dbms_random.value(10,100)),0) as prov_inst_qty
    ,s1.cust_id
    ,s2.arrive_date
from
    (select chr(level + 64) || '0001' as item from dual connect by level <= 2) s0
    ,(select 'c_000' || level as cust_id from dual connect by level <= 3) s1
    ,(select to_date(sysdate+level) as arrive_date from dual connect by level<=3 ) s2
;

drop table test_order purge;
create table test_order (
    seq             number
    , item            varchar2(8 byte)
    , prov_inst_qty   number
    , cust_id         varchar2(45 byte)
    , arrive_date     date
);

REM INSERTING into EXPORT_TABLE
set define off;
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (1,'A0001',98,'c_0001',to_date('19-12-13','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (2,'A0001',88,'c_0001',to_date('19-12-14','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (3,'A0001',52,'c_0001',to_date('19-12-15','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (4,'A0001',83,'c_0002',to_date('19-12-13','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (5,'A0001',89,'c_0002',to_date('19-12-14','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (6,'A0001',58,'c_0002',to_date('19-12-15','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (7,'A0001',62,'c_0003',to_date('19-12-13','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (8,'A0001',38,'c_0003',to_date('19-12-14','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (9,'A0001',34,'c_0003',to_date('19-12-15','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (10,'B0001',17,'c_0001',to_date('19-12-13','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (11,'B0001',42,'c_0001',to_date('19-12-14','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (12,'B0001',50,'c_0001',to_date('19-12-15','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (13,'B0001',35,'c_0002',to_date('19-12-13','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (14,'B0001',54,'c_0002',to_date('19-12-14','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (15,'B0001',26,'c_0002',to_date('19-12-15','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (16,'B0001',76,'c_0003',to_date('19-12-13','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (17,'B0001',13,'c_0003',to_date('19-12-14','RR-MM-DD'));
insert into test_order (seq,item,prov_inst_qty,cust_id,arrive_date) values (18,'B0001',20,'c_0003',to_date('19-12-15','RR-MM-DD'));
commit;

select * from test_order;

drop table test_stock purge;
create table test_stock as
with sub as(
select s1.n,column_value as nn
from(select level as n from dual connect by level<=3) s1
,table(select collect(level) from dual connect by level<=s1.n)
)
select
    s0.item
    ,s1.whs_dtm
    , s1.epr_dtm
    , trunc(abs(dbms_random.value(100, 300)), 0) as stk_qty
    , trunc(abs(dbms_random.value(1, 11)), 0) as rsv_stk_qty
from
    (select chr(level + 64) || '0001' as item from dual connect by level <= 2) s0
    ,(select to_date(sysdate-n)as whs_dtm ,to_date(sysdate-nn+30) as epr_dtm from sub ) s1
order by
    s0.item
    ,s1.whs_dtm
    , s1.epr_dtm
;


drop table test_stock purge;
create table test_stock (
    item          varchar2(8 byte)
    , whs_dtm       date
    , epr_dtm       date
    , stk_qty       number
    , rsv_stk_qty   number
);

REM INSERTING into EXPORT_TABLE
set define off;
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-08','RR-MM-DD'),270,6);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),219,9);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),168,4);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),285,2);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),294,8);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('A0001',to_date('19-12-11','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),175,7);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-08','RR-MM-DD'),195,5);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),297,3);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-09','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),185,8);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-09','RR-MM-DD'),177,10);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-10','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),267,10);
insert into test_stock (item,whs_dtm,epr_dtm,stk_qty,rsv_stk_qty) values ('B0001',to_date('19-12-11','RR-MM-DD'),to_date('20-01-10','RR-MM-DD'),161,8);
commit;

select * from test_stock;

remain_cntとcarry_over_cntをペアにして書き始める。
rules句は2行目以降をリニアに処理していることを意識する。
measures句では先頭行のみ計算して、2行目以降のデフォルト値を設定しておくイメージ(else 0)。

with stock_detail as(
    select
        row_number()over(order by item,whs_dtm,epr_dtm) as sort_key
        ,item
        ,row_number()over(partition by item order by whs_dtm,epr_dtm) as grpseq
        ,whs_dtm
        ,epr_dtm
        ,stk_qty-rsv_stk_qty as enable_cnt
    from
        test_stock
)
--select * from stock_detail;
select *
from stock_detail
model
    dimension by(sort_key)
    measures(
        item
        ,grpseq
        ,enable_cnt
        ,:upper_limit_cnt as upper_limit_cnt
        ,case
            when sort_key=1 and enable_cnt - :upper_limit_cnt >0 then enable_cnt - :upper_limit_cnt
            when sort_key=1 and enable_cnt - :upper_limit_cnt <=0 then 0
            else 0
        end as remain_cnt
        ,case
            when sort_key=1 and enable_cnt - :upper_limit_cnt >0 then 0
            when sort_key=1 and enable_cnt - :upper_limit_cnt <=0 then abs(enable_cnt - :upper_limit_cnt)
            else 0
        end as carry_over_cnt
    )
    rules automatic order(
        remain_cnt[sort_key>1]=case
            --differ previous row to current row
            --initilaize
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()]>0 then enable_cnt[cv()]-carry_over_cnt[cv()]
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()]<=0 then 0

            --same previous row to current row 
            --repeate after second row
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then enable_cnt[cv()]-carry_over_cnt[cv()-1]
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then 0
        end
        ,carry_over_cnt[sort_key>1]=case
            --differ previous row to current row
            --initilaize
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-:upper_limit_cnt>0 then 0
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-:upper_limit_cnt<=0 then abs(enable_cnt[cv()]-:upper_limit_cnt)

            --same previous row to current row 
            --repeate after second row
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then 0
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then abs(enable_cnt[cv()]-carry_over_cnt[cv()-1])
        end
    )
;

更新件数(upd_cnt)も追加。

with stock_detail as(
    select
        row_number()over(order by item,whs_dtm,epr_dtm) as sort_key
        ,item
        ,row_number()over(partition by item order by whs_dtm,epr_dtm) as grpseq
        ,whs_dtm
        ,epr_dtm
        ,stk_qty-rsv_stk_qty as enable_cnt
    from
        test_stock
)
--select * from stock_detail;
select *
from stock_detail
model
    dimension by(sort_key)
    measures(
        item
        ,grpseq
        ,enable_cnt
        ,:upper_limit_cnt as upper_limit_cnt
        ,case
            when sort_key=1 and enable_cnt - :upper_limit_cnt >0 then enable_cnt - :upper_limit_cnt
            when sort_key=1 and enable_cnt - :upper_limit_cnt <=0 then 0
            else 0
        end as remain_cnt
        ,case
            when sort_key=1 and enable_cnt - :upper_limit_cnt >0 then 0
            when sort_key=1 and enable_cnt - :upper_limit_cnt <=0 then abs(enable_cnt - :upper_limit_cnt)
            else 0
        end as carry_over_cnt
        ,case
            when sort_key=1 and enable_cnt - :upper_limit_cnt >0 then to_number(:upper_limit_cnt)
            when sort_key=1 and enable_cnt - :upper_limit_cnt <=0 then enable_cnt
            else 0
        end as upd_cnt
    )
    rules automatic order(
        remain_cnt[sort_key>1]=case
            --differ previous row to current row
            --initilaize
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()]>0 then enable_cnt[cv()]-carry_over_cnt[cv()]
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()]<=0 then 0

            --same previous row to current row 
            --repeate after second row
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then enable_cnt[cv()]-carry_over_cnt[cv()-1]
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then 0
        end
        ,carry_over_cnt[sort_key>1]=case
            --differ previous row to current row
            --initilaize
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-:upper_limit_cnt>0 then 0
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-:upper_limit_cnt<=0 then abs(enable_cnt[cv()]-:upper_limit_cnt)

            --same previous row to current row 
            --repeate after second row
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then 0
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then abs(enable_cnt[cv()]-carry_over_cnt[cv()-1])
        end
        ,upd_cnt[sort_key>1]=case
            --differ previous row to current row
            --initilaize
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-:upper_limit_cnt>0 then to_number(:upper_limit_cnt)
            when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-:upper_limit_cnt<=0 then enable_cnt[cv()]

            --same previous row to current row 
            --repeate after second row
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then carry_over_cnt[cv()-1]
            when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then enable_cnt[cv()]
        end
    )
;

引当ファンクション作成してみる。

ファンクションの引数に与えるコレクション。sort_keyは一意にしてから渡す。grpseqはitemごとに付番したもの。
enable_cntはなんらかの数量。

create or replace type args_grp is object(
    sort_key          number
    , item              varchar2(4000)
    , grpseq            number
    , enable_cnt        number
);
/

create or replace type args_grp_liz is table of args_grp;
/

ファンクションの戻り値のコレクション。残件数と繰越件数と更新件数を引数情報に付加して返却。

create or replace type rt_grp is object(
    sort_key          number
    , item              varchar2(4000)
    , grpseq            number
    , enable_cnt        number
    , remain_cnt        number
    , carry_over_cnt    number
    , upd_cnt           number
);
/

create or replace type rt_grp_liz is table of rt_grp;
/

上記2つのコレクションを宣言後以下を作成。

measures句では第2引数で受け取った上限件数を宣言しないこと。宣言するとコンパイルできない。
それ以外の句で参照する分には影響なしのはず。


create or replace function alloc(tgt args_grp_liz,upper_limit_cnt number)
return rt_grp_liz
as
rt rt_grp_liz;
begin
    select
        cast(collect(rt_grp(sort_key, item, grpseq, enable_cnt, remain_cnt, carry_over_cnt, upd_cnt)) as rt_grp_liz)
    into rt
    from(
        select sort_key, item, grpseq, enable_cnt, remain_cnt, carry_over_cnt, upd_cnt
        from table(tgt)
        model
            dimension by(sort_key)
            measures(
                item
                ,grpseq
                ,enable_cnt
                ,case
                    when sort_key=1 and enable_cnt - upper_limit_cnt >0 then enable_cnt - upper_limit_cnt
                    when sort_key=1 and enable_cnt - upper_limit_cnt <=0 then 0
                    else 0
                end as remain_cnt
                ,case
                    when sort_key=1 and enable_cnt - upper_limit_cnt >0 then 0
                    when sort_key=1 and enable_cnt - upper_limit_cnt <=0 then abs(enable_cnt - upper_limit_cnt)
                    else 0
                end as carry_over_cnt
                ,case
                    when sort_key=1 and enable_cnt - upper_limit_cnt >0 then to_number(upper_limit_cnt)
                    when sort_key=1 and enable_cnt - upper_limit_cnt <=0 then enable_cnt
                    else 0
                end as upd_cnt
            )
            rules automatic order(
                remain_cnt[sort_key>1]=case
                    --differ previous row to current row
                    --initilaize
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()]>0 then enable_cnt[cv()]-carry_over_cnt[cv()]
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()]<=0 then 0
        
                    --same previous row to current row 
                    --repeate after second row
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then enable_cnt[cv()]-carry_over_cnt[cv()-1]
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then 0
                end
                ,carry_over_cnt[sort_key>1]=case
                    --differ previous row to current row
                    --initilaize
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-upper_limit_cnt>0 then 0
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-upper_limit_cnt<=0 then abs(enable_cnt[cv()]-upper_limit_cnt)
        
                    --same previous row to current row 
                    --repeate after second row
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then 0
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then abs(enable_cnt[cv()]-carry_over_cnt[cv()-1])
                end
                ,upd_cnt[sort_key>1]=case
                    --differ previous row to current row
                    --initilaize
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-upper_limit_cnt>0 then to_number(upper_limit_cnt)
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-upper_limit_cnt<=0 then enable_cnt[cv()]
        
                    --same previous row to current row 
                    --repeate after second row
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then carry_over_cnt[cv()-1]
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then enable_cnt[cv()]
                end
            )
        );
    return rt;
end;
/

使用感

with stock_detail as(
    select
        row_number()over(order by item,whs_dtm,epr_dtm) as sort_key
        ,item
        ,row_number()over(partition by item order by whs_dtm,epr_dtm) as grpseq
        ,whs_dtm
        ,epr_dtm
        ,stk_qty-rsv_stk_qty as enable_cnt
    from
        test_stock
),stock_args as(
    select cast(collect(args_grp(sort_key,item,grpseq,enable_cnt)) as args_grp_liz) as tgt
    from stock_detail
)
select s2.sort_key, s2.item, s2.grpseq, s2.enable_cnt, s2.remain_cnt, s2.carry_over_cnt, s2.upd_cnt
from stock_args s1,table(alloc(s1.tgt,500)) s2;

grpseqはファンクションの引数として未使用のため、削除。

もっとシンプルに。

create or replace type args_grp is object(
    sort_key          number
    , item              varchar2(4000)
    , enable_cnt        number
);
/

create or replace type args_grp_liz is table of args_grp;
/

create or replace type rt_grp is object(
    sort_key          number
    , item              varchar2(4000)
    , enable_cnt        number
    , remain_cnt        number
    , carry_over_cnt    number
    , upd_cnt           number
);
/

create or replace type rt_grp_liz is table of rt_grp;
/

create or replace function alloc(tgt args_grp_liz,upper_limit_cnt number)
return rt_grp_liz
as
rt rt_grp_liz;
begin
    select
        cast(collect(rt_grp(sort_key, item, enable_cnt, remain_cnt, carry_over_cnt, upd_cnt)) as rt_grp_liz)
    into rt
    from(
        select sort_key, item, enable_cnt, remain_cnt, carry_over_cnt, upd_cnt
        from table(tgt)
        model
            dimension by(sort_key)
            measures(
                item
                ,enable_cnt
                ,case
                    when sort_key=1 and enable_cnt - upper_limit_cnt >0 then enable_cnt - upper_limit_cnt
                    when sort_key=1 and enable_cnt - upper_limit_cnt <=0 then 0
                    else 0
                end as remain_cnt
                ,case
                    when sort_key=1 and enable_cnt - upper_limit_cnt >0 then 0
                    when sort_key=1 and enable_cnt - upper_limit_cnt <=0 then abs(enable_cnt - upper_limit_cnt)
                    else 0
                end as carry_over_cnt
                ,case
                    when sort_key=1 and enable_cnt - upper_limit_cnt >0 then to_number(upper_limit_cnt)
                    when sort_key=1 and enable_cnt - upper_limit_cnt <=0 then enable_cnt
                    else 0
                end as upd_cnt
            )
            rules automatic order(
                remain_cnt[sort_key>1]=case
                    --differ previous row to current row
                    --initilaize
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()]>0 then enable_cnt[cv()]-carry_over_cnt[cv()]
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()]<=0 then 0

                    --same previous row to current row 
                    --repeate after second row
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then enable_cnt[cv()]-carry_over_cnt[cv()-1]
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then 0
                end
                ,carry_over_cnt[sort_key>1]=case
                    --differ previous row to current row
                    --initilaize
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-upper_limit_cnt>0 then 0
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-upper_limit_cnt<=0 then abs(enable_cnt[cv()]-upper_limit_cnt)

                    --same previous row to current row 
                    --repeate after second row
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then 0
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then abs(enable_cnt[cv()]-carry_over_cnt[cv()-1])
                end
                ,upd_cnt[sort_key>1]=case
                    --differ previous row to current row
                    --initilaize
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-upper_limit_cnt>0 then to_number(upper_limit_cnt)
                    when item[cv()-1]<>item[cv()] and enable_cnt[cv()]-upper_limit_cnt<=0 then enable_cnt[cv()]

                    --same previous row to current row 
                    --repeate after second row
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]>0 then carry_over_cnt[cv()-1]
                    when item[cv()-1]=item[cv()] and enable_cnt[cv()]-carry_over_cnt[cv()-1]<=0 then enable_cnt[cv()]
                end
            )
        );
    return rt;
end;
/

使用感

with stock_detail as(
    select
        row_number()over(order by item,whs_dtm,epr_dtm) as sort_key
        ,item
        ,whs_dtm
        ,epr_dtm
        ,stk_qty-rsv_stk_qty as enable_cnt
    from
        test_stock
),stock_args as(
    select cast(collect(args_grp(sort_key,item,enable_cnt)) as args_grp_liz) as tgt
    from stock_detail
)
select s2.sort_key
, s2.item
,row_number()over(partition by s2.item order by s3.whs_dtm,s3.epr_dtm) as grpseq
,s3.whs_dtm,s3.epr_dtm
,500 as prov_inst_qty
, s2.enable_cnt, s2.remain_cnt, s2.carry_over_cnt, s2.upd_cnt
from stock_args s1
,table(alloc(s1.tgt,500)) s2
,stock_detail s3
where s2.sort_key=s3.sort_key
;

order側から見た場合のうごき

with order_detail as(
    select
        row_number()over(order by item,arrive_date,cust_id,seq) as sort_key
        ,seq
        , item
        , prov_inst_qty as enable_cnt
        , cust_id
        , arrive_date
    from test_order
),order_summary as(
    select item,sum(enable_cnt) as sum_prov_inst_qty from order_detail group by item
),order_args as(
    select cast(collect(args_grp(sort_key,item,enable_cnt)) as args_grp_liz) as tgt
    from order_detail
)
select
    s1.item
    ,s2.cust_id
    ,s2.arrive_date
    ,s4.sort_key
    ,400 as sum_stk_qty
    ,s1.sum_prov_inst_qty
    ,s4.enable_cnt as prov_inst_qty
    ,sum(s4.enable_cnt)over(partition by s1.item order by s4.sort_key) as run_sum_prov_inst_qty
    ,s4.remain_cnt as carry_over_cnt
    ,s4.carry_over_cnt as remain_cnt
    ,s4.upd_cnt
from order_summary s1
,order_detail s2
,order_args s3
,table(alloc(s3.tgt,400)) s4
where s1.item=s2.item
and s2.item=s4.item
and s2.sort_key=s4.sort_key;