PostgreSQL GROUP BY与MySQL不同？ [英] PostgreSQL GROUP BY different from MySQL?

问题描述

我一直在将一些MySQL查询迁移到PostgreSQL来使用Heroku。我的大部分查询都能正常工作，但当我使用group by时，我仍然遇到类似的重复性错误：
$ b

错误：列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：列
availables.id必须出现在GROUP BY子句中或用于
聚合函数中：

SELECTavailables 。* FROMavailablesINNER
JOINroomsONrooms.id =availables.room_id WHERE
（rooms.hotel_id = 5056 AND availables.bookdate BETWEEN E'2009-10-21 '
AND E'2009-10-23'）GROUP BY availables.bookdate ORDER BY
availables.updated_at

生成SQL的Ruby代码： $ b

  expiration =可用。 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 |价格|点| | bookdate | 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表GROUP BY a 
  

每个值（一个，你不知道）。实际上，你可以猜到，因为MySQL不知道哈希聚合，所以它可能会使用排序......但它只会对a进行排序，所以行的顺序可能是随机的。除非它使用多列索引而不是排序。好吧，无论如何，它没有被查询指定。

postgres：

  SELECT DISTINCT ON（a）a，b，c，d，e FROM表ORDER BY a，b，c 
  

这为每个值a提供了1行，这一行将是根据查询指定的ORDER BY排序的第一行。简单。

请注意，这不是我计算的总计。所以GROUP BY实际上没有意义。 DISTINCT ON更有意义。

Rails与MySQL结婚，所以我不惊讶它生成的SQL在postgres中不起作用。

I've been migrating some of my MySQL queries to PostgreSQL to use Heroku. Most of my queries work fine, but I keep having a similar recurring error when I use group by:

ERROR: column "XYZ" must appear in the GROUP BY clause or be used in an aggregate function

Could someone tell me what I'm doing wrong?

MySQL which works 100%:

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 error:

ActiveRecord::StatementInvalid: PGError: ERROR: column "availables.id" must appear in the GROUP BY clause or be used in an aggregate function:
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') GROUP BY availables.bookdate ORDER BY availables.updated_at

Ruby code generating the SQL:

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')  

Expected Output (from working MySQL query):

+-----+-------+-------+------------+---------+---------------+---------------+
| id  | price | spots | bookdate   | 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 rows in set

解决方案

MySQL's totally non standards compliant GROUP BY can be emulated by Postgres' DISTINCT ON. Consider this :

mysql :

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

This delivers 1 row per value of a (which one, you don't really know). Well actually you can guess, because MySQL doesn't know about hash aggregates, so it will probably use a sort... but it will only sort on a, so the order of the rows could be random. Unless it uses a multicolumn index instead of sorting. Well, anyway, it's not specified by the query.

postgres :

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

This delivers 1 row per value of a, this row will be the first one in the sort according to the ORDER BY specified by the query. Simple.

Note that here, it's not an aggregate I'm computing. So GROUP BY actually makes no sense. DISTINCT ON makes a lot more sense.

Rails is married to MySQL, so I'm not surprised that it generates SQL that doesn't work in postgres.

这篇关于PostgreSQL GROUP BY与MySQL不同？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！