PC

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関数の利用方法を忘れがちです。たまにはこのように復習することが重要ですね。

  • この記事を書いた人

たかけの夫。IT系コンサルティング会社に勤務し、主にクラウドや機械学習に関するコンサルティング業務を行う三十路男。IT技術・投資に興味を持ち、家族と楽に人生を楽しみたい人。

-PC
-