【Sisense Data Modeling】カレンダーテーブルを自動生成する

シャローム！Sato-Gです。
新型コロナ感染拡大に伴いテレワークの生活になったけど、テレワークというのは外出は自粛しているわけで、自分でランチを作ることが多くなる。そうなると自ずとパスタが多くなるというのはスーパーに行ってもパスタ売り場の品薄状態を見てもわかる。
たまにマルゲリータのパスタとか作りたいなと思っても、一人分のフレッシュバジルってなかなか難しいよね。そもそも生バジルなんて香りが飛んでしまうから保存するものではない。
というわけで3週間前にベランダで種から育てたバジルがようやく収穫期になってきたー
この時期のバジルは摘心といって、切り戻しをする。そうするとわき芽から枝が増えて、より多くの葉をつけるようになる。摘心をしながらこの週末から収穫を始めたのでランチはパスタ、ピザを作ってみた。
ここから夏が終わるまでイタリアンな食生活を楽しみたい（←コロナ太りの典型だな）
因みにバジルはSimple Organic Lifeでも紹介されているように「蚊」対策にもなるので、窓辺にも小鉢にして置いてある。
朝、バジルの香りの中で目覚めるというのも気持ちいいよ。

1. 前回作成したカレンダーテーブルの問題点

1.1 問題点とは

前回はカレンダーテーブルに細工し、FAKE_KEYを使ってリレーションを設定することで、累計計算の歯抜けを解消した。しくみとしてはこれでOK。
が、僕はかなり手抜きしていて、「カレンダー」テーブル自体、実は問題を抱えている。
というのは、こういうこと

というわけでおわかりだろうか？
値が小さいから問題が露見していないだけで、1月15日から1月26日の間は累計されていない。
そう、実は目立たなかっただけ。

1.2 何が原因か

カレンダーテーブルを作成するSQLを見てみよう

このSQLを見るとCOVID-19の[確定日YYYYMMDD]と人口変動データの[年月日]が両方とも存在しない日は取得されない。特に新型コロナの感染が始まった初期は新規感染者がなかった日が存在し、そこが空白になってしまう。そのために感染者のいなかった1月15日から1月26日は表示されないという結果になっているのだ。

2. 抜けのないカレンダの生成

2.1 SQLの改善

感染者が出現した2020年１月14日からデータの最終日まで自動的に年月日を生成するSQLを作成してみる。
Sisenseでカスタムテーブルを作成する際のSQLの制限事項は以下のとおり

【制限事項】

・SisenseのSQLではWITH句は使えない
・SisenseのSQLではWindows関数のROW_NUMBERは使えない
・SisenseではSQL内で変数を使うことはできない

上記の制限事項を踏まえて考えた改善方針は

①適当なサイズのテーブルから行番号のみ取得する
（10年分なら3652件以上必要、でも数千万件とかは必要ないかな、
その程度のテーブルにしておこう）
②OracleのようなROWNUMなどの関数は使えないのでRANKASCで代用する
③最小の日付に①の（行番号-1）を加算することで１日刻みで日付を生成する
④日付の最大値は一番大きな日付を持つデーブルから取得し、WHERE句で制限する

作成したSQLは以下のとおり

SELECT date + rownum *(60*60*24) AS 年月日,
        GetYear(date + rownum *(60*60*24))*10000 + GetMonth(date + rownum *(60*60*24))*100 + GetDay(date + rownum *(60*60*24)) AS 年月日KEY,
        1 AS FAKE_KEY,
        0 AS ZERO
FROM
(
SELECT  d2.date,
        RANKASC(d1.確定日YYYYMMDD)-1 AS rownum
FROM [COVID-19] d1,
    (SELECT
        Createdate(
            GetYear(Min(確定日YYYYMMDD)),
            GetMonth(Min(確定日YYYYMMDD)),
            GetDay(Min(確定日YYYYMMDD))
        )
         AS date
    FROM [COVID-19]
    ) d2
) d3
WHERE date + rownum *(60*60*24)
<=
(SELECT
    MAX(年月日)
FROM [人口変動データ]
)
ORDER BY date + rownum *(60*60*24)

2.2 SQLの解説

このSQLの中では色んなことをやっているので、解説すると

①最小の年月日を[COVID-19]テーブルから取得する（Min関数）
ここではMin関数で取得するだけでなく、時刻なしのDateで取りたいので、以下のようにCreateDate関数で0:00:00の値を取得して、[date]というカラムに格納する

Createdate(
    GetYear(Min(確定日YYYYMMDD)),
    GetMonth(Min(確定日YYYYMMDD)),
    GetDay(Min(確定日YYYYMMDD))
) AS date

②同じく[COVID-19]テーブルは1万数千行なので、ここから行番号を取得する
これは①のdateに加算するカウンターなので、初日が必要だからマイナス1しておく。

RANKASC(d1.確定日YYYYMMDD)-1 AS rownum

③ ①のdateと②のrownumの全ての組み合わせを取得する
組み合わせを取得する場合は
SELECT t1.column1 t2.column2 FROM t1,t2
とキーを入れずにSELECTする。
（CROSS JOINしてもいい）

SELECT d2.date,
       RANKASC(d1.確定日YYYYMMDD)-1 AS rownum
FROM [COVID-19] d1,
(SELECT date...) d2

④ ②のdateに③のrownumを加算して年月日を作成する
SisenseのSQL内では値は秒単位で持っているため、1日単位で加算するためにはrownumに(60*60*24)を掛ける必要がある。

SELECT date + rownum *(60*60*24) AS 年月日,

⑤SELECTの範囲は[人口変動データ]の年月日のMAXまでとする

WHERE date + rownum *(60*60*24)
<=
(SELECT
    MAX(年月日)
    FROM [人口変動データ]
)

これで元のデータモデルと同様に再度リレーションを設定する。

2.3 結果の確認

元のウィジェットで確認するとこのとおり、1月14日の感染者数は1月15日以降もきちんと保持されている。
これで問題が解決！

3. まとめ

SisenseのSQLでは（複数のサグクエリを含む）１つのSQLしか実行できないし、制約も多いが、今回はちょっと頭をひねって考えてみた。
カスタムテーブルを作成するSQLの環境はもうちょっと拡張してほしいけどね。
まあ、やればできるってことで、今回はおしまい。

ではまた！