【Sisense Data Modeling】SQLでカスタムテーブルを作成

シャローム！Sato-Gです。
通販業界というのはマーケティングの宝庫だ。特に最近のECの多くは単品販売から始まり、アップセル、クロスセルできる商品を拡充していく過程をたどる。
その過程で様々な施策を考えるのに色んな手法を使うことになる。元々マンパワーを使わないのが通販だから、分析力が要求される業界と言える。
それができずに、その中で挫折してしまう企業も多い。
では通販業界の売上って、どの時点で立つのか知ってる？

一般に商品の売上は「出荷基準」か「検収基準」で行われる。
出荷基準とは商品を出荷した時点で売上計上するのに対し、検収基準は顧客に商品が納品されたことを確認して売上とする。
このどちらを選択するかは一旦決めたら変更することはできない。変更できたら利益を操作できちゃうからね。今期利益が出過ぎそうだから検収売上にしちゃうとか...
まあ、そんなことしたら経理がかわいそうでもあるんだけど。
おそらく通販業界では出荷基準が一般的だと思う。

これまで僕は通販業の分析アプリをかなり作ってきたけど、通常は注文があった時点（つまり受注）での分析を行っている。
これは顧客ベースでのマーケティングを行うためだ。出荷は顧客基準じゃないからね。
企業会計の立場からするとこういうグラフがほしいという要求は当然で出てくる。

このグラフは
【Sisense Data Modeling】データベースの準備～Generic JDBC Connectorで接続
【Sisense Data Modeling】CreateDate関数で日付項目のハンドリング
【Sisense Data Modeling】複数のキーをカラム連結でまとめる
という3つのコラムで作成してきたデータモデルでは作成できないよね。
これを実現するデータモデルを考えてみる。

1.カレンダーテーブルを作成して対応してみる

【カレンダー】
SELECT DISTINCT [注文日] AS 年月日　FROM 注文ヘッダ
UNION
SELECT DISTINCT [配送日] AS 年月日 FROM 配送

上記のカレンダーテーブルを作成してこんなデータモデルにしたとする。

このデータモデルでは年月日と注文日、配送日で結び付けられているから見た目は行けそう。
しかし、実際にウィジェットを作成しようとすると
・X-軸　年月日
・値　　Sum(明細金額)
となり、想定した結果は得られそうにない。
何か方法はありそうだけど、そもそも、注文明細に商品や会員のデータが結びつけられている以上、とても複雑になりそうだ。
このモデルでの解決法はもはや考えるのは困難だし、数式を書くのも嫌になる。

カスタムテーブル作成

2.1 データの準備

【Sisense Data Modeling】データベースの準備～Generic JDBC Connectorで接続の手順で、すべてのテーブルをインポートしておこう。
そして、注文ヘッダ、注文明細、配送の３つのテーブル以外は以下のようにリレーッションシップを設定しておく。

2.2 SQL作成

カスタムテーブルを作成する。[カスタム]ボタンを押すと、SQLエディタが表示されるので、そこで以下のSQLを作成する。
テーブル名は「明細」としておく。
ここはコピペでOK

SELECT [配送年月日] AS 年月日,
     '配送' AS 区分,
     [会員番号],
     [注文番号],
     [明細番号],
     [商品コード],
     0 AS 注文数量,
     0 AS 注文金額,
     [数量] AS 配送数量,
     [明細金額] AS 配送金額
FROM (SELECT *
      FROM "配送" d
      LEFT JOIN "注文ヘッダ" o ON d.[注文番号] = o.[注文番号]
      LEFT JOIN "注文明細" od
             ON d.[注文番号] = od.[注文番号]
            AND d.[明細番号] = od.[明細番号]) a
UNION ALL
SELECT [注文年月日] AS 年月日,
       '注文' AS 区分,
       [会員番号],
       [注文番号],
       [明細番号],
       [商品コード],
       [数量] AS 注文数量,
       [明細金額] AS 注文金額,
       0 AS 配送数量,
       0 AS 配送金額
FROM (SELECT *
      FROM "注文ヘッダ" o
        LEFT JOIN "注文明細" od ON o.[注文番号] = od.[注文番号]) b

ちょっと解説する。

ここは、配送テーブルには注文番号はあるが、会員番号がないので、配送テーブルに会員番号をセットするために、注文ヘッダからLEFT JOIINする。

SELECT *
FROM "配送" d
LEFT JOIN "注文ヘッダ" o ON d.[注文番号] = o.[注文番号]

上記でJOINされた配送テーブルに注文番号と明細番号をキーにして、商品コード、数量、明細金額をセットする（注文明細をLEFT JOIN）。

LEFT JOIN "注文明細" od
ON d.[注文番号] = od.[注文番号]
AND d.[明細番号] = od.[明細番号]) a

