wake-up-neo.com

MySQL - Auswählen einer Spalte, die nicht in Group By enthalten ist

Ich versuche, Funktionen zu einer bereits vorhandenen Anwendung hinzuzufügen und bin auf eine MySQL-Ansicht gestoßen, die etwa so aussieht:

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, es gibt also einige Aggregatfunktionen. Sie können personID auswählen, weil Sie danach gruppieren. Es wird jedoch auch eine Spalte ausgewählt, die sich nicht in einer Aggregatfunktion befindet und nicht Teil der GROUP BY-Klausel ist. Wie ist das möglich??? Wählt es nur einen zufälligen Wert, weil die Werte definitiv nicht für jede Gruppe eindeutig sind?

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

43
colithium

Es ist wahr, dass diese Funktion einige mehrdeutige Abfragen zulässt und im Hintergrund eine Ergebnismenge mit einem beliebigen Wert aus dieser Spalte zurückgibt. In der Praxis ist dies meist der Wert aus der Zeile innerhalb der Gruppe, die zuerst physisch gespeichert wird.

Diese Abfragen sind nicht eindeutig, wenn Sie nur Spalten auswählen, die funktional von den Spalten in den GROUP BY-Kriterien abhängig sind. Wenn also nur ein eindeutiger Wert der Spalte "mehrdeutig" pro Wert vorhanden ist, der die Gruppe definiert, gibt es kein Problem. Diese Abfrage wäre in Microsoft SQL Server (und ANSI SQL) ungültig, auch wenn sie logischerweise nicht zu Mehrdeutigkeiten führen kann:

SELECT AVG(table1.col1), table1.personID, persons.col4
FROM table1 JOIN persons ON (table1.personID = persons.id)
GROUP BY table1.personID;

Außerdem verfügt MySQL über einen SQL-Modus, damit es sich gemäß dem Standard verhält: ONLY_FULL_GROUP_BY

FWIW, SQLite erlaubt auch diese mehrdeutigen GROUP BY-Klauseln, wählt jedoch den Wert aus der Zeile last in der Gruppe.


Zumindest in der Version, die ich getestet habe. willkürlich bedeutet, dass entweder MySQL oder SQLite ihre Implementierung in der Zukunft ändern und ein anderes Verhalten haben könnten. Sie sollten sich daher nicht darauf verlassen, dass das Verhalten in so mehrdeutigen Fällen so bleibt, wie es derzeit ist. Es ist besser, Ihre Abfragen neu zu schreiben, um deterministisch und nicht mehrdeutig zu sein. Aus diesem Grund aktiviert MySQL 5.7 jetzt standardmäßig ONLY_FULL_GROUP_BY.

47
Bill Karwin

Ich hätte nur ein bisschen länger googeln sollen ... Es scheint, als hätte ich meine Antwort gefunden.

MySQL erweitert die Verwendung von GROUP BY so, dass Nicht aggregierte Spalten Oder Berechnungen in der SELECT-Liste Verwendet werden können, die nicht in der Klausel GROUP BY Enthalten sind. Sie können diese Funktion verwenden, um Eine bessere Leistung zu erzielen, indem Sie Unnötige Spaltensortierung und Gruppierung vermeiden. Beispielsweise brauchen Sie Nicht, um Kundenname in der folgenden Abfrage Zu gruppieren

In Standard-SQL müssten Sie der GROUP BY-Klausel Customer.name hinzufügen. In MySQL ist der Name überflüssig.

Trotzdem scheint das nur ... falsch zu sein.

11
colithium

Nehmen wir an, Sie haben eine Abfrage wie diese:

SELECT g, v 
FROM t
GROUP BY g;

In diesem Fall wählt mysql für jeden möglichen Wert für g einen der entsprechenden Werte von v.

Welche davon gewählt wird, hängt jedoch von einigen Umständen ab. 

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

Dies ist ziemlich hässlich, da die Datensätze in einer Tabelle als Satz behandelt werden sollten, bei dem die Reihenfolge der Elemente keine Rolle spielt. Das ist so "mysql-ish" ...

Wenn Sie ermitteln möchten, welchen Wert für v beibehalten werden soll, müssen Sie für t eine Unterauswahl wie folgt anwenden:

SELECT g, v 
FROM (
    SELECT * 
        FROM t 
        ORDER BY g, v DESC
) q
GROUP BY g;

Auf diese Weise definieren Sie, in welcher Reihenfolge die Datensätze der Unterabfrage von der externen Abfrage verarbeitet werden. Sie können also darauf vertrauen, welchen Wert von v die einzelnen Werte von g auswählen sollen. 

Wenn Sie jedoch einige WHERE-Bedingungen benötigen, seien Sie sehr vorsichtig. Wenn Sie der Unterabfrage die WHERE-Bedingung hinzufügen, wird das Verhalten beibehalten. Es wird immer der von Ihnen erwartete Wert zurückgegeben:

SELECT g, v 
FROM (
    SELECT * 
        FROM t 
        WHERE g = '737a8783-110c-447e-b4c2-1cbb7c6b72c9' 
        ORDER BY g, v DESC
) q
GROUP BY g;

Das erwarten Sie, die Unterauswahl filtert und ordnet die Tabelle. Die Datensätze werden dort aufbewahrt, wo g den angegebenen Wert hat, und die externe Abfrage gibt g und den ersten Wert für v zurück. 

Wenn Sie jedoch dieselbe WHERE-Bedingung zur äußeren Abfrage hinzufügen, erhalten Sie ein nicht deterministisches 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;

Überraschenderweise erhalten Sie möglicherweise unterschiedliche Werte für v, wenn Sie dieselbe Abfrage immer wieder ausführen, was ... seltsam ist. Das erwartete Verhalten besteht darin, alle Datensätze in der entsprechenden Reihenfolge aus der Unterabfrage abzurufen, sie in der äußeren Abfrage zu filtern und dann dieselbe Auswahl wie im vorherigen Beispiel auszuwählen. Tut es aber nicht 

Es wählt scheinbar zufällig einen Wert für v. Dieselbe Abfrage ergab unterschiedliche Werte für v, wenn ich mehr (~ 20) Male ausgeführt habe, die Verteilung jedoch nicht einheitlich war. 

Wenn Sie nicht ein äußeres WHERE hinzufügen, geben Sie eine HAVING-Bedingung wie folgt an:

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 erhalten Sie wieder ein konsistentes Verhalten. 

SCHLUSSFOLGERUNG: Ich würde vorschlagen, sich überhaupt nicht auf diese Technik zu verlassen. Wenn Sie wirklich wollen/müssen, dann vermeiden Sie WHERE-Bedingungen in der äußeren Abfrage. Verwenden Sie es in der inneren Abfrage, wenn Sie können, oder eine HAVING-Klausel in der äußeren Abfrage. 

Ich habe es mit diesen Daten getestet:

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 Fehler, der in neueren Versionen behoben wurde/wurde. Bitte geben Sie Rückmeldung, wenn Sie Erfahrung mit neueren Versionen haben. 

0
Csongor Halmai
select * from personel where p_id IN(select
min(dbo.personel.p_id)
FROM
personel
GROUP BY dbo.personel.p_adi)
0
Salih Kiraz