【SQL】複合キーの重複データを取得することを考える

■ はじめに

 Snowflakeのデータ取り込みで、
重複データの調査を依頼されたのでメモ。

【0】お題

定義としては、複合キーで制約していないが
運用としては、重複がない複合キーとして
使用しているテーブルに関する重複データを取得する

【1】関連するSQL

1)GROUP BY
2)HAVING
3)自己相関サブクエリ(データ行を表示させたい場合)
4)DISTINCT(データ行を表示させたい場合)

1)GROUP BY

* 重複行も含めてGROUP BYで纏める

2)HAVING

* 「1)GROUP BY」のデータに対して
 カウントして複数あった(count(*) > 1)場合のみ
 抽出する
 => これで重複のみで絞れる

3)自己相関サブクエリ(データ行を表示させたい場合)

* GROUP BYしているので、指定以外のデータは基本的に取得できない
 そのため、重複行の他のデータも表示させたい場合に
 抽出したデータと自己結合させて、表示する

4)DISTINCT(データ行を表示させたい場合)

* それでも最終的に重複行が表示されてしまうので、
 無理やりDISTINCTで重複を纏める
 => 多分、もっといい方法があると思うが、、、

【2】サンプル

* 試した環境は、PostgreSQL。

1)サンプルデータ

テーブル定義

CREATE TABLE person_tel_list
(
  person_id VARCHAR(10) NOT NULL, -- 複合キーとして使っている項目
  person_tel VARCHAR(20) NOT NULL, -- 複合キーとして使っている項目
  remarks VARCHAR(20) NOT NULL -- 複合キー以外の項目
);

-- データの挿入
INSERT INTO
  person_tel_list (person_id, person_tel, remarks)
VALUES
    ('1234567890', '090xxxxxxxxx', 'Mike')
    , ('1234567891', '090yyyyyyyy', 'Tom')
    , ('1234567892', '090zzzzzzzz', 'Smith')
    , ('1234567890', '090xxxxxxxxx', 'Mike') -- 重複 - PatternA
    , ('1234567892', '090zzzzzzzz', 'Smith') -- 重複 - PatternB
    , ('1234567893', '080xxxxxxxxx', 'Kevin')
    , ('1234567893', '070xxxxxxxxx', 'Kevin') -- 同じ人物だがTELが違う(OK)
    , ('1234567894', '03zzzzzzzzz', 'Ken')
    , ('1234567895', '03zzzzzzzzz', 'Sam') -- 同じTELだが人物が違う(OK)
    , ('1234567896', '080zzzzzzzz', 'Smith')
    , ('1234567890', '090xxxxxxxxx', 'Mike') -- 重複 - PatternA
;

2)SQL

SELECT
  DISTINCT
  main.person_id,
  main.person_tel,
  main.remarks,
  sub.duplicate
FROM
(
  -- 抽出用サブクエリ
  SELECT
   person_id, person_tel, COUNT(*) AS duplicate
  FROM
   person_tel_list
  GROUP BY
    person_id, person_tel
  HAVING
    COUNT(*) > 1
) AS sub
INNER JOIN
 person_tel_list AS main
ON
 main.person_id = sub.person_id
 AND main.person_tel = sub.person_tel
ORDER BY
 sub.duplicate DESC
;

3)出力結果

"person_id","person_tel","remarks","duplicate"
"1234567890","090xxxxxxxxx","Mike","3"
"1234567892","090zzzzzzzz","Smith","2"

【3】課題

* 前述した「無理やりDISTINCTで重複を纏める」
 => 以下、DISTINCTなしの出力結果。
 (DISTINCTだとremarksを違う値にした場合、対応できない)

DISTINCT なしの出力

"person_id","person_tel","remarks","duplicate"
"1234567890","090xxxxxxxxx","Mike","3"
"1234567890","090xxxxxxxxx","Mike","3"
"1234567890","090xxxxxxxxx","Mike","3"
"1234567892","090zzzzzzzz","Smith","2"
"1234567892","090zzzzzzzz","Smith","2"

参考文献

https://johobase.com/extracts-duplicate-records-sql/
https://qiita.com/necoyama3/items/4c24defd6f504366aebe

関連記事

文字列関数 ~ string_agg ~
https://dk521123.hatenablog.com/entry/2021/08/21/000000