Studio ODIN

PostgreSQL の DISTINCT ON

PostgreSQL には 通常の DISTINCT ではない DISTINCT ON というものがあります。

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

db1=# SELECT * FROM distinct_test;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      1
 1      |      4 |      2
 2      |      2 |      1
 2      |      2 |      2
 3      |     -1 |      1
 3      |     -1 |      2
(6 rows)

まずは通常の DISTINCT です。

db1=# SELECT DISTINCT field1, field2
      FROM distinct_test;

 field1 | field2
--------+--------
 1      |      4
 2      |      2
 3      |     -1
(3 rows)

この場合、列に指定した field1, field2 の順番でソートされます。

列の指定の順番を変えます。

db1=# SELECT DISTINCT field2, field1
      FROM distinct_test;

 field2 | field1
--------+--------
     -1 | 3
      2 | 2
      4 | 1
(3 rows)

今度は field2, field1 でソートされます。
これはまぁ、こういうものです。

DISTINCT ON を使ってみます。
DISTINCT ON (field1, field2) のカッコの中と 列の指定 "field1, field2, field3" が 違うことに注意してください。

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
      FROM distinct_test;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      1
 2      |      2 |      1
 3      |     -1 |      1
(3 rows)

行数は DISTINCT で field1, field2 を指定したときと同じです。

つまり DISTINCT ON を使用すると カッコの中で指定した列で まとめられ、カッコの中で指定していない列（この例だと field3 ）は 最初に見つかったものが出力されます。

「 DISTINCT でまとめたいけど、他の列も欲しい！」というときに便利ですね。 ただ、このままだと DISTINCT ON に指定していない列は どの値が出るのか保障されないため、ソートをかけてやる必要があります。

ORDER BY によるソートは次のようになります。

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
       FROM distinct_test
       ORDER BY field1;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      1
 2      |      2 |      1
 3      |     -1 |      1
(3 rows)

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
      FROM distinct_test
      ORDER BY field2;

ERROR: SELECT DISTINCT ON expressions must match initial
ORDER BY expressions

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
      FROM distinct_test
      ORDER BY field3;

ERROR: SELECT DISTINCT ON expressions must match initial
ORDER BY expressions

DISTINCT ON の先頭で指定されている field1 以外の 列では エラーになってしまいます。

ORDER BY でソートするには、 DISTINCT ON で指定した列（指定順）の後に、DISTINCT ON 以外の列を書く必要があります。

db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3
       FROM distinct_test
       ORDER BY field1, field2, field3 DESC;

 field1 | field2 | field3
--------+--------+--------
 1      |      4 |      2
 2      |      2 |      2
 3      |     -1 |      2
(3 rows)

こうすることによって GROUP BY で MAX() を使ったのと 同じ値を取得すことができました。