時系列に並んだ物から、個別の最新のデータだけを抜き出す SQL

at yasu 2023/08/04に更新

memo : ROW_NUMBER() OVER(PARTITION BY <column> ORDER BY <sort column> DESC)

ROW_NUMBER() 関数は OVER 句の内容でディメンション番号を付けて、ディメンション番号の順序番号を返す。

但書１ MySQL は 8.0 以上でないと使えない。
但書２ MariaDB は 10.0.2 以上でないと使えないっぽい。

at yasu 2023/08/04

例えば下記のようなテーブル構成のとき、ユーザ一覧に最終ログイン日付を表示させたいSQLを考える。

この場合 logins は複数行存在しており、最新のlogin_atだけを取り出したい。難しいのが単純に left join logins だけをしたとき、複数行あるので任意の行を取り出すので、期待したデータではなくなる。

Sample

サンプルとなるテーブルとデータの作成

mysql> CREATE TABLE users (
    ->     id BIGINT PRIMARY KEY,
    ->     name VARCHAR(255),
    ->     created_at TIMESTAMP,
    ->     deleted_at TIMESTAMP
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> CREATE TABLE logins (
    ->     id BIGINT PRIMARY KEY,
    ->     user_id BIGINT,
    ->     login_at DATETIME,
    ->     FOREIGN KEY (user_id) REFERENCES users(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

データの挿入

mysql> INSERT INTO users (id, name, created_at, deleted_at)
    -> VALUES (1, 'ユーザー1', '2023-08-01 00:00:00', NULL),
    ->        (2, 'ユーザー2', '2023-08-02 00:00:00', NULL),
    ->        (3, 'ユーザー3', '2023-08-03 00:00:00', NULL);
Query OK, 3 rows affected (0.02 sec)


mysql> INSERT INTO logins (id, user_id, login_at)
    -> VALUES (1, 1, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (2, 1, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (3, 2, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (4, 3, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (5, 2, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (6, 1, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (7, 1, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (8, 3, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (9, 1, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (10, 1, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (11, 2, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (12, 2, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY)),
    ->        (13, 3, DATE_ADD('2023-08-01', INTERVAL RAND() * 10 DAY));
Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> select * from logins;
+----+---------+---------------------+
| id | user_id | login_at            |
+----+---------+---------------------+
|  1 |       1 | 2023-08-02 00:00:00 |
|  2 |       1 | 2023-08-09 00:00:00 |
|  3 |       2 | 2023-08-08 00:00:00 |
|  4 |       3 | 2023-08-02 00:00:00 |
|  5 |       2 | 2023-08-07 00:00:00 |
|  6 |       1 | 2023-08-06 00:00:00 |
|  7 |       1 | 2023-08-10 00:00:00 |
|  8 |       3 | 2023-08-02 00:00:00 |
|  9 |       1 | 2023-08-06 00:00:00 |
| 10 |       1 | 2023-08-04 00:00:00 |
| 11 |       2 | 2023-08-01 00:00:00 |
| 12 |       2 | 2023-08-03 00:00:00 |
| 13 |       3 | 2023-08-09 00:00:00 |
+----+---------+---------------------+
13 rows in set (0.01 sec)

上記の状態の時、それぞれのユーザ id での最終ログイン日付は下記になる。

2023-08-10
2023-08-08
2023-08-09

at yasu 2023/08/04

単純な左結合

mysql> select
    ->   users.id,
    ->   users.name,
    ->   logins.login_at
    -> from users left join logins on users.id = logins.user_id;
+----+---------------+---------------------+
| id | name          | login_at            |
+----+---------------+---------------------+
|  1 | ユーザー1     | 2023-08-02 00:00:00 |
|  1 | ユーザー1     | 2023-08-09 00:00:00 |
|  1 | ユーザー1     | 2023-08-06 00:00:00 |
|  1 | ユーザー1     | 2023-08-10 00:00:00 |
|  1 | ユーザー1     | 2023-08-06 00:00:00 |
|  1 | ユーザー1     | 2023-08-04 00:00:00 |
|  2 | ユーザー2     | 2023-08-08 00:00:00 |
|  2 | ユーザー2     | 2023-08-07 00:00:00 |
|  2 | ユーザー2     | 2023-08-01 00:00:00 |
|  2 | ユーザー2     | 2023-08-03 00:00:00 |
|  3 | ユーザー3     | 2023-08-02 00:00:00 |
|  3 | ユーザー3     | 2023-08-02 00:00:00 |
|  3 | ユーザー3     | 2023-08-09 00:00:00 |
+----+---------------+---------------------+
13 rows in set (0.00 sec)

この状態から最新の物を取り出す ROW_NUMBER を使ってみる。

mysql> SELECT
    ->   users.id,
    ->   users.name,
    ->   newest_login.login_at
    -> FROM
    ->   users
    ->   left join (
    ->     select user_id, login_at from (
    ->       select user_id, login_at, 
    ->         ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_at DESC) as rn
    ->       from logins
    ->     ) tmp
    ->     where rn = 1
    ->   ) newest_login
    ->   on users.id = newest_login.user_id;
+----+---------------+---------------------+
| id | name          | login_at            |
+----+---------------+---------------------+
|  1 | ユーザー1     | 2023-08-10 00:00:00 |
|  2 | ユーザー2     | 2023-08-08 00:00:00 |
|  3 | ユーザー3     | 2023-08-09 00:00:00 |
+----+---------------+---------------------+
3 rows in set (0.01 sec)

at yasu 2023/08/04に更新

Explain Check

Explainで Indexが使われているか確認する。

Index を付ける前。SQL 文は長いので、前半のみ。

mysql> explain SELECT ...
+----+-------------+------------+------------+------+---------------+-------------+---------+-------------------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------------------+------+----------+----------------+
|  1 | PRIMARY     | users      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL              |    3 |   100.00 | NULL           |
|  1 | PRIMARY     | <derived3> | NULL       | ref  | <auto_key0>   | <auto_key0> | 17      | bt.users.id,const |    2 |   100.00 | NULL           |
|  3 | DERIVED     | logins     | NULL       | ALL  | NULL          | NULL        | NULL    | NULL              |   13 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------------------+------+----------+----------------+
3 rows in set, 2 warnings (0.01 sec)

Index の作成

mysql> create index user_id_with_logins_at_idx on logins(user_id,login_at);
Query OK, 0 rows affected (0.05 sec)

再実行をする。type, Key と Extra を見たら index, user_id_with_logins_at_idx, Using index; があり、使われている事が確認できる。

mysql> explain SELECT ...
+----+-------------+------------+------------+-------+---------------+----------------------------+---------+-------------------+------+----------+-----------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key                        | key_len | ref               | rows | filtered | Extra                       |
+----+-------------+------------+------------+-------+---------------+----------------------------+---------+-------------------+------+----------+-----------------------------+
|  1 | PRIMARY     | users      | NULL       | ALL   | NULL          | NULL                       | NULL    | NULL              |    3 |   100.00 | NULL                        |
|  1 | PRIMARY     | <derived3> | NULL       | ref   | <auto_key0>   | <auto_key0>                | 17      | bt.users.id,const |    2 |   100.00 | NULL                        |
|  3 | DERIVED     | logins     | NULL       | index | NULL          | user_id_with_logins_at_idx | 15      | NULL              |   13 |   100.00 | Using index; Using filesort |
+----+-------------+------------+------------+-------+---------------+----------------------------+---------+-------------------+------+----------+-----------------------------+
3 rows in set, 2 warnings (0.01 sec)