Problem mit "Group By" in Verbindung mit ORDER BY

[nowonowo]

Hallo zusammen,

ich habe eine Tabelle wie folgt:
start, ende, datei

Nun möchte ich die 3 Dateien mit der längsten Laufzeit ab einem bestimmten Zeitpunkt ermitteln.

Hierfür würde ich folgende Abfrage verwenden:

SELECT *,TIMESTAMPDIFF(SECOND,start,ende) AS laufzeit
FROM [TABELLE]
WHERE start >= "2016-01-25 09:04:59"
GROUP BY datei
ORDER BY laufzeit DESC
LIMIT 3

Ergebnis ohne GROUP BY:
2016-01-26 09:03:02, 2016-01-26 09:03:45, /funktionen/_anzeige/sql.php, 43
2016-01-26 08:26:44, 2016-01-26 08:27:13, /funktionen/_anzeige/sql.php, 29
2016-01-26 00:35:31, 2016-01-26 00:35:48, /niko/cronjobs/cronjobs/wetter_db/index.php, 17

Ergebnis mit GROUP BY:
2016-01-26 08:26:44, 2016-01-26 08:27:13, /funktionen/_anzeige/sql.php, 29
2016-01-26 00:35:31, 2016-01-26 00:35:48, /niko/cronjobs/cronjobs/wetter_db/index.php, 17
2016-01-26 07:24:22, 2016-01-26 07:24:32, /auslastung.php, 10

Weshalb werden hier unterschiedliche Ergebnisse geliefert?
Sinnvoll wäre es, ORDER BY vor GROUP BY zu setzen, aber das führt zu einem Syntax-Fehler...

 

[akretschmer]

Das ist, soll Bullshit. Alle Spalten im resultat müssen entweder aggregiert oder gruppiert sein. Du verstößt gegen diese Regel, MySQL zu blöd, das zu erkennen und liefert falsche Ergebnisse.

Also: mach aus dem select * ein select mit Aufzählung der benötigten Spalten und achte darauf, daß alle Spalten entweder aggregiert oder gruppiert sind. Das ORDER BY ist davon vollständig unabhängig.

Zukünftige Versionen von MySQL sowie alle anderen DB-Systeme dieser Welt werfen bei Deinem gezeigten Statement eine Syntaxfehler.

 

[nowonowo]

Vielen Dank für deine Antwort akretschmer,

ich habe mal die folgende Abfrage ausgeführt:

SELECT datei,start,ende,TIMESTAMPDIFF(SECOND,start,ende) AS laufzeit
FROM [TABELE]
WHERE start >= "2016-01-25 09:04:59"
GROUP BY datei
ORDER BY laufzeit DESC
LIMIT 3

Leider immernoch ein Unterschied im Vergleich zu der Abfrage ohne GROUP BY.

Dies liegt wohl an deinem Hinweis mit der Aggretierung / Gruppierung.
Leider weiß ich nicht, wie ich datei, start und ende (laufzeit auch?) aggretieren / gruppieren muss um das gewünschte Ergebnis zu erhalten.

 

[akretschmer]

Ähm, sorry. Du aggregierst ja gar nicht. Wozu soll aber das GROUP BY dienen?

 

[nowonowo]

Ja, ich aggretiere nicht, da ich nicht genau weiß, wie ich das machen muss..
Das GROUP BY soll dazu dienen, dass das Ergebnis nach Datei gruppiert ist, also die selbe Datei nicht mehrfach auftaucht (ich will ja die größte Laufzeit pro Datei haben).

Wenn ich das GROUP BY weglasse, dann taucht im Ergebnis die selbe Datei manchmal öfters auf, das soll ja unterbunden werden.

 

[akretschmer]

Vereinfacht sowas?

test=*# select * from nowonowo ;
 datei  | start | ende
--------+-------+------
 datei1 |  1 |  10
 datei1 |  2 |  20
 datei2 |  3 |  15
 datei2 |  5 |  10
 datei3 |  10 |  12
 datei3 |  15 |  20
(6 rows)

test=*# select datei, max(ende-start) from nowonowo group by datei;
 datei  | max
--------+-----
 datei1 |  18
 datei2 |  12
 datei3 |  5
(3 rows)

 

[akretschmer]

Vielleicht suchst auch sowas wie hier:

test=*# select datei, start, ende, ende-start as laufzeit, row_number() over (partition by datei order by ende-start desc) from nowonowo order by datei, laufzeit desc;
 datei  | start | ende | laufzeit | row_number
--------+-------+------+----------+------------
 datei1 |  2 |  20 |  18 |  1
 datei1 |  1 |  10 |  9 |  2
 datei2 |  3 |  15 |  12 |  1
 datei2 |  5 |  10 |  5 |  2
 datei3 |  15 |  20 |  5 |  1
 datei3 |  10 |  12 |  2 |  2
