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

sql oracle model clause lesson

テストデータの準備

calendarテーブル

コード表示

DROP TABLE calendar PURGE;
CREATE TABLE calendar AS
WITH make_date AS (
    SELECT
        to_char(A.date_from + ROWNUM - 1, 'YYYYMMDD') AS yyyymmdd
		,to_char(A.date_from + ROWNUM - 1,'ddd') AS dpy
		,to_char(A.date_from + ROWNUM - 1,'ww') AS wpy
		,to_char(A.date_from + ROWNUM - 1,'w') AS wpm
		,to_char(A.date_from + ROWNUM - 1,'d') AS downum
		,to_char(A.date_from + ROWNUM - 1,'dy','NLS_DATE_LANGUAGE = ENGLISH') AS dow
    FROM
        (
            SELECT
                sysdate AS date_from
                , sysdate + 30 AS date_to
            FROM
                dual
        ) A
    CONNECT BY
        LEVEL <= A.date_to - A.date_from + 1
)SELECT * FROM make_date
;

ALTER TABLE calendar ADD  CONSTRAINT calendar_pk PRIMARY KEY(yyyymmdd);

AINE@pdb1> col wpy for a3
AINE@pdb1> col wpm for a3
AINE@pdb1> col downum for a6
AINE@pdb1> select * from calendar;

YYYYMMDD DPY WPY WPM DOWNUM DOW
-------- --- --- --- ------ ------------
20190224 055 08  4   1      sun
20190225 056 08  4   2      mon
20190226 057 09  4   3      tue
20190227 058 09  4   4      wed
20190228 059 09  4   5      thu
20190301 060 09  1   6      fri
20190302 061 09  1   7      sat
20190303 062 09  1   1      sun
20190304 063 09  1   2      mon
20190305 064 10  1   3      tue
20190306 065 10  1   4      wed
20190307 066 10  1   5      thu
20190308 067 10  2   6      fri
20190309 068 10  2   7      sat
20190310 069 10  2   1      sun
20190311 070 10  2   2      mon
20190312 071 11  2   3      tue
20190313 072 11  2   4      wed
20190314 073 11  2   5      thu
20190315 074 11  3   6      fri
20190316 075 11  3   7      sat
20190317 076 11  3   1      sun
20190318 077 11  3   2      mon
20190319 078 12  3   3      tue
20190320 079 12  3   4      wed
20190321 080 12  3   5      thu
20190322 081 12  4   6      fri
20190323 082 12  4   7      sat
20190324 083 12  4   1      sun
20190325 084 12  4   2      mon
20190326 085 13  4   3      tue

31 rows selected.

Elapsed: 00:00:00.00

zaikoテーブル

コード表示

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
                sysdate AS date_from
                , sysdate + 2 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);

select * from stock;

ITEM     WAREHOUS EXPIRATI     STOCK_QTY RESERVED_STOCK_QTY
-------- -------- -------- ------------- ------------------
A0001    20190215 20190327           172                 41
A0001    20190215 20190328           136                 12
A0001    20190216 20190328           278                 25
A0001    20190214 20190327           267                 86
A0001    20190214 20190328           219                 72
A0001    20190214 20190326           278                 61
B0001    20190215 20190327           240                 56
B0001    20190215 20190328           247                 44
B0001    20190216 20190328           281                 73
B0001    20190214 20190327           285                 16
B0001    20190214 20190328           202                 29
B0001    20190214 20190326           294                 66

12 rows selected.

Elapsed: 00:00:00.00

sales_trnテーブル

コード表示

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
;

ALTER TABLE sales_trn ADD  CONSTRAINT sales_trn_pk PRIMARY KEY(seq);

AINE@pdb1> col cust_id for a7
AINE@pdb1> col arrive_date for a12
AINE@pdb1> SELECT * FROM sales_trn;

          SEQ ITEM     PROV_INST_QTY CUST_ID ARRIVE_DATE
