MySQL : group by句を使って、データ数を日時ごとでグループ化して表す

#はじめに
インターネット上のニュース記事をスクレイピングしていた際、一時間ごとにどのくらい新着記事が増えているかを表すことがありました。
googleスプレッドシート等にあるピボットテーブルを使えば簡単に表示できますが、mysqlのgroup_by機能を使っても同じように表示できたので、まとめておこうと思います。

#ピボットテーブルで表す
今回は以下のように、ニュース記事が発行された時間が「published_at」としてスプレッドシート、mysqlにあらかじめ保存されています。
![Screenshot from 2020-05-28 19-06-05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636023/f9a5a16b-a49d-b124-e145-1d50b598ed75.png)

1日のうちで、一時間ごとの新着記事数を表したいので、「published_at　年-月-日」、「published_at　時」を行要素として、「published_at」の`COUNTA`を値としてテーブルを作成します。
![Screenshot from 2020-05-28 19-21-42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636023/8aaf71e0-0f2e-32a9-19df-8b54b338609c.png)

ピボットテーブルではこのように表すことができます。

#mysqlでピボットテーブルを再現する
以下はピボットテーブルを再現するsqlです。

```sql
select 
  date_format(published_at, '%Y-%m-%d'), 
  date_format(published_at, '%H'), 
  count(*) 
from 
  articles 
group by 
  date_format(published_at, '%Y-%m-%d'), 
  date_format(published_at, '%H') 
with rollup;
```
表示したいフィールドは`select`で選んでおきます。ピボットテーブルを再現したいので、`published_at`の年月日、時間、取得数を表示するように選びます。

スプレッドシートとの対応としては、

- 「published_at　年-月-日」　　-> 　date_format(published_at, '%Y-%m-%d')
- 「published_at　時」　　　　　-> 　date_format(published_at, '%H')
- 「published_atの`COUNTA`」　-> 　count(*)

になります。published_atは`date_format`でフォーマットが必要です。countはワイルドカードで十分です。
グループ化したいデータは`group by`で選択します。published_atの年月日、時間でグループ化したいので、selectのときと同様に選択します。
また`with rollup`を使うことで1日の取得合計数を表示することができます。



以下の結果が得られます。

```
+---------------------------------------+---------------------------------+----------+
| date_format(published_at, '%Y-%m-%d') | date_format(published_at, '%H') | count(*) |
+---------------------------------------+---------------------------------+----------+
| 2020-05-23                            | 06                              |        1 |
| 2020-05-23                            | NULL                            |        1 |
| 2020-05-24                            | 11                              |        1 |
| 2020-05-24                            | 19                              |        1 |
| 2020-05-24                            | 20                              |        1 |
| 2020-05-24                            | NULL                            |        3 |
| 2020-05-25                            | 03                              |        1 |
| 2020-05-25                            | 08                              |        1 |
| 2020-05-25                            | 10                              |        2 |
| 2020-05-25                            | 11                              |        1 |
| 2020-05-25                            | 12                              |        1 |
| 2020-05-25                            | 13                              |        3 |
| 2020-05-25                            | 15                              |        3 |
| 2020-05-25                            | 17                              |        1 |
| 2020-05-25                            | 19                              |        2 |
| 2020-05-25                            | 20                              |        1 |
| 2020-05-25                            | 21                              |        1 |
| 2020-05-25                            | 22                              |        1 |
| 2020-05-25                            | NULL                            |       18 |
| 2020-05-26                            | 00                              |        2 |
| 2020-05-26                            | 05                              |        4 |
| 2020-05-26                            | 06                              |        5 |
| 2020-05-26                            | 07                              |        1 |
| 2020-05-26                            | 08                              |        2 |
| 2020-05-26                            | 09                              |        1 |
| 2020-05-26                            | 10                              |        3 |
| 2020-05-26                            | 11                              |        5 |
| 2020-05-26                            | 12                              |        5 |
| 2020-05-26                            | 14                              |        3 |
| 2020-05-26                            | 15                              |        6 |
| 2020-05-26                            | 16                              |        8 |
| 2020-05-26                            | 17                              |       19 |
| 2020-05-26                            | 18                              |       18 |
| 2020-05-26                            | 19                              |        9 |
| 2020-05-26                            | 20                              |       15 |
| 2020-05-26                            | 21                              |       17 |
| 2020-05-26                            | 22                              |       13 |
以下省略
```
ピボットテーブルと同様な表が得られています。
`with rollup`により、1日の合計取得数が`NULL`の欄に出力されています。

上の例ではピボットテーブルをできるだけ再現するために年月日と時間をわざわざ分けましたが、date_formatでフォーマットするときに日時を一緒にすることで簡略的にほしい結果を得ることもできます。

```sql
select 
  date_format(published_at, '%Y-%m-%d %H'), 
  count(*) 
from 
  articles 
group by 
  date_format(published_at, '%Y-%m-%d %H');
```
実行結果

```
+------------------------------------------+----------+
| date_format(published_at, '%Y-%m-%d %H') | count(*) |
+------------------------------------------+----------+
| 2020-05-23 06                            |        1 |
| 2020-05-24 11                            |        1 |
| 2020-05-24 19                            |        1 |
| 2020-05-24 20                            |        1 |
| 2020-05-25 03                            |        1 |
| 2020-05-25 08                            |        1 |
| 2020-05-25 10                            |        2 |
| 2020-05-25 11                            |        1 |
| 2020-05-25 12                            |        1 |
| 2020-05-25 13                            |        3 |
| 2020-05-25 15                            |        3 |
| 2020-05-25 17                            |        1 |
| 2020-05-25 19                            |        2 |
| 2020-05-25 20                            |        1 |
| 2020-05-25 21                            |        1 |
| 2020-05-25 22                            |        1 |
| 2020-05-26 00                            |        2 |
| 2020-05-26 05                            |        4 |
| 2020-05-26 06                            |        5 |
| 2020-05-26 07                            |        1 |
| 2020-05-26 08                            |        2 |
| 2020-05-26 09                            |        1 |
| 2020-05-26 10                            |        3 |
| 2020-05-26 11                            |        5 |
| 2020-05-26 12                            |        5 |
| 2020-05-26 14                            |        3 |
| 2020-05-26 15                            |        6 |
| 2020-05-26 16                            |        8 |
| 2020-05-26 17                            |       19 |
| 2020-05-26 18                            |       18 |
| 2020-05-26 19                            |        9 |
| 2020-05-26 20                            |       15 |
| 2020-05-26 21                            |       17 |
| 2020-05-26 22                            |       13 |
以下省略
```
簡略版では`with rollup`が使えませんが、先程までと同様の結果が得られています。