この記事は約39分46秒で読むことができます。

パスカルの三角形に関して

参考文献

やっと理解できた気がする。。オマージュしてみる。別のやり方で。

関数から作成、、そのまえに。

引数に渡すものと返却するものを考える。前段の段数番号と前段で構築した文字列。この2つを引数にする。返却値は次の段数で構築する文字列とその段数番号。まずは関数作成のまえに単一クエリで動きを捉えるところから始まる。

コード表示

col rn for 999
col ln for a10
col seq for 999
col seg for a10
col pre_seg for a10

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1') ) ) s1
--	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	rn
	,ln
	,seq
	,seg
	,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
FROM
	sub
;

どうして、-0を加えているのか最初分からなかった。次の段数計算に使用するためだったのかとトレースしてやっと分かる。

コード表示

  RN LN          SEQ SEG        PRE_SEG   
---- ---------- ---- ---------- ----------
   3 1-2-1         1 1          0         
   3 1-2-1         2 2          1         
   3 1-2-1         3 1          2         

-0を追加したもの。

コード表示

col rn for 999
col ln for a10
col seq for 999
col seg for a10
col pre_seg for a10

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	rn
	,ln
	,seq
	,seg
	,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
FROM
	sub
;

この規則性はわからんかった。パスカルの三角形ってきれいだな。直前の文字列が1であることを信じている感じが分かる。

コード表示

  RN LN          SEQ SEG        PRE_SEG   
---- ---------- ---- ---------- ----------
   3 1-2-1-0       1 1          0         
   3 1-2-1-0       2 2          1         
   3 1-2-1-0       3 1          2         
   3 1-2-1-0       4 0          1         

んでもって次段数と次段数に渡す文字列を構築する。

サマリ処理で実現。

コード表示

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	rn + 1 AS rn
	,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
FROM
	(
	SELECT
		rn
		,seq
		,seg
		,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
	FROM
		sub
	)
GROUP BY
	rn + 1
;

できた。あとはこいつをコレクションに変換してやるだけ。

コード表示

  RN SEG       
---- ----------
   4 1-3-3-1   

コレクションに変換してやる

コード表示

col liz for a100

WITH sub AS(
SELECT
	s1.rn
	,s1.ln
	,ROWNUM AS seq
	,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
FROM
	TABLE ( liz(item(3, '1-2-1-0') ) ) s1
CONNECT BY
	LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
)
SELECT
	CAST(COLLECT(item(rn,seg)) AS liz) AS liz
FROM
	(
	SELECT
		rn + 1 AS rn
		,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
	FROM
		(
		SELECT
			rn
			,seq
			,seg
			,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
		FROM
			sub
		)
	GROUP BY
		rn + 1
	)
;

コレクションできた!

コード表示

LIZ(RN, LN)                                                                                         
----------------------------------------------------------------------------------------------------
LIZ(ITEM(4, '1-3-3-1'))

ファンクションに落とし込む。with functionでやってみようかな。あとスキーマレベルでのファンクションも作ってみる。多分コード長くなるからファンクションに切り出したほうがいい。

12cからwith functionが使えます。bulk collectしてみる

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH
	FUNCTION calc(p_liz liz) RETURN liz IS
	rt liz :=NULL;
BEGIN
	SELECT
		item(rn,seg)
	BULK COLLECT
	INTO rt
	FROM
		(
		WITH sub AS(
		SELECT
			s1.rn
			,s1.ln
			,ROWNUM AS seq
			,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
		FROM
			(SELECT rn,ln || '-0' AS ln FROM TABLE(p_liz)) s1
		CONNECT BY
			LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
		)
		SELECT
			rn + 1 AS rn
			,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
		FROM
			(
			SELECT
				rn
				,seq
				,seg
				,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
			FROM
				sub
			)
		GROUP BY
			rn + 1
		);
	RETURN rt;
END;
SELECT calc(liz(item(3,'1-2-1'))) as liz FROM dual
/

スキーマレベルのファンクション

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/

CREATE OR REPLACE FUNCTION calc(p_liz liz)
RETURN liz
AS rt liz;
BEGIN
	SELECT
		item(rn,seg)
	BULK COLLECT
	INTO rt
	FROM
		(
		WITH sub AS(
		SELECT
			s1.rn
			,s1.ln
			,ROWNUM AS seq
			,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
		FROM
			(SELECT rn,ln || '-0' AS ln FROM TABLE(p_liz)) s1
		CONNECT BY
			LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
		)
		SELECT
			rn + 1 AS rn
			,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
		FROM
			(
			SELECT
				rn
				,seq
				,seg
				,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
			FROM
				sub
			)
		GROUP BY
			rn + 1
		);
	RETURN rt;
