He sido la migración de algunos de mis consultas de MySQL a PostgreSQL para usar Heroku. La mayoría de mis consultas funcionan bien, pero sigo teniendo una similar recurrente de error cuando uso el grupo de:

ERROR: la columna «XYZ» debe aparecer en la cláusula GROUP BY o ser utilizado en
una función de agregado

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


PostgreSQL error:

ActiveRecord::StatementInvalid: PGError: ERROR: columna
«disponibles.id» debe aparecer en la cláusula GROUP BY o ser usado en un
función de agregado:

SELECCIONE «disponibles».* DE «disponibles» INTERIOR
ÚNETE a «habitaciones» EN «habitaciones».id = «disponibles».room_id DONDE
(las habitaciones.hotel_id = 5056 Y disponibles.bookdate ENTRE E’2009-10-21′
Y E’2009-10-23′) GROUP BY disponibles.bookdate ORDEN POR
disponibles.updated_at


Ruby generador de código 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')  


Resultado esperado (a partir de trabajo de consulta de MySQL):

+-----+-------+-------+------------+---------+---------------+---------------+ 
| id | precio | 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 filas en el conjunto de 
  • sooo… sería mejor servido mediante la función distinct en bookdate? Si yo hiciera eso, sería todavía necesito la cláusula group by?
  • DISTINCT es más lento que GROUP BY. Así que usted debe ser cuidadoso y prefieren un GROUP BY solución si es posible.
InformationsquelleAutor holden | 2009-11-20

9 Comentarios

  1. 107

    MySQL totalmente no cumple con los estándares GROUP BY puede ser emulada por los Postgres’ DISTINCT ON. Considere esto:

    MySQL:

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

    Esta entrega 1 fila por cada valor de a (que uno, realmente no lo sé). Bueno, en realidad se puede adivinar, porque MySQL no sabe acerca de agregados hash, así que es probable que el uso de una especie… pero sólo de ordenar en a, por lo que el orden de las filas podría ser al azar. A menos que se utiliza un índice de múltiples columnas en lugar de la clasificación. Bueno, de todos modos, no especificada por la consulta.

    Postgres:

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

    Esta entrega 1 fila por cada valor de a, esta fila será el primero en la clasificación en función de la ORDER BY especificada por la consulta. Simple.

    Tenga en cuenta que aquí, no es un agregado estoy de computación. Así GROUP BY en realidad no tiene ningún sentido. DISTINCT ON tiene mucho más sentido.

    Rails está casado con MySQL, así que no estoy sorprendido de que genera el SQL que no funciona en Postgres.

    • Agregando a esto, sin embargo, Postgres 9.1 permite no lista de todas las columnas si su clave principal de la tabla es parte de la group by cláusula.
    • De acuerdo a la este artículo «Desmontando GRUPO de mitos», no tiene nada que ver con «no cumple con los estándares GROUP BY».
    • De acuerdo a este artículo, MySQL del GRUPO todavía no compatibles para ambas versiones de la norma, porque no se puede verificar si las columnas adicionales en la selectlist son dependientes en el grupo por columnas. Es la salida de datos incorrectos sin previo aviso (pero puede servir de utilidad a los efectos de demasiado). PG 9.1 se supone que participar de la PK de la tabla significa que todas las demás columnas son dependientes, que está a la derecha. Este no cubre el 100% (otros correcta consultas puede estar marcado como errores), sino que abarca la mayoría de los casos de uso sin devolver resultados incorrectos…
    • «Rails está casado con MySQL, así que no estoy sorprendido de que genera el SQL que no funciona en postgres.» No creo que esto es cierto, ya Postgres se ha vuelto muy popular en los Rieles de la comunidad debido a su noSQL capacidades.
    • Los rieles ya no está casada con MySQL.
    • Este MODO de respuesta da una buena explicación también a través de un ejemplo real.

  2. 16

    PostgreSQL es más compatibles con SQL de MySQL. Todos los campos a excepción del campo calculado con la función de agregación en la salida debe estar presente en la cláusula GROUP BY.

  3. 8

    De MySQL GRUPO puede ser utilizado sin una función de agregado (que es lo contrario al estándar SQL), y devuelve la primera fila en el grupo (no sé en base a qué criterios), mientras que PostgreSQL debe tener una función de agregado (MAX, SUM, etc) en la columna, en la que la cláusula GROUP BY se emite.

  4. 4

    Correcta, la solución para arreglar esto es el uso de :seleccione y seleccione cada campo que desea decorar el objeto resultante con el y un grupo de ellos.

    Desagradable – pero es como grupo por debe trabajo frente a cómo MySQL trabaja con ella por adivinar lo que significa que si no se adhieren campos en su grupo.

    • Supongo MySQL me ha estropeado, o me arruinó, cualquiera sea el adjetivo que prefieras, así que no hay mejor manera? Es decir. tirar en una función de agregado, tales como MAX(bookdate) o DISTINTO que yo decía más arriba es mucho más lento?
    • Yo me quedaría con el grupo – pero ten cuidado, sobre todo porque usted tiene que seleccionar manualmente los campos que desea decorar el objeto con. También la escritura de la selección manual con group by es una base de datos de más agnóstico enfoque, teniendo en cuenta que MSSQL (si tienes la mala suerte de tener que usarlo) y Oracle también se quejan de una manera similar.
    • DISTINTOS no significa necesariamente más lento.
  5. 3

    Si no recuerdo mal, en PostgreSQL tiene que agregar cada columna que extraer de la tabla, donde la cláusula GROUP BY se aplica a la cláusula GROUP BY.

  6. 2

    No la más bonita de la solución, pero si se cambia el parámetro de grupo a la salida de 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')
  7. 1

    De acuerdo a MySQL «Debuking GRUPO de Mitos» http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html. SQL (versión de 2003 de la norma) no requiere de columnas que se hace referencia en la lista de selección de una consulta para que también aparecen en la cláusula GROUP BY.

    • Pero, como otros han señalado, se requiere que ellos sean «funcionalmente dependiente» en las columnas, que están en la GROUP BY. MySQL capacidad de referencia alguna no agrupados columna es totalmente no-estándar, y permite a los usuarios escribir ilógico y poco de consultas.
    • Fue una norma en el tiempo, así que no es «completamente no-estándar». Yo coincido con usted, pero que será nuestro punto de vista.
    • ¿A qué hora? El artículo enlazado (a través de Wayback, o alt URL) dice que tanto SQL:1999 y SQL:2003 imponer límites en GROUP BY que MySQL se ignora.
  8. 1

    Para otros que buscan una manera de ordenar por cualquier campo, incluido el campo combinado, en postgresql, el uso de 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")

Dejar respuesta

Please enter your comment!
Please enter your name here