【Sisense Widget】同一カラムを用いたクロス集計表

こんにちは。Turtleです。

先日、友人と市販のスイーツの話になって盛り上がった。その友人はチョコパイと杏仁豆腐が好きで、「同意！！！」という気持ちがあふれ出したのだ。

そういえば東京に引っ越してきてから一度も食べてないなあ...と思い、連絡した直後にスーパーに直行した。チョコパイは当然として、杏仁豆腐には久しぶりのこともあってか感動した。これを読んでくれているみなさん、誕生日プレゼントは杏仁豆腐でお願いします。（笑）

さて、気を取り直して...
今回は同一カラムを用いたクロス集計表を作成する。見た目以上に考えることが多く、記事を作成するにあたってとても勉強になったウィジェットだった。

1. 確認

今回使用するデータの中身を確認する。
下にあるテーブルが全データとなっている。

"Count" の 1と-1はそれぞれ入会と退会を表している。また、"Date"、"ID"、"Class" は入退会した日付、会員のID、クラスをそれぞれ表している。データを見る限り入会した翌日に退会する人が続出の危険なサービスだが、今回は検証のため簡易的で非現実的なものとなっていることをご理解いただきたい。笑

SUM(Count) で退会を考慮した会員数を算出することができる（ただし、複数クラスに入会しているユーザーを別ユーザーとしてカウントする場合。同一ユーザーとしてカウントする場合は数式で少し工夫する必要がある）。また、期間指定にも対応した設計となっている。

今回のゴールは、Class × Class の会員数のクロス集計表である。イメージは以下の通り。

ものすごく簡単に見えるかもしれないが、実はこれを作成するのには意外と手間と工夫が必要になる（もしかしたら自分がただ複雑にしているだけなのかもしれないが...）。

2. 検証

さて、先ほど確認したクロス集計表を作成するにはいくつかの方法がある。

クロス集計表は言うまでもなくピボットウィジェットで作成するのだが、ピボットウィジェットでは同一カラムを行と列に指定することができない。したがって、①同一テーブルに "class_2" などの別カラムを作成する ➁クロス集計表用の別テーブルを作成する ③別テーブルを作成し、そのテーブルとリレーションを設定して作成する...などの対策が必要となる。

今回はテスト用のデータなので問題ないが、本番用のデータでCROSS JOINして "class_2" のような別カラムを持つのはあまり現実的ではない（レコード数が凄いことに...）。期間指定にも対応できるようにすることを考えると、カスタムテーブルを作成してSQLで集計するのも難しい。

よって、今回はテーブルを複製し2つのテーブルでリレーションを作成し解決する方法を採用する。もっと望ましい方法が存在する可能性は多分にあるので、あくまで参考程度に捉えてほしい。

最初にビルドした[Cross_Test]を複製し、複製したテーブルに[Cross_Test_2]と名前をつける。問題は何をキーに設定するかなのだが、今回は「会員数」を算出したいので、"ID" をキーに設定する。

今回の検証中に「なるほど...！」となったのだが、キーは「別テーブルを見る際に何をベースに見るか」だと思う（これはぼく独自の見解なので、「そんな訳ないだろ」という抗議やクレームはどうかお控えいただきたい...）。

さて、テーブル設計が終わったところで、次にクロス集計表を作成する。

※ [Cross_Test] - "Class" は [Cross_Test] テーブルのカラム "Class" を選択することを意味している

上図の設定で作成したクロス集計表と、冒頭で示したゴールを比較してみると、少し値が違っているのがわかる。
値が異なるだけでなく、今作成したクロス集計表（Total Countの方）には大きな問題がある。行で見たときと列で見たときの値が異なるのだ。（行:列として、A:C=1 に対して C:A=3）

この問題を解消するには、「なぜこのようになるのか？」を理解する必要がある。下図は、赤色が先述のA:C=1、青色がC:A=3 のTotal Countの算出方法である（なお、これはTurtleの仮説の域を出ない）。

順に説明していく。前提として、「"ID"をキーに設定している」「値には SUM([Cross_Test] - "Count")が設定されている」ことから、もう一方のテーブルを見に行くときは同じ"ID"を見に行く + Total Countには[Cross_Test]の"Count" しか影響しない ことが導き出される。

つまり、Total Count = [Cross_Test]の"Count" × 「Cross_Test_2」に存在する共通ID数 で計算されている。A:CとC:Aの値が異なるのはこのためである。

上記のことを踏まえ、「リレーションがおかしいのか？では、キーはどうするのか？」という問いが生まれたTurtleは、キーにindex（ユニークキー）を設定したり、逆に 'KEY' というユニークの真逆（何と言えばよいのだ）のキーを作成して試してみたり、

[Cross_Test] と [Cross_Test_2] の間に"ID"をSELECT DISTINCTした [ID_List] を作成したり

その他いろいろなリレーションを試したのだが、どうもうまくいかない。（明らかにうまくいくはずがないものも幾つか試した、「物は試し」って言うから...）

そしてたどり着いた答えが、「これはリレーションの問題ではない、数式の問題だ」だった。Formula担当としたことが...最近数式で解決できないものが多く、数式への信頼を失っていたのかもしれない。

そして、数式だと気づいたあとはわりとあっさり解決した。問題は、[Cross_Test_2] の"Count"を全く考慮していないことなんだから、それを考慮してあげればいい。

結論からいうと、キーは"ID"に設定したままで、以下の数式で今回の問題は解決できる。

数式をあえて日本語で書くとしたら、

IDベースで見て、「SUM(Count) > 0 が両方のテーブルで成り立つとき」（= 両方のテーブルで会員であるとき）1としてカウントし、そうでなければ0とする

ということになる。もちろん他の数式の書き方もあると思われるが、今のところこれが自分の中でしっくりきている。

3. まとめ

今回は、同一カラムでクロス集計表を作成する方法のひとつとして、テーブルの複製を紹介した。一見簡単に思われるウィジェットでも、リレーションや数式について詳細に考える必要があるということを、痛いほど思い知らされた。

今回は数値上問題ない値が出たこの辺で一旦終わりにして、またの機会に「厳密に正しい値なのか？」「期間指定するとどうなるのか？」といったところを見ていきたいと思う。

それでは！