Google DataStudio1 でこういう表示をしたい場合は多い。目標の達成割合とか、アプリケーションログから何らかのアクションを取ったユーザの割合を出したりだとか。とりあえず使うだけなら簡単に使える DataStudio だけど、これはシンプルな表示のわりに妙なテクが必要なので書いておく。
サンプルデータとして、GitHub のリポジトリとライセンスの情報が入った公開データセットを使う、なぜならスキーマがめちゃ簡単だから2。bigquery-public-data.github_repos.licenses
ここでは、MIT ライセンスを応援しているとして、全体に対する MIT ライセンスの割合を表示したダッシュボードを作りたいとする。表で割合を出すのは簡単。一方スコアカードにするのは難しい、license="mit" みたいなフィルタを作ってから、これを適用すると分母となる全体の値が取れないことに気づく。
データを統合 / 混合データソース
(おすすめではない)
目を疑うけどこういう機能がある。
license="mit" のフィルタを適用したスコアボードと、全体の値の 2つ選択して右クリックからデータを統合すると出てくる。びっくりするけど、混合データソースを作って集計するショートカットになってる。
統合キーも無いので、JOIN じゃなくて SELECT 2 本相当、こういうクエリに相当するはず。
SELECT mit / total FROM ( SELECT (SELECT COUNT(*) FROM bigquery-public-data.github_repos.licenses) as total, (SELECT COUNT(*) FROM bigquery-public-data.github_repos.licenses WHERE license = "mit") as mit )
簡単なのはよいけど、都度混合データソースを作ってしまうし、あまりおすすめではない。スコアカードはいくつも置くことが多いし、ノリノリで作ってると 混合データ (21) みたいな状態になる。既に混合データソースがあって整備されているならそれを使うべきだし、内部的にデータソースごとにキャッシュが作られている感があるので、クエリコストの面でも良くないと思う。
CASE WHEN でマップ
(おすすめ)
個人的によく使うのがこの方法。
SUM(CASE WHEN license="mit" THEN 1 ELSE 0 END) / Record Count のような計算式を書く。
あとで編集するときに何だこりゃとならないし、混合データソースにゴミが増えない。
1発で複数の条件で集計した値を取りたい場合に投げるこういうクエリを DataStudio でやってる感じ。
SELECT COUNT(*) as all_user, COUNT(CASE WHEN age >= 20 THEN 1 ELSE null END) as adult, COUNT(CASE WHEN age < 20 THEN 1 ELSE null END) as child, FROM ...
DataStudio のフィルタは SQL の WHERE 句に相当するので、クエリ全体に適用する条件にはフィルタを使って、計算フィールドで SELECT するフィールドに式を書くイメージです。
もし似たような集計を何度もやる場合は、データソースに計算フィールドとして 1 or NULL のフィールドを作っておくと便利。型は数値 デフォルトの集計は 件数 で集計フィールドを作る。
ここで COUNT を使っているのは 1 or NULL にマップしてるから。SUM でも大抵は問題ないけど SUM(NULL) は NULL なのでフィルタされた行が 0 行の場合エラーになる可能性がある。集計方法は UI 上で 合計(SUM) や 件数(COUNT)、個別件数(COUNT DISTINCT) を切り替えられるので、フィールドを作るなら 1 or NULL にマップする方が他の集計にも使いやすいことが多い気がするのでそうしている。
