mySQL: Satz mit dem minimalen Wert

[Linuchs]

Moin,

aus einem Kalender möchte ich pro Nation den nächsten Termin anzeigen

MIN( trm1.tag ) ... GROUP BY ort1.Land_kz gibt mir zwar den nächsten Tag und ich kann zum GROUP auch land_kz bekommen, aber wie komme ich an die übrigen Felder dieser Treffer?

Gibt der MIN-Wert auch die zugehörigen anderen Werte mit? Ich meine, das wäre nur zufällig/häufig, aber nicht verlässlich so.

Die MariaDB Doku hilft nicht weiter.

Gruß, Linuchs

[dedlfix]

Tach!

Gibt der MIN-Wert auch die zugehörigen anderen Werte mit? Ich meine, das wäre nur zufällig/häufig, aber nicht verlässlich so.

Wenn es Datensätze mit den Werten 1, 1, 2 und 3 gibt, dann gibt MIN() natürlich den Wert 1 zurück. Wenn du nun neben einer solchen Aggregatfunktion noch weitere Felder ins SELECT schreibst, die nicht in der GROUP-BY-Bedingung auftauchen, dann sehen das einige DBMS als Fehler an. MySQL/MariaDB in der Default-Einstellung lassen das zwar durch, aber aus welchem der beiden Datensätze die Werte kommen, ist nicht definiert.

Man kann solch ein Statement nur dann gefahrlos verwenden, wenn die Daten redundant sind, also egal welchen Datensatz man nimmt, dieselben Werte geliefert werden.

Ansonsten kannst du nur im ersten Schritt den MIN-Wert ermitteln und in einem zweiten Schritt alle Datensätze mit diesem Wert abfragen. Wenn du nun aber nur einen davon haben möchtest, musst du mit deinem Anwendungsfall klären, wie da zu verfahren ist.

dedlfix.

[Linuchs]

Wenn es Datensätze mit den Werten 1, 1, 2 und 3 gibt, dann gibt MIN() natürlich den Wert 1 zurück. ... aber aus welchem der beiden Datensätze die Werte kommen, ist nicht definiert.

Das reicht mir. Wenn es mehrere Kleinste gibt, nehme ich davon einen beliebigen.

Danke

[Rolf B]

Hallo Linuchs,

sicher?

Angenommene Datenbasis:

Col1   Col2   Col3   Col4
A       2      A23    A24     
A       1      A13    A14 
B       3      B33    B34
B       2      B23    B24

Darauf wenden wir an:

SELECT Col1, MIN(Col2) As Min2, Col3, Col4
FROM   table 
GROUP BY Col1

Ergebnis kann sein:

Col1   Min2   Col3   Col4
A       1      A23    A24
B       2      A33    A34

Warum: Für Min2 hast Du eine Aggregatfunktion und bekommst das Minimum über der Col1-Gruppe. Für Col3 und Col4 hast Du keine Aggregatfunktion und bekommst die Werte einer beliebigen Zeile aus der Col1-Gruppe. Zumeist die erste. Wenn darin nicht das Minimum steht, ist das Ergebnis Müll.

Ich habe das gerade einmal mit MYSQL 5.6 ausprobiert, genau so scheint's zu passieren.

Rolf

[Rolf B]

Hallo Linuchs,

diesen Umstand, dass MYSQL den ersten Satz nimmt den es antrifft, kann man aber auch ausnutzen:

SELECT *
FROM (SELECT key, value, col1, col2, col3)
      FROM sometable
      ORDER BY key, value) xyz
GROUP BY key

Der ORDER BY sorgt dafür, dass der Satz mit MIN(value) dem GROUP BY als erstes zum Fraße vorgeworfen wird. Für die übrigen Spalten wählt MYSQL die Werte des ersten Satzes, und - ta dah - der gewünschte Effekt ist erreicht.

ABER ABER ABER: Das ist das Ausnutzen eines beobachteten Verhaltens, das die Doku als "undefiniert" beschreibt. Es gibt keine Garantie, dass dieses Verhalten so bleiben wird. Mein Self-Join, den ich vorhin beschrieben habe, basiert auf definiertem Verhalten.

Rolf

[Linuchs]

Ich habe nun zwar pro Nation ein Event, aber das nächste in D soll erst in einigen Wochen am 13.08.2019 sein. Das stimmt nicht.

Darf man nur eine Tabelle verwenden?

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# naechstes Event pro Nation
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT
 SQL_CALC_FOUND_ROWS
 MIN( trm1.tag )
,ort1.*
,trm1.*
,trm1.id                        TID
,DATE_FORMAT(trm1.tag,'%w')     wotag
,DATE_FORMAT(trm1.tag_bis,'%w') wotag_bis

