とある日
あるテーブルにおいて
「あるカラムの値が一意（unique）でなければならなかったのに、DBのユニークインデックスが貼られていないことで一意になっていないレコード」
が大量に存在したことに気づきました。辛い。
一個一個消すとこの作業だけで寿命が尽きてしまいます。
そこで、不要なレコードは一気に消したいと思い、SQLを叩こうと思ったら意外と難しかったので記録に残します。
モデルの例
今回は中間テーブルによくある形を再現してみます。
Exampleモデル
| Column | Type | Options |
|---|---|---|
| id | integer | null: false |
| reference_a_id | integer | null: false |
| reference_b_id | integer | null: false |
| created_at | timestamp | null: false |
| updated_at | timestamp | null: false |
実際に起きていたこと
事の発端
Exampleモデル設計時には、reference_aカラムにunique: trueがかかっていませんでした。
その結果、以下のようなデータが生成されました。
| id | reference_a_id | reference_b_id | created_at | updated_at |
|---|---|---|---|---|
| 1 | 1 | 1 | 2022-06-06 06:55:17.660699 | 2016-06-06 06:55:17.660699 |
| 2 | 2 | 2 | 2022-06-07 06:55:17.660699 | 2016-06-07 06:55:17.660699 |
| 3 | 3 | 3 | 2022-06-08 06:55:17.660699 | 2016-06-08 06:55:17.660699 |
| 4 | 1 | 2 | 2022-06-09 06:55:17.660699 | 2016-06-09 06:55:17.660699 |
| 5 | 2 | 4 | 2022-06-09 06:55:17.660699 | 2016-06-09 06:55:17.660699 |
しかし、あとあと考えると 「reference_a_idに一意性制約必要じゃん！」 ということに気づきます。
そこで、reference_a_idに、unique: trueを付与しようとします。
Exampleモデル
| Column | Type | Options |
|---|---|---|
| id | integer | null: false |
| reference_a_id | integer | null: false, unique: true |
| reference_b_id | integer | null: false |
| created_at | timestamp | null: false |
| updated_at | timestamp | null: false |
しかし、もうお分かりのように元のデータが制約に引っかかるため、変更することができません。
今回はidの組み合わせで[1, 4]と[2, 5]とが該当しますね。
そこで、同じreference_a_idを持つレコードは、最新のレコードを残してあとは全て削除することにしました。
対応策の概要
今回の例では2つ削除すれば問題ないですが、これが1000, 2000とあったら収拾がつきません。
そこで「同じreference_a_idを持つレコードの中で、最新以外のレコードを削除する」ためのSQLを書く必要がありました。
つまり
あるカラムの値が同じレコードをグループ化して、そのグループ内で最新のレコードだけを残し、他は削除するという事です。
この中で、グループ化はできたものの、「そのグループ内で最新のレコードを取得する」のところでひっかかりました。
実際のSQL
先に、実際どういうSQLを作ったのか結論を言います。
SELECT文
select
examples.id
from
(
select
id,
row_number() over(PARTITION BY reference_a_id order by updated_at desc) as reference_a_order
from
examples
where
reference_a_id in(
select
reference_a_id
from
examples
group by
reference_a_id
having count(reference_a_id) > 1
)
) examples
where
examples.reference_a_order!= 1
DELETE文
delete
from
examples
where
id in(
select
examples.id
from
(
select
id,
row_number() over(PARTITION BY reference_a_id order by updated_at desc) as reference_a_order
from
examples
where
reference_a_id in(
select
reference_a_id
from
examples
group by
reference_a_id
having count(reference_a_id) > 1
)
) examples
where
examples.reference_a_order != 1
)
解説
このSQLで実行していることを一言で言うと
一度サブクエリで今回削除したい条件に当てはまるレコードを取得し、同じreference_a_idを持つレコード群ごとにグループを作り、そのグループ内でupdate_at順に擬似的に番号を振って、その番号が最新（=1）でないもの以外を取得、削除する。
息が切れちゃうような文章です。細かく説明していきます。
順番に見ていく
以下の順番で整理していきます。
- 一度サブクエリで今回削除したい条件に当てはまるレコードを取得
- 同じ
reference_a_idを持つレコード群ごとにグループを作り、そのグループ内でupdate_at順に番号を振る - その番号が最新（=1）でないもの以外を取得、削除する。
一度サブクエリで今回削除したい条件に当てはまるレコードを取得
select
reference_a_id
from
examples
group by
reference_a_id
having count(reference_a_id) > 1
group by ... having count(カラム)で、今回取得したい、reference_a_idの値が同じレコードを複数持つレコードだけを抽出します。
同じreference_a_idを持つレコード群ごとにグループを作り、そのグループ内でupdate_at順に擬似的に番号を振る
(
select
id,
row_number() over(PARTITION BY reference_a_id order by updated_at desc) as reference_a_order
from
examples
where
reference_a_id in(
select
reference_a_id
from
examples
group by
reference_a_id
having count(reference_a_id) > 1
)
) examples
ここが今回のキモですね。
ここでは、大切な文法が二つあります。
-
row_number() over() as xxx
overの引数で指定されている順番に番号を振って、as以下の変数で利用できるようにします。 -
PARTITION BY
指定されたカラムの値でまとめて、並び替えを行います。
（公式リファレンスの例を見たほうが理解が早そうです。）
今回は、reference_a_idごとにupdated_atが新しい順に並び替えたい && グループ化するカラムがreference_a_idなので、上記のような記述になります。
取得されるレコードは以下のようになります。
| id | reference_a_id | reference_b_id | created_at | updated_at | reference_a_order |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2022-06-06 06:55:17.660699 | 2016-06-06 06:55:17.660699 | 2 |
| 4 | 1 | 2 | 2022-06-09 06:55:17.660699 | 2016-06-09 06:55:17.660699 | 1 |
| 2 | 2 | 2 | 2022-06-07 06:55:17.660699 | 2016-06-07 06:55:17.660699 | 2 |
| 5 | 2 | 4 | 2022-06-09 06:55:17.660699 | 2016-06-09 06:55:17.660699 | 1 |
その番号が最新（=1）でないもの以外を取得
select
examples.id
from
(
select
id,
row_number() over(PARTITION BY reference_a_id order by updated_at desc) as reference_a_order
from
examples
where
reference_a_id in(
select
reference_a_id
from
examples
group by
reference_a_id
having count(reference_a_id) > 1
)
) examples
where
examples.reference_a_order!= 1
reference_a_orderが擬似的にレコードに番号を振ってくれているので、その番号が1ではない（= 最新のレコード以外）を取得します。
取得されるレコードは以下のようになります。
| id | reference_a_id | reference_b_id | created_at | updated_at | reference_a_order |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 2022-06-06 06:55:17.660699 | 2016-06-06 06:55:17.660699 | 2 |
| 2 | 2 | 2 | 2022-06-07 06:55:17.660699 | 2016-06-07 06:55:17.660699 | 2 |
これで、最初の目的に沿ったレコードだけを取得することができるようになりました！
あとは、UPDATEするなりDELETEするなり自由に利用できるかと思います。
お疲れ様でした！
最後に
これで寿命が尽きる前にレコードを削除できるようになりました。
危うく、アニメ版SPYxFAMILYの第2クールを見逃したまま、後悔してこの世を去るところでした。
（決定おめでとうございます！めっちゃ楽しみ。）
参考
PostgreSQLで出力結果に番号を振りたい！ SQL超初心者の勉強
PostgreSQL 13.1文書 3.5. ウィンドウ関数