MySQLでID毎に最新TIMESTAMPをもつレコードを抽出する

MySQLでID毎に最新TIMESTAMPをもつレコードを抽出する

MySQLで、ID毎に最新のTIMESTAMPを持つレコードを抽出する方法を紹介します。

具体的には、商品ID / ステータス / 更新時間の3列を持つテーブルを想定します。ある商品IDに対してステータスが変化したときのTIMESTAMPを持つテーブルです。

このテーブルに対して、商品ID毎に最新のステータスを取得することを目指します。

言葉では伝わりにくいので、下記のイメージです。

mysql> select * from test_db.test_table;
+---------+---------+---------------------+
| item_id | status | updated_time |
+---------+---------+---------------------+
| 1 | ordered | 2021-01-01 10:00:00 |
| 1 | shipped | 2021-01-02 10:00:00 |
| 1 | arrived | 2021-01-02 20:00:00 |
| 2 | ordered | 2021-03-01 09:00:00 |
| 2 | shipped | 2021-03-02 20:00:00 |
| 3 | ordered | 2021-04-01 10:00:00 |
+---------+---------+---------------------+
mysql> ここで処理して、最新ステータスだけとる
+---------+---------+---------------------+
| item_id | status | updated_time |
+---------+---------+---------------------+
| 1 | arrived | 2021-01-02 20:00:00 |
| 2 | shipped | 2021-03-02 20:00:00 |
| 3 | ordered | 2021-04-01 10:00:00 |
+---------+---------+---------------------+

結論

最初から結論です。

利用しているMySQLのバージョンに応じて、2パターン紹介します。

Window関数を利用する(MySQL8.0以降)

これはMySQL8.0以降で利用可能な書き方です。ようやくMySQLでもWindow関数が利用できるようになりましたね。

select item_id, status, updated_time
from ( select item_id, status, updated_time,
      RANK() over(partition by item_id order by updated_time DESC) as rnk
  from test_db.test_table
) tmp
where rnk=1
;

順位付けしたテーブルを作成して、外側から条件で最新レコードだけに絞っています。

ここは下記に説明を書いているので、もしよかったらご覧ください。

INNER JOINする

この方法だとMySQL8.0以前でも可能です。

select origin.item_id, origin.status, origin.updated_time
from test_db.test_table as origin
inner join ( select item_id, max(updated_time) as max_timestamp from test_db.test_table group by item_id
) tmp
ON origin.item_id = tmp.item_id
AND origin.updated_time = tmp.max_timestamp
;

最大のTIMESTAMPを持つレコードだけ抽出したテーブルを作成し、元のテーブルとINNER JOINします。

Window関数とJOINの処理はどちらが重いんだろうか、、、?

参考コードなど

ここから先は、上記の説明になります。

コードだけ見れたら十分という方は読まなくても結構です!

テーブル生成コマンド

今回サンプルで利用したテーブルの生成文を紹介しておきます。

-- テーブルの作成
create table test_db.test_table( item_id INTEGER, status VARCHAR(20), updated_time TIMESTAMP
)
;
-- 値の挿入
insert into test_db.test_table (item_id, status, updated_time) values (1, 'ordered', '2021-01-01 10:00:00'), (1, 'shipped', '2021-01-02 10:00:00'), (1, 'arrived', '2021-01-02 20:00:00'), (2, 'ordered', '2021-03-01 09:00:00'), (2, 'shipped', '2021-03-02 20:00:00'), (3, 'ordered', '2021-04-01 10:00:00')
;

Window関数部分の説明

Window関数の特徴は下記です。

  • 集約関数と似ているが、集約関数では対象の行が1行に集約されるのに対し、Window関数ではすべての行が残る
  • 行ごとに単一の値を返す
  • 各行に対してテーブル全体(関係する行)に走査するような処理が行われる

言葉だけではわかりにくいので、上記のWindow関数部分だけ取り出して、処理を確認してみます。

mysql> select *, RANK() over(partition by item_id order by updated_time DESC) as rnk from test_db.test_table;
+---------+---------+---------------------+-----+
| item_id | status | updated_time | rnk |
+---------+---------+---------------------+-----+
| 1 | arrived | 2021-01-02 20:00:00 | 1 |
| 1 | shipped | 2021-01-02 10:00:00 | 2 |
| 1 | ordered | 2021-01-01 10:00:00 | 3 |
| 2 | shipped | 2021-03-02 20:00:00 | 1 |
| 2 | arrived | 2021-03-01 09:00:00 | 2 |
| 3 | ordered | 2021-04-01 10:00:00 | 1 |
+---------+---------+---------------------+-----+

各行に対して、rnkという列が追加されていることが確認できます。

partition by <column>でグループ分けの基準となるカラムを指定し、order byで並び替え、RANK()関数で数値付けしています。

RANK()以外のWindows関数

RANK()関数以外にも利用できるWindows関数は複数あります。

利用可能な関数は、SUM(), MAX(), MIN(), AVG(), COUNT(), DENSE_RANK(), ROW_NUMBER()...などです。

例えばid毎にval列の合計値を計算したいときは下記の処理になります。

SELECT id, SUM(val) OVER (PARTITION BY id)
FROM target_table

まとめ

MySQLで、ID毎に最新のTIMESTAMPを持つレコードを抽出する方法を2パターン紹介しました。

私はWindow関数の利用方法を忘れがちです。たまにはこのように復習することが重要ですね。