horioの雑記帳

データ分析屋さんが、気の向いた事を色々メモってる雑記帳。

DBで重複行を削除する

たまに突然必要になるが、忘れそうなのでメモ。OLAP関数(Window関数、分析関数とも)一発で済むのがご利益。確認してないけど、OLAP関数が使えるDBならどれでも行けるハズ。

重複行が発生すること自体、絶対DB設計が間違ってるよね、とのヤジには、ワタシは心中では大いに賛同するものの、口にする勇気はないです…。

重複行削除のSQL

-- テーブルの用意
create table OLAPDeleteT(Val1 integer,Val2 integer);
insert into OLAPDeleteT values(1,1),(1,2),(1,2),(1,2),(1,2),(2,1),(2,1);

-- 以下が本体
delete from (select Row_Number() over(partition by Val1,Val2) as rn
               from OLAPDeleteT)
where rn > 1;

上記SQLのコピペ元。なおこのリンク先は、SQL中級者以上なら、一度はじっくり読みこむ価値がある、オススメページ。
DB2の分析関数の使用例

使う際の留意点

  • ほぼ間違いなくテーブルスキャンになるので遅い*1。レコード数が多い場合は*2、deleteのためだけに、一旦仮に全列にindexを張り、軽くででも統計情報を取り直す事も、十分に合理的な場合もある。
  • (上と関連するが)DBのソートヒープの大きさの確認。行数・列数が多いと、テーブルスキャンなのでそれなりにヒープを食うので、他人の作業の足を大いに引っ張るとか、クエリが実行できない、等々が起こり得る。

*1:全列index張っているなら別だけど、まず有り得ないでしょう…。

*2:個人的には、100万件以上なら警戒する。