【Sisense Data Modeling】SisenseのSQLでWindow関数は使える？

シャローム！Sato-Gです。

今日は今年最後の出勤日、明日から９連休の年末年始の休暇だ。
こんなことは社会人になって初めてのような気がする（が、あったんだと思う）。
大晦日も紅白ゆっくり見ようっと。

さて、お気に入りの曲No.1は何ですか？
と聞かれた時に、オリコンのシングル部門1位だと答える人はいないよね。
以前、会社の女の子が「超特急」というアイドルグループのCDを30枚くらい飲み会でばらまいていたことがある。
太っ腹！と思ったんだけど、その週だけオリコンで1位になってたw
おそらくファンクラブの人たちがオリコン1位にするために、こぞってこういうことをやったんだと思う。
恐るべし！
僕は「あのグループは新幹線だっけ？」と間違え、「（笑）超・特・急！」って言われた。
ごめん、超特急。

さて、お気に入りの曲No.1は何ですか？
この解釈は色々あるとは思うが、オリコンのシングル部門のように単なる売上数で見たら「お気に入り曲No.1」とは言えないと思う。
ストリーミングで1位？これは近そうで違う。
そこには個人という要素が欠けているからだ。

データ分析の世界では、「個人」という視点をよく持ち込む。
例えば
一定期間において、それぞれの人が一番多く聞いた曲（つまり個人の1位）で、個人の1位が最も多かった曲
って言われれば、なるほどーって思わない？

こんなことを集計する時に使うのがSQLのWindow関数だ。
そして今回は、SisenseのSQLでWindow関数が使えるのか試してみたっていう話。
前置き長すぎ！

1. Windows関数とは

分析の仕事をしているとしょっちゅう使うのが、
・この顧客は何回購入しているのか
・累積購入額はいくら
というような元データから計算しなきゃいけないやつ。

例えば、初回購入した商品が「シャンプー」で、その人がコンディショナーを買うまで何回かかったかとか...

こういうケースではBI側で集計するのはとても困難な事態になるので、通常はデータモデリングの時点で前処理しておいたほうがベターだ。
従って、SisenseではSQLで行うことになる。

上記のような場合、SQLの中級者はWindows関数を使って求める。
むしろWindow関数が使えないと面倒なことになる。使わないでやれと言われると、考えるのもうんざりする。

Window関数は、SQL標準化の規格でいうとSQL:2003で規定されたものなので、僕のように初めてSQLを使ったのがSQL92ベースという人には「何それ？」という機能だ。
単にデータ統合するとか、そんな場合には必要ないのかもしれないが、SQLで分析をしたい人はこれを知ってるかどうかで差が出る。

「お気に入り曲No.1」を例にとって説明してみよう。

・顧客ごとのストリーミング回数を求めてみる
（ストリーミングされた回数をlisten_countとする）

SUM(listen_count) OVER(PARTITION BY customer_id)

・顧客ごとにストリーミングされた回数を元に曲にランクをつける

SELECT customer_id,
song_id,
RANK() OVER(PARTITION BY customer_id ORDER BY SUM(listen_count) DESC) AS song_rank
FROM Table
GROUP BY customer_id,song_id;

...と、スマートに集計できるのがWindow関数のいいところ。
今回は顧客ごとに購入ごとに、それがその顧客にとって何回目の購入なのかを求めてみる。

2. Sisense内でWindow関数を使ってみる

今回のデータはここのファイルを使った。

・ec_sales.db = SQLiteデータ
C:/sqlite/db/ec_sales.dbに配置する

・ec_sql_union.ecube = ErastiCubeデータ
【Sisense】ErastiCubeとDashboardファイルの配布とインポートを基にやってみてね。

データモデルは以下のとおり

ここでは、注文ヘッダ、注文明細、配送の３つのテーブルを使用して、カスタムテーブル「明細」を作成し、元のテーブルを非表示設定した。
明細テーブルに購入順を入れるのがゴールだ。

2.1 カスタムテーブル作成SQLで試してみる

カスタムテーブル「明細」をクリックし、「編集とプレビュー」を選択する。

Windows関数の場合は、SQLにこの１行を追加すればいい。

DENSE_RANK() OVER(PARTITION BY 会員番号 ORDER BY 年月日,注文番号) AS 購入順,

