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

SQL PostgreSQL Snowflake

■ はじめに

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

目次

【０】お題
　補足：単純にそのデータがユニークどうか調べる場合
【１】関連するSQL
　１）GROUP BY
　２）HAVING
　３）自己相関サブクエリ
　４）DISTINCT
【２】サンプル
　１）サンプルデータ
　２）SQL文
　３）出力結果
【３】課題

【０】お題

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

補足：単純に対象項目がユニークどうか調べる場合

* distinct を使えば可能。

例 

-- １）全件カウント
select count(sample_key) from table1;

-- ２）distinct で重複をまとめてカウント
select count(distinct sample_key) from table1;

-- １）と２）の結果が同じであれば、
-- （少なくともテーブルにあるものは）ユニークである
-- そうじゃなければ、ユニークじゃない

【１】関連するSQL

１）GROUP BY
２）HAVING
３）自己相関サブクエリ（データ行を表示させたい場合）
４）DISTINCT（データ行を表示させたい場合）

１）GROUP BY

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

２）HAVING

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

３）自己相関サブクエリ（データ行を表示させたい場合）

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

４）DISTINCT（データ行を表示させたい場合）

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

【２】サンプル

* 試した環境は、PostgreSQL。

１）サンプルデータ

テーブル定義 

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
;

２）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
;

３）出力結果

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

【３】課題

* 前述した「無理やり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