(6 rows)

Das allerdings kann MySQL nicht.

 

[nowonowo]

Fast...

Bei folgender Ausgangstabelle:

datei | start | ende
--------+-------+------
datei1 | 10:00 | 10:05
datei1 | 11:00 | 11:30
datei2 | 06:30 | 08:00
datei2 | 07:30 | 08:30
datei3 | 18:00 | 18:30
datei3 | 20:00 | 20:45

soll folgendes als Ergebnis herauskommen (also ende-start=laufzeit, gruppiert nach datei, sortiert nach laufzeit absteigend):

datei | (ende-start) AS laufzeit
--------+-------
datei2 | 01:30
datei3 | 00:45
datei1 | 00:30

Im Anhang habe ich mal deinen vorherigen Befehl "select datei, max(ende-start) from nowonowo group by datei;" angehängt, jedoch wird da irgendwie nicht korrekt gerechnet (ende-start)

 

[akretschmer]

In Deinen Screenshot aggregierst Du (max() ist eine Aggregatsfunktion), DANN müssen aber alle Spallten aggregiert oder gruppiert sein - ist nicht der Fall. MySQL liefert statt einem Syntaxfehler ein (oft) falsches Resultat. Davon abgesehen ist ein Timestamp mit 0000-00-00 auch Müll, weil solch einen Zeitpunkt gibt es nicht. MySQL ist halt billiger als 'ne billige Hafennutte - schluckt halt alles.

 

[nowonowo]

Ja, klingt einleuchtend. Aber wie müsste denn nun die Abfrage genau aussehen?
Stehe da irgendwie auf dem Schlauch und komme nicht weiter.

 

[ukulele]

Ich würde sagen nowonowo sucht etwas das sich in MySQL nur so lösen ließe:

SELECT    t2.start,
        t2.ende,
        t1.datei,
        t1.laufzeit
FROM    (

SELECT    datei,
        TIMESTAMPDIFF(SECOND,start,ende) AS laufzeit
FROM    [TABELE]
GROUP BY datei

        ) t1
INNER JOIN (

SELECT    datei,
        start,
        ende,
        TIMESTAMPDIFF(SECOND,start,ende) AS laufzeit
FROM    [TABELE]

        ) t2
ON        t1.datei = t2.datei
AND        t1.laufzeit = t2.laufzeit

WHERE    t2.start >= "2016-01-25 09:04:59"
ORDER BY t1.laufzeit DESC
LIMIT 3

 

[akretschmer]

Tja. MySQL ist halt behindert. Mach ein select datei, max(ende-start) ... group by datei und und joine das wieder gegen die Ursprungstabelle über (datei, ende-start).
Schöner ginge das via Window-Funktionen.

 

[nowonowo]

Hallo ukulele,

habe soeben mal deine Abfrage ausprobiert.
Funktioniert auch soweit, allerdings werden hier nicht die größten Laufzeiten ausgegeben, sondern irgendwelche (stimmen aber mit der Datei überein!).

Folgende Abfrage liefert zwar die größten Laufzeiten, allerdings nicht das dazugehörige start und ende:

SELECT datei, MAX(TIMESTAMPDIFF(SECOND,start,ende)) AS laufzeit_max
FROM [TABELLE]
WHERE start >= "2016-01-25 09:04:59"
GROUP BY datei
ORDER BY `laufzeit_max` DESC

Evtl. lässt sich die Abfrage erweitern, z.B. so:
suche für jeden Datensatz "start" und "ende", wobei "datei" und "ende-start=laufzeit" aus dem Ergebnis bekannt sind.

@akretschmer: sorry aber das mit dem "gegen die Ursprungstabelle joinen" ist mir zu hoch, da komm ich nicht mit (kenne mich nicht so tief in der Materie aus)

 

[akretschmer]

test=*# select * from nowonowo ;
 datei  | start | ende
--------+-------+------
 datei1 |  1 |  10
 datei1 |  2 |  20
 datei2 |  3 |  15
 datei2 |  5 |  10
 datei3 |  10 |  12
 datei3 |  15 |  20
(6 rows)

test=*# select * from nowonowo n inner join (select datei, max(ende-start) from nowonowo group by datei) bla on ((bla.datei, bla.max)=(n.datei,n.ende-n.start));
 datei  | start | ende | datei  | max
--------+-------+------+--------+-----
 datei1 |  2 |  20 | datei1 |  18
 datei2 |  3 |  15 | datei2 |  12
 datei3 |  15 |  20 | datei3 |  5
(3 rows)

Grundlagen lernen!

 

[nowonowo]

Ja, dass ich nicht alles weiß ich mir bekannt.
Leider funktioniert deine Abfrage bei mir nicht so wie bei dir (siehe Anhang).
Habe allerdings an das "max(ende-start)" noch max angehängt, da es sonst einen Syntax-Fehler gibt.