tablas - ¿GRUPO de PostgreSQL POR diferente de MySQL?

que es una consulta compleja en base de datos

Question

He estado migrando algunas de mis consultas de MySQL a PostgreSQL para usar Heroku. La mayoría de mis consultas funcionan bien, pero sigo teniendo un error recurrente similar cuando uso group by:

ERROR: la columna "XYZ" debe aparecer en la cláusula GROUP BY o debe usarse en una función agregada

¿Podría alguien decirme qué estoy haciendo mal?

MySQL que funciona al 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

Error de PostgreSQL:

ActiveRecord :: StatementInvalid: PGError: ERROR: la columna "availables.id" debe aparecer en la cláusula GROUP BY o debe usarse en una función agregada:
SELECCIONE "disponibles". * DESDE "disponible" INGRESO ÚNASE "habitaciones" EN "habitaciones" .id = "disponible" .room_id DÓNDE (rooms.hotel_id = 5056 Y disponibilidad.bookdate ENTRE E''2009-10-21 ''AND E'' 2009-10-23 '') GROUP BY availables.bookdate ORDEN POR availables.updated_at

Código Ruby generando el 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'')  

Salida esperada (de la consulta de MySQL):

+-----+-------+-------+------------+---------+---------------+---------------+ | 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

Answer 1

Correcto, la solución para solucionar esto es usar: seleccionar y seleccionar cada campo con el que desea decorar el objeto resultante y agruparlo.

Desagradable, pero es cómo debe funcionar el trabajo en grupo en lugar de cómo funciona MySQL al adivinar lo que quieres decir si no agregas campos a tu grupo.

Answer 2

Creo que .uniq [1] resolverá tu problema.

[1] Available.select(''...'').uniq

Eche un vistazo a http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

Answer 3

De acuerdo con "Debuking GROUP BY Myths" de MySQL, http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html . SQL (versión 2003 del estándar) no requiere que las columnas a las que se hace referencia en la lista SELECCIONAR de una consulta aparezcan también en la cláusula GROUP BY.

Answer 4

El GROUP BY de MySQL se puede usar sin una función agregada (que es contraria al estándar SQL), y devuelve la primera fila del grupo (no sé según los criterios), mientras que PostgreSQL debe tener una función agregada (MAX, SUM, etc.) en la columna, en la que se emite la cláusula GROUP BY.

Answer 5

MySQL totalmente compatible con los estándares de GROUP BY puede ser emulado por Postgres ''DISTINCT ON. Considera esto :

mysql:

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

Esto entrega 1 fila por valor de a (cuál, realmente no lo sabe). Bueno, en realidad se puede adivinar, porque MySQL no sabe acerca de los agregados hash, por lo que probablemente utilizará un género ... pero solo ordenará en a, por lo que el orden de las filas podría ser aleatorio. A menos que use un índice de columnas múltiples en lugar de ordenar. Bueno, de todos modos, no está especificado por la consulta.

postgres:

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

Esto entrega 1 fila por valor de a, esta fila será la primera en el orden según el ORDER BY especificado por la consulta. Sencillo.

Tenga en cuenta que aquí, no es un agregado que estoy computando. Entonces GROUP BY en realidad no tiene sentido. DISTINCT ON tiene mucho más sentido.

Rails está casado con MySQL, así que no me sorprende que genere SQL que no funcione en postgres.

Answer 6

No es la solución más bonita, pero cambiar el parámetro de grupo para generar cada columna en el modelo funciona en PostgreSQL:

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 => Available.column_names.collect{|col| "availables.#{col}"},
:order => ''availables.updated_at'')

Answer 7

Para otros que buscan una forma de ordenar por cualquier campo, incluido el campo unido, en postgresql, use una subconsulta:

SELECT * FROM(
SELECT DISTINCT ON(availables.bookdate) `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'')
) AS distinct_selected
ORDER BY availables.updated_at

or arel:

subquery = SomeRecord.select("distinct on(xx.id) xx.*, jointable.order_field")
      .where("").joins(")
result = SomeRecord.select("*").from("(#{subquery.to_sql}) AS distinct_selected").order(" xx.order_field ASC, jointable.order_field ASC")

Answer 8

PostgreSQL es más compatible con SQL que MySQL. Todos los campos, excepto el campo calculado con función de agregación, en el resultado deben estar presentes en la cláusula GROUP BY.

Answer 9

Si recuerdo correctamente, en PostgreSQL debe agregar cada columna que obtiene de la tabla donde la cláusula GROUP BY se aplica a la cláusula GROUP BY.