動機
データウエアハウスの構築で、オリジナルのデータソースが持つカラムの値と分析に用いるディメンションテーブルのカラムの値が大きく異なるときがあります。そして、次のようなメンドクサさがあります。
- データソースはコンピュータの処理のしやすさの都合でコード化されていて、人が見たときに易しくない。
- データソースは他所で管理しているので、自分が使いたいコード体系になっていない。
- データソースは他所で管理しているので、予告なくコードが変化する(コードが追加、削除)。
予告なく変化するので、プログラムの中に変換処理を記述するのは、これまたメンドクサイので、データソースのコードから自分たちが使う分析用ラベルを生み出す変換テーブルを設けて、これを元にデータソースに分析用のラベルを付与したりします。
ワイルドカードや範囲指定の変換テーブルかませるSQLの例
変換テーブルは単純な変換表にはならず、ワイルドカードや数値の範囲指定などを用いることが、まぁまぁあるので、それを考慮したSQLの事例紹介です(以下、Oracleデータベースで試してます)。
SELECT
RES.ORDER_ID,
RES.CUST_NM,
RES.ITEM_NM,
RES.SIZE_VAL,
RES.COUNT,
RES.ANALYTICAL_LABEL
FROM
(
SELECT
DS.*,
CT.ANALYTICAL_LABEL,
ROW_NUMBER()
-- 結合した結果から、優先順位が最小のものを選択する
-- ROW_NUMBER() 関数を使って、優先順位の順に行番号を付ける
-- PARTITION BY 句で、注文IDごとに行番号をリセットする
-- ORDER BY 句で、優先順位の昇順に並べる
OVER(PARTITION BY DS.ORDER_ID
ORDER BY
CT.PRIORITY
) AS RN
FROM
T_ORDER DS
-- 変換テーブルとの結合条件は、顧客名と商品名がワイルドカードに一致し、サイズが範囲内にあること
-- INNERにするかOUTERにするかですが、変換テーブルになんでもヒットするレコードがあればINNERでOK
-- なければ注文でヒットしなかったレコードが抽出されない
INNER JOIN T_ADD_DIMENSION CT ON DS.CUST_NM LIKE CT.CUST_NM
AND DS.ITEM_NM LIKE CT.ITEM_NM
AND DS.SIZE_VAL BETWEEN CT.SIZE_FROM AND CT.SIZE_TO
) RES
WHERE
-- WHERE 句で、行番号が1のものだけを抽出する
RES.RN = 1;
と、いきなり結果SQLを見ても何のことやらなので、以下に簡単な要件と実際にテーブルを作るDDLなどを交えながら説明します。
事例用の要件
例えばこんな要件です。
要件:
注文データの顧客名は支店名が含まれているので、これを集約したい。商品も集約しつつ、サイズも大中小に分類した分析用ラベルを注文データに付与して、分析用の注文情報ファクトテーブルを作りたい。
例えばこんな注文データに自分たちの分析用ラベルを付与します。
付与する条件は次の通りです。
注文データは次のように集約します。
商品とサイズは次のように集約します。
- KESHIGOMUで始まる商品名はサイズが1から5は消しゴム小
- KESHIGOMUで始まる商品名はサイズが6から10は消しゴム中
- KESHIGOMUで始まる商品名はサイズが11から20は消しゴム大
- ENPITSUで終わる商品名はサイズが80から150は鉛筆小
- ENPITSUで終わる商品名はサイズが151から220は鉛筆大
これらの要件を次のような分析用ラベル付与変換テーブルで定義します。
この表は次のようなフローチャートをテーブルにした表と考えてください。
実装例
データソース:注文の作成
DROP TABLE T_ORDER;
CREATE TABLE T_ORDER (
ORDER_ID NUMBER(6, 0)
NOT NULL ENABLE,
CUST_NM VARCHAR2(20 BYTE),
ITEM_NM VARCHAR2(20 BYTE),
SIZE_VAL NUMBER(5, 0),
COUNT NUMBER(5, 0),
CONSTRAINT T_ORDER_PK PRIMARY KEY ( ORDER_ID )
USING INDEX
);
DROP INDEX T_ORDER_INDEX1;
CREATE INDEX T_ORDER_INDEX1 ON
T_ORDER (
SIZE_VAL
);
TRUNCATE TABLE T_ORDER;
Insert into T_ORDER (ORDER_ID,CUST_NM,ITEM_NM,SIZE_VAL,COUNT) values (1,'AAHONSHA','KESHIGOMU-PINK',3,100);
Insert into T_ORDER (ORDER_ID,CUST_NM,ITEM_NM,SIZE_VAL,COUNT) values (2,'AATOUHOKU','KESHIGOMU-PINK',6,200);
Insert into T_ORDER (ORDER_ID,CUST_NM,ITEM_NM,SIZE_VAL,COUNT) values (3,'BBBKYUSHU','KESHIGOMU-PINK',12,300);
Insert into T_ORDER (ORDER_ID,CUST_NM,ITEM_NM,SIZE_VAL,COUNT) values (4,'AATOUHOKU','KESHIGOMU-BLUE',50,200);
Insert into T_ORDER (ORDER_ID,CUST_NM,ITEM_NM,SIZE_VAL,COUNT) values (5,'AATOUHOKU','2B-ENPITSU',200,300);
Insert into T_ORDER (ORDER_ID,CUST_NM,ITEM_NM,SIZE_VAL,COUNT) values (6,'BBBKANSAI','2B-ENPITSU',100,200);
Insert into T_ORDER (ORDER_ID,CUST_NM,ITEM_NM,SIZE_VAL,COUNT) values (7,'AAHONSHA','HB-ENPITSU',200,200);
COMMIT;
変換テーブル:ワイルドカードや範囲指定の変換テーブル作成
DROP TABLE T_ADD_DIMENSION;
CREATE TABLE T_ADD_DIMENSION (
PRIORITY NUMBER(5, 0) NOT NULL,
CUST_NM VARCHAR2(20 BYTE),
ITEM_NM VARCHAR2(20 BYTE),
SIZE_FROM NUMBER(5, 0),
SIZE_TO NUMBER(5, 0),
ANALYTICAL_LABEL VARCHAR2(100 BYTE),
CONSTRAINT T_ADD_DIMENSION_PK PRIMARY KEY ( PRIORITY )
USING INDEX
);
TRUNCATE TABLE T_ADD_DIMENSION;
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (1,'AA%','KESHIGOMU%',1,5,'あいう会社_消しゴム小');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (2,'AA%','KESHIGOMU%',6,10,'あいう会社_消しゴム中');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (3,'AA%','KESHIGOMU%',11,20,'あいう会社_消しゴム大');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (4,'AA%','%ENPITSU',80,150,'あいう会社_鉛筆小');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (5,'AA%','%ENPITSU',151,220,'あいう会社_鉛筆大');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (6,'BBB%','KESHIGOMU%',1,5,'かきく会社_消しゴム小');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (7,'BBB%','KESHIGOMU%',6,10,'かきく会社_消しゴム中');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (8,'BBB%','KESHIGOMU%',11,20,'かきく会社_消しゴム大');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (9,'BBB%','%ENPITSU',80,150,'かきく会社_鉛筆小');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (10,'BBB%','%ENPITSU',151,220,'かきく会社_鉛筆大');
Insert into T_ADD_DIMENSION (PRIORITY,CUST_NM,ITEM_NM,SIZE_FROM,SIZE_TO,ANALYTICAL_LABEL) values (99999,'%','%',0,99999,'変換できませんでした');
COMMIT;
再掲:ワイルドカードや範囲指定の変換テーブルをかませて分析ラベルを付与するSQL
ポイントは、変換テーブルをLIKEやBETWEENで結合するところです。
- 変換テーブルの
CUST_NM
やITEM_NM
には、それぞれAA%
やKESHIGOMU%
のようにワイルドカードがありますので、LIKE
演算子を使います。 - 数値の範囲指定はBETWEEN演算子で範囲を絞り込んでます。
- INNER JOIN ON句でLIKEやBETWEENで絞り込んでも、一つの注文IDに複数の変換テーブルのレコードがヒットします(変換テーブルの最後の行にワイルドカードだけのレコードがあるので)。このため、ROW_NUMBER関数を使って行番号を振り、外側のWHERE句で行番号が1のもの、つまり優先順位の高いレコードに絞り込みます。
ちょっと、このSQLが残念なのは、LIKE演算子の右辺がカラムなので、左辺の項目にインデックスを付与していても効果がないことです。DS.CUST_NM LIKE 'AA%'
のように右辺が末尾ワイルドカードの定数だとオプティマイザがSQLを解析する際にインデックスを使ってくれるのですが。。。BETWEEN演算子はインデックスを使ってくれるので、注文のSIZE_VAL
にインデックスをつけるのは効果があります。
SELECT
RES.ORDER_ID,
RES.CUST_NM,
RES.ITEM_NM,
RES.SIZE_VAL,
RES.COUNT,
RES.ANALYTICAL_LABEL
FROM
(
SELECT
DS.*,
CT.ANALYTICAL_LABEL,
ROW_NUMBER()
-- 結合した結果から、優先順位が最小のものを選択する
-- ROW_NUMBER() 関数を使って、優先順位の順に行番号を付ける
-- PARTITION BY 句で、注文IDごとに行番号をリセットする
-- ORDER BY 句で、優先順位の昇順に並べる
OVER(PARTITION BY DS.ORDER_ID
ORDER BY
CT.PRIORITY
) AS RN
FROM
T_ORDER DS
-- 変換テーブルとの結合条件は、顧客名と商品名がワイルドカードに一致し、サイズが範囲内にあること
-- INNERにするかOUTERにするかですが、変換テーブルになんでもヒットするレコードがあればINNERでOK
-- なければ注文でヒットしなかったレコードが抽出されない
INNER JOIN T_ADD_DIMENSION CT ON DS.CUST_NM LIKE CT.CUST_NM
AND DS.ITEM_NM LIKE CT.ITEM_NM
AND DS.SIZE_VAL BETWEEN CT.SIZE_FROM AND CT.SIZE_TO
) RES
WHERE
-- WHERE 句で、行番号が1のものだけを抽出する
RES.RN = 1;