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

# とある日
あるテーブルにおいて

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

#### 一度サブクエリで今回削除したい条件に当てはまるレコードを取得
```sql
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順に擬似的に番号を振る
```sql
(
    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**
指定されたカラムの値でまとめて、並び替えを行います。
（[公式リファレンス](https://www.postgresql.jp/document/9.4/html/tutorial-window.html)の例を見たほうが理解が早そうです。）

今回は、`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）でないもの以外を取得
``` sql
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超初心者の勉強](https://programmer-life.work/sql/postgresql-row-number)
[PostgreSQL 13.1文書 3.5. ウィンドウ関数](https://www.postgresql.jp/document/13/html/tutorial-window.html)