MySQLで結果を日付ごとにグループ化し、重複値の件数を表示するクエリの書き方

はじめに

MySQLでは、GROUP BY句とDATE()関数、COUNT()関数を組み合わせることで、日付単位でレコードをグループ化し、重複する値の件数を簡単に集計できます。ここでは、到着日時ごとに乗客コードを集計する例を通して、その具体的な方法を解説します。

1. サンプルテーブルの作成

まず、以下のSQLを実行してテーブルを作成します。

mysql> create table DemoTable1496
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> PassengerCode varchar(20),
-> ArrivalDate datetime
-> );
Query OK, 0 rows affected (0.85 sec)

このテーブルは、自動採番のId、乗客コードを格納するPassengerCode、到着日時を格納するArrivalDateの3つのカラムを持っています。

2. レコードの挿入

次に、INSERTコマンドを使ってテーブルにレコードを登録します。

mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('202','2013-03-12 10:12:34');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('202_John','2013-03-12 11:00:00');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('204','2013-03-12 10:12:34');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('208','2013-03-14 11:10:00');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('202','2013-03-18 12:00:34');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('202','2013-03-18 04:10:01');
Query OK, 1 row affected (0.15 sec)

3. 登録データの確認

SELECT文でテーブル内の全レコードを表示してみましょう。

mysql> select * from DemoTable1496;

実行結果は以下の通りです。

+----+---------------+---------------------+
| Id | PassengerCode | ArrivalDate         |
+----+---------------+---------------------+
|  1 | 202           | 2013-03-12 10:12:34 |
|  2 | 202_John      | 2013-03-12 11:00:00 |
|  3 | 204           | 2013-03-12 10:12:34 |
|  4 | 208           | 2013-03-14 11:10:00 |
|  5 | 202           | 2013-03-18 12:00:34 |
|  6 | 202           | 2013-03-18 04:10:01 |
+----+---------------+---------------------+
6 rows in set (0.00 sec)

4. 日付ごとにグループ化して重複件数を表示するクエリ

ここが本題です。DATE()関数で日付部分だけを抽出し、GROUP BYでグループ化、COUNT()で件数を集計します。さらにWHERE句とLIKE、CONCAT()を組み合わせることで、特定の乗客コード（この例では「202」を含むコード）に絞り込んでいます。

mysql> select date(ArrivalDate),count(ArrivalDate) from DemoTable1496
-> where PassengerCode like concat('%','202','%')
-> group by date(ArrivalDate);

実行結果は以下の通りです。

+-------------------+--------------------+
| date(ArrivalDate) | count(ArrivalDate) |
+-------------------+--------------------+
| 2013-03-12        |                  2 |
| 2013-03-18        |                  2 |
+-------------------+--------------------+
2 rows in set (0.00 sec)

結果の見方とポイント

上記の結果から、乗客コードに「202」を含むレコードは、2013年3月12日に2件、2013年3月18日に2件存在していることがわかります。なお、「202_John」もLIKE '%202%'の条件に一致するため、集計対象に含まれている点に注意してください。

ポイントをまとめると以下の通りです。

• DATE()関数：datetime型の値から日付部分のみを取り出します。時刻を無視して日単位で集計する際に必須です。
• GROUP BY date(ArrivalDate)：同じ日付のレコードを1つのグループにまとめます。
• COUNT(ArrivalDate)：各グループ内のレコード件数、つまり重複値の数を返します。
• LIKE CONCAT('%','202','%')：部分一致検索により、指定した文字列を含むコードだけを抽出します。

このように、GROUP BYと集計関数を組み合わせれば、日別の発生件数や重複データの傾向を簡単に把握できます。ログ解析やアクセス集計など、さまざまな場面で応用できるテクニックなので、ぜひ覚えておきましょう。