sql oracle 優先順位を表現する

#まえがき

マスタ等で保持しているデータに優先順位を付番し、キーに紐付くマスタデータが存在すれば、そのマスタ値を適用し、存在しなければ、デフォルトのマスタ値を適用するような仕組みをSQLで表現してみました。表現できているはず。。


#参考文献
>[sql oracle recursive lesson@create_cumming_inc_item.sql]
(https://qiita.com/ukijumotahaneniarukenia/items/b69e7af6928f6fabf4fc)


#練習仕様
商品が属しているゾーン・エリア・ブロックごとに温度と湿度を管理しているテーブルがあります。
このテーブルに対象商品のゾーン・エリア・ブロックごとの設定値が登録されている場合、その値を優先し、登録されていない場合は、デフォルト値を適用します。

優先順位の仕様は以下のとおり。

①zone単位で登録されているかいないか

②area単位に登録されているかいないか

③block単位に登録されているかいないか

以上の分岐網羅パターンのうち、最初に当てはまったパターンの値を優先させる。

#サンプルデータ

デフォルト値は*を含むレコードで表現しています。

```sql:init.sql

DROP TABLE temp_humid_by_item_mst;

CREATE TABLE temp_humid_by_item_mst (
	item         CHAR(6) NOT NULL
	, ZONE          CHAR(2) NOT NULL
	, area          CHAR(3) NOT NULL
	, BLOCK         CHAR(4) NOT NULL
	, temperature   NUMBER(2, 0) NOT NULL
	, humidity      NUMBER(2, 0) NOT NULL
	, PRIMARY KEY (
	item
	,ZONE
	, area
	, BLOCK )
);

INSERT INTO temp_humid_by_item_mst
WITH src AS (
SELECT
    CASE WHEN LEVEL <= 4 THEN '**' ELSE '01' END AS ZONE
    ,CASE WHEN LEVEL IN (1,2,5,6) THEN '***' ELSE '001' END AS area
    ,CASE WHEN LEVEL IN (1,3,5,7) THEN '****' ELSE '0001' END AS BLOCK
    ,TRUNC(ABS(dbms_random.VALUE(9,35)),0) AS temperature
    ,TRUNC(ABS(dbms_random.VALUE(20,68)),0) AS humidity
FROM
    dual
CONNECT BY
    LEVEL <= 8
)
SELECT s2.item,s1.* FROM src s1 CROSS JOIN (SELECT DISTINCT item FROM cumming) s2
;
COMMIT;


```

#サンプルデータ確認

```sql:conf_data.sql

SELECT
	CASE
		WHEN s1.ZONE = '**' AND s1.area = '***' AND s1.BLOCK = '****' THEN 8
		WHEN s1.ZONE = '**' AND s1.area = '***' AND s1.BLOCK <> '****' THEN 7
		WHEN s1.ZONE = '**' AND s1.area <> '***' AND s1.BLOCK = '****' THEN 6
		WHEN s1.ZONE = '**' AND s1.area <> '***' AND s1.BLOCK <> '****' THEN 5
		WHEN s1.ZONE <> '**' AND s1.area = '***' AND s1.BLOCK = '****' THEN 4
		WHEN s1.ZONE <> '**' AND s1.area = '***' AND s1.BLOCK <> '****' THEN 3
		WHEN s1.ZONE <> '**' AND s1.area <> '***' AND s1.BLOCK = '****' THEN 2
		WHEN s1.ZONE <> '**' AND s1.area <> '***' AND s1.BLOCK <> '****' THEN 1
	END AS classifiy
	,s1.*
FROM
	temp_humid_by_item_mst s1
WHERE
	s1.item = 'p_pS2H'
ORDER BY
	s1.ZONE
	,s1.area
	,s1.BLOCK
;

```

【conf_data.sqlの取得結果】

商品を絞って確認しています。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/a6c80a4a-38c5-9380-feb0-f76a0451ee3a.png)

次にitemごとのzone,area,blockを管理しているテーブルを作成してみます。
このテーブルからトランcummingのitemに対するzone,area,blockを知ることができます。

