MySQL: Ganzen Satz finden zu MIN( tag )

[Linuchs]

Hallo,

vor einigen Monaten wurde dieses Thema schon diskutiert, aber mangels Suchwort finde ich es nicht wieder.

Von Veranstaltern, die 1..n Termine haben, möchte ich den jeweils nächsten, den finde ich mit

...
,MIN( CONCAT( trm1.tag, ' ', trm1.uhr )) min_tag_uhr
...
GROUP BY    trm1.adress_id
ORDER BY    min_tag_uhr

Nun interessiert mich der Ort in diesen Sätzen, aber welche Werte werden bei GROUP BY geliefert? Es könnte wohl ein (zufälliger) Ort aus den Treffern vor dem GROUP BY sein?

Gruß, Linuchs

[Rolf B]

Hallo Linuchs,

das ist fast richtig. Aus welcher Row Du den Wert einer Spalte bekommst, die nicht im GROUP BY steht, ist nicht zufällig, sondern undefiniert. D.h. du wirst schon konsistent immer den gleichen Wert bekommen. Aber es gibt kein Sprachkonstrukt, das Dir die Row garantiert. VERMUTLICH wirst Du den richtigen Wert bekommen, wenn Du die Table mit einem Cluster-Index auf address_id, tag und uhr anlegst, weil die Rows dann genau so gespeichert sind, dass die MIN-Row die erste ist die er antrifft. Es gibt aber keine Garantie.

Angesichts der Tatsache, dass diese Frage so unglaublich oft gestellt wird, sollte man doch meinen, dass die SQL Designer dafür ein Sprachkonstrukt anbieten würden. Aber nein.

Man sollte auch meinen, dass es eine Tupel-Version von MIN gäbe, so dass man MIN(tag, uhr) schreiben kann und am kleinsten Tag die kleinste Uhrzeit bekäme. Aber nein.

Klassische Lösung: Self-Join vom GROUP-Konstrukt mit der Tabelle:

SELECT trm1.min_tag_uhr, trm2.ort
FROM (SELECT adress_id, MIN( CONCAT( tag, ' ', uhr )) AS min_tag_uhr
      FROM termine
      GROUP BY adress_id) trm1
  JOIN termine trm2 
   ON trm1.adress_id = trm2.adress_id 
  AND trm1.min_tag_uhr = CONCAT(trm2.tag, ' ', trm2.uhr)

Dedlfix würde den Selfjoin vermutlich sofort durch einen Subselect ersetzen. Ich glaube, das sähe dann so aus (ungetestet):

SELECT trm1.adress_id, trm1.ort, trm1.tag, trm1.uhr
FROM termine trm1
WHERE CONCAT(trm1.tag, trm1.uhr) =
         ( SELECT MIN(CONCAT(trm1.tag, trm1.uhr))
           FROM termine trm2
           WHERE trm1.adress_id=trm2.adress )

Vielleicht auch so:

SELECT trm1.adress_id, 
       trm1.nächster, 
       (SELECT trm2.ort FROM termine trm2 
        WHERE trm2.adress_id = trm1.adress_id 
          AND CONCAT(trm2.tag, ' ', trm2.uhr) = trm1.nächster) as ort
FROM (SELECT adress_id, MIN(CONCAT(tag, ' ', uhr)) as nächster,
      FROM termine trm1
      GROUP BY adress_id) trm1
ORDER BY trm1.nächster

Allerdings ist das alles angesichts des CONCAT nicht gerade effizient. Du solltest je nach Query-Aufkommen überlegen, ob Du für tag_uhr nicht eine persistente, berechnete Spalte hinzufügst und nach der auch indexierst, denn wenn du tausende von Zeilen hast, ist das eine ziemliche Table-Scannerei.

Moderne Lösung: Wenn Du MYSQL 8 oder eine aktuelle MariaDB hast, kannst Du auch Window-Funktionen benutzen. Die gibt's im MYSQL 5 nicht.

SELECT adress_id,
       ort, tag, uhr,
       ROW_NUMBER() OVER(PARTITION BY adress_id 
                         ORDER BY tag, uhr) as term_row
FROM termine
WHERE term_row = 1
ORDER BY tag, uhr

Müsste zumindest so oder so ähnlich aussehen. Ich habe Windowfunktionen noch nicht selbst gebraucht und müsste erstmal Tests machen, um Funktionsgarantie zu geben. Aber Handbuchlesen kannst Du sicher auch selbst. Und vermutlich kannst Du den ROW_NUMBER Ausdruck auch direkt ins WHERE schreiben und brauchst ihn nicht in der SELECT Liste.

