[MySQL][PostgreSQL]重複を取り除く distinct と group by

まず、GROUP BY は重複を取り除くためのものではありません。

MySQL だけを長いことやってたら重複を取り除くために GROUP BY を使ってしまいがちだと思う。ググっても同じような記事書いてる人を見かけるので僕だけじゃないはず。

今回 PostgreSQL を使っててどうも思い通りにいかないので調べてみたら、そもそも GROUP BY は集計するためのものじゃないわけで。MySQL が気を利かせてくれた結果、重複を取り除くのに使えただけで本来の用途ではないと。ただ、ややこしいことに裏技的に GROUP BY を使うことでパフォーマンスを出そう!みたいな記事も見かける。

では、重複を取り除くにはどうするべきか?DISTINCT でしょう。

単純に重複しているレコードを取り除くには?

# MySQL
SELECT DISTINCT name, mail, tel FROM members

# PostgreSQL
SELECT DISTINCT name, mail, tel FROM members

まったく同じレコードが取り除かれるわけだけど、よくあるのは特定のカラムだけ重複を省きたいケース。上記だとそれができない。

特定のカラムが重複しているレコードを取り除くには?

# MySQL
SELECT name, mail, tel FROM members GROUP BY name

# PostgreSQL
SELECT DISTINCT on (name) name, mail, tel FROM members

これで特定のカラムの重複だけ取り除くことができる。しかし、そのカラム以外がどのレコードのものになるか保証できない。“たまたま”見つけたレコードの値になってしまう。それだと困るわけで、例えばあるカラムが重複してた場合に一番若いレコードを取得するにはどうしたらいいか?

特定のカラムが重複しているレコードを取り除く際に一番若いレコードを残すには?

# MySQL
SELECT name, mail, tel FROM (SELECT * FROM members ORDER BY id DESC) AS tmp GROUP BY name

# PostgreSQL
SELECT DISTINCT on (name) name, mail, tel FROM members ORDER BY name, id DESC

PostgreSQL は DISTINCT ON と ORDER BY で意図したことが可能。MySQL はサブクエリを使えば意図したことが可能。でも DISTINCT は使えない。だから重複を取り除くためについつい GROUP BY を使うようになってしまうのだろうなと。

ちなみに、MySQL のクエリを PostgreSQL で投げると must appear in the GROUP BY clause or be used in an aggregate function って怒られる。PostgreSQL では GROUP BY が使いにくい。

おまけ。PostgreSQL で null を含むカラムでソートするときはこうしたらいいらしい。
ORDER BY (i is null), i DESC

かわのくんとは

Web系IT企業でプログラミングやマネジメントをしています。趣味で音楽を少々。

Youtubeでライブ動画配信中

Ustreamでライブ動画配信中

スマートフォン向けにPCサイトを自動変換(コンバート)する『CONV2SP』 CSS作成支援ツール『CSSツクール』