Redashのグラフで一部の期間を強調する

NAVITIME_Tech

2023年2月6日 10:00

こんにちは、こんぶです。
ナビタイムジャパンでバイク専用ナビゲーションアプリ『ツーリングサポーター』の開発を担当しています。
今回はRedashを使って一部の期間を強調するやり方についてお話ししたいと思います。

Redashとは

Redashは様々なデータソースに接続してデータを視覚的に見やすくするBIツールです。クエリを書くことによって簡単にグラフが作成出来るためとても便利です。

Connect and query your data sources, build dashboards to visualize data and share them with your company.

出典：Redash

やりたかったこと

下の画像の様に、RedashではSQLで集計をすると対応するグラフを同時に生成してくれる機能があります。
画像のように一定期間の部分だけ強調した色に変更したかったのですが、一部の棒グラフだけ色を変更する様な機能が無かったため、集計する際のSQLを工夫して実現しました。

作成した背景

ある機能の利用状況を定期的に確認しており、なおかつ比較対象として1週間前の数値と比較しながら確認したいというデータがありました。
求めているグラフを作成すること自体は、エクセルやGoogleスプレッドシート等にCSVを取り込み、グラフを編集すれば簡単に作成できます。しかし毎回CSV抽出→グラフを編集するのは少し手間なので、Redashを開いてすぐ確認出来るように作成しました。
また、Redashでグラフを作成することが出来ればダッシュボード上で他のグラフ等と一緒に確認することが可能になります。

作成手順

Redash上でのグラフの編集機能について

Redashではクエリ編集画面でSQLを入力し、「Execute」ボタンをタップすると表形式とグラフ形式で結果を表示してくれます。
グラフの色や縦軸/横軸の設定を変更したい場合は、画面下部にある「Edit Visualization」をタップすると、グラフをどの様に表示したいかが設定出来ます。

「Edit Visualization」を開くと下図の様な画面が表示されます。
社外秘情報のため、具体的なカラム名などは隠していますが、X軸/Y軸にどのカラムを設定するかを指定することが出来ます。
その他、グラフの色やグラフの形式（棒グラフや円グラフ等）を選択することが可能です。

具体的な実現方法について

どうやって掲題のグラフを実現したかですが、「積み上げグラフ」を利用して作成しています。
作成の手順は以下になります。

集計する期間のうち、強調したい期間以外の集計値が0になるサブクエリを作成
集計する期間のうち、強調したい期間のみ集計値が0になるサブクエリを作成
1, 2で作成したクエリをJoinで連結
「Edit Visualization」でx軸に日付カラム、y軸に①の集計値のカラムと②の集計地のカラムを指定します。
Stacking欄に「Stack」を指定する（積み上げ形式を指定）
ColorタブでY軸に指定した各棒グラフの色を変更する

1~3のサンプルSQLは以下になります。
（サンプルでは各期間は以下の設定でSQLを作成しています）
集計期間：2022/12/20~2023/01/02
強調期間：2022/12/24~2022/12/29

1 .集計する期間のうち、強調したい期間以外の集計値が0になるサブクエリを作成

WITH specific_date AS (
SELECT 日付カラム, IF('20221224' <= 日付カラム and '20221229' >= 日付カラム, 集計したい値, 0) as 集計値
FROM
テーブル名
WHERE 日付カラム >= '20221220'
AND 日付カラム <= '20230102'
)

2. 集計する期間のうち、強調したい期間のみ集計値が0になるサブクエリを作成

,un_specific_date AS (
SELECT 日付カラム, IF('20221224' > 日付カラム or '20221229' < 日付カラム, 集計したい値, 0) as 集計値
FROM
テーブル名
WHERE 日付カラム >= '20221220'
AND 日付カラム <= '20230102'
)

3. 1, 2で作成したクエリをJoinで連結

SELECT a.日付カラム,a.集計値, b.集計値
FROM un_specific_date a 
LEFT OUTER JOIN 
specific_date b
ON a.日付カラム = b.日付カラム 
WHERE a.日付カラム >= '20221220' 
AND a.日付カラム <= '20230102' 
order by a.日付カラム

以上の手順で「一部の期間を強調するグラフ」を作成することが出来ます。

集計期間が被っている問題について

上記で紹介した手順はシンプルで分かりやすいのですが、同じ集計期間で2回集計してしまっています。
期間が短く集計量が少ないのであれば上記の手順でも良いのですが、期間が増えると集計量が多くなり、サーバーへの負担も大きくなってしまいます。
そのため、期間を被らない様に集計すると以下のようになります。

WITH specific_date AS (
SELECT 日付カラム, 集計値
FROM 
テーブル名
WHERE '20221224' <= 日付カラム 
AND '20221229' >= 日付カラム
)

,un_specific_date AS (
SELECT 日付カラム, 集計値
FROM 
テーブル名
WHERE  (
('20221224' > 日付カラム AND 日付カラム >= '20221220') 
or ('20221229' < 日付カラム AND 日付カラム <= '20230102')
)
)

SELECT a.日付カラム,a.集計値, b.集計値
FROM un_specific_date a
LEFT OUTER JOIN
 specific_date b
ON
 a.日付カラム = b.日付カラム
UNION
SELECT b.日付カラム,a.集計値, b.集計値
FROM un_specific_date a
RIGHT OUTER JOIN
 specific_date b
ON
 a.日付カラム = b.日付カラム
order by 日付カラム

OUTER JOIN で連結している部分ですが、以下の様な手順を踏んでいます。

b.集計値にnullが入り、強調しない期間の値のみが入っている表を作成（LEFT OUTER JOIN部分）
a.集計値にnullが入り、強調する期間の値のみが入っている表を作成（RIGHT OUTER JOIN部分）
1と2をunionで連結

3まで実行すると実行結果は以下の様な表になります。

いかがでしたでしょうか。行っていること自体はシンプルですが、活用出来る場面はあると思います。

活用出来る場面

1週間前の同じ曜日の数値と比較したい場合
新機能をリリースしたが、リリース前後で数値の比較をしたい場合

また、もう少し複雑なSQLを組めば特定の曜日だけ別の色にするといったことも出来るかと思います。

おわりに

今回はクエリを工夫してグラフを見やすくする取り組みを紹介させて頂きました。
クエリの書き方次第で簡単に見やすいグラフを作成することが出来るのがRedashの魅力だと思います。
また、本文中詳しくは触れませんでしたが、グラフの見た目を変更するVisualizationsの基本的な使い方はこちらのページで紹介されていますのでご参考にして下さい。

最後までお読みいただきありがとうございました。

※2023/02/06追記

Visualization Editorの「Group by」機能を使うことにより、もっと簡単に求めているグラフを作成することが可能でしたので追記致します。

まず、SQLは以下のようになります。

SELECT 日付カラム,集計値, IF('20221224' <= 日付カラム and '20221229' >= 日付カラム, 'specific_date', 'un_specific_date') AS type
FROM テーブル名
WHERE 日付カラム >= '20221220'
and 日付カラム <= '20230102'
GROUP BY 1,3
ORDER BY 1

上記のSQLを実行すると以下のような表になります。
typeカラムを追加することにより、強調すべき値かどうかを表に持たせるようにしました。

この状態で「Visualization Editor」の「Group by」欄に"type"を指定することで求めているグラフを作成することが出来ます。