再帰処理中に蓄積した結果を参照する

#まえがき
実現したいことが別にあって、その過程で発生した内容を解決した際のメモを残します。
発生した内容は**再帰処理中において再帰処理で蓄積した結果を参照し、次の再帰処理でも使用する**です。エラーになったときの様子もそのまま残します。

#テストデータ

```sql
DROP TABLE test____$$____ PURGE;
CREATE TABLE test____$$____ (
    grp   NUMBER
    , seq   NUMBER
    , wgt   NUMBER
);

SET DEFINE OFF;
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (0,1,200);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (0,2,0);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (0,3,0);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (0,4,80);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (0,5,150);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (0,6,0);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (0,7,140);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (1,1,100);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (1,2,240);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (1,3,0);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (1,4,200);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (1,5,0);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (1,6,0);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (1,7,130);
INSERT INTO test____$$____ (grp,seq,wgt) VALUES (1,8,150);
COMMIT;
```

#求めたいイメージ

>grpごとにseq順で処理していくなかで、pre_wgtが0の場合、直前までのseqの範囲におけるpost_wgtの最大値+1000の値をpost_wgtに設定したい。次のseqでは直前で更新したpost_wgtも最大値を取得する際の範囲に含まれるようにするのがやりたいこと。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/f2d9677d-81bc-b483-f9ef-0d41d4f36933.png)


#いけたとおもた

[ORA-32042: 再帰的WITH句はそれ自体がUNION ALLブランチの1つを直接参照する必要があります](https://docs.oracle.com/cd/E16338_01/server.112/b56318/e29250.htm)

>いけるとおもったんだけどなー。だめだめだ。

```sql
WITH rec(
	grp
	, seq
	, pre_wgt
	, post_wgt
	, mx_wgt
)AS(
SELECT
	grp
	, seq
	, wgt AS pre_wgt
	, wgt AS post_wgt
	, wgt AS mx_wgt
FROM
	test____$$____
WHERE
	seq = 1
UNION ALL
SELECT
	s1.grp
	, s1.seq + 1
	, s1.wgt AS pre_wgt
	, CASE WHEN s1.wgt = 0 THEN s2.mx_wgt + 1000 ELSE s1.wgt END AS post_wgt
	, s2.mx_wgt AS mx_wgt
FROM
	test____$$____ s1
	,LATERAL(
				SELECT
					s2.seq + 1 AS seq
					,MAX(s2.mx_wgt)OVER() AS mx_wgt
				FROM
					rec s2
				WHERE
					s1.grp = s2.grp
				AND s1.seq + 1 > s2.seq
			)s2
WHERE
	s1.seq + 1 = s2.seq
)SELECT * FROM rec;
```

#コレクションで持ちまわした。

@tlokweng さんの記事を参考にしました。難しかったのが、直前までの再帰処理で蓄積した結果のなかから、最大値を求める方法でした。**function mx_ele**をplで作成し、直前までのコレクションを引数に渡し、再帰callして実現しました。

[オラクルの単一SQLクエリでパスカルの三角形をいくつか](https://qiita.com/tlokweng/items/29c92e97c036682db3ab)

```sql

drop function mx_ele;
/

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

create or replace function mx_ele(p_liz item_liz)
return number
as mx number;
begin
	select max(to_number(column_value)) into mx from table(p_liz);
	return mx;
end;
/

with sub as (select level as lv from dual connect by level <= 10)
select LISTAGG('''' || lv || '''',',')within group (order by rownum) as liz from sub;

select mx_ele(item_liz('1','2','3','4','5','6','7','8','9','10')) as mx from dual;

```

>作成したファンクション使用した。うまくいった。

```sql
WITH rec(
	grp
	, seq
	, wgt
	, pre_wgt
	, liz
	, pre_liz
	, mx_wgt
)AS(
SELECT
	grp
	, seq
	, wgt
	, wgt AS pre_wgt
	, item_liz(wgt) AS liz
	, NULL AS pre_liz
	, wgt AS mx_wgt
FROM
	test____$$____
WHERE
	seq = 1
UNION ALL
SELECT
	s2.grp
	, s2.seq
	, CASE WHEN s2.wgt = 0 THEN mx_ele(s1.liz) + 1000 ELSE s2.wgt END AS wgt
	, s2.wgt AS pre_wgt
	, s1.liz MULTISET UNION ALL item_liz(CASE WHEN  s2.wgt = 0 THEN mx_ele(s1.liz) + 1000 ELSE s2.wgt END) AS liz
	, s1.liz AS pre_liz
	, mx_ele(s1.liz) AS mx_wgt
FROM
	rec s1
	,test____$$____ s2
WHERE
	s1.grp = s2.grp
AND s1.seq + 1 = s2.seq
)SELECT * FROM rec ORDER BY grp,seq;

```

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/748db9d9-bdf2-bdb8-0b66-90bbcb34e5c0.png)

#あとがき

@tlokweng さんの記事でヒントを得て解決しました。ありがとうございます。

#20190323追記

>lateralでも蓄積した結果を参照できるようにかけた。where句以外でも再帰集合recと相関を保ちながらlateral句内で再帰処理結果を参照できる。これよく考えたら、すごい。以下の記事でも言及しています。

>[単一SQLクエリで穴掘り法や壁伸ばし法を使って迷路をちゃんと作ってみる](https://qiita.com/tlokweng/items/135b6333a956c1a88adb#%E3%81%8A%E3%82%8F%E3%82%8A%E3%81%AB)

##うまくいかなかったやつ
>**MAX(s1.post_wgt) OVER () AS mx_wgt**これだと**5**行目の**mx_wgt**列の値が**2200**になっていないのでおかしい。最初の行から現在行までのみを集計・集約計算範囲とするのはいいけど、常に**s1.post_wgt**を参照するとほしい結果を得られない。じゃあどうするか。**post_wgt**と**mx_wgt**の値を比較して大きいほうを取得するようにする。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/a39180fa-8763-6ab4-aa2c-e390b3b078cf.png)

```sql
WITH rec(
    grp
    , seq
    , pre_wgt
    , post_wgt
    , mx_wgt
)AS(
SELECT
    grp
    , seq
    , wgt AS pre_wgt
    , wgt AS post_wgt
    , wgt AS mx_wgt
FROM
    test____$$____
WHERE
    seq = 1
UNION ALL
SELECT
    s2.grp
    , s2.seq
    , s2.wgt AS pre_wgt
    , CASE WHEN s2.wgt = 0 THEN s3.mx_wgt + 1000 ELSE s2.wgt END AS post_wgt
    , s3.mx_wgt AS mx_wgt
FROM
    rec s1
	,test____$$____ s2
    ,LATERAL(
				SELECT
					MAX(mx_wgt) AS mx_wgt
				FROM
					(
					SELECT
						MAX(s1.post_wgt) OVER () AS mx_wgt
					FROM
						test____$$____ s3
					WHERE
						s1.grp = s3.grp
					)
            )s3
WHERE
	s1.grp = s2.grp
AND s1.seq + 1 = s2.seq
)SELECT * FROM rec ORDER BY grp,seq;

```

##うまくいったやつ
>**MAX(CASE WHEN s1.mx_wgt >= s1.post_wgt THEN s1.mx_wgt ELSE s1.post_wgt END) OVER ()**に書き換えた。コメントでもいただきました、grp内での最大値と比較してみるといい感じになりました。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/0bc278df-6737-dd42-cdd0-6b324d3a40a2.png)


