【MySQL】ログイン日時を1時間ごとにグループ化して、直近の時間帯にログインしたユーザーレコードを取得する方法

はじめに

MySQLでは、サブクエリとJOIN条件を組み合わせることで、ユーザーのログイン日時を1時間単位でグループ化し、各時間帯の中で最も新しい（直近の）ログインレコードだけを抽出することができます。本記事では、その具体的な手法をサンプルコードとともに解説します。

基本となるクエリの構文

まず、基本となる構文を見てみましょう。サブクエリで時間ごとの最大タイムスタンプを取得し、それをもとに元のテーブルと結合します。

SELECT yourTablevariableName.*
FROM
(
   SELECT MAX(UNIX_TIMESTAMP(yourDateTimeColumnName)) AS anyAliasName
   FROM getLatestHour
   GROUP BY HOUR(UserLoginDateTime)
) yourOuterVariableName
JOIN yourTableName yourTablevariableName
ON UNIX_TIMESTAMP(yourDateTimeColumnName) = yourOuterVariableName.yourAliasName
WHERE DATE(yourDateTimeColumnName) = 'yourDateValue';

サンプルテーブルの作成

実際に動作を確認するため、テスト用のテーブルを作成しましょう。ここでは、ユーザーID・ユーザー名・ログイン日時を持つ「getLatestHour」テーブルを作成します。

mysql> create table getLatestHour
   -> (
   -> UserId int,
   -> UserName varchar(20),
   -> UserLoginDateTime datetime
   -> );
Query OK, 0 rows affected (0.68 sec)

テストデータの挿入

次に、INSERT文を使ってユーザーのログイン記録を登録します。あえて異なる時間帯に分散させたデータを投入することで、グループ化の効果がわかりやすくなります。

mysql> insert into getLatestHour values(100,'John','2019-02-04 10:55:51');
Query OK, 1 row affected (0.27 sec)
mysql> insert into getLatestHour values(101,'Larry','2019-02-04 12:30:40');
Query OK, 1 row affected (0.16 sec)
mysql> insert into getLatestHour values(102,'Carol','2019-02-04 12:40:46');
Query OK, 1 row affected (0.20 sec)
mysql> insert into getLatestHour values(103,'David','2019-02-04 12:44:54');
Query OK, 1 row affected (0.17 sec)
mysql> insert into getLatestHour values(104,'Bob','2019-02-04 12:47:59');
Query OK, 1 row affected (0.15 sec)

登録データの確認

SELECT文ですべてのレコードを表示して、正しく登録されているか確認します。

mysql> select *from getLatestHour;

実行結果は以下の通りです。10時台に1件、12時台に4件のログイン記録があることがわかります。

+--------+----------+---------------------+
| UserId | UserName | UserLoginDateTime   |
+--------+----------+---------------------+
|    100 | John     | 2019-02-04 10:55:51 |
|    101 | Larry    | 2019-02-04 12:30:40 |
|    102 | Carol    | 2019-02-04 12:40:46 |
|    103 | David    | 2019-02-04 12:44:54 |
|    104 | Bob      | 2019-02-04 12:47:59 |
+--------+----------+---------------------+
5 rows in set (0.00 sec)

1時間ごとにグループ化して直近のレコードを取得する

いよいよ本題のクエリです。HOUR()関数でログイン時刻の「時」部分を抽出してグループ化し、各グループ内でMAX()関数により最新のタイムスタンプを特定します。その結果と元テーブルをJOINすることで、各時間帯の最新レコードのみを取得できます。

mysql> SELECT tbl1.*
   -> FROM (
   -> SELECT MAX(UNIX_TIMESTAMP(UserLoginDateTime)) AS m1
   -> FROM getLatestHour
   -> GROUP BY HOUR(UserLoginDateTime)
   -> ) var1
   -> JOIN getLatestHour tbl1
   -> ON UNIX_TIMESTAMP(UserLoginDateTime) = var1.m1
   -> WHERE DATE(UserLoginDateTime) = '2019-02-04';

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

+--------+----------+---------------------+
| UserId | UserName | UserLoginDateTime   |
+--------+----------+---------------------+
|    100 | John     | 2019-02-04 10:55:51 |
|    104 | Bob      | 2019-02-04 12:47:59 |
+--------+----------+---------------------+
2 rows in set (0.05 sec)

結果の解説

出力結果を見ると、10時台にログインしたJohn（10:55:51）と、12時台に最後にログインしたBob（12:47:59）の2件が取得されています。12時台にはLarry、Carol、Davidもログインしていますが、それぞれより後の時刻に他のユーザーがログインしているため除外され、各時間帯で最も新しいレコードのみが表示されます。

このように、GROUP BYとHOUR()関数、そしてUNIX_TIMESTAMP()を組み合わせたサブクエリ＋JOINのテクニックを使えば、「時間単位での最新レコード抽出」というよくある要件をシンプルなSQLで実現できます。ログ分析やアクセス履歴の集計など、さまざまな場面で応用できるので、ぜひ覚えておきましょう。