Redashでドリルダウンを実現する

10 minute read

風音屋の兼業データアナリスト、星野（@mochigenmai）です。この記事ではRedashでドリルダウンを実現する方法を紹介します。

redash_logo

背景：データ分析の必需品、ドリルダウン

ドリルダウンとは階層データを絞り込むことです。階層データには以下のようなものがあります。

日付（年 > 月 > 日）
地域（地方 > 都道府県 > 市区町村）
商品（大カテゴリ > 小カテゴリ > SKU）

風音屋ではドリルダウンが頻繁に使われています。過去のデータ分析案件では、顧客データのうち「ロイヤリティが高い顧客」のデータを絞り込み、その増減要因を分析した結果、新商品の企画に繋がったこともあります。

顧客分析ドリルダウン

上図は「データ基盤による利益最大化と初期構築プロセス」より

困ったこと：Redashにはドリルダウン機能がない

Redashは気軽にクエリを書いてグラフを描画できるBIツールです。主な利用の流れは「1. SQLを実行する」→「2. SQLの実行結果をグラフで表示する」→「3. 複数のグラフを組み合わせてダッシュボードを作る」となります。あくまでクエリの実行結果を表示することがメインで、インタラクティブにクエリを実行できるわけではありません。そのため、ドリルダウン機能は用意されていません。

他のBIツールの中にはドリルダウンの機能が用意されているものもあります。例えば、Google DataStudio（日本での名前はGoogleデータポータル）では、公式ヘルプページでドリルダウンの実現方法が紹介されています。

実現したいこと：Redashでドリルダウンを実現したい

本稿のゴールは、他のBIツールと同じようにRedashでドリルダウンを実現することです。以下のようなフォーマットのデータを「東京都」「2021年8月」などでドリルダウンしながら、売上推移をグラフで確認したいと思います。

マスタデータ

大カテゴリ	小カテゴリ	タイトル
本・書籍	技術書	データマネジメントが30分でわかる本

トランザクションデータ

タイトル	価格	購入年月日	購入者	購入地域
データマネジメントが30分でわかる本	500円	2021年8月7日	ゆずたそ	東京都

解決策：Redashでドリルダウンを実現する2つの方法

Redashでドリルダウンを行う主な方法は「マルチフィルター」と「リンクを辿る方法」の2つです。

データ量が少なければマルチフィルターのほうが手軽に実現できます。
データ量が多い場合はリンクを辿る方法を選ぶのが現実的です。

それぞれの方法の利点・欠点は以下のようになります。

手法	利点	欠点
マルチフィルター	書くクエリの数を最小限に抑えられる。1つの画面で済む。「全選択」や「全解除」ができる。	1つのクエリでN1（個々のデータ）まで可視化すると挙動が重くなる。カテゴリごとの合計値を出すには PivotTable を使わないといけない。
リンクを辿ってドリルダウン	挙動が安定する。初期値を設定するとカテゴリ単位で集計できる。	クエリとビジュアライズを大量に作らないといけない。構築、保守、運用が負担になる。

技術調査にあたって、@ariarijpさん、@SassaHero さんにアドバイスをいただきました。

解決策①「マルチフィルター」

マルチフィルター機能を使うと、以下のような表示になります。画面上部の「大カテゴリ」（Primary_category）や「小カテゴリ」（Secondary_category）を変更すると、リアルタイムに下部の内容が絞り込まれます。

muliti_filter

「マルチフィルター」の使い方

カラム名のエイリアスに <columnName>::multi-filter を指定するだけで適用できます。 Redash公式サイトのユーザーガイドにある「Query Filters」を参考にして、動作確認を行いました。

SELECT
    primary_category AS "primary_category::multi-filter",
    secondary_category AS "secondary_category::multi-filter",
    COUNT(*) AS cnt
FROM
    query_4
GROUP BY
    primary_category,
    secondary_category

RedashのQueryResults機能を利用しているため、FROM句は参照先のクエリ番号（query_4）となっています。「query_1」から「query_3」の内容は後述します。

Tips：BigQuery利用時の注意点

接続先がBigQueryだと、カラム名のエイリアスで「::」がサポートされていないため、以下のようなエラーが出てしまいます。

Note that you can use __filter or __multiFilter, (double underscore instead of double quotes) if your database doesn’t support :: in column names (such as BigQuery).

先程のページ「Query Filters 」に回避策が記載されています。 BigQuery で利用する場合は「::」ではなく、以下のように「__」（アンダーバー2つ）を使ってください。

SELECT
    primary_category AS primary_category__multiFilter

「マルチフィルター」の利点

クエリのエイリアスを変更するだけなので簡単に利用できます。
フィルター選択時に「全選択」「全解除」ができるので、ドリルダウンがしやすいです。

「マルチフィルター」の欠点

SQLの実行結果をRedashの画面上でフィルタするだけなので、データ量が多いとRedashの挙動が重くなります。
画面上でフィルタするだけなので、ダウンロードするCSVには影響しません。
- ダウンロード結果を絞り込みたい場合はSQLで絞る必要があります。
画面上でフィルタするだけで、大カテゴリや小カテゴリ単位で集計できるわけではありません。
- 集計にはPivotTable を利用する必要があります（下図参照）。

pivot_table

解決策②「リンクを辿ってドリルダウン」

2つ以上クエリを事前に準備して、パラメータ機能を使ってドリルダウンする方法です。以下のような表示になります。

大カテゴリでの集計、小カテゴリにドリルダウンするURL

primary_category

小カテゴリでの集計、N1にドリルダウンするURL

