PostgreSQL GROUP BY unterscheidet sich von MySQL?

Ich habe die Migration einige meiner MySQL-Abfragen für die PostgreSQL verwenden, Heroku. Die meisten meiner Anfragen funktionieren, aber ich halte eine ähnliche wiederkehrende Fehler, wenn ich group by:

FEHLER: Spalte "XYZ" muss in der GROUP BY-Klausel oder verwendet werden
eine Aggregatfunktion

Könnte mir jemand sagen was ich falsch mache?

MySQL, das funktioniert 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-Fehler:

ActiveRecord::StatementInvalid: PGError: FEHLER: Spalte
"availables.id" muss in der GROUP BY-Klausel oder in einem
die Aggregat-Funktion:
WÄHLEN Sie "availables".* VON "availables" INNERE
JOIN "Zimmer" "Zimmer".id = "availables".room_id WO
(Zimmer.hotel_id = 5056 UND availables.bookdate ZWISCHEN E'2009-10-21'
UND E'2009-10-23') GROUP BY availables.bookdate, UM DURCH
availables.updated_at

Ruby code generieren der 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')

Erwartete Ausgabe (arbeiten, MySQL-query):

+-----+-------+-------+------------+---------+---------------+---------------+ 
| id | Preis | 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... ich wäre besser bedient mit der distinct-Funktion auf bookdate? Wenn ich das täte, würde ich noch die group by-Klausel?
DISTINCT ist langsamer als GROUP BY. So sollten Sie vorsichtig sein und lieber eine GROUP BY Lösung, wenn es möglich ist.

InformationsquelleAutor holden | 2009-11-20

107

MySQL völlig nicht standards-konform GROUP BY kann emuliert werden, indem Postgres' DISTINCT ON. Bedenken Sie:

MySQL:
```
SELECT a,b,c,d,e FROM table GROUP BY a
```
Dies liefert 1 Zeile pro Wert a (die eine, die Sie nicht wirklich kennen). Naja, eigentlich kann man erraten, weil MySQL nicht weiß, über hash-Aggregate, so wird es wahrscheinlich eine Art... aber es wird nur eine Art auf a, so ist die Reihenfolge der Zeilen könnte zufällig sein. Es sei denn, es verwendet einen mehrspaltigen index anstelle der Sortierung. Nun, wie auch immer, es ist nicht angegeben, die von der Abfrage.

Postgres:
```
SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c
```
Dies liefert 1 Zeile pro Wert a dieser Zeile wird die erste sein, die in der Sortierung nach der ORDER BY von der Abfrage angegebene. Einfach.

Beachten Sie, dass hier, es ist nicht ein Aggregat ich bin computing. So GROUP BY eigentlich keinen Sinn macht. DISTINCT ON macht viel mehr Sinn.

Schienen verheiratet ist MySQL, so bin ich nicht überrascht, dass es erzeugt SQL-Script, das nicht funktioniert in Postgres.

Hinzufügen, um dieses, obwohl, Postgres 9.1 ermöglicht, nicht die Liste aller Spalten, wenn Ihre Tabelle den Primärschlüssel Teil der group by - Klausel.
Nach diesen Artikel "Debunking GRUPPE VON Mythen", es hat nichts zu tun mit "nicht-standards konforme GRUPPE DURCH".
Laut diesem Artikel, MySQL GROUP BY ist noch immer nicht kompatibel zu beiden Versionen des standard, denn es wird nicht überprüft, ob die zusätzlichen Spalten in der Auswahlliste sind abhängig von der group by-Spalten. Es wird die falsche Ausgabe von Daten ohne Warnung (kann aber dienen nützlichen Zwecken zu). PG 9.1 wird davon ausgegangen, dass incuding der PK einer Tabelle bedeutet, dass alle anderen Spalten abhängig sind, welcher der richtige ist. Dies erstreckt sich nicht auf die standard-100% (andere richtige Fragen können gekennzeichnet werden als Fehler) aber deckt die meisten Anwendungsfälle ohne wieder falsche Ergebnisse...
"Rails ist verheiratet mit MySQL, so bin ich nicht überrascht, dass es erzeugt SQL-Script, das nicht funktioniert in postgres." Ich glaube nicht, dass dies wahr ist, mehr, als Postgres ist mittlerweile sehr beliebt in der Rails-community wegen seiner noSQL-Fähigkeiten.
Schienen ist nicht mehr verheiratet zu MySQL.