,ort1.land_kz
,ort1.plz
,ort1.name        ort_name
,ort1.geo_breite  ort_geo_breite
,ort1.geo_laenge  ort_geo_laenge

#,ROUND( 6366.0 * ACOS( SIN() *SIN(RADIANS(ort1.geo_breite)) +COS() *COS(RADIANS(ort1.geo_breite)) *COS(RADIANS(ort1.geo_laenge) - ))) distanz_km
,adr1.id      VIP
,adr1.firma1
,trp1.id          TRP
,trp1.bild_url    trp_bild_url  -- fuer icon_webcam
,trp1.bild_titel  trp_bild_titel
,typ1.id          TYP
,typ1.name        typ_name

FROM      (bia_termine trm1
          ,bia_orte ort1 )

LEFT JOIN   bia_adressen adr1
ON          adr1.id = trm1.adress_id

LEFT JOIN   bia_treffpunkte trp1
ON          trp1.id = trm1.treffpunkt_id

LEFT JOIN   bia_termintypen typ1
ON          typ1.id = trm1.typ_id

WHERE       trm1.owner_id = '1'

AND         ort1.id = trm1.ort_id
AND        (trm1.gesperrt_bis IS NULL OR trm1.gesperrt_bis <= NOW())
AND         trm1.intern_kz = 0
AND         (trm1.tag >= '2019-07-01' OR  trm1.tag_bis >= '2019-07-01' )
AND         (trm1.tag <= '2020-06-30' OR  trm1.tag_bis <= '2020-06-30' )
# AND         (trm1.tag >= CURDATE() OR  trm1.tag_bis >= CURDATE() )
GROUP BY    ort1.land_kz

Ein Ausweg wäre vielleicht

 MIN( CONCAT( trm1.tag, '|', trm1.id ))  tag_id

dann hätte ich die ids zu den gesuchten Terminen. Aber schon seeehr konstruiert.

Linuchs

[Linuchs]

Der Fehler lag wohl in diesen Zeilen:

AND         (trm1.tag >= '2019-07-01' OR  trm1.tag_bis >= '2019-07-01' )
AND         (trm1.tag <= '2020-06-30' OR  trm1.tag_bis <= '2020-06-30' )

Da tag_bis bei eintägigen Events NULL sein kann, habe ich dieses probiert:

AND         (trm1.tag >= '2019-07-01' OR  trm1.tag_bis IS NOT NULL AND trm1.tag_bis >= '2019-07-01' )
AND         (trm1.tag <= '2020-06-30' OR  trm1.tag_bis IS NULL      OR trm1.tag_bis <= '2020-06-30' )

Gibt aber auch nicht die erwarteten Ergebnisse. Irgendwie habe ich den Einschluss/Ausschluss der NULL nicht verstanden. Nun habe ich es gekürzt, aber mehrtägige Events, die vor heute beginnen und noch laufen, sind nicht enthalten:

AND         (trm1.tag >= '2019-07-01' )
AND         (trm1.tag <= '2020-06-30' )

Linuchs

[Rolf B]

Hallo Linuchs,

du möchtest Events sehen, deren Ende heute oder in der Zukunft liegt? Und bei eintägigen Events ist tag_bis null, da musst Du tag verwenden?

D.h. bei mehrtägigen Terminen ist trm1.tag eigentlich wurscht.

Wie wäre es mit
COALESCE(tag_bis, tag) >= '2019-07-01' AND tag < '2020-07-01'

Rolf

[Linuchs]

Hallo Rolf,

Wie wäre es mit

COALESCE(tag_bis, tag) >= '2019-07-01' AND tag < '2020-07-01'

Was es alles gibt ... Danke für die Anregung.

Wenn ich den Juli betrachte, möchte ich eintägige Events vom 1. - 31.07. und mehrtägige Events, die komplett im Juli liegen, im Juli beginnen oder im Juli enden.

Dein Beispiel ist verkürzt. Ist das so richtig?

#AND (tag >= "2019-07-01" OR tag_bis >= "2019-07-01") -- Problem, wenn tag_bis NULL ist
 AND (tag >= "2019-07-01" OR COALESCE( trm1.tag_bis, trm1.tag ) >= "2019-07-01")  -- tag_bis darf NULL sein
#AND (tag <= "2019-07-31" OR tag_bis <= "2019-07-31") -- Problem, wenn tag_bis NULL ist
 AND (tag <= "2019-07-31" OR COALESCE( trm1.tag_bis, trm1.tag ) <= "2019-07-31")  -- tag_bis darf NULL sein

Linuchs

[Rolf B]

Hallo Linuchs,

möchte ich eintägige Events vom 1. - 31.07. und mehrtägige Events, die komplett im Juli liegen, im Juli beginnen oder im Juli enden.