Wichtig ist, dass Du ROW_NUMBER nimmst und nicht RANK. Zum einen dürfte ROW_NUMBER weniger aufwändig sein, zum anderen liefert RANK bei gleichen Werten auch den gleichen Rang. Was bei Dir vermutlich nicht vorkommt.

Aber verstehe ich das richtig, dass Du diese Abfrage übergreifend über mehrere Veranstalter hast? Wenn Du sie nur für einen Veranstalter machst, ist es einfacher, da reicht ein ORDER BY tag, uhr LIMIT 1.

Rolf

[Linuchs]

Hallo Rolf,

danke für deine Ausführungen. Ich bin ziemlich sicher, dass diese Frage schon mal gelöst wurde und ich die Lösung in eines meiner ca. 150 Programme eingebaut habe. Aber wo?

Wäre toll, wenn ich eine Übersicht der von mir gestarteten Fäden sehen könnte. Diese Funktion habe ich aber nicht gefunden.

Gruß, Linuchs

PS: Meine DB ist MySQL-Vers.=[10.1.37-MariaDB-0+deb9u1]

[Der Martin]

Hallo Linuchs,

Wäre toll, wenn ich eine Übersicht der von mir gestarteten Fäden sehen könnte. Diese Funktion habe ich aber nicht gefunden.

eine Liste aller Beiträge von dir bekommst du mit dem Suchbegriff author:Linuchs (gern auch kombiniert mit weiteren Stichworten). Aber das auf alle Threads einzugrenzen, die du gestartet hast, gibt die Suchfunktion leider nicht her.

Live long and pros healthy,
 Martin

[Rolf B]

Hallo Martin,

man muss auch auf das Startdatum achten. Per Default sind's "nur" die letzten 2 Jahre.

Eine Beschränkung auf Threads, die man selbst eröffnet hat, gibt's nicht, richtig, aber bei Linuchs ist das so schlimm nicht. Zumeist diskutiert er nur in seinen eigenen Threads mit. Oder ich erinnere mich falsch 😉

Rolf

[Der Martin]

n'Abend Rolf,

man muss auch auf das Startdatum achten. Per Default sind's "nur" die letzten 2 Jahre.

ja, ein Default, das mich oft nervt.Bei mir sind's oft Stichworte, bet denen ich beim besten Willen nicht sagen könnte, ob der gesuchte Beitrag zwei oder sieben Jahre her ist. Das persönlihche Zeitgefühl ist immer wieder überraschend.

Live long and pros healthy,
 Martin

[Linuchs]

Zumeist diskutiert er nur in seinen eigenen Threads mit.

Ich schaue immer mal wieder, wo ich Antworten geben kann. Aber entweder weiß ich es nicht oder der Faden ist schon so ausführlich, dass ich als Teilnehmer hinterherlaufe.

Was ich nur noch selten mache: Am Thema vorbei schreiben. Ja, manchmal fehlt ein Komma und ich hätte noch zwei in der Schublade. Muss ich das verraten? Bei „vorraussichtlich“ wird das r nicht so bayerisch gerollt, und bayerisch schreibt man vielleicht ohne e.

[Rolf B]

Hallo Linuchs,

Maria 10.1 kennt noch keine Window-Funktionen. Die kommen in 10.2.

• Maria 10.1 ist out-of-support.
• Support von MariaDB 10.2 endet Mai 2022
• Support von MariaDB 10.3 ist für Mai 2023 angekündigt
• Support von MariaDB 10.4 ist für Juni 2024 angekündigt
• Support von MariaDB 10.5 ist für Juni 2025 angekündigt
• MariaDB 10.6 ist die aktuelle, stabile Version. Ein End of Support ist nicht angekündigt, aber wenn ich mir den Rest so anschaue, dürfte es Juni 2026 sein.
• MariaDB 10.7 ist in Entwicklung.

Empfehlung: Update nach Maria 10.6. Meine Spielinstallation ist aber auch noch 10.5...

Rolf

[Linuchs]

Ich bin ziemlich sicher, dass diese Frage schon mal gelöst wurde

Nee, war nur so ähnlich: GROUP_CONCAT gibt alle Werte einer Spalte aus, die vor GROUP BY gefunden wurden

[dedlfix]

Tach!

Klassische Lösung: Self-Join vom GROUP-Konstrukt mit der Tabelle:

