LoginSignup
11
4

More than 1 year has passed since last update.

@yuki-b4(齋藤 祐希)in株式会社リーディングマーク

【PostgresSQL】同じ値を持つレコードのグループから、最新のレコードを残して他は全て削除する。

Posted at

とある日

あるテーブルにおいて

「あるカラムの値が一意（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）でないもの以外を取得、削除する。

息が切れちゃうような文章です。細かく説明していきます。

順番に見ていく

以下の順番で整理していきます。

  1. 一度サブクエリで今回削除したい条件に当てはまるレコードを取得
  2. 同じreference_a_idを持つレコード群ごとにグループを作り、そのグループ内でupdate_at順に番号を振る
  3. その番号が最新（=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. ウィンドウ関数

11
4
4

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
11
4