```sql
WITH rec(
    grp
    , seq
    , pre_wgt
    , post_wgt
    , mx_wgt
)AS(
SELECT
    grp
    , seq
    , wgt AS pre_wgt
    , wgt AS post_wgt
    , wgt AS mx_wgt
FROM
    test____$$____
WHERE
    seq = 1
UNION ALL
SELECT
    s2.grp
    , s2.seq
    , s2.wgt AS pre_wgt
    , CASE WHEN s2.wgt = 0 THEN s3.mx_wgt + 1000 ELSE s2.wgt END AS post_wgt
    , s3.mx_wgt AS mx_wgt
FROM
    rec s1
	,test____$$____ s2
    ,LATERAL(
				SELECT
					MAX(mx_wgt) AS mx_wgt
				FROM
					(
					SELECT
						MAX(CASE WHEN s1.mx_wgt >= s1.post_wgt THEN s1.mx_wgt ELSE s1.post_wgt END) OVER () AS mx_wgt
					FROM
						test____$$____ s3
					WHERE
						s1.grp = s3.grp
					)
            )s3
WHERE
	s1.grp = s2.grp
AND s1.seq + 1 = s2.seq
)SELECT * FROM rec ORDER BY grp,seq;
```

>再帰withでのリニア処理を実現しているのはコメントアウト以外の部分。ここが骨組み。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/e092c292-3c60-7659-cafe-178e98089cd4.png)

```sql
WITH rec(
    grp
    , seq
    , pre_wgt
--    , post_wgt
--    , mx_wgt
)AS(
SELECT
    grp
    , seq
    , wgt AS pre_wgt
--    , wgt AS post_wgt
--    , wgt AS mx_wgt
FROM
    test____$$____
WHERE
    seq = 1
UNION ALL
SELECT
    s2.grp
    , s2.seq
    , s2.wgt AS pre_wgt
--    , CASE WHEN s2.wgt = 0 THEN s3.mx_wgt + 1000 ELSE s2.wgt END AS post_wgt
--    , s3.mx_wgt AS mx_wgt
FROM
    rec s1
    ,test____$$____ s2
--    ,LATERAL(
--                SELECT
--                    MAX(mx_wgt) AS mx_wgt
--                FROM
--                    (
--                    SELECT
--                        MAX(CASE WHEN s1.mx_wgt >= s1.post_wgt THEN s1.mx_wgt ELSE s1.post_wgt END) OVER () AS mx_wgt
--                    FROM
--                        test____$$____ s3
--                    WHERE
--                        s1.grp = s3.grp
--                    )
--            )s3
WHERE
    s1.grp = s2.grp
AND s1.seq + 1 = s2.seq
)SELECT * FROM rec ORDER BY grp,seq;
```

