#はじめに
今回はこちらの続きとなります。
前回の記事では、とりあえず新着したニュースを数えただけでしたが、今回はそれに加えてニュースのカテゴリごとに新着数を出した表を作りたいと思います。
googleスプレッドシートのピボットテーブルでは以下のように作成されます。
ピボットテーブルを使えば、追加で列要素にカテゴリを選択することで簡単に表を作ることができます。
このように行と列でデータをグループ化して表にしたものを「クロス集計」といいます。
今回はmysqlでクロス集計をするという内容になります。
こちらの記事を参考にさせていただきました。
#mysqlでクロス集計
DBにはあらかじめニュースの新着日時が「published_at」で、カテゴリが「category」として保存されています。
今回は「経済」「国際」「政治」「エンタメ」「スポーツ」の５つのカテゴリに絞ってクロス集計してみます。
以下今回の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文を打つのは相当めんどくさいですし、間違いも起こります。
グループ化するデータの種類の数だけきちんと出力でき、かつ簡便な方法を見つけたいと思います。