【PostgreSQL】重複行削除で使えるDISTINCT ON

はじめに

ある列の重複は削除した上で他の列も含めて取得したいなどのケースで便利なDISTINCT ONという構文がPostgreSQLには存在する。
この構文は標準SQLには存在しないので注意。
標準SQLの普通のDISTINCTは特定の列の重複のみ除外して他の列も取得ということはできず、重複除外に指定するか取得しないかしかできない。

この構文を使用しなければGROUP BYや副問合せを駆使しなければならないのでそれに比べるとかなり直感的に書くことができる。

下記のサンプルテーブルに対して使用していく。

postgres=# create table t1 (c1 int, c2 int);
postgres=# insert into t1 values (1, 1), (1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (2, 2), (2, 3);
postgres=# select * from t1;
 c1 | c2
----+----
  1 |  1
  1 |  1
  1 |  2
  1 |  3
  2 |  1
  2 |  2
  2 |  2
  2 |  3
(8 rows)

DISTINCT ONの使い方

SELECTキーワードと列指定の間にDISTINCT ON (式1, 式2,...)とすることで使用できる。
式には単に列名を使用するのが基本的な使い方になる。
式はそれぞれ個別に評価され、すべての式の結果が同じものは重複しているとして最初の行しか出力されない。
このときの最初の行はORDER BYしていない限りどのレコードが選ばれるかは確定できないことに注意。

特定列の重複を削除した上で全列を取得

c1を指定する

postgres=# select distinct on (c1) * from t1;
 c1 | c2
----+----
  1 |  1
  2 |  1
(2 rows)

c1内での重複がなくなった。

c2を指定する

postgres=# select distinct on (c2) * from t1;
 c1 | c2
----+----
  1 |  1
  2 |  2
  1 |  3
(3 rows)

c2内での重複がなくなった。

完全重複行削除

これについては標準SQLでDISTINCTを使用するのと同じなのであえて使う必要はないが問題なくできる。

postgres=# select distinct on (c1, c2) * from t1;
 c1 | c2
----+----
  1 |  1
  1 |  2
  1 |  3
  2 |  1
  2 |  2
  2 |  3
(6 rows)

1 1と2 2で重複していた行が削除された。

ちなみにただのDISTINCTを使うとこうなる。

postgres=# select distinct c1, c2 from t1;
 c1 | c2
----+----
  1 |  1
  2 |  3
  1 |  3
  2 |  2
  1 |  2
  2 |  1
(6 rows)

順番は不定とはいえけっこう差が出た。
重複除外のタイミングの違いだろうか？

参考