PostgreSQLのGROUP BYはMySQLと違う？

質問

Herokuを使用するために、私のMySQLクエリのいくつかをPostgreSQLに移行しています。私のクエリのほとんどは問題なく動作しますが、group byを使用すると同様のエラーが繰り返し発生します。

ERROR: 列 "XYZ" は GROUP BY 句に表示されるか、または集約関数で使用されなければなりません。 集約関数で使用されなければなりません。

誰か私が何を間違えているのか教えていただけませんか？

100％動作するMySQLです。

SELECT `availables`.*
FROM `availables`
INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id
WHERE (rooms.hotel_id = 5056 AND availables.bookdate BETWEEN '2009-11-22' AND '2009-11-24')
GROUP BY availables.bookdate
ORDER BY availables.updated_at

PostgreSQLのエラーです。

ActiveRecord::StatementInvalid: PGError: ERROR: 列 は、GROUP BY 節に表示するか、集約関数で使用する必要があります。 集約関数で使用する必要があります。

SELECT "availables".* FROM "availables" INNER JOIN "rooms" ON "rooms".id = "availables".room_id WHERE (rooms.hotel_id = 5056 AND availables.bookdate BETWEEN E'2009-10-21' AND E'2009-10-23') とE'2009-10-23') GROUP BY availables.bookdate ORDER BY availables.updated_at

SQLを生成するRubyのコードです。

expiration = Available.find(:all,
    :joins => [ :room ],
    :conditions => [ "rooms.hotel_id = ? AND availables.bookdate BETWEEN ? AND ?", hostel_id, date.to_s, (date+days-1).to_s ],
    :group => 'availables.bookdate',
    :order => 'availables.updated_at')  

期待される出力（作業中のMySQLクエリから）。

+-----+-------+-------+------------+---------+---------------+---------------+
| id｜価格｜スポット｜ブックデート｜room_id｜created_at｜updated_at｜。
+-----+-------+-------+------------+---------+---------------+---------------+
| 414 | 38.0 | 1 | 2009-11-22 | 1762 | 2009-11-20... | 2009-11-20... |
| 415 | 38.0 | 1 | 2009-11-23 | 1762 | 2009-11-20... | 2009-11-20... |
| 416 | 38.0 | 2 | 2009-11-24 | 1762 | 2009-11-20... | 2009-11-20... |
+-----+-------+-------+------------+---------+---------------+---------------+
セットで3行

どのように解決するのですか？

MySQL の完全な非標準化 GROUP BY は、Postgres の DISTINCT ON . これを考えてみましょう。

MySQLを

SELECT a,b,c,d,e FROM table GROUP BY a

の値ごとに1行ずつ配信されます。 a の値ごとに 1 行を配信します (どれがそうなのか、あなたは本当に知らないのです)。MySQL はハッシュアグリゲートについて知らないので、おそらくソートを使用することになるでしょう...しかし、それは a でしかソートされないので、行の順序はランダムになる可能性があります。ソートの代わりに複数列のインデックスを使用しない限りは。まあ、とにかく、それはクエリによって指定されていないのです。

Postgresです。

SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c

の値ごとに1行ずつ配信されます。 a に従ってソートされ、この行が最初の行となります。 ORDER BY がクエリで指定されたものであることを示します。シンプルですね。

ここでは、私が計算しているのは集計ではないことに注意してください。そのため GROUP BY は実際には意味を持ちません。 DISTINCT ON はもっと意味があります。

RailsはMySQLと結婚しているので、Postgresで動作しないSQLを生成しても不思議ではありません。