簡単に解説すると、以下のようになる。
・DENSE_RANK()
ランクをつける。RANK()との違いはこう。
タイムで１位が２人、2位が１人、３位が１人いたら、金メダル2人、銅メダル1、銅メダル0人になるのがRANK()、金メダル2人、銀メダル1人、銅メダル1になるのがDENSE_RANK()。
・OVER
Windows関数ではOVER句で集計条件を設定する
・PARTITION BY
どの項目で区切るか、GROUP BYみたいなもんだが、今回の場合は要は全体の行の中から会員番号でくくってパーティション切る（これがWindow）ということ。これで会員番号単位のかたまりができていると思えばいい。
・ORDER BY
会員番号単位のパーティション内のレコードの並び替え。今回は年月日と注文番号でソート指定。

これで、顧客ごとに年月日、注文番号順に連番が振られるはず。

Error! 使えない。
DENSE_RANK()と入力した時点で、認識してくれなかった。。。
結果

SisenseのSQLでは、Window関数はサポートされていない

ということがわかった。

2.2 インポートクエリのSQLで試してみる

SisenseのSQLでWindow関数が使えないことはわかったが、インポートクエリはデータベース側のSQLを使用するから問題ないはずだ。
注文ヘッダは注文番号単位になっているから、１回の購入で１レコード発生している。
よってここでWindow関数を入れてみる。

注文ヘッダテーブルをクリックしてサブメニューから「接続設定」→「テーブルの変更」を選択する。
注文ヘッダのクエリを編集する。

SELECT *,
ROW_NUMBER() OVER(PARTITION BY 会員番号 ORDER BY 注文年月日,注文番号) AS 購入順
FROM `注文ヘッダ`

今回はDENSE_RANK()ではなくROW_NUMBER()でいい。なぜなら、注文番号単位でユニークなレコードになっているから。
これで連番が振れるはずなので、プレビューしてみる。

できた！インポートクエリはデータベースのSQLなんだから当然といえば当然だけど。
あとはカスタムテーブルの「明細」のSQLを編集する。

SELECT  年月日,
        区分,
        c.[会員番号],
        [注文番号],
        CASE
            WHEN [区分] = '注文' AND m.[登録年月日]>=ToDatetime('2018/04/01') THEN [購入順]
            ELSE NULL
        END AS [購入順],
        [明細番号],
        [商品コード],
        [注文数量],
        [注文金額],
        [配送数量],
        [配送金額],
        CASE
            WHEN YearDiff(c.年月日,m.生年月日)<0 THEN 0
            ELSE YearDiff(c.年月日,m.生年月日)
        END AS 購入時年齢,
        CASE
            WHEN YearDiff(c.年月日,m.生年月日)<0 THEN 0
            ELSE Floor(YearDiff(c.年月日,m.生年月日)/10)*10
        END
        AS 購入時年代
FROM
(
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 区分,        [会員番号],
       o.[注文番号],
       [購入順],
       [明細番号],
       [商品コード],
       [数量] AS 注文数量,
       [明細金額] AS 注文金額,
       0 AS 配送数量,
       0 AS 配送金額
FROM "注文ヘッダ" o
        LEFT JOIN "注文明細" od ON o.[注文番号] = od.[注文番号]
) c
LEFT JOIN "会員" m
ON c.[会員番号] = m.[会員番号]

修正箇所は以下のとおり
①配送テーブルからSELECTするカラムに[購入順]を追加
②注文ヘッダからSELECTするカラムに[購入順]を追加
③全体のSELECT（5行目）に購入順を追加

CASE
  WHEN [区分] = '注文' AND m.[登録年月日]>=ToDatetime('2018/04/01') THEN [購入順]
  ELSE NULL
END AS [購入順],

このCASE文は
・購入順は区分が注文の場合のみ
・注文データは2018/04/01以降であるからそれより前に会員登録した顧客は以前注文があった可能性があるため購入順を設定できない
という２つの条件で購入順を設定することとし、該当しない場合はNULLとするというもの。

結果、以下のとおり、購入順が設定されている。

このように、インポートクエリでWindow関数を使用してあげれば、問題なく目的は達せられた。

3. まとめ

SQLでデータ分析を行う、またはデータモデル作成時に前処理を行う時にWindow関数は便利だ。
Window関数を使用すると、過去3ヶ月分の累計値を入れるとか、さらに応用すれば移動平均を使ってZチャートを作成することも可能だ。
これで、データ分析の幅は広がるね。

ではまた！