PostgreSQL GROUPE PAR les différents à partir de MySQL?
J'ai été à la migration de certains de mes requêtes MySQL à PostgreSQL pour utiliser Heroku. La plupart de mes questions, beau travail, mais je continue à avoir un semblable récurrents d'erreur lorsque j'utilise du groupe par:
ERREUR: la colonne "XYZ" doit apparaître dans la clause GROUP BY ou être utilisé dans
une fonction d'agrégation
Quelqu'un pourrait-il me dire ce que je fais mal?
MySQL qui fonctionne à 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 erreur:
ActiveRecord::StatementInvalid: PGError: ERREUR: la colonne
"disponibles.id" doit apparaître dans la clause GROUP BY ou être utilisé dans un
fonction d'agrégation:
SÉLECTIONNEZ "disponibles".* DE "disponibles" INTÉRIEURE
REJOINDRE "chambres" SUR "chambres".id = "disponibles".room_id OÙ
(chambres.hotel_id = 5056 ET disponibles.bookdate ENTRE E'2009-10-21'
ET E'2009-10-23') GROUP BY disponibles.bookdate COMMANDE PAR
disponibles.updated_at
Code Ruby générer le 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')
Sortie attendue (de travail de requête MySQL):
+-----+-------+-------+------------+---------+---------------+---------------+ | id | prix | 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
- sooo... serais-je mieux servi en utilisant la fonction distinct sur bookdate? Si je l'ai fait, ai-je toujours besoin de la clause group by?
DISTINCT
est plus lent queGROUP BY
. Donc vous devez être prudent et préfère uneGROUP BY
solution si c'est possible.
Vous devez vous connecter pour publier un commentaire.
MySQL est totalement non conforme aux normes
GROUP BY
peut être émulé par Postgres'DISTINCT ON
. Considérez ceci:MySQL:
Cette offre 1 ligne par valeur de
a
(dont l'un, vous ne savez pas vraiment). Eh bien en fait vous pouvez le deviner, parce que MySQL ne sais pas à propos de hachage agrégats, donc il va probablement utiliser une sorte... mais il ne sorte sura
, de sorte que l'ordre des lignes peut être aléatoire. Sauf si elle utilise un index multicolonne plutôt que de les trier. Eh bien, de toute façon, il n'est pas spécifié par la requête.Postgres:
Cette offre 1 ligne par valeur de
a
, cette ligne sera le premier à les trier en fonction de laORDER BY
spécifié par la requête. Simple.Notez qu'ici, ce n'est pas un agrégat je suis de l'informatique. Donc
GROUP BY
en réalité n'a pas de sens.DISTINCT ON
fait beaucoup plus de sens.Rails est marié à MySQL, donc je ne suis pas surpris qu'il génère le SQL qui ne fonctionne pas dans Postgres.
group by
clause.PostgreSQL est plus SQL conforme que MySQL. Tous les domaines à l'exception du champ calculé avec la fonction d'agrégation - dans la sortie doit être présent dans la clause GROUP BY.
MySQL GROUP BY peut être utilisé sans une fonction d'agrégation (ce qui est contraire à la norme SQL), et renvoie la première ligne dans le groupe (je ne sais pas en fonction de quels critères), tandis que PostgreSQL doit avoir une fonction d'agrégation (MAX, SUM, etc) sur la colonne, sur laquelle la clause GROUP BY est émis.
Correcte, la solution pour corriger cela est à utiliser :sélectionnez et sélectionnez chaque champ que vous souhaitez décorer l'objet résultant avec de groupe et par eux.
Méchant - mais c'est la manière de groupe par devrait de travail, par opposition à la façon dont MySQL fonctionne avec elle en devinant ce que tu veux dire, si vous ne respectez pas les champs dans votre groupe de.
Si je me souviens bien, dans PostgreSQL, vous devez ajouter toutes les colonnes que vous les récupérer à partir de la table où la clause GROUP BY s'applique à la clause GROUP BY.
Pas la plus jolie de la solution, mais en changeant le paramètre de groupe à la sortie de chaque colonne dans le modèle fonctionne dans PostgreSQL:
Selon MySQL "Debuking GROUPE PAR des Mythes" http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html. SQL (version 2003 de la norme) n'exige pas des colonnes référencées dans la liste de sélection d'une requête s'affichent également dans la clause GROUP BY.
GROUP BY
. MySQL est la capacité de référence aucun non regroupées colonne est entièrement non-standard, et permet aux utilisateurs d'écrire illogique et peu fiable des requêtes.GROUP BY
que MySQL ne tient pas compte.Pour d'autres, la recherche d'un moyen de commande par tout domaine, y compris le champ de jointure, dans postgresql, utiliser une sous-requête:
Je pense que .uniq [1] pour résoudre votre problème.
Prendre un coup d'oeil à http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields