Help us understand the problem. What is going on with this article?

postgresでgroup by エラー

More than 5 years have passed since last update.

このようなデータがあったとして、

 id | content 
----+---------
  1 | 渋谷
  2 | 恵比寿
  3 | 五反田
  4 | 渋谷
  5 | 中目黒

contentがユニークな下記のようなデータを取得したいとします。

 id | content 
----+---------
  1 | 渋谷
  2 | 恵比寿
  3 | 五反田
  5 | 中目黒

mysqlに慣れたいた自分は下記のようなsql文を発行。

select * from result group by content;

すると

ERROR:  column "result.id" must appear in the GROUP BY clause or be used in an aggregate function
 1: select * from result group by content;

ふむ。result.idgroup by節に現れるか、集計関数として使われなければならない。
後者のほうはイマイチ意味がわからないので、group by節に追加。

select * from result group by id, content;
 id | content 
----+---------
  3 | 五反田
  5 | 中目黒
  1 | 渋谷
  4 | 渋谷
  2 | 恵比寿
(5 )

んん?予想した結果と違う。

ここで色々ぐぐってみると要するにgroup byした結果、id = 1の渋谷とid = 4の渋谷どちらを表示すればよいかわからないのでエラー、ということらしい。

なるほど、たしかに。

こうゆうときはdistinct句を使うと "一番最初の行" を取得してくれるから、これを使うと良いらしい。

SELECT DISTINCT ON ( expression [, ...] )は各行集合の中で、指定した式が等しいと評価した最初の行のみを保持します。

最初の行ということは id = 1の渋谷、ですかね。

select distinct on (content) * from result;
 id | content 
----+---------
  5 | 中目黒
  3 | 五反田
  2 | 恵比寿
  1 | 渋谷
(4 行)

id = 1の予想はあっていましたが、順番が逆順になっています。

じゃぁこうしよう。

select distinct on (content) * from result order by id asc;

またエラー。

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
 1: select distinct on (content) * from result order by id;
                          ^

distinct onの条件式はorder byの一番最初の条件式とマッチする必要がある、とのこと。

え、、こうゆうこと?

select distinct on (content) * from result order by content, id asc;
 id | content 
----+---------
  5 | 中目黒
  3 | 五反田
  2 | 恵比寿
  1 | 渋谷
(4 )

エラーこそ出ないもののちがう。。

ここでdistinct句のリファレンスをもう一度読んでみる。

各集合の"最初の行"は、ORDER BYを使用して目的の行が確実に最初に現れるようにしない限り予測することはできないことに注意してください。

なるほど・・。つまりここで使っているorder byid = 1の渋谷とid = 4の渋谷のどちらを選ぶか、ってゆう条件に使用されているのかな。たとえば前述の式をascdescにしてみる。

select distinct on (content) * from result order by content, id desc;
 id | content 
----+---------
  5 | 中目黒
  3 | 五反田
  2 | 恵比寿
  4 | 渋谷
(4 )

ほら、id = 4のほうになった。つまりdistinct onorder byはセットで使うべきなのですな。となると、この結果をサブクエリにして再度idorderしてやるくらいしか思いつきません。

select * from (select distinct on (content) * from result order by content, id asc) as distincted_table order by id asc;
 id | content 
----+---------
  1 | 渋谷
  2 | 恵比寿
  3 | 五反田
  5 | 中目黒
(4 )

できました〜パチパチ。

とはいえdistinct_onでなくgroup byでやる方法はないものか。

YusukeHigaki
株式会社ニコリーで代表をしております。起業当初は自分でも開発をしていたのですが、最近はCTO以下、自分より遥かに優秀なメンバーにバトンタッチし、もっぱら企画や営業周りを統括しています。自分で書いていた経験もあり、エンジニアという職種に対するリスペクトは絶大です。
http://ticktockblog.com
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away