SQL WINDOW関数を用いた重複レコードの排除

SQLで重複を排除するならGROUP BYやDISTINCTなどが思い浮かぶと思います。

実際ほとんどの場合はそれで済むと思いますが、今回は少し変わったクエリで重複の排除をする必要がありました。

例えば以下のようなテーブルがあったとします。

このテーブルをuser_pcという名前とします。

このuser_pcテーブルから以下の条件でレコードを取得する必要があったとします。

• osがNULLでかつ20歳未満のレコードは取得しない。
• osがNULLなら必ず重複が存在しないので重複排除の必要はない。
• nameとuser_idが重複しているレコードは重複を排除して取得する。
• 全てのカラムをSELECTする。
• 1度のクエリで取得する。

こんな条件で取得した結果を何に使うのかはさておき、シンプルにGROUP BYとDISTINCTを使うと重複の対象のカラムしか取れないので使えません。

やり方

タイトルにある通り、WINDOW関数のROW_NUMBERとOVER句を使って重複を排除しつつ全てのカラムを取得します。

ROW_NUMBERはレコードに連番を当てる関数で、OVER句にPARTITION BYの条件を加えて重複のときだけ連番が増えるようにします。

今回のクエリは2つの違ったSELECT文を用意してUNIONでくっつけますが、重複を排除する方だけ上記のように記述します。

まずosがNULLでかつ20歳未満のレコードを取得するSELECT文を用意します。

SELECT name, user_id, os, age FROM user_pc WHERE os IS NULL AND age > 20

こちらは何も捻りがなく、結果は「田中二郎」のレコードが取れるだけです。

次にosがNULLではないレコードのnameとuser_idを重複排除した状態で全てのカラムを取得します。

SELECT name, user_id, os, age
FROM (
  SELECT ROW_NUMBER() OVER(PARTITION BY name, user_id) AS row_num, *
  FROM user_pc
  WHERE os IS NOT NULL
)
WHERE row_num = 1

このクエリはFROM内で副問い合わせをし、ROW_NUMBERを使って連番を付けていますが、この連番を付ける条件にnameとuser_idの重複を指定しています。

このFROMの中だけの取得結果は以下のようになります。

重複のないデータは全てrow_num=1となってますが佐藤三郎だけ重複しているので1〜3までの連番が振り分けられました。

先に副問い合わせでこの結果を出し、次にこの結果をWHERE row_num = 1と絞ることで重複したレコードを排除したという仕組みです。

結果、以下のレコードのみが残ります。

SELECT name, user_id, os, age FROM user_pc WHERE os IS NULL AND age > 20
UNION
SELECT name, user_id, os, age
FROM (
  SELECT ROW_NUMBER() OVER(PARTITION BY name, user_id) AS row_num, *
  FROM user_pc
  WHERE os IS NOT NULL
)
WHERE row_num = 1

取得結果は以下となります。

以上で重複を排除しつつ全てのカラムをSELECTすることができました。

今回はWINDOW関数を使い特殊なケースの重複排除をしましたが、本来は集計や分析などに使うようです。

より詳しい使い方は下記の参考サイトを見ると良いと思います。

参考

• WINDOW関数について。
  • https://qiita.com/HiromuMasuda0228/items/0b20d461f1a80bd30cfc
  • https://qiita.com/ngyuki/items/c35dd314815939ffbeb6
• OVER句 PARTITION BYについて。
  • https://zukucode.com/2017/08/sql-over-partition-by.html
• MySQLでは8以上でないと使えないので注意。
  • https://dev.mysql.com/doc/refman/8.0/en/window-functions.html