はじめに

こんにちは、ルーターのmiyakawaです。

データ集計時、どのような傾向が見られるかを確認する際に日付ごとに分類することがあります。職務ではスクレイピングしているデータの取得日時を日付ごとに集計することで新しい傾向が見えるかもしれません。 今回はMySQLとPostgreSQLで日付ごとに集計する方法を紹介します。

MySQLとPostgreSQLのバージョン

MySQLのサーバーバージョンになります。

MariaDB [(none)]> SELECT version();
+-----------------+
| version()       |
+-----------------+
| 10.6.15-MariaDB |
+-----------------+
1 row in set (0.002 sec)

PostgreSQLのサーバーバージョンになります。

test=# SELECT version();
                                                             version                                                             
---------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.10 (Homebrew) on x86_64-apple-darwin22.6.0, compiled by Apple clang version 14.0.3 (clang-1403.0.22.14.1), 64-bit
(1 row)

使用するテーブル

MySQLの場合、使用するテーブルは以下のSQLで作成します。

CREATE TABLE table_name (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  created_at DEFAULT CURRENT_TIMESTAMP(),
  updated_at DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP
);

また、PostgreSQLの場合での使用するテーブルは以下のSQLで作成します。

CREATE TABLE table_name (
  id SERIAL NOT NULL PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

MySQLではidカラムにAUTO_INCREMENTを設定してテーブルを作成しています。PostgreSQLではidカラムはテーブル作成時にSERIAL型で作成したものになります。また日時を表す型はMySQLの場合はDATETIME型になりますが、PostgreSQLの場合はTIMESTAMP型になります。 ここでcreated_atカラムで日付ごとに集計します。

日付ごとに集計する方法

MySQLの場合

日付で集計するSQLは以下の通りです。

SELECT DATE_FORMAT(table_name.created_at, "%Y-%m-%d") AS cnt_date,
COUNT(1)
FROM table_name
GROUP BY cnt_date
ORDER BY cnt_date;

出力結果になります。

+------------+----------+
| cnt_date   | COUNT(1) |
+------------+----------+
| 2023-11-07 |     2517 |
| 2023-11-08 |     3175 |
| 2023-11-09 |     3132 |
| 2023-11-10 |     2535 |
| 2023-11-11 |     2509 |
| 2023-11-12 |     2978 |
| 2023-11-13 |     2759 |
| 2023-11-14 |     3131 |
| 2023-11-15 |     2761 |
| 2023-11-16 |     2985 |
+------------+----------+
10 rows in set (0.027 sec)

MySQLの場合はDATE_FORMAT関数を使用し、集計するcreated_atを日付情報に変換することで日付の集計を行います。ここでcreated_atカラムから%Yは年の情報を、%mは月の情報を、%dは日の情報をそれぞれ表示します。

PostgreSQLの場合

日付で集計するSQLは以下の通りです。

SELECT to_char(table_name.created_at, 'YYYY-MM-DD') AS cnt_date,
COUNT(1)
FROM table_name
GROUP BY cnt_date
ORDER BY cnt_date;

出力結果になります。

  cnt_date  | count 
------------+-------
 2023-11-07 |  2517
 2023-11-08 |  3175
 2023-11-09 |  3132
 2023-11-10 |  2535
 2023-11-11 |  2509
 2023-11-12 |  2978
 2023-11-13 |  2759
 2023-11-14 |  3131
 2023-11-15 |  2761
 2023-11-16 |  2985
(10 rows)

PostgreSQLの場合はto_char関数を使用し、集計するcreated_atを日付に変換します。to_charの場合は年の情報をYYYY、月の情報をMM、日の情報をDDとする必要があります。

この方法で日付ごとに集計ができます。またcnt_dateの形を日付ではなく、月ごとや時間帯に変更して集計することも可能です。

集計結果が0件の日付を集計する方法

現在紹介したSQL文で日付の集計を行うことができます。しかし、例えば2023/11/09のデータが存在しない場合は集計結果は以下の通りになります。

MariaDB [test]> SELECT DATE_FORMAT(table_name.created_at, "%Y-%m-%d") AS cnt_date,
    -> COUNT(1)
    -> FROM table_name
    -> GROUP BY cnt_date;
+------------+----------+
| cnt_date   | COUNT(1) |
+------------+----------+
| 2023-11-07 |     2517 |
| 2023-11-08 |     3175 |
| 2023-11-10 |     2535 |
| 2023-11-11 |     2509 |
| 2023-11-12 |     2978 |
| 2023-11-13 |     2759 |
| 2023-11-14 |     3131 |
| 2023-11-15 |     2761 |
| 2023-11-16 |     2985 |
+------------+----------+
9 rows in set (0.025 sec)

この集計結果には2023/11/09が集計テーブルから消えた状態で表示されます。ここではデータが存在しない日付に対して明示的に0を表示する方法を紹介します。

PostgreSQLの場合

PostgreSQLではGENERATE_SERIES関数があり、これを利用することでその場でカレンダーテーブルを作成できます。

SELECT to_char(
  GENERATE_SERIES('2023-11-07' :: DATE, '2023-11-16' :: DATE, '1 day'),
  'YYYY-MM-DD') AS cal_date;

以下実行結果になります。

  cal_date  
------------
 2023-11-07
 2023-11-08
 2023-11-09
 2023-11-10
 2023-11-11
 2023-11-12
 2023-11-13
 2023-11-14
 2023-11-15
 2023-11-16
(10 rows)

あとはこの作成したカレンダーテーブルと集計データを外部結合することでデータがない日付を表示することができます。値がない部分を0と表示すれば完成です。SQL文は以下の通りです。

SELECT c.cal_date AS cnt_date,
COALESCE(tn.count, 0) AS count
FROM (SELECT to_char(
    GENERATE_SERIES('2023-11-07' :: DATE, '2023-11-16' :: DATE, '1 day'),
    'YYYY-MM-DD') AS cal_date) c
LEFT OUTER JOIN(
  SELECT to_char(table_name.created_at, 'YYYY-MM-DD') AS cnt_date,
  COUNT(1) AS count
  FROM table_name
  GROUP BY cnt_date) tn
ON tn.cnt_date = c.cal_date;

以下、出力結果になります。正しくデータの存在しない日付の行も表示されています。

  cnt_date  | count 
------------+-------
 2023-11-07 |  2517
 2023-11-08 |  3175
 2023-11-09 |     0
 2023-11-10 |  2535
 2023-11-11 |  2509
 2023-11-12 |  2978
 2023-11-13 |  2759
 2023-11-14 |  3131
 2023-11-15 |  2761
 2023-11-16 |  2985
(10 rows)

MySQLの場合

MySQLの場合はPostgreSQLのようにGENERATE_SERIES関数はないです。そのため日付を表すカレンダーテーブルを作成する必要があります。カレンダーを作成したあとはPostgreSQLと同じようにカレンダーテーブルを外部結合すると集計は可能です。

SELECT c.cal_date AS cnt_date,
COALESCE(tn.count, 0) AS count
FROM calenders c
LEFT OUTER JOIN(
  SELECT to_char(table_name.created_at, 'YYYY-MM-DD') AS cnt_date,
  COUNT(1) AS count
  FROM table_name
  GROUP BY cnt_date) tn
ON tn.cnt_date = c.cal_date
ORDER BY c.cal_date;

以下、出力結果になります。正しくデータの存在しない日付の行も表示されています。

+------------+-------+
| cnt_date   | count |
+------------+-------+
| 2023-11-07 |  2517 |
| 2023-11-08 |  3175 |
| 2023-11-09 |     0 |
| 2023-11-10 |  2535 |
| 2023-11-11 |  2509 |
| 2023-11-12 |  2978 |
| 2023-11-13 |  2759 |
| 2023-11-14 |  3131 |
| 2023-11-15 |  2761 |
| 2023-11-16 |  2985 |
+------------+-------+
10 rows in set (0.140 sec)

終わりに

MySQLとPostgreSQLでの日付の集計方法とそれぞれのデータが存在しない日付を補完するデータ整形を行いました。PostgreSQLではSQLで仮想的なカレンダーテーブルを作成できるため、データが存在しない日付のデータを簡単に生成できますが、MySQLでは新たにテーブルを作成する必要があります。

データが存在しない日付を埋める必要がある場合は、PostgreSQLの利用を検討してみても良いかもしれません。