MySQLでクロス集計する

#はじめに
今回は[こちら](https://qiita.com/tetoralynx/items/bf775bcf9a9b3f6f5c10)の続きとなります。
前回の記事では、とりあえず新着したニュースを数えただけでしたが、今回はそれに加えてニュースのカテゴリごとに新着数を出した表を作りたいと思います。
googleスプレッドシートのピボットテーブルでは以下のように作成されます。
![Screenshot from 2020-05-29 17-49-05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636023/7ec3a336-1457-449b-2b8c-5b4649cd136d.png)
ピボットテーブルを使えば、追加で列要素にカテゴリを選択することで簡単に表を作ることができます。
このように行と列でデータをグループ化して表にしたものを「クロス集計」といいます。
今回はmysqlでクロス集計をするという内容になります。

こちらの記事を参考にさせていただきました。
>[MySQLでクロス集計してみた](http://torhamzedd.halteria.com/2010/06/mysql.html)

#mysqlでクロス集計
DBにはあらかじめニュースの新着日時が「published_at」で、カテゴリが「category」として保存されています。
![Screenshot from 2020-05-29 18-08-28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/636023/d9361cf5-5c49-ecba-11f3-8950db543ca3.png)

今回は「経済」「国際」「政治」「エンタメ」「スポーツ」の５つのカテゴリに絞ってクロス集計してみます。

以下今回のsql文です。

```sql
select
  date_format(published_at, '%Y-%m-%d') as 年月日,
  date_format(published_at, '%H') as 時間,
  count(case when category='経済' then 1 else null end) as 経済,
  count(case when category='国際' then 1 else null end) as 国際,
  count(case when category='政治' then 1 else null end) as 政治,
  count(case when category='エンタメ' then 1 else null end) as エンタメ,
  count(case when category='スポーツ' then 1 else null end) as スポーツ
from 
  articles
group by 
  年月日, 時間
with rollup
```
表には「年月日」、「時間」、それぞれのカテゴリを表示させたいので、`select`ですべてのフィールドを選んでいきます。
それぞれのカテゴリごとのニュース数は`count`で集計します。`count`の中は`case`式で条件を指定しており、categoryが該当のカテゴリ名であれば「1」、そうでなければnullにし、それらをカウントするという形でニュース数を数えています。
また選択するフィールドは`as`によってエイリアスを指定しています。エイリアスを指定することでフィールドの見出しもスッキリし、年月日と時間については`group by`でグループ化を指定する際に簡単に書くことができます。

以下実行結果です。

```
+------------+--------+--------+--------+--------+--------------+--------------+
| 年月日     | 時間   | 経済   | 国際   | 政治   | エンタメ     | スポーツ     |
+------------+--------+--------+--------+--------+--------------+--------------+
| 2020-05-23 | 06     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-23 | NULL   |      0 |      0 |      0 |            0 |            0 |
| 2020-05-24 | 11     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-24 | 19     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-24 | 20     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-24 | NULL   |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 03     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 08     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 10     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 11     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 12     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 13     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 15     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 17     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 19     |      0 |      0 |      1 |            0 |            0 |
| 2020-05-25 | 20     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 21     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | 22     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-25 | NULL   |      0 |      0 |      1 |            0 |            0 |
| 2020-05-26 | 00     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-26 | 05     |      0 |      0 |      3 |            0 |            0 |
| 2020-05-26 | 06     |      0 |      0 |      3 |            0 |            0 |
| 2020-05-26 | 07     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-26 | 08     |      0 |      0 |      2 |            0 |            0 |
| 2020-05-26 | 09     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-26 | 10     |      0 |      0 |      1 |            0 |            0 |
| 2020-05-26 | 11     |      0 |      0 |      1 |            0 |            0 |
| 2020-05-26 | 12     |      0 |      0 |      1 |            0 |            0 |
| 2020-05-26 | 14     |      0 |      0 |      1 |            0 |            0 |
| 2020-05-26 | 15     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-26 | 16     |      0 |      0 |      1 |            0 |            0 |
| 2020-05-26 | 17     |      0 |      1 |      3 |            0 |            0 |
| 2020-05-26 | 18     |      0 |      0 |      1 |            0 |            0 |
| 2020-05-26 | 19     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-26 | 20     |      0 |      0 |      1 |            0 |            0 |
| 2020-05-26 | 21     |      0 |      2 |      2 |            0 |            0 |
| 2020-05-26 | 22     |      0 |      0 |      3 |            0 |            2 |
| 2020-05-26 | 23     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-26 | NULL   |      0 |      3 |     23 |            0 |            2 |
| 2020-05-27 | 00     |      1 |      1 |      0 |            1 |            0 |
| 2020-05-27 | 01     |      1 |      0 |      0 |            1 |            0 |
| 2020-05-27 | 02     |      0 |      0 |      0 |            1 |            0 |
| 2020-05-27 | 03     |      0 |      0 |      1 |            1 |            1 |
| 2020-05-27 | 04     |      1 |      0 |      3 |            1 |            0 |
| 2020-05-27 | 05     |      5 |      0 |      0 |            1 |            1 |
| 2020-05-27 | 06     |      6 |      1 |      3 |           11 |            9 |
| 2020-05-27 | 07     |      1 |      0 |      1 |            1 |            5 |
| 2020-05-27 | 08     |      2 |      1 |      3 |            3 |            6 |
| 2020-05-27 | 09     |      3 |      0 |      0 |            6 |            2 |
| 2020-05-27 | 10     |      1 |      5 |      2 |            6 |            3 |
| 2020-05-27 | 11     |      6 |      4 |      4 |            9 |            9 |
| 2020-05-27 | 12     |      2 |      5 |      2 |           15 |           13 |
| 2020-05-27 | 13     |      3 |      5 |      1 |            9 |            5 |
| 2020-05-27 | 14     |      1 |      4 |      4 |            3 |            6 |
| 2020-05-27 | 15     |      0 |      3 |      0 |            5 |            0 |
| 2020-05-27 | 16     |      0 |      1 |      1 |            0 |            1 |
| 2020-05-27 | 17     |      0 |      1 |      3 |            0 |            1 |
| 2020-05-27 | 18     |      1 |      1 |      1 |            0 |            0 |
| 2020-05-27 | 19     |      1 |      1 |      2 |            6 |            1 |
| 2020-05-27 | 20     |      2 |      4 |      4 |            7 |            3 |
| 2020-05-27 | 21     |      4 |      4 |      1 |            5 |            1 |
| 2020-05-27 | 22     |      0 |      2 |      1 |            6 |            4 |
| 2020-05-27 | 23     |      0 |      0 |      1 |            2 |            2 |
| 2020-05-27 | NULL   |     41 |     43 |     38 |          100 |           73 |
| 2020-05-28 | 00     |      0 |      2 |      0 |            3 |            3 |
| 2020-05-28 | 01     |      0 |      2 |      1 |            2 |            0 |
| 2020-05-28 | 02     |      1 |      0 |      0 |            1 |            1 |
| 2020-05-28 | 03     |      1 |      1 |      0 |            3 |            0 |
| 2020-05-28 | 04     |      1 |      2 |      0 |            3 |            0 |
| 2020-05-28 | 05     |      6 |      1 |      0 |            5 |            5 |
| 2020-05-28 | 06     |     10 |      4 |      4 |           14 |            8 |
| 2020-05-28 | 07     |      1 |      4 |      0 |           10 |            4 |
| 2020-05-28 | 08     |      7 |      4 |      5 |            9 |           14 |
| 2020-05-28 | 09     |      4 |      2 |      1 |            9 |           17 |
| 2020-05-28 | 10     |      3 |      2 |      1 |           17 |            6 |
| 2020-05-28 | 11     |      0 |      4 |      5 |           10 |           15 |
| 2020-05-28 | 12     |      0 |      2 |      0 |            9 |            7 |
| 2020-05-28 | 14     |      0 |      0 |      0 |            0 |            0 |
| 2020-05-28 | 15     |      1 |      1 |      0 |            0 |            0 |
| 2020-05-28 | 16     |      0 |      0 |      0 |            0 |            2 |
| 2020-05-28 | 17     |      0 |      0 |      0 |            0 |            2 |
| 2020-05-28 | 18     |      0 |      1 |      0 |            0 |            0 |
| 2020-05-28 | 19     |      0 |      2 |      0 |            0 |            0 |
| 2020-05-28 | 20     |      0 |      0 |      0 |            2 |            0 |
| 2020-05-28 | 21     |      0 |      0 |      0 |            1 |            0 |
| 2020-05-28 | NULL   |     35 |     34 |     17 |           98 |           84 |
| NULL       | NULL   |     76 |     80 |     79 |          198 |          159 |
+------------+--------+--------+--------+--------+--------------+--------------+
```

クロス集計できました。

#課題
クロス集計は一応できましたが、今回はカテゴリを5つのみに絞って修正しています。現実のデータの種類というのはもっと多いことがあるはずです。（ちなみに今回のcategoryは20種類以上あります）
その種類の数だけ先程のsql文を打つのは相当めんどくさいですし、間違いも起こります。
グループ化するデータの種類の数だけきちんと出力でき、かつ簡便な方法を見つけたいと思います。