------------- -------- ------------- ------- ------------
            1 A0001               20 c_0001  20190224
            2 A0001               96 c_0001  20190225
            3 A0001               53 c_0001  20190226
            4 A0001               44 c_0002  20190224
            5 A0001               15 c_0002  20190225
            6 A0001               26 c_0002  20190226
            7 A0001               92 c_0003  20190224
            8 A0001               46 c_0003  20190225
            9 A0001               81 c_0003  20190226
           10 B0001               56 c_0001  20190224
           11 B0001               78 c_0001  20190225
           12 B0001               59 c_0001  20190226
           13 B0001               20 c_0002  20190224
           14 B0001               16 c_0002  20190225
           15 B0001               87 c_0002  20190226
           16 B0001               78 c_0003  20190224
           17 B0001               44 c_0003  20190225
           18 B0001               27 c_0003  20190226

18 rows selected.

Elapsed: 00:00:00.00

sales_trnとcalendarを結合して週ごととかで分析できるようにしてみる

コード表示

select * from sales_trn s1 inner join calendar s2 on s1.arrive_date = s2.yyyymmdd order by s1.seq;

          SEQ ITEM     PROV_INST_QTY CUST_ID ARRIVE_DATE  YYYYMMDD DPY WPY WPM DOWNUM DOW
------------- -------- ------------- ------- ------------ -------- --- --- --- ------ ------------
            1 A0001               13 c_0001  20190224     20190224 055 08  4   1      sun
            2 A0001               42 c_0001  20190225     20190225 056 08  4   2      mon
            3 A0001               89 c_0001  20190226     20190226 057 09  4   3      tue
            4 A0001               65 c_0002  20190224     20190224 055 08  4   1      sun
            5 A0001               45 c_0002  20190225     20190225 056 08  4   2      mon
            6 A0001               79 c_0002  20190226     20190226 057 09  4   3      tue
            7 A0001               54 c_0003  20190224     20190224 055 08  4   1      sun
            8 A0001               83 c_0003  20190225     20190225 056 08  4   2      mon
            9 A0001               11 c_0003  20190226     20190226 057 09  4   3      tue
           10 B0001               58 c_0001  20190224     20190224 055 08  4   1      sun
           11 B0001               73 c_0001  20190225     20190225 056 08  4   2      mon
           12 B0001               84 c_0001  20190226     20190226 057 09  4   3      tue
           13 B0001               69 c_0002  20190224     20190224 055 08  4   1      sun
           14 B0001               37 c_0002  20190225     20190225 056 08  4   2      mon
           15 B0001               56 c_0002  20190226     20190226 057 09  4   3      tue
           16 B0001               82 c_0003  20190224     20190224 055 08  4   1      sun
           17 B0001               35 c_0003  20190225     20190225 056 08  4   2      mon
           18 B0001               82 c_0003  20190226     20190226 057 09  4   3      tue

18 rows selected.

Elapsed: 00:00:00.00

cust_id,itemごとに wpy,seqの順でprov_inst_qtyを漸化的に加算してみる

コード表示

WITH sub AS (
SELECT
	*
FROM
	sales_trn s1
		INNER JOIN calendar s2
			ON
				s1.arrive_date = s2.yyyymmdd
)
SELECT
	*
FROM
	sub
MODEL
	PARTITION BY (cust_id,item)
	DIMENSION BY (row_number() OVER (ORDER BY wpy,seq) AS rn)
	MEASURES(
				prov_inst_qty AS cume_qty
				, prov_inst_qty
				, arrive_date
				, dpy
				, wpy
				, wpm
				, downum
				, dow
			)
	RULES(
		cume_qty[any] ORDER BY rn = nvl(cume_qty[cv()-1],0) + prov_inst_qty[cv()]
	)
ORDER BY
	cust_id
	,item
	, rn
;

CUST_ID ITEM                RN      CUME_QTY PROV_INST_QTY ARRIVE_DATE  DPY WPY WPM DOWNUM DOW
------- -------- ------------- ------------- ------------- ------------ --- --- --- ------ ------------
c_0001  A0001                1            13            13 20190224     055 08  4   1      sun
c_0001  A0001                2            55            42 20190225     056 08  4   2      mon
c_0001  A0001               13            89            89 20190226     057 09  4   3      tue
c_0001  B0001                7            58            58 20190224     055 08  4   1      sun
c_0001  B0001                8           131            73 20190225     056 08  4   2      mon
c_0001  B0001               16            84            84 20190226     057 09  4   3      tue
c_0002  A0001                3            65            65 20190224     055 08  4   1      sun
c_0002  A0001                4           110            45 20190225     056 08  4   2      mon
c_0002  A0001               14            79            79 20190226     057 09  4   3      tue
c_0002  B0001                9            69            69 20190224     055 08  4   1      sun
c_0002  B0001               10           106            37 20190225     056 08  4   2      mon
c_0002  B0001               17            56            56 20190226     057 09  4   3      tue
c_0003  A0001                5            54            54 20190224     055 08  4   1      sun
c_0003  A0001                6           137            83 20190225     056 08  4   2      mon
c_0003  A0001               15            11            11 20190226     057 09  4   3      tue
c_0003  B0001               11            82            82 20190224     055 08  4   1      sun
c_0003  B0001               12           117            35 20190225     056 08  4   2      mon
c_0003  B0001               18            82            82 20190226     057 09  4   3      tue

