0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

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

Posted at

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

#ピボットテーブルで表す
今回は以下のように、ニュース記事が発行された時間が「published_at」としてスプレッドシート、mysqlにあらかじめ保存されています。
Screenshot from 2020-05-28 19-06-05.png

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

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

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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?