0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

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

Last updated at Posted at 2024-01-27

動機

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

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

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

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

事例用の要件

例えばこんな要件です。

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

例えばこんな注文データに自分たちの分析用ラベルを付与します。
image.png

付与したい分析用ラベルは次の通りです。
image.png

付与する条件は次の通りです。
注文データは次のように集約します。
image.png

商品とサイズは次のように集約します。

  • KESHIGOMUで始まる商品名はサイズが1から5は消しゴム小
  • KESHIGOMUで始まる商品名はサイズが6から10は消しゴム中
  • KESHIGOMUで始まる商品名はサイズが11から20は消しゴム大
  • ENPITSUで終わる商品名はサイズが80から150は鉛筆小
  • ENPITSUで終わる商品名はサイズが151から220は鉛筆大

これらの要件を次のような分析用ラベル付与変換テーブルで定義します。
image.png

この表は次のようなフローチャートをテーブルにした表と考えてください。
image.png

結果として、こんな表を作りたいです。
image.png

実装例

データソース:注文の作成

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_NMITEM_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;
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?