PostgreSQLで重複レコードを一覧チェックする方法｜array_aggでIDをまとめて表示

管理システムのテーブルを確認していると、同じ値を持つレコードが何件も見つかることがあります。

筆者も自作のToDo管理アプリを確認していたとき、同じ item_code を持つレコードが紛れ込んでいることに気づきました。

重複件数だけなら GROUP BY と COUNT で確認できます。ただ、「具体的にどのIDが重複しているのか」まで調べるには、IDも一緒に並べたいところです。

PostgreSQLではGROUP BYとHAVINGで重複している値を絞り込み、array_aggで該当するIDを1行にまとめて表示できます。

array_aggはPostgreSQL8.4以降で使用可能です。

select
    item_code,
    count(*) as "件数",
    array_agg(id order by id desc) as "ID一覧"
from
    test_tbl
where
    item_code is not null
group by
    item_code
having
    count(*) > 1
order by
    item_code;

 item_code | 件数 | ID一覧
-----------+------+---------
 A001      |    3 | {5,3,1}
 A002      |    2 | {6,2}
(2 rows)

A001を持つレコードが3件あり、該当するIDは5、3、1だと1行で確認できます。筆者的にはわかりやすくて気に入っています。

GROUP BYとHAVINGで重複を検出する

まずは、重複している値と件数だけを調べる基本形です。

select
    【重複を判定したい列】,
    count(*) as "件数"
from
    【テーブル名】
group by
    【重複を判定したい列】
having
    count(*) > 1;

重複を判定したい列ごとにレコードをまとめCOUNT(*)で件数を数えています。WHEREは集約前のレコードを絞り込むために使います。HAVINGはGROUP BYで集約した後の結果を絞り込むために使います。

今回は2件以上存在する値を探したいのでHAVING COUNT(*) > 1としています。

このクエリで分かるのは「どの値が何件存在するか」までです。該当するレコードのIDは表示されません。

array_aggで該当するIDをまとめる

重複しているレコードのIDも確認したい場合はarray_aggをSELECT句へ追加します。

※【】で囲んだ部分を実際のテーブル名や列名へ置き換えてください。

select
    【重複を判定したい列】,
    count(*) as "件数",
    array_agg(【一覧表示したい列】 order by 【一覧表示したい列】 desc) as "ID一覧"
from
    【テーブル名】
where
    【重複を判定したい列】 is not null
group by
    【重複を判定したい列】
having
    count(*) > 1
order by
    【重複を判定したい列】;

array_aggはグループ内にある複数行の値をPostgreSQLの配列としてまとめる集約関数です。ORDER BY id DESCを入れているため配列内のIDは大きい順に並びます。

array_aggの引数や並び順の詳しい仕様はPostgreSQL公式ドキュメントの「集約関数」でも確認できます。

列名の大文字を保つならダブルクォートを付ける

PostgreSQLではダブルクォートで囲まれていない識別子の英字は小文字として扱われます。

たとえば、次のように書いた場合です。

array_agg(id) AS ID一覧

実際の列名はid一覧として扱われる可能性があります。表示名をID一覧のままにしたい場合はダブルクォートで囲みます。

array_agg(id) AS "ID一覧"

NULLを除外するかはデータの意味で決める

GROUP BY item_codeを実行するとitem_codeがNULLのレコードも1つのグループにまとまります。そのためNULLのレコードが複数あればHAVING COUNT(*) > 1の結果にNULLのグループも含まれます。

ただ、NULLを除外すべきかどうかは業務の仕様によって変わります。item_codeが未採番であることを示し、未採番のレコード同士を重複とみなさないならWHERE item_code IS NOT NULLで除外します。

反対に、NULLが複数存在する状態そのものをデータ不備として調べたい場合は、除外してはいけません。「NULLは必ず除外する」ではなく、何を重複と定義するかを決めてから条件を追加するのが安全です。

複数列の組み合わせで重複を調べる

重複を探したい条件が1列とは限りません。たとえばuser_idとitem_codeの組み合わせが重複しているレコードを探す場合は、両方をGROUP BYへ指定します。

select
    user_id,
    item_code,
    count(*) as "件数",
    array_agg(id order by id desc) as "ID一覧"
from
    test_tbl
group by
    user_id,
    item_code
having
    count(*) > 1
order by
    user_id,
    item_code;

このクエリではuser_idだけ、またはitem_codeだけが同じレコードは重複扱いになりません。2つの値が両方とも同じレコードだけが抽出されます。

文字列で出力するならstring_aggを使う

array_aggの戻り値はPostgreSQLの配列型で{5,3,1}のように表示されます。

PostgreSQL内で配列として処理するならこのままのほうが便利です。画面表示やCSV出力などで、カンマ区切りの文字列が欲しい場合はstring_aggも使えます。

select
    item_code,
    count(*) as "件数",
    string_agg(id::text,', 'order by id desc) as "ID一覧"
from
    test_tbl
where
    item_code is not null
group by
    item_code
having
    count(*) > 1
order by
    item_code;

出力結果のID一覧は5, 3, 1のように文字列になります。この例ではstring_agg(text, text)を使うため、整数型のidをid::textで文字列へ変換しています。

大きなテーブルでは対象範囲を絞って確認する

行数が多いテーブルではGROUP BYや集約処理に時間がかかる場合があります。

負荷はテーブルの行数だけで決まるものではなく、絞り込み条件、インデックス、実行計画、PostgreSQLの設定などによっても変わります。本番環境でいきなり全件を対象にするのが不安なら、まず日付やステータスで範囲を絞って確認します。

実行前にEXPLAINで実行計画を確認する方法もあります。

explain
select
    item_code,
    count(*) as "件数",
    array_agg(id order by id desc) as "ID一覧"
from
    test_tbl
where
    item_code is not null
group by
    item_code
having
    count(*) > 1;

この記事のクエリは重複を確認するためのSELECT文なのでレコード自体は変更しません。

見つかった重複レコードを削除するときはどのレコードを残すのかを先に決める必要があります。更新日時が新しいレコードを残すのか、最小のIDを残すのかで削除条件は変わります。

一覧を確認せず、そのままDELETE文へ置き換えるのは避けたほうが安全です。

PostgreSQLのSQLや接続設定、Dockerでの環境構築記事はこちらのページにまとめています。

あわせて読みたい

データベースまとめ PostgreSQLの接続設定や時系列SQL、Dockerを使ったPostgreSQL・SQL Server環境構築記事をまとめたページです。

よくある疑問

array_agg(DISTINCT id ORDER BY id DESC)

array_to_string(array_agg(id ORDER BY id DESC), ', ')