💭
SQLを使って簡単に重複データの検出と排除する方法
「SQLではじめるデータ分析」という書籍の備忘録です。
業務で活用できるクエリが多くありそうなので、忘れないためにもアウトプットしておきます。
この書籍についての説明、以下公式より抜粋
クラウドの普及とともに、SQLの利用範囲は拡大し、データサイエンティストもデータベースを直接扱う機会が増えています。
本書は、データ分析に関わるSQLのテクニックを学び、分析プロセスにおいてSQLを最大限に活用するためのものです。
時系列解析などでは、SQLが日付・時刻の比較や処理に長けており、威力を発揮できる最たるものです。
本書ではデータの前処理から始め、時系列解析、コホート分析、テキスト分析、異常検知など、SQLの操作を通じて高度なテクニックまで学ぶことができる構成になっています。
サンプルコードはGitHubからダウンロード可能で、実際に手を動かしながら学ぶことができます。
環境構築
PostgreSQLのdocker構築とデータの流し込み
公式の手順に沿って構築します。 私はWindows10 のWSL2の環境上に構築しました。
あと、2章の重複検出排除ではサンプルデータが無かったので、自前で用意しました。
※もしかしたら私が見逃しているだけでサンプルデータあるかもしれません。。。
PythonのFakerを使ったサンプルデータの作成とPostgreSQLへのデータ流し込み
こちらの記事からFakerのコードをお借りしました。
一部修正して以下のコードでサンプルデータを作成します。
import csv
from faker import Faker
fake = Faker()
header = ['ID', 'NAME', 'BIRTH', 'ADDRESS', 'JOB']
rows = []
for i in range(10):
row = []
row.append(fake.random_number(digits=10))
row.append(fake.name())
row.append(fake.date_of_birth().strftime('%Y-%m-%d'))
row.append(fake.address().replace("\n", " "))
row.append(fake.job())
rows.append(row)
with open('test.csv', 'w',newline='') as f:
writer = csv.writer(f)
writer.writerow(header)
writer.writerows(rows)
f.close()
作成したCSVファイル内のいくつかの行データを手動で複製し、重複のあるデータを疑似的に作成します。
test.csv
ID,NAME,BIRTH,ADDRESS,JOB
1913339154,Elizabeth Montgomery,1968-02-16,"92574 Adam Haven Higginsburgh, NJ 41407",Computer games developer
7376192079,Stacy Davis,1946-09-12,"2004 Shannon Corners Williamsshire, MS 32623",Analytical chemist
5162628283,Joshua Patel,2007-12-20,"676 Huang Crescent Lake Bobbyfort, WI 62350","Education officer, community"
3770411761,Debra Brown,1996-05-23,"080 Joanna Shoals Suite 194 Matthewhaven, UT 46244","Production assistant, radio"
9938082532,Carmen Choi,2005-10-03,"06403 Jack Summit Angelafurt, HI 57675",Editorial assistant
8597570472,Cody Foster,1908-07-23,"784 Wendy Harbors Suite 350 Port Nathaniel, AL 96860",Production engineer
9133970938,Zachary Waters,1954-04-21,"0727 Earl Mission Bowmanport, FM 61417",Housing manager/officer
8056049298,Bryan Atkins,1913-05-25,"431 Alvarez Land Port Ashleyhaven, NH 98668","Teacher, special educational needs"
7316540982,Lindsey Fox,1929-01-30,"6925 Jensen Flat New Elizabethside, GA 52687",Translator
8955713017,Jordan Key,1988-02-20,"PSC 0884, Box 5763 APO AA 99310","Producer, television/film/video"
9938082532,Carmen Choi,2005-10-03,"06403 Jack Summit Angelafurt, HI 57675",Editorial assistant
9133970938,Zachary Waters,1954-04-21,"0727 Earl Mission Bowmanport, FM 61417",Housing manager/officer
docker cpコマンドを使って、docker上にtest.csvをアップロードします。
PostgresのlocalpathはPostgreSQL環境構築時に使ったパスを流用します。
docker cp "./test.csv" postgres:/localpath/
Postgresにテーブルを作成します。
create table faker_demo (id bigint, name varchar(20), birth timestamp, address varchar(150), job varchar(150));
作成したテーブルへCSVファイルを入れ込みます。
\copy faker_demo from '/localpath/test.csv' DELIMITER ',' CSV HEADER;
データが入っているか確認します。
postgres=# select * from faker_demo limit 5;
id | name | birth | address | job
------------+----------------------+---------------------+----------------------------------------------------+------------------------------
1913339154 | Elizabeth Montgomery | 1968-02-16 00:00:00 | 92574 Adam Haven Higginsburgh, NJ 41407 | Computer games developer
7376192079 | Stacy Davis | 1946-09-12 00:00:00 | 2004 Shannon Corners Williamsshire, MS 32623 | Analytical chemist
5162628283 | Joshua Patel | 2007-12-20 00:00:00 | 676 Huang Crescent Lake Bobbyfort, WI 62350 | Education officer, community
3770411761 | Debra Brown | 1996-05-23 00:00:00 | 080 Joanna Shoals Suite 194 Matthewhaven, UT 46244 | Production assistant, radio
9938082532 | Carmen Choi | 2005-10-03 00:00:00 | 06403 Jack Summit Angelafurt, HI 57675 | Editorial assistant
(5 rows)
これで事前準備はOKです。
データの重複の検出
検出と言うより、土の業に重複があるかを確認する方法が近いです。
今回のサンプルデータは12行しかないので、頑張れば目視で重複を見つけ出せると思いますが、百行単位となると、目が疲れちゃいますよね。
なので簡単に重複があるかないかを調べるクエリがこちらです。
ロジックとしては、サブクエリを使って全てのカラムに対してgroup byで集約し、その行数をカウントします。
するとrecodsカラムにカウント値が表示され、どの行が重複を起こしているかを確認できます。
postgres=# select * from ( select id, name, birth, address, job, count(*) as records from faker_demo group by 1,2,3,4,5 ) a;
id | name | birth | address | job | records
------------+----------------------+---------------------+------------------------------------------------------+------------------------------------+---------
9133970938 | Zachary Waters | 1954-04-21 00:00:00 | 0727 Earl Mission Bowmanport, FM 61417 | Housing manager/officer | 2
8955713017 | Jordan Key | 1988-02-20 00:00:00 | PSC 0884, Box 5763 APO AA 99310 | Producer, television/film/video | 1
8597570472 | Cody Foster | 1908-07-23 00:00:00 | 784 Wendy Harbors Suite 350 Port Nathaniel, AL 96860 | Production engineer | 1
8056049298 | Bryan Atkins | 1913-05-25 00:00:00 | 431 Alvarez Land Port Ashleyhaven, NH 98668 | Teacher, special educational needs | 1
3770411761 | Debra Brown | 1996-05-23 00:00:00 | 080 Joanna Shoals Suite 194 Matthewhaven, UT 46244 | Production assistant, radio | 1
5162628283 | Joshua Patel | 2007-12-20 00:00:00 | 676 Huang Crescent Lake Bobbyfort, WI 62350 | Education officer, community | 1
1913339154 | Elizabeth Montgomery | 1968-02-16 00:00:00 | 92574 Adam Haven Higginsburgh, NJ 41407 | Computer games developer | 1
7316540982 | Lindsey Fox | 1929-01-30 00:00:00 | 6925 Jensen Flat New Elizabethside, GA 52687 | Translator | 1
9938082532 | Carmen Choi | 2005-10-03 00:00:00 | 06403 Jack Summit Angelafurt, HI 57675 | Editorial assistant | 2
7376192079 | Stacy Davis | 1946-09-12 00:00:00 | 2004 Shannon Corners Williamsshire, MS 32623 | Analytical chemist | 1
(10 rows)
postgres=#
重複行のみのデータを抽出したい場合は、以下のクエリが参考となります。
全ての行（重複含む）からdistinct（重複を排除）した行をexcept allで差分を出せば抽出できます。
postgres=# select id, name, birth, address, job from faker_demo except all select distinct * from faker_demo;
id | name | birth | address | job
------------+----------------+---------------------+----------------------------------------+-------------------------
9133970938 | Zachary Waters | 1954-04-21 00:00:00 | 0727 Earl Mission Bowmanport, FM 61417 | Housing manager/officer
9938082532 | Carmen Choi | 2005-10-03 00:00:00 | 06403 Jack Summit Angelafurt, HI 57675 | Editorial assistant
(2 rows)
postgres=#
GROUP BYとDISTINCTを使った重複削除
まず、以下のような重複データのあるテーブルを用意します。
postgres=# select a.customer_id, a.customer_name, a.customer_email from customers a join transactions b on a.customer_id = b.customer_id;
customer_id | customer_name | customer_email
-------------+---------------+-------------------
1 | alice | alice@example.com
1 | alice | alice@example.com
2 | bob | bob@example.com
2 | bob | bob@example.com
3 | carol | carol@example.com
3 | carol | carol@example.com
3 | carol | carol@example.com
(7 rows)
DISTINCTを使えば、簡単に重複を排除してくれます。
postgres=# select distinct a.customer_id, a.customer_name, a.customer_email from customers a join transactions b on a.customer_id = b.customer_id;
customer_id | customer_name | customer_email
-------------+---------------+-------------------
2 | bob | bob@example.com
1 | alice | alice@example.com
3 | carol | carol@example.com
(3 rows)
postgres=#
これと同じ結果をGROUP BYでも行えます。
postgres=# select a.customer_id, a.customer_name, a.customer_email from customers a join transactions b on a.customer_id = b.customer_id group by 1,2,3;
customer_id | customer_name | customer_email
-------------+---------------+-------------------
2 | bob | bob@example.com
3 | carol | carol@example.com
1 | alice | alice@example.com
(3 rows)
postgres=#
ただ、重複を排除するだけではなく、重複が起きた原因を突き止めるためにもどのデータが重複を起こしているのかを調査することは重要だと思います。
そのうえで、重複を排除しても良いと判断できればDISTINCTを使うのが良いと思いました。
Discussion