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__multi-filter"

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

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

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

  • 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

categories

  • books

books

  • receipts

receipts

お手軽に試すのであれば、BigQueryの一般公開データセットから階層構造を持つデータ(natalityなど)をご利用ください。

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. マルチフィルター
  2. リンクを辿ってドリルダウン

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

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

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

Tags: ,

Posted:

Author: mochigenmai

星野(@mochigenmai)。 風音屋の兼業データアナリスト。 アウトプットに勉強会「Redash Meetup」発表や『DWH利用者アンケート2021』レポート作成など。