sql oracle cross apply outer apply lateral


まえがき

そして、つい先日、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

-----------------------------------------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------------------

| 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 実行結果(開く)

image.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 |
---------------------------------------------------------------------------------------



左相関 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 実行結果(開く)

image.png

-----------------------------------------------------------------------------------------------------------------------

| 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 applycross 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 nullfilter述語をつけると必ずフルスキャン(TABLE ACCESS FULL)が発生していることも確認できます。

lateral_sql_exec_plan(開く)

image.png

image.png

-----------------------------------------------------------------------------------------------

| 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



テストデータ

stocksales_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。はじめよりはだいぶマシになった気がする。問題は結合条件をlateralunion 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)実行結果(開く)

image.png

image.png

-------------------------------------------------------------------------------------------------------

| 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日目はヒント句で遊んでいた内容をそのまま投稿します。

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