はじめに

業務システムにて、あるテーブルのあるカラムに対して、本来あるべき「ユニーク制約」がついていなかった。後からテーブル設計の間違いに気がついて、ユニーク制約を追加したいものの、運用がはじまって既に該当カラムで重複しているレコードがあって、追加できない・・といった経験はないでしょうか？　私はあります。

このような場合、重複している行をテーブルから削除してからユニーク制約を追加することになります。

この記事では「重複している行をテーブルから削除する」SQLの操作についてまとめます。

動作確認環境

macOS Big Sur(バージョン11.2.1)
psql (PostgreSQL) 13.1

初級編: 1つのカラムでユニーク制約を追加したい場合

以下のようなテーブルがあるとします。

id	station_name
1	渋谷
2	渋谷
3	渋谷
4	恵比寿
5	恵比寿
6	五反田

山手線の駅名を管理するテーブルがあり、駅名でユニークにしたい！　と思ったタイミングで既に重複するレコードが入っていた・・というケースを想定します。

上記の状態の場合は「渋谷」のレコードが3件、「恵比寿」のレコードが2件あります。これをそれぞれ1件づつ残るようにしたいです。

基本的に重複しているレコードのうち、idが小さいを残すことにしましょう。
id=1（渋谷）, 4（恵比寿）のレコードを残し、id=2, 3（渋谷）, 5（恵比寿）のレコードを削除することにします。

サンプル用テーブルを手元の環境で作成する場合は、以下を実行してください。

create_sample_data_01.sql

CREATE TABLE stations(
  id SERIAL NOT NULL,
  station_name varchar(20),
  PRIMARY KEY (id)
);
INSERT INTO stations(station_name) VALUES ('渋谷');
INSERT INTO stations(station_name) VALUES ('渋谷');
INSERT INTO stations(station_name) VALUES ('渋谷');
INSERT INTO stations(station_name) VALUES ('恵比寿');
INSERT INTO stations(station_name) VALUES ('恵比寿');
INSERT INTO stations(station_name) VALUES ('五反田');

1. 重複データを確認したい

そもそも重複があるかどうかを確認する場合は、以下のSQL文を実行します。

# SELECT station_name FROM stations GROUP BY station_name HAVING count(id) > 1;

 station_name 
--------------
 渋谷
 恵比寿
(2 rows)

ユニークにしたいカラム（station_name）でGROUP BYでまとめ、まとめた後にレコード数(=count(id))が1より大きくなるものを抽出しました。

2. 重複したデータのうち、最小のidのレコードのみ出力したい

残したいレコードのidを調べるには、以下のSQL文を実行します。

# SELECT MIN(id), station_name FROM stations GROUP BY station_name HAVING count(id) > 1;

 min | station_name 
-----+--------------
   1 | 渋谷
   4 | 恵比寿

さきほどと同じ条件ですが、idの最小値であるMIN(id)をSELECT句で指定しました。

3. 重複したデータのうち、最小のid以外のみ出力したい（消すレコードだけ出力したい）

そもそもやりたかったことは
「id=1（渋谷）, 4（恵比寿）のレコードを残し、id=2, 3（渋谷）, 5（恵比寿）のレコードを削除したい」
でした。削除するレコードだけを抽出してみましょう。

ここでは自己結合というテクニックを使います。

# SELECT id, station_name FROM stations S1 WHERE id >
  (SELECT MIN(S2.id) FROM stations S2 
                     WHERE S1.station_name = S2.station_name);

 id | station_name 
----+--------------
  2 | 渋谷
  3 | 渋谷
  5 | 恵比寿
(3 rows)

左側のS1の表を基準にして、上から1行づつ追ってみましょう。
左側のS1の表にて、id=1 の行を考えます。

左側のS1の表にて、id=1となっている行の駅名は「渋谷」です。
これと同じ駅名になっている行は、右側の表では id=1, 2, 3の行が該当します。
SQL文の (かっこ) で囲まれているSELECT文で、この3件が選択されます。
id=1, 2, 3 のうち最小値 MIN(id)は 1 になります。

S1のid=1の行では、
WHERE id > 1
とはならないため、S1.id=1 の行は出力されません。

次にS1の id=2 の行を考えましょう。
またしても駅名は「渋谷」なので、右側の表 S2 では id=1, 2, 3の行が選択され、MIN(id) は 1 となります。

S1の id=2　の行では、
WHERE id > 1
がマッチするため、結果行に出力されます。

同様に、id=3の行、id=4の行・・を考えると、上記のように
id=2, 3, 5
の行が選択されることが分かります。

4. 重複のある行を消す

ここまでできたら、あとは簡単です。