```sql:create_item_by_zone_area_block_mst.sql

DROP TABLE item_by_zone_area_block_mst;

CREATE TABLE item_by_zone_area_block_mst (item ,ZONE,area,BLOCK,PRIMARY KEY(item))AS
WITH src_zone AS (
SELECT lpad(LEVEL,2,0) AS ZONE FROM dual CONNECT BY LEVEL <= 2
),src_area AS(
SELECT lpad(LEVEL,3,0) AS area FROM dual CONNECT BY LEVEL <= 2
),src_block AS(
SELECT lpad(LEVEL,4,0) AS BLOCK FROM dual CONNECT BY LEVEL <= 2
),src_item AS(
SELECT ROWNUM AS rn,s1.* FROM (SELECT DISTINCT item FROM cumming) s1
),cmb AS (
SELECT ROWNUM AS rn ,COUNT(*) OVER () AS cnt,s1.ZONE, s2.area, s3.BLOCK FROM src_zone s1,src_area s2, src_block s3
)
,grp AS (
SELECT row_number() OVER (PARTITION BY grp ORDER BY rn) AS rnnn,rn,item,grp FROM( SELECT tmp.*,SUM(tmp.flg) OVER (PARTITION BY tmp.ZONE, tmp.area, tmp.BLOCK ORDER BY tmp.rnn,tmp.rn) AS grp FROM (SELECT s1.rn, s1.item, s2.rn AS rnn,s2.ZONE, s2.area, s2.BLOCK,CASE WHEN EXISTS(SELECT 1 FROM cmb s2 WHERE MOD(s1.rn,s2.cnt) = 1) THEN 1 ELSE 0 END AS flg FROM src_item s1,cmb s2)tmp ) GROUP BY rn,item,grp
)
SELECT item,ZONE,area,BLOCK FROM grp s1 INNER JOIN cmb s2 ON s1.rnnn = s2.rn
;

```

```sql:conf_item_attribute.sql

SELECT * FROM item_by_zone_area_block_mst;

```

【conf_item_attribute.sqlの取得結果（一部抜粋）】
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/e776b6a2-a8d6-e4cc-9a67-bff57ff15499.png)



#優先順位の確認

商品がどの温度、湿度で管理されているか確認します。