18 rows selected.

Elapsed: 00:00:00.01

参照モデルについてふれる

以下を参照。参照モデルに関してはメインモデルからの参照のみを許可されている。

参照モデル  
参照モデル  

マスタを準備

コード表示

DROP TABLE basket_mst PURGE;
CREATE TABLE basket_mst(basket_no,upper_limit_weight,upper_limit_volume) AS 
SELECT
    'bas'|| rpad(LEVEL,2,0) AS basket_no
	,TRUNC(ABS(dbms_random.VALUE(100,1000)),0) AS upper_limit_weight
	,TRUNC(ABS(dbms_random.VALUE(100,1000)),0) AS upper_limit_volume
FROM
    dual
CONNECT BY
	LEVEL <=3
;

ALTER TABLE basket_mst ADD  CONSTRAINT basket_mst_pk PRIMARY KEY(basket_no);

DROP TABLE item_mst PURGE;
CREATE TABLE item_mst(item,weight,VOLUME) AS 
SELECT
    CHR(LEVEL + 64) || '0001' AS item
	,TRUNC(ABS(dbms_random.VALUE(1,10)),0) AS weight
	,TRUNC(ABS(dbms_random.VALUE(1,10)),0) AS VOLUME
FROM
    dual
CONNECT BY
    LEVEL <= 2
;

ALTER TABLE item_mst ADD CONSTRAINT item_mst_pk PRIMARY KEY(item);

DROP TABLE cust_mst PURGE;
CREATE TABLE cust_mst(cust_id,basket_no) AS 
SELECT
    'c_000' || LEVEL AS cust_id 
	,'bas'|| rpad(LEVEL,2,0) AS basket_no
FROM
    dual
CONNECT BY
    LEVEL <= 3
;

ALTER TABLE cust_mst ADD CONSTRAINT cust_mst_pk PRIMARY KEY(cust_id);

参照モデルについてふれるまえに再帰withでイメージするうごきを

もととなるインラインクエリはこちら

コード表示

SELECT
    row_number() OVER (PARTITION BY s1.cust_id ORDER BY s1.arrive_date,s1.seq) AS rn
	,s1.arrive_date
	,s1.cust_id
	,s1.seq
	,s1.item
	,s3.upper_limit_volume
	,s3.upper_limit_weight
	,s4.VOLUME
	,s4.weight
    ,s4.VOLUME * s1.prov_inst_qty AS sum_volume
    ,s4.weight * s1.prov_inst_qty AS sum_weight
FROM
	sales_trn s1
		INNER JOIN cust_mst s2
			ON
				s1.cust_id = s2.cust_id
		INNER JOIN basket_mst s3
			ON
				s2.basket_no = s3.basket_no
		INNER JOIN item_mst s4
			ON
				s1.item = s4.item
;

重量、容積のいづれかが上限超えたら、新規付番するといったイメージ

再帰系のsqlの特徴として、非再帰項を並列化できるから、row_number()もcust_idごとに付番し、再帰項ではrn,cust_idをwhere句に追加している。処理単位はwhere句に書く。

コード表示

