MySQL - Auswahl einer Spalte, die nicht in Group By

Ich versuche zum hinzufügen von Funktionen zu einer vorhandenen Anwendung, und ich kam in eine MySQL-Ansicht, so etwas wie dieses:

SELECT
     AVG(table_name.col1),
     AVG(table_name.col2),
     AVG(table_name.col3),
     table_name.personID,
     table_name.col4
FROM table_name
GROUP BY table_name.personID;

OK also es gibt ein paar Aggregatfunktionen. Sie können wählen, personID, weil Sie die Gruppierung, indem Sie es. Aber es ist auch die Auswahl einer Spalte, die nicht in einer Aggregatfunktion und nicht Teil der GROUP BY-Klausel. Wie ist das möglich??? Ist es einfach, wählen Sie eine zufällige Wert, da die Werte definitiv nicht eindeutig pro Gruppe?

Wo ich herkomme (MSSQL-Server), das ist ein Fehler. Kann mir jemand erklären, dieses Verhalten zu mir und warum ist es erlaubt in MySQL?

InformationsquelleAutor colithium | 2009-06-21

group-by mysql

47

Es ist wahr, dass dieses feature ermöglicht einige mehrdeutige Abfragen, und im hintergrund gibt ein ResultSet zurück, wird ein beliebiger Wert ausgewählt aus dieser Spalte. In der Praxis, es neigt dazu, den Wert aus der Zeile, in der Gruppe, die physisch gespeichert ersten.

Diese Abfragen sind nicht mehrdeutig, wenn Sie nur die Spalten auswählen, die funktional abhängig von der Spalte(N) in der GROUP BY-Kriterien. In anderen Worten, wenn es kann nur einen eindeutigen Wert von der "Doppeldeutige" - Spalte pro Wert definiert die Gruppe, es gibt kein problem. Diese Abfrage wäre illegal in Microsoft SQL Server (und ANSI-SQL), obwohl es logisch nicht führen Mehrdeutigkeit:
```
SELECT AVG(table1.col1), table1.personID, persons.col4
FROM table1 JOIN persons ON (table1.personID = persons.id)
GROUP BY table1.personID;
```
Außerdem hat MySQL eine SQL-Modus, um es Verhalten sich nach der standard: ONLY_FULL_GROUP_BY

FWIW, SQLite erlaubt auch diese mehrdeutige GROUP BY-Klauseln, aber es wählt den Wert aus der letzten Zeile in der Gruppe.^†

^† zumindest in der version, die ich getestet. Was es bedeutet, zu sein beliebigen ist, dass entweder MySQL oder SQLite ändern könnte, deren Umsetzung in der Zukunft, und haben einige sich anders Verhalten. Sie sollten deshalb nicht ausschließlich auf das Verhalten bleiben, wie es derzeit in unklaren Fällen wie diesem. Es ist besser zu umschreiben, Ihre Anfragen werden deterministische und nicht mehrdeutig. Das ist, warum MySQL 5.7 jetzt ONLY_FULL_GROUP_BY aktiviert standardmäßig.

Ich möchte zu der Bemerkung, dass dies nicht ganz richtig. Wie von ANSI SQL-99 markierten Felder müssen Aggregate, sind funktional abhängig von der group by-Klausel. Dabei ist die Auswahl Benutzername bei der Gruppierung nach der user_id ist völlig in Ordnung. SQL Server und Oracle nicht erfüllen, denn Sie würde nicht zulassen, dass Benutzername ausgewählt werden, wenn nur die user_id ist in der group by-Liste, und MySQL nicht erfüllen, weil er nicht überprüfen, ob die einzelnen Spalte ausgewählt ist, ist wirklich funktionell abhängig vom user_id.
vielen Dank, Sie sind richtig. MySQL 5.7 wurde verbessert, und es ist viel intelligenter, in diesem Fall der Unterstützung von ANSI SQL.
Hier ist auch ein Artikel, der beschreibt, dass die Ausgabe: percona.com/blog/2006/09/06/...

InformationsquelleAutor Bill Karwin
11

Sollte ich Googelte ein wenig länger... Es scheint, ich fand meine Antwort.

MySQL erweitert die Verwendung von GROUP BY so
die Sie verwenden können nonaggregated Spalten
oder Berechnungen in der SELECT-Liste
die nicht in der GROUP BY
Klausel. Sie können diese Funktion verwenden zu
Holen Sie sich eine bessere performance durch Vermeidung von
unnötige Spalte Sortieren und
die Gruppierung. Zum Beispiel, brauchen Sie nicht
um die Gruppe auf den Kunden.Namen in der
folgende Abfrage

In standard-SQL müssten Sie
Kunden.name der GROUP BY-Klausel.
In MySQL ist der name überflüssig ist.

Immer noch, dass scheint nur... falsch.

Du hast Recht, dass es falsch ist. Es ist! Während ich bin sicher, es gibt einige Ausnahme-Fällen, wie gezeigt, von Bill Karwin oben, viel zu oft habe ich gesehen-Entwickler, die nicht wissen, dass die Daten gut genug, oder wie diese Funktion wirklich funktioniert, das schreiben von Abfragen mit ungeeignetem group by-Klauseln und erhalten schlechte Ergebnisse. Sollte dieses feature ist standardmäßig deaktiviert und darf absichtlich überschrieben mit einer Abfrage die option für die Verwendung in Fällen, in denen der Techniker ist informiert genug, um es zu verwenden.
Es ist nicht mehr "falsch" als SELECT * FROM table1 Ergebnisse zurück, die in einer bestimmten, konsistenten Reihenfolge: es ist ein feature, kein bug.
Das ist eindeutig "falsch" (noch schlimmer:)). SQL definiert zu werden, eine set-basierte Sprache und die Reihenfolge der Elemente in einer Menge ist irrelevant. Die SELECT * FROM table1 gibt den gleichen Satz von Datensätzen, solange die Datensätze der Tabelle nicht ändern. Durch Kontrast, die eine Gruppierte Abfrage verschiedene Datensätze, je nach der Reihenfolge, wie die Datensätze in die Tabelle eingefügt wurden. Das ist definitiv falsch. Ähnlich wie viele andere Dinge im Zusammenhang mit mysql, das ist eine gefährliche gotcha mysql versucht zu verkaufen, als ein "feature".