Wenn Du Events haben willst, die im Juli laufen, aber ggf. vorher schon begonnen haben, musst Du
COALESCE(trm1.tag_end, trm1.tag) >= '2019-07-01'
abfragen.

Für Events, die im Juli beginnen und bis in den August hinein laufen dürfen, fragst Du nach trm1.tag <= '2019-07-31'

Jedenfalls ist (tag >= "2019-07-01" OR COALESCE( trm1.tag_bis, trm1.tag ) >= "2019-07-01") unnötig kompliziert. Der links vom OR abgefragte Zeitraum ist eine Teilmenge des rechts abgefragten Zeitraums, daher ist der linke Teil überflüssig. Das gleiche gilt für deine Abfrage der Obergrenze.

Rolf

[Rolf B]

Hallo Linuchs,

autsch. Jetzt weiß ich, warum Du die Query erstmal nur gekürzt präsentiert hattest 😂

Aber egal. Auch mit dieser Query sollte ein GROUP BY funktionieren. Um sicher zu gehen, dass ansonsten alles ok ist, ändere den GROUP BY einmal vorübergehend in einen ORDER BY ort1.land_kz, trm1.tag. Dann siehst Du ja, was er Dir für D als erstes ausweist. Wenn Du andere Termine im System hast, die vorher erscheinen müssten, dann prüfe, ob die von einer anderen WHERE Bedingung entfernt werden.

Meinen Vorschlag von 11:49 kannst Du aber trotzdem anwenden. Meine dort aus dem Ärmel geschüttelte hypothetische Query war ja fast richtig. Nachdem du bitte erstmal die grundsätzliche Richtigkeit deiner Query überprüft hast, ergänze sie wie folgt:

...
FROM      (bia_termine trm1
          ,bia_orte ort1 )

LEFT JOIN   bia_adressen adr1
...

ersetze durch

...
FROM       bia_termine trm1 
JOIN       bia_orte ort1
        ON ort1.id = trm1.ort_id

JOIN      (SELECT ort2.land_kz, MIN(trm2.tag) AS min_tag
           FROM bia_termine trm2 JOIN bia_orte ort2 ON ort2.id = trm2.ort_id
           GROUP BY ort2.land_kz) key
        ON key.land_kz = ort1.land_kz AND key.min_tag = trm1.tag

LEFT JOIN   bia_adressen adr1
...
AND         ort1.id = trm1.ort_id       <<<-- Zeile entfällt
...

Der JOIN mit dem key Alias sorgt dafür, dass Du pro Land 1-N Zeilen zum frühesten Termin bekommst. Deswegen muss der äußere GROUP BY stehen bleiben, damit Du nur einen Termin pro Tag bekommst.

Rolf

[Sample Simplizissimus]

Hier gab es eine Menge erstaunlich komplizierter Lösungsvorschläge...

wenn es mehrere Kleinste gibt, nehme ich davon einen beliebigen.

Das macht es einfach:

SELECT foo, bar
FROM table
ORDER BY tok
LIMIT 1;

Liefert die Spalten foo und bar des ersten Datensatzes der nach tok sortierten Tabelle. Mithin die Zeile, in der tok am kleinsten ist, wenn es mehrere gleiche tok gibt, eben eine beliebige der Zeilen mit dem kleinsten tok.

[Rolf B]

Hallo Simplizist,

in der Problemstellung war ein GROUP BY.

Rolf

[Sample Simplizissimus]

Hallo Simplizist,

in der Problemstellung war ein GROUP BY.

Was aber im Kern nichts daran ändert, dass ORDER BY und LIMIT die Instrumente sind, mit denen man die Datensätze für den größten oder kleinsten Wert regelmäßig aussiebt.

@Linuchs: Was übrigens daneben auch die Gedanken hinsichtlich einer View ergänzt: Schon mal über eine Funktion nachgedacht?

[Rolf B]

Hallo Simplizist,

LIMIT wirkt auf die Gesamtmenge nach HAVING, nicht auf die Sätze einer GROUP BY Gruppe. Wenn Du also den Satz zu einem kleinsten Wert pro Gruppe willst, nützt das nichts.

Die Window-Funktionen von MYSQL 8 können innerhalb einer Partition selektieren, aber angesichts des Datenmonsters, das Linuchs da einsetzt, würde das wohl übel unübersichtlich. Obwohl - man könnte nach land_kz und tag sortieren, über land_kz partitionieren und dann row_number()=1 auswählen.

Rolf

[Linuchs]

Schon mal über eine Funktion nachgedacht?

Als ich 2001 von ORACLE zu mySQL kam, war so Einiges nicht möglich. Kein Wunder, bei einer kostenlosen Software. Da habe ich so manche Verrenkung gemacht und inzwischen die pfiffigen Möglichkeiten sowohl verpennt als auch verlernt.