```sql:chk_priority.sql

WITH sub AS (
SELECT
	s1.item
	,s2.ZONE
	,s2.area
	,s2.BLOCK
	,CASE
		WHEN
			EXISTS(
					SELECT
						1
					FROM
						temp_humid_by_item_mst s3
					WHERE
						s1.item = s3.item
					AND s3.ZONE <> '**'
					AND s3.area <> '***'
					AND s3.BLOCK <> '****'
					AND s2.ZONE = s3.ZONE
					AND s2.area = s3.area
					AND s2.BLOCK = s3.BLOCK
					) THEN 1
		ELSE 0 END AS priority_1
	,CASE
		WHEN
			EXISTS(
					SELECT
						1
					FROM
						temp_humid_by_item_mst s3
					WHERE
						s1.item = s3.item
					AND s3.ZONE <> '**'
					AND s3.area <> '***'
					AND s3.BLOCK = '****'
					AND s2.ZONE = s3.ZONE
					AND s2.area = s3.area
--					and s2.BLOCK = s3.BLOCK
					) THEN 1
		ELSE 0 END AS priority_2
	,CASE
		WHEN
			EXISTS(
					SELECT
						1
					FROM
						temp_humid_by_item_mst s3
					WHERE
						s1.item = s3.item
					AND s3.ZONE <> '**'
					AND s3.area = '***'
					AND s3.BLOCK <> '****'
					AND s2.ZONE = s3.ZONE
--					and s2.AREA = s3.AREA
					AND s2.BLOCK = s3.BLOCK
					) THEN 1
		ELSE 0 END AS priority_3
	,CASE
		WHEN
			EXISTS(
					SELECT
						1
					FROM
						temp_humid_by_item_mst s3
					WHERE
						s1.item = s3.item
					AND s3.ZONE <> '**'
					AND s3.area = '***'
					AND s3.BLOCK = '****'
					AND s2.ZONE = s3.ZONE
--					and s2.AREA = s3.AREA
--					and s2.BLOCK = s3.BLOCK
					) THEN 1
		ELSE 0 END AS priority_4
	,CASE
		WHEN
			EXISTS(
					SELECT
						1
					FROM
						temp_humid_by_item_mst s3
					WHERE
						s1.item = s3.item
					AND s3.ZONE = '**'
					AND s3.area <> '***'
					AND s3.BLOCK <> '****'
--					and s2.ZONE = s3.ZONE
					AND s2.area = s3.area
					AND s2.BLOCK = s3.BLOCK
					) THEN 1
		ELSE 0 END AS priority_5
	,CASE
		WHEN
			EXISTS(
					SELECT
						1
					FROM
						temp_humid_by_item_mst s3
					WHERE
						s1.item = s3.item
					AND s3.ZONE = '**'
					AND s3.area <> '***'
					AND s3.BLOCK = '****'
--					and s2.ZONE = s3.ZONE
					AND s2.area = s3.area
--					and s2.BLOCK = s3.BLOCK
					) THEN 1
		ELSE 0 END AS priority_6
	,CASE
		WHEN
			EXISTS(
					SELECT
						1
					FROM
						temp_humid_by_item_mst s3
					WHERE
						s1.item = s3.item
					AND s3.ZONE = '**'
					AND s3.area = '***'
					AND s3.BLOCK <> '****'
--					and s2.ZONE = s3.ZONE
--					and s2.AREA = s3.AREA
					AND s2.BLOCK = s3.BLOCK
					) THEN 1
		ELSE 0 END AS priority_7
	,CASE
		WHEN
			EXISTS(
					SELECT
						1
					FROM
						temp_humid_by_item_mst s3
					WHERE
						s1.item = s3.item
					AND s3.ZONE <> '**'
					AND s3.area <> '***'
					AND s3.BLOCK <> '****'
--					and s2.ZONE = s3.ZONE
--					and s2.AREA = s3.AREA
--					and s2.BLOCK = s3.BLOCK
					) THEN 1
		ELSE 0 END AS priority_8
FROM
	cumming s1
		INNER JOIN item_by_zone_area_block_mst s2
			ON
				s1.item = s2.item
)
SELECT
	s2.classifiy
	,s1.item
	,s1.ZONE
	,s1.area
	,s1.BLOCK
	,s2.ZONE AS apply_zone
	,s2.area AS apply_area
	,s2.BLOCK AS apply_block
	,s2.temperature
	,s2.humidity
FROM
	(
	SELECT item, ZONE, area, BLOCK, to_number(substr(MIN(vals),-1)) AS PRIORITY FROM sub UNPIVOT(cols FOR vals IN (priority_1, priority_2, priority_3, priority_4, priority_5, priority_6, priority_7, priority_8)) WHERE cols = 1 GROUP BY item, ZONE, area, BLOCK, cols
	) s1
	INNER JOIN (SELECT CASE WHEN s1.ZONE = '**' AND s1.area = '***' AND s1.BLOCK = '****' THEN 8 WHEN s1.ZONE = '**' AND s1.area = '***' AND s1.BLOCK <> '****' THEN 7 WHEN s1.ZONE = '**' AND s1.area <> '***' AND s1.BLOCK = '****' THEN 6 WHEN s1.ZONE = '**' AND s1.area <> '***' AND s1.BLOCK <> '****' THEN 5 WHEN s1.ZONE <> '**' AND s1.area = '***' AND s1.BLOCK = '****' THEN 4 WHEN s1.ZONE <> '**' AND s1.area = '***' AND s1.BLOCK <> '****' THEN 3 WHEN s1.ZONE <> '**' AND s1.area <> '***' AND s1.BLOCK = '****' THEN 2 WHEN s1.ZONE <> '**' AND s1.area <> '***' AND s1.BLOCK <> '****' THEN 1 END AS classifiy ,s1.* FROM temp_humid_by_item_mst s1 ) s2
		ON
			s1.item = s2.item
		AND s1.PRIORITY = s2.classifiy
ORDER BY
	s2.classifiy
;

```

【chk_priority.sqlの取得結果】

いけてそうだなー（感覚）

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/3355425e-4bb7-4246-ed34-2172bf656875.png)

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/6ecf42b3-20ce-4dfc-eb8a-39f9eaeb51a8.png)


#パッチ適用後の優先順位の確認

マスタ内容によって優先順位の変化を確認できるようにpatch.sqlをあててみます。

```sql:patch.sql

insert into temp_humid_by_item_mst select ITEM, ZONE, AREA, '0002' as BLOCK, TEMPERATURE, HUMIDITY from temp_humid_by_item_mst where item in ('p_QNV8','p_UVPO','p_p_1Y','p_4LLB','p_pA19') and zone = '01' and area = '001' and block <> '****';
delete from temp_humid_by_item_mst where item in ('p_QNV8','p_UVPO','p_p_1Y','p_4LLB','p_pA19') and zone = '01' and area = '001' and block = '0001';
commit;

delete from temp_humid_by_item_mst where item in ('p_HV01','p_pHS1','p_GEJ7','p_QZ2L') and zone = '**' and area = '***' and block = '0001';
commit;

update temp_humid_by_item_mst set area = '002' where item in ('p_p_1D','p_L0MO','p_pC4J','p_p9JJ') and zone = '**' and area = '001' and block = '****';
commit;


```

【patch.sql適用後のchk_priority.sqlの取得結果】

パターン2と6と7が消え、1と8が増えた!

![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/b1855994-9ddd-50a8-d5d2-23d843c77f40.png)
![image.png](https://qiita-image-store.s3.amazonaws.com/0/286037/b5079dab-11f8-a43b-6b7f-82ae76ca4e0f.png)


#あとがき

テーブルのデータ構成とも相まってexistsを使えば、優先順位も表現できるんだなーと勉強になりました。
いちいち登録がめんどくさいみたいな状況のときに、こういう仕組みがあると、楽できるのではないかとおもいました。

こういう表現もあるよ！みたいなのがもしあれば、お待ちしております。

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

#20181010追記

@youmil_rainさんが別の書き方をご教示してくださったので、追記したいと思います。
全部で3つあります。@youmil_rainさん、ありがとうございます。

①coalesceを使った方法
②not existsを使った方法
③window functionを使った方法

##①coalesceを使った方法
対象の商品マスタに対して設定マスタの優先順位ごとの集合を順に結合します。
対象の商品マスタに紐づいたものからselect句で優先順位の高い順にreturnしていく方法です。
coalesceの引数は優先順位の高い列から書き始めます。


```sql:coalensce.sql

SELECT
	it.*
	, coalesce(t1.zone, t2.zone, t3.zone, t4.zone, t5.zone, t6.zone, t7.zone, t8.zone) AS zone
	, coalesce(t1.area, t2.area, t3.area, t4.area, t5.area, t6.area, t7.area, t8.area) AS area
	, coalesce(t1.block, t2.block, t3.block, t4.block, t5.block, t6.block, t7.block, t8.block) AS block
	, coalesce(t1.temperature, t2.temperature, t3.temperature, t4.temperature, t5.temperature, t6.temperature, t7.temperature, t8.temperature) AS temperature
	, coalesce(t1.humidity, t2.humidity, t3.humidity, t4.humidity, t5.humidity, t6.humidity, t7.humidity, t8.humidity) AS humidity
FROM
	item_by_zone_area_block_mst it--商品マスタ
	LEFT JOIN temp_humid_by_item_mst t1--設定マスタ優先順位１
		ON t1.item = it.item
			AND t1.zone = it.zone
			AND t1.area = it.area
			AND t1.block = it.block
	LEFT JOIN temp_humid_by_item_mst t2--設定マスタ優先順位２
		ON t2.item = it.item
			AND t2.zone = it.zone
			AND t2.area = it.area
			AND t2.block = '****'
	LEFT JOIN temp_humid_by_item_mst t3--設定マスタ優先順位３
		ON t3.item = it.item
			AND t3.zone = it.zone
			AND t3.area = '***'
			AND t3.block = it.block
	LEFT JOIN temp_humid_by_item_mst t4--設定マスタ優先順位４
		ON t4.item = it.item
			AND t4.zone = it.zone
			AND t4.area = '***'
			AND t4.block = '****'
	LEFT JOIN temp_humid_by_item_mst t5--設定マスタ優先順位５
		ON t5.item = it.item
			AND t5.zone = '**'
			AND t5.area = it.area
			AND t5.block = it.block
	LEFT JOIN temp_humid_by_item_mst t6--設定マスタ優先順位６
		ON t6.item = it.item
			AND t6.zone = '**'
			AND t6.area = it.area
			AND t6.block = '****'
	LEFT JOIN temp_humid_by_item_mst t7--設定マスタ優先順位７
		ON t7.item = it.item
			AND t7.zone = '**'
			AND t7.area = '***'
			AND t7.block = it.block
	LEFT JOIN temp_humid_by_item_mst t8--設定マスタ優先順位８
		ON t8.item = it.item
			AND t8.zone = '**'
			AND t8.area = '***'
			AND t8.block = '****'
;

```

##②not existsを使った方法

対象の商品マスタと設定マスタを共通レコードまで全部残すように結合します。
同じ商品のなかで、zone,area,blockごとの設定値を反映したpriority列をcase式で一時的に作成しています。作成したpriority列の値を比べ、対象itemに紐付く設定マスタのpriority値が最小のものを取得しています。（priority列の値が小さいほど優先順位を高く評価している）商品ごとにグルグルループしているイメージ。最小見つかったらreturn。

全部アスタリスクだったら4+2+1=7で7>7⇔not false⇔trueとなるので、個別設定なくてもデフォルトの設定内容が取得できるようになっています。（自身が最小で*以外の設定が存在しないケース）

```sql:not_exists.sql

SELECT
	*
FROM
	item_by_zone_area_block_mst it
		INNER JOIN temp_humid_by_item_mst t1--共通レコードまで全部残すように内部結合
			ON t1.item = it.item
		AND t1.zone in (it.zone,'**')
		AND t1.area in (it.area,'***')
		AND t1.block in (it.block,'****')
		AND NOT EXISTS (
						SELECT
							1
						FROM
							temp_humid_by_item_mst t2
						WHERE
							t2.item = it.item--同一itemなかで
						AND t2.zone in (it.zone,'**')
						AND t2.area in (it.area,'***')
						AND t2.block in (it.block,'****')
						AND--自身のpriority列よりも小さいpriorityが存在しない場合（priority列の値が小さいほど優先順位を高く評価している）
							--いいかえれば、自身よりも優先順位が高いものが存在しない場合
								--３つの加算値をひとつの列に見立てている(仮にpriority列とする)
								case t2.zone when '**' then 4 else 0 end
							  + case t2.area when '***' then 2 else 0 end
							  + case t2.block when '****' then 1 else 0 end
							  <
								--３つの加算値をひとつの列に見立てている(仮にpriority列とする)
								case t1.zone when '**' then 4 else 0 end
							  + case t1.area when '***' then 2 else 0 end
							  + case t1.block when '****' then 1 else 0 end
						)
;


```

##③window functionを使った方法

最初は見たとき、やりたいことがすっとあたまに入ってくる表現でした。
P=1をコメントインしたり、コメントアウトしていじってみるとよくわかります。
order by caseの使い方が実現したい表現にすごくマッチしているイメージです。

```sql:window_func.sql

WITH temp AS (
SELECT
	it.item
     , it.ZONE
     , it.area
     , it.BLOCK
     , t1.ZONE AS apply_zone
     , t1.area AS apply_area
     , t1.BLOCK AS apply_block
     , t1.temperature
     , t1.humidity
     , row_number() OVER (PARTITION BY it.item ORDER BY CASE t1.ZONE WHEN '**' THEN 1 ELSE 0 END
                                                      , CASE t1.area WHEN '***' THEN 1 ELSE 0 END
                                                      , CASE t1.BLOCK WHEN '****' THEN 1 ELSE 0 END) P
FROM
	item_by_zone_area_block_mst it
		INNER JOIN temp_humid_by_item_mst t1
			ON
				t1.item = it.item
			AND t1.ZONE IN (it.ZONE,'**')
			AND t1.area IN (it.area,'***')
			AND t1.BLOCK IN (it.BLOCK,'****')
)
SELECT
	*
FROM
	temp
WHERE
	P = 1
;


```