sql oracle model clause lesson

#まえがき

model句練習したいなーと思い、書きはじめました。
付番系は見かけることがおおいので、これを題材にしようと思いました。

#参考文献

>[sql oracle recursive lesson@create_data.sql]
(https://qiita.com/ukijumotahaneniarukenia/items/b69e7af6928f6fabf4fc)

>[Patterns everywhere – Find them fast!Oracle Database 12cのSQLパターン・マッチング
]
(https://www.oracle.com/technetwork/jp/database/bi-datawarehousing/wp-sql-pattern-matching-1965433-ja.pdf)

#練習仕様

顧客の購入データがあったとして、そのデータがcumeテーブルに書き込まれ、蓄積されてくるとする。
蓄積されているcumeテーブルからデータを取り出し、uptakeテーブルに以下の付番ルールを満たしてから取り込むことにする。

顧客が日ごとに購入した単位に伝票番号を付番し、同一の伝票番号単位に商品数分、伝票明細番号を付番する（同じ商品であろうと、伝票明細番号は新規付番する）

#データ準備

2人の顧客の本日から1日後のデータ分を対象に取り組んでみたいと思います。
ここでの2人はランダムなので、よしなに。

```sql:create_cume.sql

DROP TABLE cume;

CREATE TABLE cume AS SELECT * FROM sales_trn WHERE cust_id IN ( 'c_1EAX' , 'c_1F0A' ) AND purchase_date BETWEEN TO_CHAR(SYSDATE, 'yyyymmdd') AND TO_CHAR(SYSDATE + INTERVAL '1' DAY, 'yyyymmdd'); 

--▼def_tab
--CREATE TABLE cume (
--	seq             NUMBER(38, 0)
--	, item            VARCHAR2(24 BYTE)
--	, qty             NUMBER
--	, price           NUMBER
--	, cust_id         VARCHAR2(24 BYTE)
--	, purchase_date   DATE
--)
--;

```

```sql:conf_cume_data.sql

select count(seq) from cume;

```

【conf_cume_data.sqlの取得結果】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/95d4b141-b3a1-23c6-360d-4df7ff960c64.png)


取り込み先のテーブルはこちらです。

```sql:create_uptake.sql

drop table uptake;

CREATE TABLE uptake (
	 cust_id               VARCHAR2(24 BYTE)
	, purchase_date         DATE
	, slip_number           NUMBER
	, slip_details_number   NUMBER
	, item                  VARCHAR2(24 BYTE)
	, qty                   NUMBER
	, price                 NUMBER
);

```

#model句による付番

model句を使った付番処理です。model句たのしい。

```sql:insert_uptake.sql

INSERT /*+ APPEND */ INTO uptake--ダイレクトパスインサート
WITH for_slip_number AS (
SELECT
	rn,seq,cust_id, purchase_date, slip_number, slip_details_number, item, qty ,price
FROM
	cume
MODEL
	DIMENSION BY (row_number() OVER (ORDER BY cust_id,purchase_date,seq) AS rn)
	MEASURES(seq,cust_id,purchase_date,1 AS slip_number,1 AS slip_details_number,item,qty,price)
	RULES(
			slip_number[rn > 1] ORDER BY rn =
			CASE
				WHEN cust_id[cv()] <> cust_id[cv() - 1]
					OR purchase_date[cv()] <> purchase_date[cv() - 1] THEN slip_number[cv() -1] + 1
				ELSE slip_number[cv() - 1] END
			)
)
,for_slip_details_number AS (
SELECT
	rn, seq,cust_id, purchase_date, slip_number, slip_details_number, item, qty ,price
FROM
	for_slip_number
MODEL
	PARTITION BY (slip_number)
	DIMENSION BY (rn)
	MEASURES(seq,cust_id,purchase_date,1 AS slip_details_number,item,qty,price)
	RULES(
			slip_details_number[rn > 1] ORDER BY rn =
			CASE
				WHEN item[cv()] <> item[cv() - 1] THEN slip_details_number[cv() - 1] + 1
				ELSE nvl(slip_details_number[cv() - 1],0) + 1 END--同日に同じ商品を何回も購入するときもincre、partition byの切り替わりのときも1にrefreshしてくれる
			)
)
SELECT cust_id, purchase_date, slip_number, slip_details_number, item, qty ,price FROM for_slip_details_number ORDER BY slip_number, slip_details_number
;

```

【insert_uptakeの取得結果（select句の部分の一部抜粋）】

for_slip_details_numberの処理で指定しているpartition byのslip_numberが切り替わるところで、
case文else句内で、slip_details_numberを1にrefreshしています。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/5357003b-36c2-32bd-0789-8f47e1a82e68.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/69dbdce0-a4c5-f4b2-5964-c6046759e639.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/67533545-c257-3400-8992-ac645f4b50b9.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/abcb68c8-f360-1261-6ffb-22c1567b2f59.png)