# DELETE FROM stations S1 WHERE id > 
  (SELECT MIN(S2.id) FROM stations S2 
                     WHERE S1.station_name = S2.station_name);
DELETE 3


# SELECT * FROM stations;
 id | station_name 
----+--------------
  1 | 渋谷
  4 | 恵比寿
  6 | 五反田
(3 rows)

無事、station_name列でユニークになりました。

中級編: 複数カラムの組み合わせでユニークにしたい場合

駅名と飲食店を管理する以下のようなテーブルがあるとします。

id	station_name	shop_name
1	渋谷	富士そば
2	渋谷	富士そば
3	渋谷	はなまるうどん
4	恵比寿	富士そば
5	恵比寿	はなまるうどん
6	恵比寿	はなまるうどん
7	五反田	はなまるうどん

駅ごとにどの飲食店があるかを表したテーブルです。ひとつの駅の近くには同じ店は１件しかないものとします（あくまでサンプルです。東口店と西口店があるはず！というようなツッコミは無しでお願いします！）
駅名と店名の組み合わせでユニークにしたい。だがしかし、そのユニーク制約をつける前にデータが入ってきてしまっている。しかも重複してしまっているじゃねえか・・・といったケースを想定しています。

サンプル用テーブルを手元の環境で作成する場合は、以下を実行してください。

create_sample_data_02.sql

CREATE TABLE station_shops(
  id SERIAL NOT NULL,
  station_name varchar(20),
  shop_name varchar(20),
  PRIMARY KEY (id)
);
INSERT INTO station_shops(station_name, shop_name) VALUES ('渋谷',  '富士そば');
INSERT INTO station_shops(station_name, shop_name) VALUES ('渋谷',  '富士そば');
INSERT INTO station_shops(station_name, shop_name) VALUES ('渋谷',  'はなまるうどん');
INSERT INTO station_shops(station_name, shop_name) VALUES ('恵比寿', '富士そば');
INSERT INTO station_shops(station_name, shop_name) VALUES ('恵比寿', 'はなまるうどん');
INSERT INTO station_shops(station_name, shop_name) VALUES ('恵比寿', 'はなまるうどん');
INSERT INTO station_shops(station_name, shop_name) VALUES ('五反田', 'はなまるうどん');

1. 重複データを確認

GROUP BYの後のカラムを２つ指定します。

# SELECT station_name, shop_name FROM station_shops GROUP BY station_name, shop_name HAVING count(id) > 1;

 station_name |   shop_name    
--------------+----------------
 恵比寿       | はなまるうどん
 渋谷         | 富士そば
(2 rows)

（・・手元の環境で試したところ、この順番で出力されていました。本当は「渋谷」の行が先に来てほしいような気もしましたが、深追いせず、このままとします）

2. 重複したデータのうち、最小のidのレコードのみ出力

同様に、GROUP BYの後のカラムを２つ指定します。

SELECT MIN(id), station_name, shop_name FROM station_shops GROUP BY station_name, shop_name HAVING count(id) > 1;

 min | station_name |   shop_name    
-----+--------------+----------------
   5 | 恵比寿       | はなまるうどん
   1 | 渋谷         | 富士そば
(2 rows)

3. 重複したデータのうち、最小のid以外のみ出力（消す行だけ出力)

同様に・・・。

SELECT id, station_name, shop_name FROM station_shops S1 WHERE id >
  (SELECT MIN(S2.id) FROM station_shops S2 
                     WHERE S1.station_name = S2.station_name 
                         AND S1.shop_name = S2.shop_name);

 id | station_name |   shop_name    
----+--------------+----------------
  2 | 渋谷         | 富士そば
  6 | 恵比寿       | はなまるうどん

4. 重複行を削除！

消します。

DELETE FROM station_shops S1 WHERE id >
  (SELECT MIN(S2.id) FROM station_shops S2 
                     WHERE S1.station_name = S2.station_name 
                         AND S1.shop_name = S2.shop_name);
DELETE 2

# SELECT * FROM station_shops;

 id | station_name |   shop_name    
----+--------------+----------------
  1 | 渋谷         | 富士そば
  3 | 渋谷         | はなまるうどん
  4 | 恵比寿       | 富士そば
  5 | 恵比寿       | はなまるうどん
  7 | 五反田       | はなまるうどん
(5 rows)

無事、ユニークキーを追加できる状態となりました。

上級編: 重複レコードを論理削除したいとき

業務システムではレコードを物理削除ではなく論理削除したい、といったケースもあります。論理削除とは、DELETEでレコードを消すのではなく、delete_flag のようなカラムを追加し、delete_flagが1なら削除されたとみなす、というような考え方です。ここでは、Railsプロジェクトで gem 'paranoia' がインストールされている環境を想定します。

paranoia

