SQLで重複削除するには、GROUP BYとサブクエリによる自己結合を組み合わせる

SQLの重複したデータを削除する方法について、サンプルコードを紹介しながらまとめています。

SQLの重複削除にはgroup byとサブクエリを組み合わせる

salaries（年収）テーブルには、社員ごとの年収（salary）と、有効期限（from_date、to_date）が格納されています。そのため、1人の社員につき複数のレコードが存在ます。

salariesテーブルから、最新のレコード以外の重複データを削除するSQLを考えていきましょう。まずは、重複データのうち、削除せずに残しておくデータを抽出します。以下のSQLは、emp_no（社員番号）とto_date（有効期限の終了日）を抽出するSQLです。

mysql> select emp_no, to_date from salaries limit 10;
+--------+--------------+
| emp_no | max(to_date) |
+--------+--------------+
|  10001 | 1987-06-26   |
|  10001 | 1988-06-25   |
|  10001 | 1989-06-25   |
|  10001 | 1990-06-25   |
|  10001 | 1991-06-25   |
|  10001 | 1992-06-24   |
|  10001 | 1993-06-24   |
|  10001 | 1994-06-24   |
|  10001 | 1995-06-24   |
|  10001 | 1996-06-23   |
+--------+--------------+
10 rows in set (4.96 sec)

max関数とgroup byを使って、重複データから、有効期限が最大のもののみを抽出します。9999-01-01は、有効期限が現在も有効であることを表しています。退職した社員もいるので、すべてのデータが9999-01-01というわけではありません。

mysql> select emp_no, max(to_date) from salaries group by emp_no limit 10;
+--------+--------------+
| emp_no | max(to_date) |
+--------+--------------+
|  10001 | 9999-01-01   |
|  10002 | 9999-01-01   |
|  10003 | 9999-01-01   |
|  10004 | 9999-01-01   |
|  10005 | 9999-01-01   |
|  10006 | 9999-01-01   |
|  10007 | 9999-01-01   |
|  10008 | 2000-07-31   |
|  10009 | 9999-01-01   |
|  10010 | 9999-01-01   |
+--------+--------------+
10 rows in set (0.00 sec)

上記のSQLをサブクエリとして使用し、NOT INで、最新の年収データ以外を抽出します。

SELECT *
FROM   salaries
WHERE  ( emp_no, to_date ) NOT IN (SELECT tmp1.emp_no  AS emp_no,
                                          tmp1.to_date AS to_date
                                   FROM   (SELECT emp_no,
                                                  Max(to_date) AS to_date
                                           FROM   salaries
                                           GROUP  BY emp_no) AS tmp1)
LIMIT  20; 

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10002 |  65828 | 1996-08-03 | 1997-08-03 |
|  10002 |  65909 | 1997-08-03 | 1998-08-03 |
|  10002 |  67534 | 1998-08-03 | 1999-08-03 |
|  10002 |  69366 | 1999-08-03 | 2000-08-02 |
+--------+--------+------------+------------+
20 rows in set (1.19 sec)

自分自身のテーブルをサブクエリに使う「自己結合」を行っています。tmp1という別名をつけているのは、別テーブルとして扱うためです。select文をdelete文に置き換えて実行するとこうなります。

DELETE FROM salaries
WHERE  ( emp_no, to_date ) NOT IN (SELECT tmp1.emp_no  AS emp_no,
                                          tmp1.to_date AS to_date
                                   FROM   (SELECT emp_no,
                                                  Max(to_date) AS to_date
                                           FROM   salaries
                                           GROUP  BY emp_no) AS tmp1); 
Query OK, 2543867 rows affected (29.07 sec)

【関連記事】
▶SQLで重複を削除するサンプルコード　最新データを残してdeleteするには？ 

最終型のSQLだけを見るとかなり複雑ですが、１つ１つ分解していくと、単純なSQLの組み合わせであることがわかると思います。今回は重複削除の方法の1つとしてサンプルコードを紹介しました。

まとめ

ポテパンダの一言メモ

• SQLの重複のうち最新データを抽出するにはgroup byと集計関数（max）を組み合わせる
• 重複データを抽出するには、全データからnot inで最新データ以外を抽出する
• 重複データを削除するには、重複データを抽出する条件をdelete文に適用する