はじめに
QuickSightでMySQLのデータソースの作成が出来ているということが前提になってます。
データソースの作成方法については、公式のマニュアルを確認してください。
この記事でやること
以下のような毎月の合計値と、1日あたりの平均値をQuickSightで表示する
つまずいた点
1日あたりの平均値を表示する為、データが１件もない日は０件としてカウントさせること。
↓イメージ↓
| Date | DataCount |
|---|---|
| 2013/01/01 | 3 |
| 2013/01/02 | 4 |
| 2013/01/03 | 0 |
| 2013/01/04 | 6 |
| ・・・ | ・・・ |
| 2013/01/31 | 1 |
下記のSQLだと、1月3日のデータが取得出来ないため、正確な1日あたりの平均値が表示出来なかった。
SELECT DATE(`AddDate`) as `Date`, Count(`id`) as `DataCount`
FROM `sampleTable`
WHERE `AddDate` BETWEEN '2013/01/01 00:00:00' AND '2013/01/31 23:59:59'
GROUP BY DATE(`AddDate`)
つまずいた点の解決方法
この記事を参考にして、解決することが出来ました。
1.１〜９の値を持ったテーブルを用意する。
-- テーブル作成
CREATE TABLE `number_table` (
`number` int(11)
);
-- データのインポート
INSERT INTO `number_table` (`number`)
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2.ビューを作成する
CREATE VIEW view_sampleNumbers AS
SELECT (`n1`.`number` + (`n2`.`number` * 10) + (`n3`.`number` * 100)) AS `Number`
FROM `number_table` AS `n1` JOIN `number_table` AS `n2` JOIN `number_table` AS `n3`;
3.下記のSQLを実行して、2013年1月1日〜12月31日までの日付が表示されることを確認する。
SELECT ADDDATE('2013-01-01', V.Number) as Date
FROM view_sampleNumbers as V
WHERE ADDDATE('2013-01-01', V.Number) BETWEEN '2013-01-01' AND '2013-12-31'
4.下記のSQLを実行して、2013年1月1日〜12月31日までの日付と、1日あたりの合計値が表示されることを確認が出来たら完了です。
※日付の範囲と「sampleTable」の箇所は、自分のローカル環境のDB情報に合わせて変更してください※
SELECT ADDDATE('2013-01-01', V.Number) as Date,IFNULL(Count(S.id),0) as DataCount
FROM view_sampleNumbers as V LEFT JOIN sampleTable as S
ON ADDDATE('2013-01-01', V.Number) = DATE(S.`AddDate`)
WHERE ADDDATE('2013-01-01', V.Number) BETWEEN '2013-01-01' AND '2013-12-31'
GROUP BY ADDDATE('2013-01-01', V.Number);
この問題を解決出来たら、あとはQuickSightで上記のSQLを使用してデータセットを作成するだけです。
データを可視化する
1.データセットを作成する際に、「カスタムSQLを使用」を選択して、上記の4.で実行したSQLでデータセットを作成する。
2.データセット作成後、分析画面に遷移すると思うので、以下の内容の計算フィールドを追加する。
3.計算フィールドの作成に成功したら、可視化するビジュアルタイプを選択する。（今回はピポットテーブルを選びます。）
4.フィールドウェルを以下の通りに設定する。
5.これで1ヶ月毎の合計値と平均値を出せるのですが、ちょっと見づらいので整形します。
ビジュアルの整形
1.date(MONTH)→形式→その他のフォーマットオプション...の順に選択していく。
2.データのフォーマットを「カスタム」にして、YYYY年MMMに設定する。
3.ビジュアルのフォーマットの「値の名前」を以下のようにする。
4.年間の合計と、1日あたりの平均を表示したい場合は、「合計」の「行の合計を表示」にチェックを入れる
これで、以下のようなピポットテーブルが作成できるかと思います。
おわりに
QuickSightで用意されている関数を上手く使えば、頑張ってビュー作ったりしなくても日別集計を簡単に出来そうな気がする・・・。