データウェアハウス構築でデータソースにワイルドカードや範囲指定の変換テーブルかませてファクトテーブルを作る

動機

データウエアハウスの構築で、オリジナルのデータソースが持つカラムの値と分析に用いるディメンションテーブルのカラムの値が大きく異なるときがあります。そして、次のようなメンドクサさがあります。

• データソースはコンピュータの処理のしやすさの都合でコード化されていて、人が見たときに易しくない。
• データソースは他所で管理しているので、自分が使いたいコード体系になっていない。
• データソースは他所で管理しているので、予告なくコードが変化する(コードが追加、削除)。

予告なく変化するので、プログラムの中に変換処理を記述するのは、これまたメンドクサイので、データソースのコードから自分たちが使う分析用ラベルを生み出す変換テーブルを設けて、これを元にデータソースに分析用のラベルを付与したりします。

ワイルドカードや範囲指定の変換テーブルかませるSQLの例

変換テーブルは単純な変換表にはならず、ワイルドカードや数値の範囲指定などを用いることが、まぁまぁあるので、それを考慮したSQLの事例紹介です(以下、Oracleデータベースで試してます)。

ワイルドカードや範囲指定の変換テーブルをかませて分析ラベルを付与するSQL

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;