今回のデータはトランの商品ごとの値段がランダムなので、一商品につき一意の値段を設定できるpatchも用意しました。（なんとなく）

```sql:patch.sql

MERGE INTO item_mst tar
USING(
WITH src AS (
SELECT row_number() OVER (ORDER BY item) AS rn,item, item_kbn, price, weight, VOLUME FROM item_mst
),rec(
		rn,item, item_kbn, price, weight, VOLUME
		)AS(
		SELECT rn,item, item_kbn, 1000 + rn AS price, weight, VOLUME FROM src WHERE rn = 1
		UNION ALL
		SELECT s2.rn,s2.item, s2.item_kbn, 1000 + s2.rn AS price, s2.weight, s2.VOLUME FROM rec s1,src s2 WHERE s1.rn + 1 = s2.rn
		)
SELECT item, item_kbn, price, weight, VOLUME FROM rec
) src
ON(tar.item = src.item)
WHEN MATCHED THEN UPDATE SET tar.price = src.price
;

COMMIT;

MERGE INTO cume tar
USING item_mst src
ON(tar.item = src.item)
WHEN MATCHED THEN UPDATE SET tar.price = src.price
;

COMMIT;

```

#おまけ
100万件のsales_trnをuptakeに取り込むのにどのくらい時間かかるのか計測してみたいと思います。

```sql:cnt_sales_trn.sql
select count(seq) from sales_trn;
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/e6f0550b-e4e2-9252-be74-eb7049d1ccfb.png)

```sql:insert_uptake.sql

INSERT /*+ APPEND */ INTO uptake--ダイレクトパスインサート
WITH for_slip_number AS (
SELECT
    rn,seq,cust_id, purchase_date, slip_number, slip_details_number, item, qty ,price
FROM
    sales_trn
MODEL
    DIMENSION BY (row_number() OVER (ORDER BY cust_id,purchase_date,seq) AS rn)
    MEASURES(seq,cust_id,purchase_date,1 AS slip_number,1 AS slip_details_number,item,qty,price)
    RULES(
            slip_number[rn > 1] ORDER BY rn =
            CASE
                WHEN cust_id[cv()] <> cust_id[cv() - 1]
                    OR purchase_date[cv()] <> purchase_date[cv() - 1] THEN slip_number[cv() -1] + 1
                ELSE slip_number[cv() - 1] END
            )
)
,for_slip_details_number AS (
SELECT
    rn, seq,cust_id, purchase_date, slip_number, slip_details_number, item, qty ,price
FROM
    for_slip_number
MODEL
    PARTITION BY (slip_number)
    DIMENSION BY (rn)
    MEASURES(seq,cust_id,purchase_date,1 AS slip_details_number,item,qty,price)
    RULES(
            slip_details_number[rn > 1] ORDER BY rn =
            CASE
                WHEN item[cv()] <> item[cv() - 1] THEN slip_details_number[cv() - 1] + 1
                ELSE nvl(slip_details_number[cv() - 1],0) + 1 END--同日に同じ商品を何回も購入するときもincre、partition byの切り替わりのときも1にrefreshしてくれる
            )
)
SELECT cust_id, purchase_date, slip_number, slip_details_number, item, qty ,price FROM for_slip_details_number ORDER BY slip_number, slip_details_number
;

commit;

```

```sql

SQL> alter system checkpoint;

システムが変更されました。

経過: 00:00:00.08
SQL> alter system switch logfile;

システムが変更されました。

経過: 00:00:00.02
SQL> alter system flush shared_pool;

システムが変更されました。

経過: 00:00:00.46
SQL> alter system flush buffer_cache;

システムが変更されました。

経過: 00:00:00.04
SQL> @insert_uptake.sql

1000000行が作成されました。

経過: 00:02:13.16

```

#あとがき
プログラムでなくとも自由度の高いmodel句ならいろいろ表現できそうだなーと可能性を感じました。（javaプログラムはse3年目にもなるのに得意じゃないから、model句が魅力的に見える。。）

ロジックが一箇所にまとまり、ソースコードがスマートになるのではなんてことも思いました。
match_recognize句の記事にも書いてありました。（参考文献を参照。）

表現できることを探っていきます。

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