タスクが重複起動してデータが二重登録された！新しいほうのレコードだけ削除するSQL

あ、まずい…重複してる

バッチ処理を手動で叩いたら、自動起動と被ってしまった。
ログを見ると、同じ work_id のデータが2件ずつ入っている。

id=1, captured_at=2024-03-01 09:00:05  ← これを残したい
id=1, captured_at=2024-03-01 09:00:47  ← これを消したい（新しいほう）

やりたいことは、同じ id で同じ時間帯（時単位）に2件以上あるものは、新しいほうを削除したい。

想定環境

• PostgreSQL（DATE_TRUNC を使用）

テーブル定義

サンプルとしてテーブル定義とサンプルデータを付けましたので、興味ある方は問題としてチャレンジしてみてください。

CREATE TABLE task_results (
    id           INTEGER      NOT NULL,
    captured_at  TIMESTAMP    NOT NULL,
    task_name    VARCHAR(100) NOT NULL,
    result_value INTEGER
);

サンプルデータ

以下のCSVをインポートして試してみてください。

id,captured_at,task_name,result_value
1,2024-03-01 09:00:05,batch_aggregate,1500
1,2024-03-01 09:00:47,batch_aggregate,1500
2,2024-03-01 09:01:12,batch_aggregate,2300
2,2024-03-01 09:01:58,batch_aggregate,2300
3,2024-03-01 10:00:08,batch_report,800
4,2024-03-01 10:00:33,batch_report,1200
4,2024-03-01 10:00:59,batch_report,1200
5,2024-03-01 10:01:20,batch_report,3400

この時点でのデータ状態:

id が 1・2・4 のデータが同じ時間帯（同じ「時」）に2件ずつある状態。

まず SELECT で確認する

SELECT
    id
    , captured_at
    , ROW_NUMBER() OVER (
        PARTITION BY
            id
            , DATE_TRUNC('hour', captured_at)
        ORDER BY
            captured_at DESC
    ) AS rn
FROM
    task_results
ORDER BY
    id, captured_at;

結果イメージ:

rn = 1 が最新レコード。ここから「2件以上あるグループの rn = 1」だけを削除すればOKです。

DELETE文

DELETE 
FROM
    task_results 
WHERE
    (id, captured_at) IN ( 
        SELECT
            id
            , captured_at 
        FROM
            ( 
                SELECT
                    id
                    , captured_at
                    , ROW_NUMBER() OVER ( 
                        PARTITION BY
                            id
                            , DATE_TRUNC('hour', captured_at) 
                        ORDER BY
                            captured_at DESC    -- 新しい順に番号付け
                    ) AS rn 
                FROM
                    task_results
            ) ranked 
        WHERE
            rn = 1                              -- 各グループの最新1件
            AND (id, DATE_TRUNC('hour', captured_at)) IN ( 
                -- 2件以上存在するグループのみ対象
                SELECT
                    id
                    , DATE_TRUNC('hour', captured_at) 
                FROM
                    task_results 
                GROUP BY
                    id
                    , DATE_TRUNC('hour', captured_at) 
                HAVING
                    COUNT(*) >= 2
            )
    );

まとめ

少し組み立てが大変ですので、お役に立てれば幸いです！