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

sql model iteration について理解を深めたい

参考文献

model句の例7 group_concatもどき  

テストデータの準備

参考文献のデータとsqlをすこしいじりながら動きを確かめる。その履歴を残す。

コード表示

drop table EmuGrConT purge;
create table EmuGrConT(ID,SortKey,Str) as
select 1,10,'AA' from dual union all
select 1,20,'BB' from dual union all
select 1,35,'CC' from dual union all
select 2,10,'DD' from dual union all
select 2,25,'DD' from dual union all
select 2,30,'EE' from dual union all
select 2,35,'DD' from dual union all
select 3,10,'FF' from dual;

UNTIL presentv(str[iteration_number],1,0) = 0 の場合

presentv関数は以下の参考文献を参照。

PRESENTV  
コード表示

SELECT
	*
FROM
	emugrcont
MODEL
	PARTITION BY ( ID )
	DIMENSION BY ( row_number() OVER( PARTITION BY ID ORDER BY sortkey ) AS rn )
	MEASURES (
	  str
	, CAST(NULL AS VARCHAR2(20) ) AS constr
	, sortkey
	, CAST(NULL AS VARCHAR2(20) ) AS tmpstr
	, CAST(NULL AS VARCHAR2(20) ) AS recrstr
	, CAST(NULL AS NUMBER) AS itcnt 
	)
RULES ITERATE(50) UNTIL presentv(str[iteration_number],1,0) = 0 (
		constr[0] = constr[0] 
						|| ','
	                    || str[iteration_number + 1]
		, tmpstr[iteration_number] = str[iteration_number]
		, recrstr[iteration_number] = recrstr[iteration_number - 1]
						|| case when presentv(str[iteration_number - 1],1,0) = 0 then '' else ',' end
						|| str[iteration_number]
		, itcnt[iteration_number] = iteration_number
)
ORDER BY
	ID
	,CASE
		WHEN rn = 0 THEN 999999
		ELSE rn
	END
	;

constrにsortkeyの先頭文字が出力されていることから、後判定なんだね。until。itcntに0が出力されていることから、iteration_numberは初期値0か。str[0]にあたる文字はないので、presentv(str[iteration_number],1,0)は0を返却。untilの終了条件を満たすので、loop終了するかと思いきや、最初の1回はloopする。後判定だから。rules句はpartition by の単位で実行される!

id=1の場合の様子

iteration_number constr str[iteration_number] presentv(str[iteration_number],1,0) presentv(str[iteration_number],1,0) = 0
0 ,AA str[0]=null 0 true

id=1の場合の様子

UNTIL presentv(str[iteration_number+1],1,0) = 0 の場合

presentv関数は以下の参考文献を参照。

PRESENTV  
コード表示

SELECT
	*
FROM
	emugrcont
MODEL
	PARTITION BY ( ID )
	DIMENSION BY ( row_number() OVER( PARTITION BY ID ORDER BY sortkey ) AS rn )
	MEASURES (
	  str
	, CAST(NULL AS VARCHAR2(20) ) AS constr
	, sortkey
	, CAST(NULL AS VARCHAR2(20) ) AS tmpstr
	, CAST(NULL AS VARCHAR2(20) ) AS recrstr
	, CAST(NULL AS NUMBER) AS itcnt 
	)
RULES ITERATE(50) UNTIL presentv(str[iteration_number + 1],1,0) = 0 (
		constr[0] = constr[0] 
						|| ','
	                    || str[iteration_number + 1]
		, tmpstr[iteration_number] = str[iteration_number]
		, recrstr[iteration_number] = recrstr[iteration_number - 1]
						|| case when presentv(str[iteration_number - 1],1,0) = 0 then '' else ',' end
						|| str[iteration_number]
		, itcnt[iteration_number] = iteration_number
)
ORDER BY
	ID
	,CASE
		WHEN rn = 0 THEN 999999
		ELSE rn
	END
	;

id=1の場合の様子

iteration_number constr str[iteration_number + 1] presentv(str[iteration_number + 1],1,0) presentv(str[iteration_number + 1],1,0) = 0
0 ,AA str[1]=AA 1 false
1 ,AA,BB str[2]=BB 1 false
2 ,AA,BB,CC str[3]=CC 1 false
3 ,AA,BB,CC str[4]=null 0 true

id=1の場合の様子

listaggにframe指定の機能がほしい

ほしすぎる

コード表示