SELECT trm1.min_tag_uhr, trm2.ort
FROM (SELECT adress_id, MIN( CONCAT( tag, ' ', uhr )) AS min_tag_uhr
      FROM termine
      GROUP BY adress_id) trm1
  JOIN termine trm2 
   ON trm1.adress_id = trm2.adress_id 
  AND trm1.min_tag_uhr = CONCAT(trm2.tag, ' ', trm2.uhr)

Dedlfix würde den Selfjoin vermutlich sofort durch einen Subselect ersetzen. Ich glaube, das sähe dann so aus (ungetestet):

Ich würde vor allem das CONCAT() zu ersetzen versuchen. Denn solche Konstrukte, die für jeden Datensatz erst berechnet werden müssen, bevor sie ausgewertet werden können, sind Index-Killer.

SELECT trm1.min_tag_uhr, trm2.ort
FROM (SELECT adress_id, tag, uhr
      FROM termine
      GROUP BY adress_id
      ORDER BY tag, uhr
      LIMIT 1
     ) trm1
  JOIN termine trm2 
    ON trm1.adress_id = trm2.adress_id 
      AND trm1.tag = trm2.tag
      AND trm1.uhr = trm2.uhr

Inwieweit das funktioniert, kann ich nicht sagen, weil immernoch nach Spalten selektiert wird, nach denen nicht gruppiert wird, und sie so undefinierte Ergebnisse liefern.

dedlfix.

[Rolf B]

Hallo dedlfix,

ja, der "undefined order" Aspekt bleibt erhalten.

Ich würde vor allem das CONCAT() zu ersetzen versuchen.

Ich auch, deswegen schlug ich ja die berechnete, persistente Spalte vor.

SELECT ...
GROUP BY ...
LIMIT 1

ist merkwürdig - das liefert doch nur das Ergebnis für die erste Adress-ID und nicht den ersten Satz pro Gruppe. Zumindest ist das bei mir hier so.

Linuchs, sind Tag und Uhr CHAR-Fehler? Oder sind sie korrekt DATE und TIME? Es ist vermutlich zu viel Legacy - aber ich nehme an, dein Leben wäre viel einfacher, wenn das eine einzige DATETIME Spalte wäre.

Rolf

[dedlfix]

Tach!

SELECT ...
GROUP BY ...
LIMIT 1

ist merkwürdig - das liefert doch nur das Ergebnis für die erste Adress-ID und nicht den ersten Satz pro Gruppe. Zumindest ist das bei mir hier so.

Ich hab es nicht geprüft, aber wenn du das so sagst, war das wohl kein Weg. Wenn tag und uhr nicht zwei Spalten wären, sondern ein DateTime-Feld, könnte man das doch recht einfach über eine Corellated Subquery zurückgeben und muss nicht gruppieren.

dedlfix.

[Rolf B]

Hallo Rolf,

SELECT adress_id,
       ort, tag, uhr,
       ROW_NUMBER() OVER(PARTITION BY adress_id 
                         ORDER BY tag, uhr) as term_row
FROM termine
WHERE term_row = 1
ORDER BY tag, uhr

geht nicht. Man kann term_row nicht im WHERE ansprechen und man kann keine Window-Funktion im WHERE verwenden.

Was geht, ist eine CTE (Common Table Expression) - ab Maria DB 10.2.1:

WITH terminsort AS (
   SELECT adress_id, ort, tag, uhr,
          ROW_NUMBER() OVER(PARTITION BY adress_id 
                            ORDER BY tag, uhr) as zeile
   FROM termine
)
SELECT adress_id, ort, tag, uhr
FROM terminsort
WHERE zeile = 1

oder, wenn man noch eine Alt-Maria, mit einem SELECT als FROM-Angabe. MYSQL 5.x kennt keine Window-Funktionen, da bleibt's beim Selfjoin.

SELECT ts.adress_id, ts.ort, ts.tag, ts.uhr
FROM (SELECT adress_id, ort, tag, uhr,
       ROW_NUMBER() OVER(PARTITION BY adress_id 
                         ORDER BY tag, uhr) as zeile
      FROM termine) ts
WHERE ts.zeile = 1

Rolf

[Linuchs]

Habe das in der Form gelöst, dass ich alle benötigten Felder in ein CONCAT gepackt habe:

SELECT
 MIN( CONCAT( trm1.tag, '@', trm1.uhr, '@', trm1.id, '@', DATE_FORMAT(trm1.tag,'%w'), '@', ort1.id, '@', ort1.land_kz, '@', ort1.plz, '@', ort1.name, '@', adr1.id, '@', adr1.firma1 )) nxt_termin
...
GROUP BY    trm1.adress_id
ORDER BY    nxt_termin

die explodieren dann und stehen zur Verfügung:

  while ( $row_naechster_termin = @mysql_fetch_assoc( $res_naechste_termine )) {
    $arr      = explode( "@", $row_naechster_termin['nxt_termin'] );

Der Programm-Durchlauf dauert knapp 0.1 sec, damit kann ich leben.

Gruß, Linuchs

[Rolf B]

Hallo Linuchs,

es funktioniert für Dich und dann lassen wir es dabei. Es dürfte wohl auch tatsächlich schneller sein als eine correlated subquery oder ein self join.

Aber schau Dir mal CONCAT_WS an. Dieser Funktion übergibst Du das Trennzeichen als ersten Parameter und dann alle zu konkatenierenden Werte. Dann musst Du das '@' nicht ständig wiederholen.

Überlegung am Rande: Da Du die tag-Spalte im PHP wohl ohnehin in ein Date konvertieren musst, hättest Du die Wochentagbestimmung auch dort durchführen können. Aber es macht im SQL den Code auch nicht wirklich fetter...

Für die Nachwelt möchte ich aber sagen: Vorsicht damit.

• Bei Linuchs ist die Chance hoch, dass die Daten kein @ enthalten, so dass der EXPLODE das Erwartete tut. Aber wehe, die Lösung wird anderswohin übertragen und eins der so eingesammelten Felder enthält doch mal ein @ Zeichen. Dieser CONCAT erzeugt eine Art CSV-String, und damit muss man immer aufpassen.
• Die MIN Bildung funktioniert hier, weil tag und uhr feste Länge haben. Das Datum muss dafür Bei variabler Länge führt die Verkettung zu falschen oder unerwarteten Ergebnissen
• An Stelle von CONCAT oder CONCAT_WS könnte man auch JSON_ARRAY nehmen (Ab Maria DB 10.2.3 oder MYSQL 8. Zumindest MariaDB kann ein MIN auf JSON-Werte bilden, und im PHP kann man sie dann auch wieder mit JSON_DECODE zerlegen.
• Die effizienteste Lösung würde ich aber immer noch bei der ROW_NUMBER Idee suchen - vorbehaltlich eines Tests mit großen Datenmengen.

Rolf

[Rolf B]

Hallo Linuchs,

ich habe jetzt eine Testtabelle angelegt und Performance gemessen.

Dein Vorgehen ist aus Sicht der reinen DB Lehre völlig bäh.

Und wie alles vieles, was bäh ist, flutscht es wie ein Zäpfchen. Auf meiner Testtabelle (50000 Rows, 100 Adressen, demnach je Adresse ca 500 Termine) braucht deine Abfrage 30 bis 40 Millisekunden. Egal ob mit oder ohne Index, er macht immer einen Tablescan, den aber fix.

Mein Vorschlag mit der CTE ist deutlich am langsamsten. Ohne Index 2500 Sekunden, mit Primary Index auf (adress_id, tag, uhr) immerhin 250ms.

Die Lösung mit Selfjoin liegt bei 80ms und kommt runter auf 30ms wenn ich eine persistente Zusatzspalte nextTermin in die tabelle lege, die mit CONCAT(tag,'@',uhr) befüllt ist und einen Index auf adress_id und nextTermin lege.

Mit dieser Zusatzspalte und Zusatzindex geht deine Query aber runter auf "Unmessbar kurz" - solange ich das Minimum nur auf tag@uhr bilde. Deine Langfassung mit allen Feldern drin kann wieder den Index nicht nutzen und kommt auf 40ms bis 50ms.

Hier ist der Selfjoin besser als deine Lösung.

Die dedlfixsche Subselect-Lösung ist ebenfalls rasend schnell (0-15ms), kann aber nur eine Spalte liefern. Mischt man wieder Bäh ins Getriebe, d.h. bringt in den Subselect eine CONCAT_WS('@',ort,id,foo,bar,baz) hinein, kann man damit wieder mehr als eine Spalte herausholen und hat unterm Strich die schnellste Lösung.

Aber die Selfjoin-Lösung ist - mit Index und nextTermin-Feld in der DB - konkurrenzfähig und deutlich näher an dem, was SQL ist.

Rolf