WITH src AS (
SELECT
    row_number() OVER (PARTITION BY s1.cust_id ORDER BY s1.arrive_date,s1.seq) AS rn
	,s1.arrive_date
	,s1.cust_id
	,s1.seq
	,s1.item
	,s3.upper_limit_volume
	,s3.upper_limit_weight
	,s4.VOLUME
	,s4.weight
    ,s4.VOLUME * s1.prov_inst_qty AS sum_volume
    ,s4.weight * s1.prov_inst_qty AS sum_weight
FROM
	sales_trn s1
		INNER JOIN cust_mst s2
			ON
				s1.cust_id = s2.cust_id
		INNER JOIN basket_mst s3
			ON
				s2.basket_no = s3.basket_no
		INNER JOIN item_mst s4
			ON
				s1.item = s4.item
),rec (
    item
	,arrive_date
	,cust_id
	, seq
	, rn
    , sum_volume
    , sum_weight
    , upper_limit_volume
    , upper_limit_weight
    , cume_volume
    , cume_weight
    , pre_cume_volume
    , pre_cume_weight
    , over_flg
    , grp
) AS (
    SELECT
		item
		,arrive_date
		,cust_id
		,seq
        , rn
        , sum_volume
        , sum_weight
        , upper_limit_volume
        , upper_limit_weight
        , sum_volume AS cume_volume
        , sum_weight AS cume_weight
        , 0 AS pre_cume_volume
        , 0 AS pre_cume_weight
        , 1 AS over_flg
        , 1 AS grp
    FROM
        src
    WHERE
        rn = 1
    UNION ALL
    SELECT
		s2.item
		,s2.arrive_date
		,s2.cust_id
		,s2.seq
        ,s2.rn
        , s2.sum_volume
        , s2.sum_weight
        , s2.upper_limit_volume
        , s2.upper_limit_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_volume + s2.sum_volume
            ELSE s2.sum_volume
        END AS cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.cume_weight + s2.sum_weight
            ELSE s2.sum_weight
        END AS cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_volume + s1.sum_volume
            ELSE 0
        END AS pre_cume_volume
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN s1.pre_cume_weight + s1.sum_weight
            ELSE 0
        END AS pre_cume_weight
        , CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS over_flg
        ,s1.grp
        + CASE
            WHEN s1.cume_volume + s2.sum_volume <= s2.upper_limit_volume
                AND s1.cume_weight + s2.sum_weight <= s2.upper_limit_weight THEN 0
            ELSE 1
        END AS grp
    FROM
        rec s1
        , src s2
    WHERE
        s1.rn + 1 = s2.rn
	and s1.cust_id = s2.cust_id
)
SELECT
    *
FROM
    rec
ORDER BY
	cust_id
	,arrive_date
	,seq
;

参照モデル使ってみたやつ

再帰withと付番のしかたは異なる。セルのアドレスを意識した書き方になるので、row_number()の付番は処理単位も含めてorder by に書く。dimension by に処理単位を記載するように意識する。参照先のマスタのpkは処理単位dimension by に切り出すイメージかな。参照先のマスタのdimension byはマスタのpk項目を記載する。rules句で参照するときはpk項目を突合せるイメージ。なお、通しでrow_number()を付番する関係上、cust_idがブレイクしたことを検知できなくなる。measures句にその検知用フラグをlag()で仕込んでおく。仕込んだフラグをrules句で参照して、cust_idが切り替わった場合は新規付番を開始する。あとは最後にランニング集計かな。

コード表示