END;
/

SELECT calc(liz(item(3,'1-2-1'))) as liz FROM dual;

LIZ(RN, LN)                                                                                         
----------------------------------------------------------------------------------------------------
LIZ(ITEM(4, '1-3-3-1'))

再帰してみる。まずはイメージから。

5段ぐらいで。

コード表示

SELECT * FROM table(calc(liz(item(1,'1'))));
SELECT * FROM table(calc(liz(item(2,'1-1'))));
SELECT * FROM table(calc(liz(item(3,'1-2-1'))));
SELECT * FROM table(calc(liz(item(4,'1-3-3-1'))));

  RN LN                                                
---- --------------------------------------------------
   2 1-1                                               


  RN LN                                                
---- --------------------------------------------------
   3 1-2-1                                             


  RN LN                                                
---- --------------------------------------------------
   4 1-3-3-1                                           


  RN LN                                                
---- --------------------------------------------------
   5 1-4-6-4-1                                         

スキーマレベルのファンクションでグルグルする

スキーマレベルのファンクションを使用している。おおぉ

コード表示

COL ln FOR a10
COL triangle FOR a50

WITH sub AS(
SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= 5
),rec(rn,ln)AS(
SELECT s1.rn,to_char(s1.rn) FROM sub s1 WHERE s1.rn = 1
UNION ALL
SELECT s2.rn,s2.ln FROM rec s1,TABLE(calc(liz(item(s1.rn,to_char(s1.ln)))))s2
WHERE
	s1.rn < 5
)SELECT
	s1.*
	,MAX(LENGTH(s1.ln))OVER() AS mx
	,LENGTH(s1.ln) AS len
	,MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln) AS dif
	,(MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2 AS div
	,TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0) AS tnc
	,lpad(' ',TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0),' ') || s1.ln AS triangle
FROM
	rec s1
;

lpadの使い方がパズルのピースみたいに気持ちよかった。

コード表示

  RN LN                 MX        LEN        DIF        DIV        TNC TRIANGLE                                          
---- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------
   1 1                   9          1          8          4          4     1                                             
   2 1-1                 9          3          6          3          3    1-1                                            
   3 1-2-1               9          5          4          2          2   1-2-1                                           
   4 1-3-3-1             9          7          2          1          1  1-3-3-1                                          
   5 1-4-6-4-1           9          9          0          0          0 1-4-6-4-1                                         

即時レベルのwithファンクションでグルグルする

即時レベルのwithファンクションを使用している。おおぉ。再帰でないwithと再帰withとwithファンクションのコラボ。再帰でないwithは,(カンマ)つけなくていいんだね。勉強になった。最後は;(セミコロン)でなくて/(スラッシュ)で、締める。

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH
	FUNCTION calc(p_liz liz) RETURN liz IS
	rt liz :=NULL;
BEGIN
	SELECT
		item(rn,seg)
	BULK COLLECT
	INTO rt
	FROM
		(
		WITH sub AS(
		SELECT
			s1.rn
			,s1.ln
			,ROWNUM AS seq
			,decode(ROWNUM,1,substr(s1.ln,1,1),substr(s1.ln,instr(s1.ln,'-',1,ROWNUM - 1) + 1,nvl(nullif(instr(s1.ln,'-',1,ROWNUM),0),4000) - instr(s1.ln,'-',1,ROWNUM - 1) - 1)) AS seg
		FROM
			(SELECT rn,ln || '-0' AS ln FROM TABLE(p_liz)) s1
		CONNECT BY
			LENGTH(s1.ln) - LENGTH(REPLACE(s1.ln,'-','')) + 1 >= LEVEL
		)
		SELECT
			rn + 1 AS rn
			,LISTAGG(to_number(seg) + to_number(pre_seg),'-')WITHIN GROUP (ORDER BY seq)  AS seg
		FROM
			(
			SELECT
				rn
				,seq
				,seg
				,LAG(seg,1,0)OVER(ORDER BY seq) AS pre_seg
			FROM
				sub
			)
		GROUP BY
			rn + 1
		);
	RETURN rt;
END;
sub AS(
SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= 5
),rec(rn,ln)AS(
SELECT s1.rn,to_char(s1.rn) FROM sub s1 WHERE s1.rn = 1
UNION ALL
SELECT s2.rn,s2.ln FROM rec s1,TABLE(calc(liz(item(s1.rn,to_char(s1.ln)))))s2
WHERE
	s1.rn < 5
)SELECT
	s1.*
	,MAX(LENGTH(s1.ln))OVER() AS mx
	,LENGTH(s1.ln) AS len
	,MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln) AS dif
	,(MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2 AS div
	,TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0) AS tnc
	,lpad(' ',TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0),' ') || s1.ln AS triangle