配送テーブルには注文ヘッダと注文明細がJOINされているので、必要なカラムを取り出す。
その時の注意点は２つある
・データが「配送」なのか「注文」なのかを区別するため「区分」をつけておく
・数式をシンプルにするため、注文と配送の数量、金額のカラムは別々に作成しておく

SELECT  [配送年月日] AS 年月日,
	'配送' AS 区分,
	[会員番号],
	[注文番号],
	[明細番号],
	[商品コード],
	0 AS 注文数量,
	0 AS 注文金額,
	[数量] AS 配送数量,
	[明細金額] AS 配送金額
FROM ...

上記で配送だけを含むテーブルができたので、以下の部分で注文関連をマージする。
どこかで説明はするけど、UNION ALLは重複を許してデータを下から追加する時に使用する。
（ただのUNIONは重複排除）

UNION ALL
SELECT ...

注文ヘッダに注文明細をマージし、必要なカラムを取り出す。
こちらは配送データではないので、配送数量、配送金額は0としておく。

SELECT [注文年月日] AS 年月日,
     '注文' AS 区分,
     [会員番号],
     [注文番号],
     [明細番号],
     [商品コード],
     [数量] AS 注文数量,
     [明細金額] AS 注文金額,
     0 AS 配送数量,
     0 AS 配送金額
FROM (SELECT *
      FROM "注文ヘッダ" o
      LEFT JOIN "注文明細" od ON o.[注文番号] = od.[注文番号]) b

2.3 Sisenseの関数を追加してみる

上記のSQLでは、年月日はyyyymmdd形式になっている。これではDate型にならないよね。
SQL ServerみたいにCONVERTとかFORMATなんか使えないのかと思って試してみたけど、SisenseのSQLはデータベース固有の関数は使えないっぽい。
至って標準的なものと考えたほうが良さそうだ。
じゃあ、Sisenseの関数を使っちゃえばOK？こんな感じで...

CreateDate(
ToInt(Substring(ToString([配送年月日]),1,4)),
ToInt(Substring(ToString([配送年月日]),5,2)),
ToInt(Substring(ToString([配送年月日]),7,2))
) AS 年月日,

これ正解。実はこれで通る。
注文年月日も同じように書き換えると全体はこうなる。

SELECT  CreateDate(
        ToInt(Substring(ToString([配送年月日]),1,4)),
        ToInt(Substring(ToString([配送年月日]),5,2)),
        ToInt(Substring(ToString([配送年月日]),7,2))
        ) AS 年月日,
         '配送' AS 区分,
        [会員番号],
        [注文番号],
        [明細番号],
        [商品コード],
        0 AS 注文数量,
        0 AS 注文金額,
        [数量] AS 配送数量,
        [明細金額] AS 配送金額
FROM (SELECT *
      FROM "配送" d
      LEFT JOIN "注文ヘッダ" o ON d.[注文番号] = o.[注文番号]
      LEFT JOIN "注文明細" od
             ON d.[注文番号] = od.[注文番号]
            AND d.[明細番号] = od.[明細番号]) a
UNION ALL
SELECT  CreateDate(
        ToInt(Substring(ToString([注文年月日]),1,4)),
        ToInt(Substring(ToString([注文年月日]),5,2)),
        ToInt(Substring(ToString([注文年月日]),7,2))
        ) AS 年月日,
       '注文' AS 区分,
       [会員番号],
       [注文番号],
       [明細番号],
       [商品コード],
       [数量] AS 注文数量,
       [明細金額] AS 注文金額,
       0 AS 配送数量,
       0 AS 配送金額
FROM (SELECT *
      FROM "注文ヘッダ" o
        LEFT JOIN "注文明細" od ON o.[注文番号] = od.[注文番号]) b

これでプレビューすると

いけそう。

2.4 仕上げ

明細テーブルができたら「注文ヘッダ」「注文明細」「配送」はもういらないから、非表示設定してビルドしてみる。
完成形はこちら。

3.まとめ

今回はSQLの知識がない人にはやや厳しかったかもしれない。
簡単なSQLはまた別途説明する機会は設けたいなと思っている。

今回の趣旨は、既にインポート済みのデータから新たにカスタムテーブルを作成するということだ。
カスタムテーブルの作成は、SQLのSELECTでデータを抽出して行う。
一つのSQL文のみが有効で、複数のSQLを連続して処理するようなスクリプトは書けない。
カスタムテーブルができたら、それを元にしてまたカスタムテーブルを作成するという手順になる。
当然だが、CREATE TABLEとかDROP TABLEはできない。カスタムテーブルを作成すのが目的だから。
不要になったテーブルは「非表示」設定しておくことでダッシュボードデザイナーからは見えなくなるので、まあ大丈夫といえば大丈夫なんだけど、データが多くなるとディスクサイズも気になるしなあーと思う人はカスタムインポートを使用してデータベースからインポートするSQLを編集するば可能だ。
この方法は次回説明したい。