【SQL】自己結合で重複を削除し、最新のレコードを取得する方法

同一テーブルで重複するカラムがある中で、その重複を削除した上で、ある条件に合致したレコードを取得する方法を勉強したため備忘録として残すもの。

実装方法

ユーザーテーブル(Users)

id	name	Age	gender	created_At
1	Williams	10	men	2023-03-15 08:30:00
2	Bob	23	men	2023-04-22 14:15:00
3	Alice	21	women	2023-05-05 19:45:00
4	Johnson	27	men	2023-06-10 10:00:00
5	Emma	17	women	2023-07-18 21:30:00
6	Brown	18	men	2023-08-03 11:45:00
7	Williams	25	men	2023-09-12 16:15:00
8	Alice	30	women	2023-10-25 23:00:00
9	Emma	22	women	2023-11-07 05:30:00
10	Jones	20	men	2023-12-30 12:15:00

上記のようなレコードがあったとする。

このレコードでは名前が重複している。
しかし、
「名前の重複を削除した上で、名前ごとに一番新しいレコードを取得する」
という要件があったとする。

この要件に応えるためのクエリについて解説したいと思う。

まず、日時が一番新しい名前を、重複を削除した上で取得する

このためには「MAX関数」と「group by」を組み合わせることで取得できる

SELECT name, MAX(created_at) AS max_created_at
FROM Users
GROUP BY name;

上記のクエリの結果が以下になる。

name	created_At
Bob	2023-04-22 14:15:00
Johnson	2023-06-10 10:00:00
Brown	2023-08-03 11:45:00
Williams	2023-09-12 16:15:00
Alice	2023-10-25 23:00:00
Emma	2023-11-07 05:30:00
Jones	2023-12-30 12:15:00

名前の重複が削除されており、最新の日時のデータが残っている。
これのデータを利用することから、上記のクエリをサブクエリとして使用することになる。

次に自己結合を行い、結合条件をnameとcreated_atに指定する。

SELECT Users.*
JOIN (
    SELECT name, MAX(created_at) AS max_created_at
    FROM Users
    GROUP BY name
) subUsers ON Users.name = subUsers.name AND Users.created_at = subUsers.created_at;

結合する条件を

ON Users.name = subUsers.name AND Users.created_at = subUsers.created_at

とすることで、「サブクエリと同じ名前かつ、サブクエリで取得した名前ごとの最新日時と一致するレコードと結合」する挙動になる。

このクエリによって、以下の結果になる。

id	name	Age	gender	created_At
2	Bob	23	men	2023-04-22 14:15:00
4	Johnson	27	men	2023-06-10 10:00:00
6	Brown	18	men	2023-08-03 11:45:00
7	Williams	25	men	2023-09-12 16:15:00
8	Alice	30	women	2023-10-25 23:00:00
9	Emma	22	women	2023-11-07 05:30:00
10	Jones	20	men	2023-12-30 12:15:00

まとめ

自己結合の使い所がいまいち理解していなかったが、実務で使う必要があったため自己結合について少しは理解が進んだ。