InformationsquelleAutor colithium
0
```
select * from personel where p_id IN(select
min(dbo.personel.p_id)
FROM
personel
GROUP BY dbo.personel.p_adi)
```
Dies ist definitiv keine Antwort auf die Frage
Es muss nicht, aber es Art erklärt, was passiert. Der obige code ist ein Beispiel, wie diese nicht-standard-Verhalten kann modelliert werden mit standard-SQL.

InformationsquelleAutor Salih Kiraz
0

Lassen Sie uns sagen, Sie haben eine Abfrage wie diese:
```
SELECT g, v 
FROM t
GROUP BY g;
```
In diesem Fall für jeden möglichen Wert für g, mysql wählt eine der entsprechenden Werte von v.

Aber, was man gewählt wird, hängt von einigen Umständen ab.

Ich habe irgendwo gelesen, dass für jede Gruppe g, wird der erste Wert v gehalten wird, in der Reihenfolge, wie die Datensätze in die Tabelle eingefügt wurden t.

Dies ist ziemlich hässlich, da die Datensätze in einer Tabelle behandelt werden soll als ein set, wo die Reihenfolge der Elemente nicht von Bedeutung. Das ist so "mysql-ish"...

Wenn Sie möchten, um zu bestimmen, welcher Wert für v zu halten, müssen Sie einen Antrag einer untergeordneten select-Anweisung für t wie diese:
```
SELECT g, v 
FROM (
    SELECT * 
        FROM t 
        ORDER BY g, v DESC
) q
GROUP BY g;
```
Diese Weise definieren Sie, in welcher Reihenfolge die Datensätze der Unterabfrage verarbeitet werden, indem die externe Abfrage, damit Sie Vertrauen können, welchen Wert der v es Holen für die einzelnen Werte der g.

Allerdings, wenn Sie brauchen etwas, WO die Bedingungen dann sehr vorsichtig sein. Wenn Sie fügen der WHERE-Bedingung der Unterabfrage dann hält es das Verhalten, es wird immer wieder der Wert, den Sie erwarten:
```
SELECT g, v 
FROM (
    SELECT * 
        FROM t 
        WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9' 
        ORDER BY g, v DESC
) q
GROUP BY g;
```
Dies ist, was Sie erwarten, die untergeordnete select-Filter und sortiert die Tabelle. Es hält die Datensätze, in denen g hat den angegebenen Wert und die externe Abfrage, die g und der erste Wert für v.

Jedoch, wenn Sie hinzufügen, die gleiche WHERE-Bedingung der äußeren Abfrage, dann bekommst du eine nicht-deterministische Ergebnis:
```
SELECT g, v 
FROM (
    SELECT * 
        FROM t 
        -- WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9' 
        ORDER BY g, v DESC
) q
WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9'
GROUP BY g;
```
Überraschend ist, erhalten Sie möglicherweise unterschiedliche Werte für v bei der Ausführung die gleiche Abfrage wieder und wieder... seltsam. Das erwartete Verhalten ist, um alle Datensätze in der entsprechenden Reihenfolge aus der Unterabfrage filtern Sie in der äußeren Abfrage und dann Kommissionierung die gleiche, wie es nahm in den vorherigen Beispiel. Aber es funktioniert nicht.

Wählt er einen Wert für v scheinbar zufällig. Die gleiche Abfrage unterschiedliche Werte für v wenn ich ausgeführt mehr (~20), manchmal aber die Verteilung war nicht einheitlich.

Wenn anstelle der Zugabe eine äußere, WO Sie angeben, eine Bedingung wie diese:
```
SELECT g, v 
FROM (
    SELECT * 
        FROM t1 
        -- WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9' 
        ORDER BY g, v DESC
) q
-- WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9'
GROUP BY g
HAVING g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9';
```
Dann bekommen Sie ein konsequentes Verhalten wieder.

FAZIT: ich würde vorschlagen, sich nicht darauf zu verlassen, die auf dieser Technik überhaupt. Wenn Sie wirklich wollen/müssen, um dann zu vermeiden, wenn die Verhältnisse in der äußeren Abfrage. Verwenden Sie es in der inneren Abfrage, ob Sie oder einer HAVING-Klausel in der äußeren Abfrage.

Getestet habe ich es mit diesen Daten:
```
CREATE TABLE t1 (
    v INT,
    g VARCHAR(36)
);

INSERT INTO t1 VALUES (1, '737a8783-110c-447e-b4c2-1cbb7c6b72c9');
INSERT INTO t1 VALUES (2, '737a8783-110c-447e-b4c2-1cbb7c6b72c9');
```
in mysql 5.6.41.

Vielleicht ist es nur ein bug, der bekommt/bekam behoben in neueren Versionen, bitte geben Sie feedback, wenn Sie haben Erfahrung mit neueren Versionen.

InformationsquelleAutor Csongor Halmai

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.