paranoiaでは、対象のテーブルには常に'deleted_at' カラムが存在します。デフォルト値を'1970-01-01 00:00:00'とします。
deleted_atがデフォルト値のレコードは、生きている（削除されていない）レコードです。deleted_atがデフォルト値以外のレコードは、削除されたレコードとして扱われます。

最初の「駅名管理テーブル」を論理削除対応させたテーブルを考えます。

id	station_name	deleted_at
1	渋谷	1970-01-01 00:00:00
2	渋谷	1970-01-01 00:00:00
3	渋谷	1970-01-01 00:00:00
4	恵比寿	1970-01-01 00:00:00
5	恵比寿	1970-01-01 00:00:00
6	五反田	1970-01-01 00:00:00

サンプル用テーブルを手元の環境で作成する場合は、以下を実行してください。

create_sample_data_03.sql

CREATE TABLE stations(
  id SERIAL NOT NULL,
  station_name varchar(20),
  deleted_at timestamp DEFAULT '1970-01-01 00:00:00',
  PRIMARY KEY (id)
);
INSERT INTO stations(station_name) VALUES ('渋谷');
INSERT INTO stations(station_name) VALUES ('渋谷');
INSERT INTO stations(station_name) VALUES ('渋谷');
INSERT INTO stations(station_name) VALUES ('恵比寿');
INSERT INTO stations(station_name) VALUES ('恵比寿');
INSERT INTO stations(station_name) VALUES ('五反田');

システムの挙動として「駅名」でユニークにしたい場合、テーブル定義は station_nameとdeleted_atの組み合わせでユニーク制約を追加します（station_nameだけでユニークにした場合、論理削除されたキーのレコードが追加できなくなってしまいます）。

正しくない例

削除したい場合は deleted_atを現在時刻に更新すればよいため、まず思いつくのは以下のようなクエリーです。

# update stations S1 SET deleted_at = NOW() WHERE id >
  (SELECT MIN(S2.id) FROM stations S2 
                     WHERE S1.station_name = S2.station_name);
UPDATE 3

結果を確認してみましょう。

# SELECT * FROM stations ORDER BY id;
 id | station_name |         deleted_at         
----+--------------+----------------------------
  1 | 渋谷         | 1970-01-01 00:00:00
  2 | 渋谷         | 2021-03-06 22:55:04.967859
  3 | 渋谷         | 2021-03-06 22:55:04.967859
  4 | 恵比寿       | 1970-01-01 00:00:00
  5 | 恵比寿       | 2021-03-06 22:55:04.967859
  6 | 五反田       | 1970-01-01 00:00:00
(6 rows)

一見 id=2, 3, 5のレコードが削除扱いされていて、問題ないように思えます。ですが、このままだとstation_name + deleted_atでユニーク制約を追加できません。なぜなら id=2 の行と id=3 の行が重複してしまっているからです。
PostgreSQLでは、NOW()は現在のトランザクションが開始された時間を返します。

https://www.postgresql.jp/document/12/html/datatype-datetime.html

deleted_atを更新するときに、「同じ時間にならないように」工夫する必要があります。

正しい例(PostgreSQLの場合)

clock_timestamp() を使います。

# UPDATE stations S1 SET deleted_at = clock_timestamp() WHERE id >
  (SELECT MIN(S2.id) FROM stations S2 
                     WHERE S1.station_name = S2.station_name 
                         AND S1.deleted_at = S2.deleted_at);
UPDATE 3

# SELECT * FROM stations ORDER BY id;
 id | station_name |         deleted_at         
----+--------------+----------------------------
  1 | 渋谷         | 1970-01-01 00:00:00
  2 | 渋谷         | 2021-03-07 00:37:11.939193
  3 | 渋谷         | 2021-03-07 00:37:11.93924
  4 | 恵比寿       | 1970-01-01 00:00:00
  5 | 恵比寿       | 2021-03-07 00:37:11.939249
  6 | 五反田       | 1970-01-01 00:00:00
(6 rows)

https://www.postgresql.jp/document/8.3/html/functions-datetime.html

clock_timestamp()は実際の現在時刻を返しますので、その値は単一のSQLコマンドであっても異なります。

MySQLの場合について

残念ながら、MySQLでは上記のクエリーはうまく動作しません。サブクエリで自分のテーブルと結合しつつupdateしようとするとエラーになります。また、初めから deleted_at をdatetime(6)として設定している場合は、deleted_at = SYSDATE(6) として更新することでpostgresの clock_timestamp() と同等の動きになりますが、ただのdatetime型だと秒単位の更新となってしまいます。
こちらについては、別の記事で考えたいと思います。

参考文献

達人に学ぶSQL徹底指南書第2版初級者で終わりたくないあなたへ