secondary_category

N1での集計

title

カラムの値にドリルダウン先のダッシュボードへのリンクをHTMLで記述し、リンクを辿ることでドリルダウンを再現できます。GMOペパボさんの「minne のマーケティングダッシュボードを Redash に移行しました - ペパボテックブログ」を参考にして、動作確認を行いました。

「リンクを辿ってドリルダウン」の使い方

カテゴリの階層ごとにクエリを用意します。

大カテゴリでの集計

SELECT
    primary_category,
    COUNT(*) AS cnt,
    '<a href="http://localhost:8080/queries/7?p_primary_category=' || primary_category || '" target = "_blank">ドリルダウンする</a>' AS drill_down
FROM
    query_4
GROUP BY
    primary_category

小カテゴリでの集計

SELECT
    secondary_category,
    COUNT(*) AS cnt,
    '<a href="http://localhost:8080/queries/9?p_primary_category=' || primary_category || '&p_secondary_category=' || secondary_category || '" target = "_blank">ドリルダウンする</a>' AS drill_down
FROM
    query_4
WHERE
    '' = 'xxx' OR primary_category = ''
GROUP BY
    primary_category,
    secondary_category

N1での集計

SELECT
    title,
    COUNT(*) AS cnt
FROM
    query_4
WHERE
    ('' = 'xxx' OR primary_category = '')
    AND ('' = 'xxx' OR secondary_category = '')
GROUP BY
    primary_category,
    secondary_category,
    title

Tips：絞り込み条件を指定しないときの挙動

ドリルダウンで絞り込み条件をしないときには全てのレコードを表示するのが理想です。パラメータの初期値（initialValue）を以下のようにクエリで指定すれば、レコードを全件表示できます。

 = <initialValue> OR <columnName> = 

「リンクを辿ってドリルダウン」の利点

WHERE句で条件を絞り込んでいるため、マルチフィルターに比べてRedashの挙動が安定します。
初期値（今回は xxx）でクエリを実行すれば、大カテゴリや小カテゴリ単位での集計が可能になります（下図）。

param_xxx

「リンクを辿ってドリルダウン」の欠点

ドリルダウンする度にクエリが実行されるので、毎回クエリ完了まで待たないといけません。
「大カテゴリ → 小カテゴリ → N1」とリンクを辿るごとにクエリが必要なので、複数のクエリを書かないといけません。
- 事前の設計が必要になりますし、構築、保守、運用のコストがかかります。

Appendix：動作確認環境

同じように動作確認を行うための環境構築についての紹介です。

macOS 11.3 BigSur
Docker for Mac 3.5.0
Redash v8.0.0.b32245

1) テストデータを作成

スプレッドシートでテストデータを用意しました。

categories

2) Redashをローカル環境に構築

Redash公式サイトのDeveloper Guideを参考にしてDockerで動かします。

3) QueryResults を利用できるように設定する

Redash公式サイトの「Data Sources > Querying Existing Query Results」を参考にして「Data Source」に「Query Results」を追加します。

4) スプレッドシートのデータをRedashで利用できるようにする

Redash公式サイトの「Data Sources > Google Sheets」を参考にしてインポートを行います。

5) インポートしたデータをクエリとして保存する

import_data

6) QueryResultsでSQLを書く

以下のクエリで結合したデータを用意しました。

SELECT
    books.id,
    categories."primary" AS primary_category,
    categories.secondary AS secondary_category,
    books.title,
    books.price,
    receipts.purchase_date,
    receipts.purchaser,
    receipts.region
FROM
    query_1 AS books
INNER JOIN
    query_2 AS categories
ON
    books.category_id = categories.id
INNER JOIN
    query_3 AS receipts
ON
    books.id = receipts.book_id

あとはこのデータを使って「①マルチフィルター」と「②リンクを辿ってドリルダウン」を試せば、本記事の内容を再現できます。

Appendix：もう1つのフィルター「シングルフィルター」

Redashのフィルター機能には「マルチフィルター」の他に「シングルフィルター」というものもあります。 1つの項目のみでフィルターしたい時に利用します。そのため、全ての項目の集計やそこからのドリルダウンはできません。マルチフィルターでも同じことはできるので、あえてシングルフィルターを使わなくてもいいと思います。

single_filter

クエリとしては以下のようになります。

SELECT
    primary_category AS "primary_category::filter",
    secondary_category AS "secondary_category::filter",
    COUNT(*) AS cnt
FROM
    query_4
GROUP BY
    primary_category,
    secondary_category

まとめ

本稿では、Redash を使った可視化におけるドリルダウンを可能にする方法について、具体的なユースケースを例に以下の2つを紹介しました。

マルチフィルター
リンクを辿ってドリルダウン

今回のユースケースにおいては、それぞれ以下のような特徴がありました。

手法	利点	欠点
マルチフィルター	書くクエリの数を最小限に抑えられる。1つの画面で済む。「全選択」や「全解除」ができる。	1つのクエリでN1（個々のデータ）まで可視化すると挙動が重くなる。カテゴリごとの合計値を出すには PivotTable を使わないといけない。
リンクを辿ってドリルダウン	挙動が安定する。初期値を設定するとカテゴリ単位で集計できる。	クエリとビジュアライズを大量に作らないといけない。構築、保守、運用が負担になる。

2つの方法の使い分けとしては、パフォーマンスに問題がないデータ量なら「マルチフィルター」を利用し、パフォーマンスに問題があるデータ量なら「リンクを辿ってドリルダウン」を利用することが好ましいように思います。

Twitter Facebook LinkedIn