DROP TABLE sales_trn PURGE;
CREATE TABLE sales_trn(seq, 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
                sysdate AS date_from
                , sysdate + 2 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
;

コード表示

SELECT
	*
FROM
	sales_trn
MODEL
	PARTITION BY (cust_id)
	DIMENSION BY (row_number() OVER (PARTITION BY cust_id ORDER BY seq) AS rn)
	MEASURES(
		seq
		,item
		,prov_inst_qty
		,arrive_date
		, CAST(NULL AS VARCHAR2(20) ) AS recr
		, CAST(NULL AS NUMBER ) AS cume
		, CAST(NULL AS NUMBER) AS itcnt
		, LISTAGG(prov_inst_qty,'+') WITHIN GROUP (ORDER BY seq ) OVER (PARTITION BY cust_id ) AS anarecr
		, SUM(prov_inst_qty)OVER(PARTITION BY cust_id ORDER BY seq) AS anacume
	)
RULES ITERATE(50) UNTIL presentv(prov_inst_qty[iteration_number + 1],1,0) = 0 (
		recr[iteration_number] = recr[iteration_number - 1]
						|| CASE WHEN presentv(prov_inst_qty[iteration_number - 1],1,0) = 0 THEN '' ELSE '+' END
						|| prov_inst_qty[iteration_number]
		, itcnt[iteration_number] = iteration_number
		, cume[iteration_number] = nvl(cume[iteration_number - 1],0) + prov_inst_qty[iteration_number]
)
;

反復だ

反復モデル

rnが10と11のときあやしいけど

コード表示

SELECT
	rn
	, num
	, it
	, dsp
FROM
	dual
MODEL
	DIMENSION BY ( 0 AS rn )
	MEASURES ( 1024 AS num
	, CAST(NULL AS NUMBER) AS it
	, CAST(NULL AS VARCHAR2(4000) ) AS dsp )
	RULES ITERATE(5)
	( num[iteration_number] =
		CASE
			WHEN iteration_number = 0 THEN num[iteration_number]
			ELSE num[iteration_number - 1] / 2
		END
		, dsp[iteration_number] =
		CASE
			WHEN iteration_number = 0 THEN 'non-rec'
			ELSE 'rec'
		END
		, it[iteration_number] = iteration_number )
ORDER BY
	rn;

AINE@pdb1> col rn for 999
AINE@pdb1> col num for 999999
AINE@pdb1> col it for 99999
AINE@pdb1> col dsp for a10
AINE@pdb1> SELECT rn , num , it , dsp FROM dual MODEL DIMENSION BY ( 0 AS rn ) MEASURES ( 1024 AS num , CAST(NULL AS NUMBER) AS it , CAST(NULL AS VARCHAR2(4000) ) AS dsp ) RULES ITERATE(5) ( num[iteration_number] = CASE WHEN iteration_number = 0 THEN num[iteration_number] ELSE num[iteration_number - 1] / 2 END , dsp[iteration_number] = CASE WHEN iteration_number = 0 THEN 'non-rec' ELSE 'rec' END , it[iteration_number] = iteration_number ) ORDER BY rn;

  RN     NUM            IT DSP
---- ------- ------------- ----------
   0    1024             0 non-rec
   1     512             1 rec
   2     256             2 rec
   3     128             3 rec
   4      64             4 rec

5 rows selected.

Elapsed: 00:00:00.00

INE@pdb1> SELECT rn , num , it , dsp FROM dual MODEL DIMENSION BY ( 0 AS rn ) MEASURES ( 1024 AS num , CAST(NULL AS NUMBER) AS it , CAST(NULL AS VARCHAR2(4000) ) AS dsp ) RULES ITERATE(12) ( num[iteration_number] = CASE WHEN iteration_number = 0 THEN num[iteration_number] ELSE num[iteration_number - 1] / 2 END , dsp[iteration_number] = CASE WHEN iteration_number = 0 THEN 'non-rec' ELSE 'rec' END , it[iteration_number] = iteration_number ) ORDER BY rn;

  RN     NUM            IT DSP
---- ------- ------------- ----------
   0    1024             0 non-rec
   1     512             1 rec
   2     256             2 rec
   3     128             3 rec
   4      64             4 rec
   5      32             5 rec
   6      16             6 rec
   7       8             7 rec
   8       4             8 rec
   9       2             9 rec
  10       1            10 rec
  11       1            11 rec

12 rows selected.

Elapsed: 00:00:00.00
AINE@pdb1> SELECT rn , num , it , dsp FROM dual MODEL DIMENSION BY ( 0 AS rn ) MEASURES ( 1024 AS num , CAST(NULL AS NUMBER) AS it , CAST(NULL AS VARCHAR2(4000) ) AS dsp ) RULES ITERATE(13) ( num[iteration_number] = CASE WHEN iteration_number = 0 THEN num[iteration_number] ELSE num[iteration_number - 1] / 2 END , dsp[iteration_number] = CASE WHEN iteration_number = 0 THEN 'non-rec' ELSE 'rec' END , it[iteration_number] = iteration_number ) ORDER BY rn;

  RN     NUM            IT DSP
---- ------- ------------- ----------
   0    1024             0 non-rec
   1     512             1 rec
   2     256             2 rec
   3     128             3 rec
   4      64             4 rec
   5      32             5 rec
   6      16             6 rec
   7       8             7 rec
   8       4             8 rec
   9       2             9 rec
  10       1            10 rec
  11       1            11 rec
  12       0            12 rec

13 rows selected.

Elapsed: 00:00:00.00

Leave a Reply

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