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

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

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

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

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

``` SQL:ワイルドカードや範囲指定の変換テーブルをかませて分析ラベルを付与する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などを交えながら説明します。

# 事例用の要件
例えばこんな要件です。

:::note info
要件:
注文データの顧客名は支店名が含まれているので、これを集約したい。商品も集約しつつ、サイズも大中小に分類した分析用ラベルを注文データに付与して、分析用の注文情報ファクトテーブルを作りたい。
:::

例えばこんな注文データに自分たちの分析用ラベルを付与します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/76786/8636bb8f-4cd0-d95e-c6a1-30eb822ff458.png)

付与したい分析用ラベルは次の通りです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/76786/ab48435f-0058-be69-b4bd-0afced8328e6.png)

付与する条件は次の通りです。
注文データは次のように集約します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/76786/d648a54f-265e-f189-2cc6-b3ad3fe1e415.png)

商品とサイズは次のように集約します。
- KESHIGOMUで始まる商品名はサイズが1から5は消しゴム小
- KESHIGOMUで始まる商品名はサイズが6から10は消しゴム中
- KESHIGOMUで始まる商品名はサイズが11から20は消しゴム大
- ENPITSUで終わる商品名はサイズが80から150は鉛筆小
- ENPITSUで終わる商品名はサイズが151から220は鉛筆大

これらの要件を次のような分析用ラベル付与変換テーブルで定義します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/76786/4c0db4ee-b3f9-58d8-aaf8-378b6a2ecf80.png)

この表は次のようなフローチャートをテーブルにした表と考えてください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/76786/71eef6b3-f623-0635-f609-d84464081199.png)



結果として、こんな表を作りたいです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/76786/57e28a88-3063-58b8-0120-73a9b6b85a70.png)

# 実装例
## データソース:注文の作成
```SQL
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;
```

## 変換テーブル:ワイルドカードや範囲指定の変換テーブル作成
```SQL
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``にインデックスをつけるのは効果があります。

``` 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;
```