MySQLで、ID毎に最新のTIMESTAMPを持つレコードを抽出する方法を紹介します。
具体的には、商品ID / ステータス / 更新時間の３列を持つテーブルを想定します。ある商品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を持つレコードを抽出する方法を２パターン紹介しました。
私はWindow関数の利用方法を忘れがちです。たまにはこのように復習することが重要ですね。