>**lateral**句内で再帰集合**rec**と相関（**where**句で指定**s1.grp = s3.grp**）させながら、行間参照し、何かしらの集計・集約計算等を明細行レベルで行い、計算結果を**inline**にする。**inline**にしたあと、最後サマル。サマらないと、不当に行増える。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/0bc278df-6737-dd42-cdd0-6b324d3a40a2.png)


```sql
WITH rec(
    grp
    , seq
    , pre_wgt
    , post_wgt
    , mx_wgt
)AS(
SELECT
    grp
    , seq
    , wgt AS pre_wgt
    , wgt AS post_wgt
    , wgt AS mx_wgt
FROM
    test____$$____
WHERE
    seq = 1
UNION ALL
SELECT
    s2.grp
    , s2.seq
    , s2.wgt AS pre_wgt
    , CASE WHEN s2.wgt = 0 THEN s3.mx_wgt + 1000 ELSE s2.wgt END AS post_wgt
    , s3.mx_wgt AS mx_wgt
FROM
    rec s1
    ,test____$$____ s2
    ,LATERAL(
                SELECT
                    MAX(mx_wgt) AS mx_wgt
                FROM
                    (
                    SELECT
                        MAX(CASE WHEN s1.mx_wgt >= s1.post_wgt THEN s1.mx_wgt ELSE s1.post_wgt END) OVER () AS mx_wgt
                    FROM
                        test____$$____ s3
                    WHERE
                        s1.grp = s3.grp
                    )
            )s3
WHERE
    s1.grp = s2.grp
AND s1.seq + 1 = s2.seq
)SELECT * FROM rec ORDER BY grp,seq;
```


##まとめ
>**分析関数**と**lateral句**と**再帰with**のコンボはつよい。**ラテラル句内でのunion all**による**処理分岐**も加わったら、かなりhot。シンプルな例を模索していきたい。あと、**再帰でないwith**でワークビューみたいなのを切り出して、切り出したその集合と**再帰with**で生成した処理結果集合を相関させることも**多段飛び参照**で実現できるらしいので、シンプルな例を模索していきたい。見つけたらupしよう。以上、ありがとうございました。


# 20191211追記

model句便利

```sql
drop table test_tbl purge;
create table test_tbl (
    grp   number
    , seq   number
    , wgt   number
);

set define off;
insert into test_tbl (grp,seq,wgt) values (0,1,200);
--insert into test_tbl (grp,seq,wgt) values (0,1,0);
insert into test_tbl (grp,seq,wgt) values (0,2,0);
insert into test_tbl (grp,seq,wgt) values (0,3,0);
insert into test_tbl (grp,seq,wgt) values (0,4,80);
insert into test_tbl (grp,seq,wgt) values (0,5,150);
insert into test_tbl (grp,seq,wgt) values (0,6,0);
insert into test_tbl (grp,seq,wgt) values (0,7,140);
insert into test_tbl (grp,seq,wgt) values (1,1,100);
--insert into test_tbl (grp,seq,wgt) values (1,1,0);
insert into test_tbl (grp,seq,wgt) values (1,2,240);
insert into test_tbl (grp,seq,wgt) values (1,3,0);
insert into test_tbl (grp,seq,wgt) values (1,4,200);
insert into test_tbl (grp,seq,wgt) values (1,5,0);
insert into test_tbl (grp,seq,wgt) values (1,6,0);
insert into test_tbl (grp,seq,wgt) values (1,7,130);
insert into test_tbl (grp,seq,wgt) values (1,8,150);
commit;
```


```sql
select
    rn,grp,seq,wgt
    ,add_wgt
    ,pre_done_wgt
    ,case when wgt=0 then add_wgt else wgt end as post_done_wgt
from
    test_tbl
model
    dimension by (row_number()over(order by grp,seq) as rn)
    measures(grp,seq,wgt
    ,case
        when seq=1 and wgt=0 then wgt+1000
        when seq=1 and wgt<>0 then wgt
        when seq<>1 then wgt
    end as add_wgt
    --ora-32637 handling
    ,case
        when seq=1 and wgt=0 then wgt+1000
        when seq=1 and wgt<>0 then wgt
        when seq<>1 then wgt
    end as pre_done_wgt
    )
    rules(
        add_wgt[rn>1]=case
                        when grp[cv()-1] <> grp[cv()] then pre_done_wgt[cv()]
                        when grp[cv()-1] = grp[cv()] and pre_done_wgt[cv()]=0 then add_wgt[cv()-1]+1000
                        when grp[cv()-1] = grp[cv()] and wgt[cv()]>add_wgt[cv()-1] then wgt[cv()]
                        when grp[cv()-1] = grp[cv()] and wgt[cv()]<=add_wgt[cv()-1] then add_wgt[cv()-1]
                        when grp[cv()-1] = grp[cv()] and pre_done_wgt[cv()]<>0 then add_wgt[cv()-1]
                      end
    )
;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/329436bf-ba58-c890-fe2f-f498472fc445.png)


グループ内の先頭行を0にした場合

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286037/9f655465-4fb3-7b88-6ab5-0684a0d0eec0.png)