WITH sub AS (
SELECT
	s1.cust_id
	,s1.arrive_date
	,s1.seq
	,s1.item
	,s4.VOLUME
	,s4.weight
    ,s4.VOLUME * s1.prov_inst_qty AS sum_volume
    ,s4.weight * s1.prov_inst_qty AS sum_weight
FROM
	sales_trn s1
		INNER JOIN item_mst s4
			ON
				s1.item = s4.item
)
,calc AS(
SELECT
	*
FROM
	sub
MODEL
	REFERENCE basket_mst_ref ON (
		SELECT
			s1.basket_no
			,s1.upper_limit_volume
			,s1.upper_limit_weight
			,s2.cust_id
		FROM
			basket_mst s1
				INNER JOIN cust_mst s2
					ON
						s1.basket_no = s2.basket_no
		)
		DIMENSION BY (cust_id)
		MEASURES(upper_limit_volume,upper_limit_weight)
	MAIN sub
		DIMENSION BY (cust_id,row_number() OVER (ORDER BY cust_id,arrive_date,seq) AS rn)
		MEASURES(
					item
					, arrive_date
					, seq
					, sum_volume
					, sum_weight
					, CASE WHEN LAG(cust_id,1,1) OVER (ORDER BY cust_id,arrive_date,seq) <> cust_id THEN 1 ELSE 0 END AS break_flg
					, 0 AS cume_volume
					, 0 AS cume_weight
					, 0 AS volume_over_flg
					, 0 AS weight_over_flg
					, 1 AS over_flg
					)
    RULES AUTOMATIC ORDER(
    cume_volume[cust_id,ANY] ORDER BY rn =
    sum_volume[cv(),cv()] +
    CASE
        WHEN nvl(cume_volume[cv(),cv() - 1], 0) + sum_volume[cv(),cv()] <= basket_mst_ref.upper_limit_volume[cv(cust_id)]
            AND nvl(cume_weight[cv(),cv() - 1], 0) + sum_weight[cv(),cv()] <= basket_mst_ref.upper_limit_weight[cv(cust_id)] THEN nvl(cume_volume[cv(),cv() - 1], 0)
        ELSE 0
    END
    ,cume_weight[cust_id,ANY] ORDER BY rn =
    sum_weight[cv(),cv()] +
    CASE
        WHEN nvl(cume_volume[cv(),cv() - 1], 0) + sum_volume[cv(),cv()] <= basket_mst_ref.upper_limit_volume[cv(cust_id)]
            AND nvl(cume_weight[cv(),cv() - 1], 0) + sum_weight[cv(),cv()] <= basket_mst_ref.upper_limit_weight[cv(cust_id)] THEN nvl(cume_weight[cv(),cv() - 1], 0)
        ELSE 0
    END
    ,over_flg[cust_id,rn > 1] ORDER BY rn = CASE
												WHEN break_flg[cv(),cv()] = 1 THEN 1 
												WHEN nvl(cume_volume[cv(),cv() - 1], 0) + sum_volume[cv(),cv()] <= basket_mst_ref.upper_limit_volume[cv(cust_id)]
													AND nvl(cume_weight[cv(),cv() - 1], 0) + sum_weight[cv(),cv()] <= basket_mst_ref.upper_limit_weight[cv(cust_id)]THEN 0
												ELSE 1
											 END
    ,volume_over_flg[cust_id,ANY] ORDER BY rn = CASE
													WHEN nvl(cume_volume[cv(),cv() - 1], 0) + sum_volume[cv(),cv()] <= basket_mst_ref.upper_limit_volume[cv(cust_id)] THEN 0
													ELSE 1
												 END
    ,weight_over_flg[cust_id,ANY] ORDER BY rn = CASE
													WHEN nvl(cume_weight[cv(),cv() - 1], 0) + sum_weight[cv(),cv()] <= basket_mst_ref.upper_limit_weight[cv(cust_id)] THEN 0
													ELSE 1
												 END
            )
ORDER BY
	rn
)
SELECT
	s1.*
	,SUM(s1.over_flg) OVER (PARTITION BY s1.cust_id ORDER BY s1.rn) AS grp
FROM
	calc s1
;

マルチセル参照は分析関数で代用できる

分析関数ルール句にかけるんだね。雰囲気だけど、マルチセルは範囲を横断的にってニュアンスだから、分析関数でフレームを指定できる限り、相性は抜群だと思う。DIMENSION BYにrow_number()で番号振るときはpartition byで指定した項目も含めないとね。とあとできづいた。ルール句で分析関数を使用するときはorder by指定を左辺にしないこと。右辺の分析関数はおもうままに書いたらいい。

コード表示

WITH sub AS (
SELECT
	*
FROM
	sales_trn s1
		INNER JOIN calendar s2
			ON
				s1.arrive_date = s2.yyyymmdd
)
SELECT
	*
FROM
	sub
MODEL
	PARTITION BY (cust_id,item)
	DIMENSION BY (row_number() OVER (ORDER BY cust_id,item,wpy,seq) AS rn)
	MEASURES(
				prov_inst_qty AS cume_qty
				,0 AS cume_anafunc_qty
				, prov_inst_qty
				, arrive_date
				, dpy
				, wpy
				, wpm
				, downum
				, dow
				,seq
			)
	RULES(
		cume_qty[any] ORDER BY rn = nvl(cume_qty[cv()-1],0) + prov_inst_qty[cv()]
		,cume_anafunc_qty[any]= sum(prov_inst_qty)over(partition by cust_id,item ORDER BY wpy,seq rows between UNBOUNDED PRECEDING and current row)
		
	)
ORDER BY
	cust_id
	,item
	, rn
;

Leave a Reply

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