InformationsquelleAutor peufeu
16

PostgreSQL, SQL-konform als MySQL. Alle Felder - mit Ausnahme berechnete Feld mit aggregation-Funktion - in der Ausgabe vorhanden sein müssen in der GROUP BY-Klausel.

InformationsquelleAutor Erlock
8

MySQL hat GROUP BY verwendet werden kann, ohne eine Aggregat-Funktion (die im Gegensatz zu dem SQL-standard), und gibt die erste Zeile in der Gruppe (ich weiß nicht, auf Basis welcher Kriterien), während PostgreSQL muss eine Aggregatfunktion (MAX, SUM, etc.) auf die Spalte, auf die der GROUP BY-Klausel ausgestellt wird.

InformationsquelleAutor Bozho
4

Richtig, die Lösung zur Behebung dieses ist zu verwenden :wählen Sie, und wählen Sie jedes Feld, das Sie möchten, dekorieren Sie das resultierende Objekt mit group by.

Böse - aber es ist, wie die Gruppe von sollte Arbeit als Gegensatz zu, wie MySQL arbeitet mit ihm durch erraten, was Sie bedeuten, wenn Sie nicht-stick Felder in Ihrer Gruppe durch.

Ich nehme an, MySQL hat, verwöhnt mich, oder hat mich ruiniert, je nachdem, welches Adjektiv Sie bevorzugen, so gibt es keine bessere Möglichkeit? Dh. werfen Sie eine Aggregatfunktion wie MAX(bookdate) oder UNTERSCHIEDLICHE, die mir wurde gesagt oben ist es viel langsamer?
Ich würde stick mit group by, aber seien Sie vorsichtig, vor allem, da Sie manuell auswählen, welche Felder Sie möchten, dekorieren Sie das Objekt mit. Auch das schreiben des Handbuchs select mit group by wird eine weitere Datenbank agnostischen Ansatz, wenn man bedenkt, dass MSSQL (wenn Sie sind unglücklich genug, um es zu benutzen) und Oracle werden auch Klagen in ähnlicher Weise.
DISTINCT bedeutet nicht unbedingt langsamer.

InformationsquelleAutor Omar Qureshi
3

Wenn ich mich richtig erinnere, in PostgreSQL, müssen Sie jede Spalte, die Sie abrufen aus, um die Tabelle, wo die GROUP BY-Klausel gilt zu der GROUP BY-Klausel.

InformationsquelleAutor Franz

Nicht die hübscheste Lösung, aber die änderung der group-parameter, um die Ausgabe jeder Spalte im Modell funktioniert in 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')

InformationsquelleAutor Ilia

1

Laut MySQL "Debuking GRUPPE VON Mythen" http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html. SQL (2003-version der standard) nicht benötigt referenzierten Spalten in der SELECT-Liste einer Abfrage, der auch in der GROUP BY-Klausel.

Aber, wie andere haben darauf hingewiesen, es erfordert Sie "funktional abhängig" von Spalten, die in der GROUP BY. MySQL-die Fähigkeit zur Referenz auf jeden nicht-gruppierte Spalte ist völlig nicht-standard, und ermöglicht es Benutzern, schreiben, unlogisch und unzuverlässig Abfragen.
Es war ein standard an der Zeit, so ist es nicht "ganz" non-standard." Ich Seite mit Ihnen, aber das wird unserer Meinung nach.
Zu welcher Zeit? Die verlinkten Artikel (via Wayback oder alt-URL), sagt, dass beide SQL:1999 und SQL:2003 Grenzwerte für GROUP BY dass MySQL ignoriert.

InformationsquelleAutor Leito

Für andere, die nach einem Weg suchen, um durch jedes Feld, einschließlich der verknüpften Feld in der postgresql-verwenden einer Unterabfrage:

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

InformationsquelleAutor riley

0

Ich denke, dass .uniq [1] wird Ihr problem lösen.
```
[1] Available.select('...').uniq
```
Werfen Sie einen Blick auf http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

InformationsquelleAutor Lucas D'Avila

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.

MySQL:

Postgres: