まえがき
マスタ等で保持しているデータに優先順位を付番し、キーに紐付くマスタデータが存在すれば、そのマスタ値を適用し、存在しなければ、デフォルトのマスタ値を適用するような仕組みをSQLで表現してみました。表現できているはず。。
参考文献
練習仕様
商品が属しているゾーン・エリア・ブロックごとに温度と湿度を管理しているテーブルがあります。
このテーブルに対象商品のゾーン・エリア・ブロックごとの設定値が登録されている場合、その値を優先し、登録されていない場合は、デフォルト値を適用します。
優先順位の仕様は以下のとおり。
①zone単位で登録されているかいないか
②area単位に登録されているかいないか
③block単位に登録されているかいないか
以上の分岐網羅パターンのうち、最初に当てはまったパターンの値を優先させる。
サンプルデータ
デフォルト値は*を含むレコードで表現しています。
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;
サンプルデータ確認
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の取得結果】
商品を絞って確認しています。
次にitemごとのzone,area,blockを管理しているテーブルを作成してみます。
このテーブルからトランcummingのitemに対するzone,area,blockを知ることができます。
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
;
conf_item_attribute.sql
SELECT * FROM item_by_zone_area_block_mst;
【conf_item_attribute.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の取得結果】
いけてそうだなー（感覚）
パッチ適用後の優先順位の確認
マスタ内容によって優先順位の変化を確認できるようにpatch.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が増えた!
あとがき
テーブルのデータ構成とも相まってexistsを使えば、優先順位も表現できるんだなーと勉強になりました。
いちいち登録がめんどくさいみたいな状況のときに、こういう仕組みがあると、楽できるのではないかとおもいました。
こういう表現もあるよ！みたいなのがもしあれば、お待ちしております。
以上、ありがとうございました。
20181010追記
@youmil_rainさんが別の書き方をご教示してくださったので、追記したいと思います。
全部で3つあります。@youmil_rainさん、ありがとうございます。
①coalesceを使った方法
②not existsを使った方法
③window functionを使った方法
①coalesceを使った方法
対象の商品マスタに対して設定マスタの優先順位ごとの集合を順に結合します。
対象の商品マスタに紐づいたものからselect句で優先順位の高い順にreturnしていく方法です。
coalesceの引数は優先順位の高い列から書き始めます。
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となるので、個別設定なくてもデフォルトの設定内容が取得できるようになっています。（自身が最小で*以外の設定が存在しないケース）
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の使い方が実現したい表現にすごくマッチしているイメージです。
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
;