FROM
	rec s1
/

lpadの使い方がパズルのピースみたいに気持ちよかった。

コード表示

  RN LN                 MX        LEN        DIF        DIV        TNC TRIANGLE                                          
---- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------------------
   1 1                   9          1          8          4          4     1                                             
   2 1-1                 9          3          6          3          3    1-1                                            
   3 1-2-1               9          5          4          2          2   1-2-1                                           
   4 1-3-3-1             9          7          2          1          1  1-3-3-1                                          
   5 1-4-6-4-1           9          9          0          0          0 1-4-6-4-1                                         

再帰段数をバインド変数にしておしまい。

バインド変数stkを入力して使う。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL triangle FOR a170
DEFINE stk=15


WITH sub AS(
SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <= &stk
),rec(rn,ln)AS(
SELECT s1.rn,to_char(s1.rn) FROM sub s1 WHERE s1.rn = 1
UNION ALL
SELECT s2.rn,s2.ln FROM rec s1,TABLE(calc(liz(item(s1.rn,to_char(s1.ln)))))s2
WHERE
	s1.rn < &stk
)SELECT
	lpad(' ',TRUNC((MAX(LENGTH(s1.ln))OVER() - LENGTH(s1.ln))/2,0),' ') || s1.ln AS triangle
FROM
	rec s1
;

tlokwegさんすごすぎ。。。

コード表示

TRIANGLE                                                                                                                                                                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                            1
                           1-1
                          1-2-1
                         1-3-3-1
                        1-4-6-4-1
                      1-5-10-10-5-1
                     1-6-15-20-15-6-1
                   1-7-21-35-35-21-7-1
                  1-8-28-56-70-56-28-8-1
               1-9-36-84-126-126-84-36-9-1
           1-10-45-120-210-252-210-120-45-10-1
         1-11-55-165-330-462-462-330-165-55-11-1
       1-12-66-220-495-792-924-792-495-220-66-12-1
   1-13-78-286-715-1287-1716-1716-1287-715-286-78-13-1
1-14-91-364-1001-2002-3003-3432-3003-2002-1001-364-91-14-1

15行が選択されました。 

再帰関数でも実現できた!

バインド変数stkを入力して使う。

コード表示

SET PAGESIZE 50000
SET LINESIZE 1000
SET TAB OFF
SET TRIMSPOOL ON
COL triangle FOR a170

DEFINE stk=15
/
CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
CREATE OR REPLACE FUNCTION calc_rec (
	p_liz liz
	, p_stk NUMBER
) RETURN liz AS
	rt   liz := p_liz;
BEGIN
	SELECT
		CASE
			WHEN p_stk = 0 THEN liz(item(NULL, NULL) )
			ELSE rt MULTISET UNION ALL calc_rec(CAST(collect(item(rn, seg) ) AS liz), p_stk - 1)
		END
	INTO rt
	FROM
		(
			WITH sub AS (
				SELECT
					s1.rn
					, s1.ln
					, ROWNUM AS seq
					, DECODE(ROWNUM, 1, substr(s1.ln, 1, 1), substr(s1.ln, instr(s1.ln, '-', 1, ROWNUM - 1) + 1, nvl(nullif(instr(s1.ln, '-', 1, ROWNUM), 0), 4000) - instr(s1.ln, '-', 1, ROWNUM - 1) - 1) ) AS seg
				FROM
					(
						SELECT
							rn
							, ln || '-0' AS ln
						FROM
							TABLE ( p_liz )
					) s1
				CONNECT BY
					length(s1.ln) - length(replace(s1.ln, '-', '') ) + 1 >= level
			) SELECT
				rn + 1 AS rn
				, LISTAGG(to_number(seg) + to_number(pre_seg), '-') WITHIN GROUP(
					ORDER BY
						seq
				) AS seg
			  FROM
				(
					SELECT
						rn
						, seq
						, seg
						, LAG(seg, 1, 0) OVER(
							ORDER BY
								seq
						) AS pre_seg
					FROM
						sub
				)
			  GROUP BY
				rn + 1
		);

	RETURN rt;
END;
/

SELECT
	lpad(' ',TRUNC((MAX(LENGTH(ln))OVER() - LENGTH(ln))/2,0),' ') || ln AS triangle
FROM
	TABLE(calc_rec(liz(item(1,'1')),&stk))
WHERE
	rn IS NOT NULL
/

Leave a Reply

Your email address will not be published. Required fields are marked *