Muss ich mich mal mit beschäftigen.

Linuchs

[Rolf B]

Hallo Linuchs,

das macht man mittels eines Join des GROUP BY Ergebnisses mit der GROUP BY Quelle. Mit einer einzigen Table sieht das grundsätzlich so aus:

SELECT a.key, a.value, a.col1, a.col2, a.col3, ...
FROM table a JOIN (SELECT key, MIN(value) FROM table GROUP BY key) b 
               ON a.key=b.key AND a.value=b.value

Deine Ausgangstabelle in ein JOIN-Ergebnis, d.h. du musst überall da, wo table steht, deinen JOIN eintragen.

Da ich keine Ahnung habe, wie deine Query aussieht, unterstelle ich mal einfach die folgende Ausgangsquery. Deine wird anders aussehen, aber das macht nichts, es geht ja um's Prinzip.

SELECT ort1.land_kz, min(trm1.tag)
FROM Orte as ort1 JOIN termine as trm1 ON trm1.ortid=ort1.ortid
GROUP BY ort1.land_kz

Darauf wenden wir nun das oben gezeigte Pattern an, und erhalten:

SELECT ort2.ort_id, ort2.land_kz, ort2.dings, ort2.bums,
       trm2.tag, trm2.foo, trm2.bar, trm2.baz
FROM orte as ort2 
     JOIN termine as trm2 ON ort2.ortid=ort2.ortid
     JOIN (SELECT ort1.land_kz, MIN(trm1.tag) as min_tag
           FROM Orte as ort1 
                JOIN termine as trm1 ON trm1.ortid=ort1.ortid
           GROUP BY ort1.land_kz) key
       ON key.land_kz = ort2.land_kz AND key.min_tag = trm2.tag

Das liest sich scheußlich, ist aber leider so. Du kannst die Lesbarkeit leicht steigern wenn du den JOIN von Terminen und Orten als VIEW definierst, aber ob es das wirklich besser macht... Keine Ahnung.

Rolf

[Linuchs]

Es sieht so aus, dass NICHT der Treffer ausgeliefert wird, der dem gefundenen MIN Datum entspricht.

OFFENBAR wird der letzte gefundene Treffer des verlangten Zeitraums ausgeliefert, BEVOR MIN gebildet wird.

MIN( CONCAT( trm1.tag, '|', trm1.id ))  tag_id

für D im Juli ergibt 2019-05-18|26142. Das könnte stimmen, die Termin-ID ist eine Ausstellung vom Samstag, 18. Mai 2019 bis Sonntag, 21.07.

mitgeliefert wird aber ein Event am 28.7., womöglich das letzte im gewünschten Zeitraum.

[dedlfix]

Tach!

Es sieht so aus, dass NICHT der Treffer ausgeliefert wird, der dem gefundenen MIN Datum entspricht.

OFFENBAR wird der letzte gefundene Treffer des verlangten Zeitraums ausgeliefert, BEVOR MIN gebildet wird.

Achja, jetzt wo du es sagst ... Es sind beliebige Daten aus der Gruppe. Welche Aggregatfunktion verwendet wird, spielt keine Rolle. Man kann ja auch MIN() und MAX() gleichzeitig nehmen. Oder SUM(), oder AVG(). Woher sollen denn dann die Daten kommen?

Da muss ich meine Antwort korrigieren, dass aus dem Teil

aber aus welchem der beiden Datensätze die Werte kommen, ist nicht definiert.

wird:

aber aus welchem der Datensätze der Gruppe die Werte kommen, ist nicht definiert.

Der Rest bleibt, insbesondere der letzte Abschnitt zur Vorgehensweise. Wenn nur die MIN-Datensätzt interessieren, muss in einer zweiten Abfrage der MIN-Wert als WHERE-Kriterium genommen werden. Das kann man auch als Subselect schachteln.

dedlfix.

[Linuchs]

Wenn nur die MIN-Datensätzt interessieren, muss in einer zweiten Abfrage der MIN-Wert als WHERE-Kriterium genommen werden.

Ich mache jetzt 2 Durchgänge.

Fasse den Tag und die ID zusammen:

MIN( CONCAT( trm1.tag, '|', trm1.id )) tag_id

bekomme das Feld tag_id. Aus jedem Treffer hole ich die ID und bereite sie auf ( 4711,4812,5687 ) um sie im zweiten Durchlauf zu verwenden:

WHERE trm1.id IN ( 4711,4812,5687 )

Das kann man auch als Subselect schachteln.

Ich wüsste nicht, wie ich die Verbindung zwischen dem gesuchten Datum und der zuhgehörigen ID hinbekomme in einem Select

Linuchs