MySQL+QuickSightで1ヶ月毎の日別データを可視化してみる

##はじめに
`QuickSightでMySQLのデータソースの作成が出来ている`ということが前提になってます。
データソースの作成方法については、公式のマニュアルを確認してください。

##この記事でやること
以下のような毎月の合計値と、1日あたりの平均値をQuickSightで表示する
<img width="329" alt="スクリーンショット 2019-12-10 18.33.39.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/529516/de8564e7-ccee-b4f5-1c58-23a34a144fce.png">


##つまずいた点
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日あたりの平均値が表示出来なかった。

```sql
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`)
```

##つまずいた点の解決方法
[この記事](https://bitstar.jp/blog/2010/11/11/2388/)を参考にして、解決することが出来ました。
1.１〜９の値を持ったテーブルを用意する。

```sql
-- テーブル作成
CREATE TABLE `number_table` (
  `number` int(11)
);

-- データのインポート
INSERT INTO `number_table` (`number`)
VALUES	(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
```

2.ビューを作成する

```sql
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日までの日付が表示されることを確認する。

```sql

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情報に合わせて変更してください※

```sql

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.データセット作成後、分析画面に遷移すると思うので、以下の内容の計算フィールドを追加する。
<img width="403" alt="スクリーンショット 2019-12-10 19.40.34.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/529516/39376652-ce3e-0a83-22ae-5bcded151b4e.png">
3.計算フィールドの作成に成功したら、可視化するビジュアルタイプを選択する。（今回はピポットテーブルを選びます。）
4.フィールドウェルを以下の通りに設定する。
<img width="847" alt="スクリーンショット 2019-12-10 20.07.29.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/529516/f53a8262-227e-eb04-386e-cf1090f3844e.png">
5.これで1ヶ月毎の合計値と平均値を出せるのですが、ちょっと見づらいので整形します。
<img width="326" alt="スクリーンショット 2019-12-10 20.10.13.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/529516/0b00931b-f3a9-250c-1bf3-6ea46f3612c4.png">
##ビジュアルの整形
1.date(MONTH)→形式→その他のフォーマットオプション...の順に選択していく。
<img width="706" alt="スクリーンショット 2019-12-10 20.13.10.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/529516/0b92461f-3926-b826-a979-52771707f12f.png">
2.データのフォーマットを「カスタム」にして、`YYYY年MMM`に設定する。
<img width="259" alt="スクリーンショット 2019-12-10 20.16.11.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/529516/2efbe519-8c34-67fb-e461-f03c49fa03fa.png">
3.ビジュアルのフォーマットの「値の名前」を以下のようにする。
<img width="257" alt="スクリーンショット 2019-12-10 20.22.55.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/529516/bde62e7d-5400-a9dc-d009-b5927d08ff0a.png">
4.年間の合計と、1日あたりの平均を表示したい場合は、「合計」の「行の合計を表示」にチェックを入れる
<img width="264" alt="スクリーンショット 2019-12-10 20.26.15.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/529516/086a2212-bb69-81e2-7ee6-2a0f0c13ce7c.png">
これで、以下のようなピポットテーブルが作成できるかと思います。
<img width="334" alt="スクリーンショット 2019-12-10 20.32.32.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/529516/e64fcd51-f4f5-37bb-c4f8-f63631e317bf.png">

##おわりに
QuickSightで用意されている関数を上手く使えば、頑張ってビュー作ったりしなくても日別集計を簡単に出来そうな気がする・・・。