はじめに

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

ピボットテーブルで表す

今回は以下のように、ニュース記事が発行された時間が「published_at」としてスプレッドシート、mysqlにあらかじめ保存されています。

1日のうちで、一時間ごとの新着記事数を表したいので、「published_at　年-月-日」、「published_at　時」を行要素として、「published_at」のCOUNTAを値としてテーブルを作成します。

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

mysqlでピボットテーブルを再現する

以下はピボットテーブルを再現する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でフォーマットするときに日時を一緒にすることで簡略的にほしい結果を得ることもできます。

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が使えませんが、先程までと同様の結果が得られています。