Komplexes Select-Statement mit GROUP BY - wie gehts?

[Dennis]

Hallo an alle da draußen ;-)

Ich hänge gerade bei einer kleinen Datenbank-Abfrage - folgende Struktur sei gegeben:

name   | condition | inserted            | value
 --------+-----------+---------------------+--------
  WertA  | 10        | 2007-06-25 23:45:28 | 0.35
  WertA  | 12        | 2007-06-25 23:45:28 | 0.50
  WertA  | 10        | 2007-06-25 23:40:38 | 0.32
  WertA  | 12        | 2007-06-25 23:40:38 | 0.48
  WertB  | 4         | 2007-06-25 23:35:28 | 1.75
  WertB  | 7         | 2007-06-25 23:35:28 | 3.49
  WertB  | 4         | 2007-06-25 23:30:38 | 1.82
  WertB  | 7         | 2007-06-25 23:30:38 | 3.58

Der Zusamenhang ist folgender: name und condition sind theoretisch UNIQUE, praktisch ist allerdings eine History-Funktion gegeben, sodass name, condition und inserted UNIQUE sind. Sinn und Zweck: Zu einem bestimmten Zeitpunkt (inserted) hat eine bestimmte Einstellung (name) unter einer bestimmten Bedingung (condition) einen bestimmten Wert (value)

Obige Tabelle kann natürlich noch mehr Inhalt sein - es können noch mehr Optionen enthalten sein (z.B. "WertC") und es können noch mehr History-Stände der vorhandenen Optionen enthalten sein (z.B. "WertA" unter der Bedingung "10" von letztem Monat).

Nun gilt es daran ein paar Aufgaben umzusetzen. Als erstes wäre das das Auslesen des aktuellen Wertes einer Option X unter Bedingung Y - kein Problem:

~~~sql SELECT name, condition, inserted, value
  FROM config
  WHERE name = X AND condition = Y
  ORDER BY validation DESC
  LIMIT 1

  
Auch das Auslesen aller vorhandenen Optionen und deren Bedingungen ist kein Problem:  
  
  ~~~sql
SELECT name, condition  
  FROM config  
  GROUP BY name, condition

Doch wie kriege ich diese beiden Ergebnisse miteinander verbunden? Sprich: Wie kriege ich eine Ergebnisliste, mit allen enthaltenen Optionen und allen Bedingungen in der aktuellsten Version?

So muss das Ergebnis aussehen:

name   | condition | inserted            | value
 --------+-----------+---------------------+--------
  WertA  | 10        | 2007-06-25 23:45:28 | 0.35
  WertA  | 12        | 2007-06-25 23:45:28 | 0.50
  WertB  | 4         | 2007-06-25 23:35:28 | 1.75
  WertB  | 7         | 2007-06-25 23:35:28 | 3.49

Natürlich könnte ich das ganze clientseitig lösen, indem ich zuerst den zweiten SQL-Query an den Server ja - mir damit alle vorhandenen Optionen besorge - und dann für jede Option einen Query absetze der mir den aktuellen Wert holt.

Aber das wäre ja langweilig - ich hätte das gerne in einem SQL-Query, jedoch ist es mir bis jetzt noch nicht gelungen das zu realisieren, sämtliche Versuche mit HAVING scheiterten (dann kam meist gar nichts mehr zurück *g*). Also, wie macht man so etwas?

Viele Grüße,
  ~ Dennis.

[Skeeve]

Moin!

Mit MySQL sollte das hier funktionieren:

select name, condition, max(inserted), value from config group by name,condition

-- Skeeve

[Dennis]

Hi Skeeve,

select name, condition, max(inserted), value from config group by name,condition

Das hatte ich auch schon probiert - leider erfolglos, wie dieses Result-Set zeigt:

name   | condition | inserted            | value
 --------+-----------+---------------------+--------
  WertA  | 10        | 2007-06-25 23:45:28 | 0.32
  WertA  | 12        | 2007-06-25 23:45:28 | 0.48
  WertB  | 4         | 2007-06-25 23:35:28 | 1.82
  WertB  | 7         | 2007-06-25 23:35:28 | 3.58

Die Ergebnisse sind hier eigentlich wie gewünscht - nur value (auf das es ja gerade ankommt *g*) wird mehr oder weniger zufällig gewählt. In diesen Fällen stammt der Value-Wert jeweils von den alten Einträcken.

Im Prinzip ist obiger SQL-Befehl also nur ein Täuschungsversuch - man wählt einen Datensatz aus und verpasst diesen einfach mit dem neuesten Timestamp :-)

Viele Grüße,
  ~ Dennis.

[globe]

n'abend,

select name, condition, max(inserted), value from config group by name,condition

zum Ersten wird das nicht funktionieren. Zum zweiten würde das (wie Dennis bereits erkannt hat) nicht funktionieren wie erwartet.

Du gruppierst nach name und condition. Wählst eine Aggregat-Funktion (nämlich MAX()), um den höchsten Wert für inserted zu ermitteln. Aber was machst du denn mit value? _welcher_ Value soll denn da kommen? Der value des Datensatzes mit dem größten Sch..z? Genau! - nicht identifizierbar -> Problem. Entweder jagst du value ebenfalls durch eine Aggregat-Funktion (was in diesem Fall null Sinn macht, da du einen bestimmten Wert haben möchtest), oder du gruppierst nach dieser Spalte (was mindestens noch weniger Sinn macht), oder du wirfst die Spalte da raus.

Weiter benutzt du MAX(inserted) auf eine Art, die dir definitiv nicht das Ergebnis liefert, das du haben möchtest. Was bekommst du? Genau! - das größte Datum einer jeden (name, condition) Gruppe. Was bekommst du nicht? Genau! - den Datensatz mit dem höchsten Datum einer jeden (name, condition) Gruppe.

Ilja lieferte bereits eine Lösung, welche auf [korrelierenden] SubQueries (ab MySQL4.1) setzt.

Korrelierende SubQueries sind im Endeffekt Unterabfragen, die in Abhängigkeit der äußeren Abfrage laufen. Korrelierend bedeutet hier also, dass für jede (name, condition) Gruppe die Query erneut ausgeführt werden muss. (Was zu Performanceproblemen führen kann, wenn oft auf die DB gefeuert)

weiterhin schönen abend...

[Skeeve]

Moin!

zum Ersten wird das nicht funktionieren. Zum zweiten würde das (wie Dennis bereits erkannt hat) nicht funktionieren wie erwartet.

Das merkwürdige ist ja: Es hat hier funktioniert. Mit den Daten, die Dennis gepostet hat. Ich hätte auch erwartet, daß die fehlenden werte aus der Reihe stammen, aus der auch die anderen Werte stammen. Also in diesem Fall aus der, aus der der Max-Wert kommt. Dummerweise fällt mir jetzt gerade auf: Was, wenn Max 2 Mal vorhanden ist...

Ich sehe Deine Argumente also ein. Zudem bin ich beim Umsetzen eines stark ähnlichen Queries auf Oracle auf genau dieses Problem gestoßen und mußte auch kompliziert drumherumarbeiten. Ich denke, ich werde diesen Thread im Auge behalten und mein Query danach nochmal in Augenschein nehmen.

-- Skeeve

[Skeeve]

Moin!

Ilja lieferte bereits eine Lösung, welche auf [korrelierenden] SubQueries (ab MySQL4.1) setzt.

Okay... Ich habe mich aber auch mal dran versucht und bin an Eurer Meinung zu dem hier interessiert:

  
SELECT `name`, `condition`, `inserted`, `value` FROM `config` WHERE (`name`, `condition`, `inserted`) IN (SELECT `name`, `condition`, max(`inserted`) FROM `config` GROUP BY `name`, `condition`)  

-- Skeeve

[Vinzenz Mai]

Hallo Skeeve,

Okay... Ich habe mich aber auch mal dran versucht und bin an Eurer Meinung zu dem hier interessiert:

SELECT name, condition, inserted, value FROM config WHERE (name, condition, inserted) IN (SELECT name, condition, max(inserted) FROM config GROUP BY name, condition)

  
einfach nur schlimm :-(  
  
Das Archiv ist übrigens voll mit Beiträgen zu korrelierten Subselects, ein Beispiel wäre z.B:  
  
</archiv/2007/1/t143558/#m932327>  
mit  
</archiv/2006/7/t133015/#m861544>  
<http://dev.mysql.com/doc/refman/4.1/en/correlated-subqueries.html>  
  
Und wie Ilja und ich oft betonen: Die Art des Umgangs von MySQL mit der GROUP-BY-Klausel ist der häufigste Grund für das Nichtverstehen dieser Klausel von SQL-Anfängern.  
  
  
Freundliche Grüße  
  
Vinzenz  
  
(\*) FGA: Frequently given answer ...

[Skeeve]

Moin!

einfach nur schlimm :-(

"Tolle" Antwort. Hilft mir richtig viel. :-(

Das Archiv ist übrigens voll mit Beiträgen zu korrelierten Subselects, ein Beispiel wäre z.B:

Und wie Ilja und ich oft betonen: Die Art des Umgangs von MySQL mit der GROUP-BY-Klausel ist der häufigste Grund für das Nichtverstehen dieser Klausel von SQL-Anfängern.

Das was ich da geschrieben habe, habe ich auf Oracle getestet.

-- Skeeve

[Ilja]

yo,

Das was ich da geschrieben habe, habe ich auf Oracle getestet.

ahh, ein Oracle Kollege, das freut mich, da es hier doch meistens um mysql geht.

"Tolle" Antwort. Hilft mir richtig viel. :-(

die antwort von Vinz ist schon hilfreich, der richtige umgang mit group by ist gerade unter mysql nicht ganz trivial, auch wenn deine abfrage in diesem falle syntaktisch richtig ist.

aber ich würde sie trotzdem so nicht einsetzen, auch wenn sie zum gleichen ergebnis führt, da ich sie sehr unübersichtlich finde. gerade wenn man viel mit sql-anweisungen zu tun hat, ist ein schnelles lesen und verstehen der abfrage sehr wichtig.

Ilja

[Skeeve]

Moin!

gerade wenn man viel mit sql-anweisungen zu tun hat, ist ein schnelles lesen und verstehen der abfrage sehr wichtig.

Vielleicht liegt es daran, daß ich nicht viel mit SQL Abfragen zu tun habe. Meine Abfrage fand ich verständlicher. Das innere SELECT sucht mir alle Gruppen von "name" und "condition" raus und dazu das größte "inserted". zu diesen sucht mir dann das äußere die passenden Values hinzu.

Ich muß allerdings auch sagen, daß ich gerade heute mit so einem Fall zu tun hatte, wo mir auch das hier nicht weitergeholfen hat und ich nur mit der Hilfe eines Kollegen auf die dreistufig Verschachtelte Abfrage zustande bekommen habe.

vereinfachte Ausgangslage
Spalten Name, Vers, Variante, Tstamp
Zu jedem Namen gibt es mehrere Varianten. Jede Kombination davon kann in mehreren Versionen vorliegen. Jede Version wurde zu nahezu demselben Zeitpunkt eingetragen.

Ziel ist nun, die aktuellste Version zu finden, wobei die größe der Versionsnummer nicht maßgeblich ist, sondern der Timestamp

Beispiel:
Name Variante Vers Tstamp
A    1        1    20:00
A    2        1    20:01
A    1        2    21:00
A    2        2    21:01
A    1        3    19:00
A    2        3    19:01
B    1        1    10:00
B    2        1    10:01
B    1        2    11:00
B    2        2    11:01
B    1        3    12:00
B    2        3    12:01

Ergebnis sollte sein
Name Variante Vers Tstamp
A    1        2    21:00
A    2        2    21:01
B    1        3    12:00
B    2        3    12:01

Statement dazu

  
SELECT  
 Name,  
 Variante,  
 Vers,  
 Tstamp  
FROM my_table t1  
WHERE t1.Vers IN  
(  
 SELECT t2.Vers  
 FROM my_table t2  
 WHERE t2.Name = t1.Name  
 AND t2.Tstamp IN  
 (  
  SELECT MAX(t3.Tstamp)  
  FROM my_table t3  
  WHERE t3.Name = t2.Name  
 )  
)  

Ob das auch kürzer geht? Ich bin mir aber relativ sicher: Mit GROUP BY käme ich hier nicht weiter.

Was sagen die 2 Koriphäen?

-- Skeeve

[Ilja]

yo,

Ob das auch kürzer geht? Ich bin mir aber relativ sicher: Mit GROUP BY käme ich hier nicht weiter.
Was sagen die 2 Koriphäen?

SELECT t1.Name, t1.Variante, t1.Vers, MAX(t1.Tstamp)
FROM my_table t1
GROUP BY Name, Variante, Vers
HAVING MAX(t1.Tstamp) =
 (SELECT MAX(t2.Tstamp)
  FROM my_table t2
  WHERE t1.name = t2.name
  AND t1.variante = t2.variante
 )
;

PS: ES gibt noch eine bessere lösung mit nur einer unterabfrage, die du aus deiner abfrage ableiten kannst....

Ilja

[Skeeve]

Moin!

PS: ES gibt noch eine bessere lösung mit nur einer unterabfrage, die du aus deiner abfrage ableiten kannst....

Ich verstehe ja DAS noch nichteinmal ;-)

-- Skeeve

[Ilja]

yo,

PS: ES gibt noch eine bessere lösung mit nur einer unterabfrage, die du aus deiner abfrage ableiten kannst....

Ich verstehe ja DAS noch nichteinmal ;-)

SELECT
 Name,
 Variante,
 Vers,
 Tstamp
FROM my_table t1
WHERE t1.Tstamp =
 (
  SELECT MAX(t2.Tstamp)
  FROM my_table t2
  WHERE t1.Name = t2.Name
  AND t1.variante = t2.variante
 )
;

Ilja

[Skeeve]

Moin!

SELECT
Name,
Variante,
Vers,
Tstamp
FROM my_table t1
WHERE t1.Tstamp =
(
  SELECT MAX(t2.Tstamp)
  FROM my_table t2
  WHERE t1.Name = t2.Name
  AND t1.variante = t2.variante
)
;

Das erscheint mir fast zu einfach um wahr zu sein...

Das muß ich erstmal mit ein paar Beispielen selbst testen...

-- Skeeve

[Ilja]

moin,

~~~sql

SELECT name, condition, inserted, value

FROM config
  WHERE name = X AND condition = Y
  ORDER BY validation DESC
  LIMIT 1

  
dieser code ist nicht ganz sauber. die LIMIT funktionalität von mysql ist sehr praktisch, verleitet aber all zuoft zu "bequemlichkeitsfehlern". du musst den sonderfall behandeln, wenn es datensätze gibt, mit gleichen werten in den spalten name, condition und validation. dies kann zwar von der fachlichkeit her ausgeschlossen sein, ich würde aber trotzdem immer einen anderen weg gehen, nämlich über eine unterabfrage und der maxfuntion. dann ist man immer auf der sicheren seite.  
  

> Wie kriege ich eine Ergebnisliste, mit allen enthaltenen Optionen und allen Bedingungen in der aktuellsten Version?  
  
und wenn du das von mir oben genannte auch umsetzt, dann ist der schritt nicht mehr weit, dein problem zu lösen, nämlich eine korrelierte unterabfrage mit der max-aggreat-funktion über die inserted spalte.  
  
SELECT t1.name, t1.condition, t1.insterted, t1.value  
FROM config t1  
WHERE t1.inserted =  
 (SELECT (MAX t2.inserted)  
  FROM config t2  
  WHERE t1.name = t2.name  
  AND t1.condition = t2.condition  
 )  
;  
  
Ilja

[Dennis]

Hi Ilja,

SELECT t1.name, t1.condition, t1.insterted, t1.value
FROM config t1
WHERE t1.inserted =
(SELECT (MAX t2.inserted)
  FROM config t2
  WHERE t1.name = t2.name
  AND t1.condition = t2.condition
)
;

Kleiner Tippfehler ist drin, aber ansonsten funktioniert das - vielen Dank :-)

Ich hab mir schon gedacht, dass die Lösung mit dem Limit eigentlich nur ein Work-Around ist, aber irgendwie wollte es mir gestern Abend nicht mehr gelingen eine Lösung mit Sub-Query zu finden *g*

Allerdings beachtet diese Lösung eine Variante noch nicht, nämlich wenn condition gleich NULL ist. Für manche Optionen gibt es nämlich einfach keine Bedingung, sodass condition NULL sein darf. Ich hab deshalb den WHERE Teil des Sub-Querys noch so umgebastelt:

~~~sql WHERE
    t1.name = t2.name
    AND (
      t1.condition IS NULL
      OR t1.condition = t2.condition
    )

  
Letztendlich will ich dann noch die Umkehrmenge des obigen Results abrufen (also alle Einträge, die ungültig sind, weil sie in der Vergangenheit liegen), was sich durch ein  
  
  `WHERE t1.inserted != `{:.language-sql}[... Sub-Query ...]  
  
machen lässt ;-)  
  
  
Viele Grüße,  
  ~ Dennis.

-- 
Mein [SelfCode](http://community.de.selfhtml.org/fanprojekte/selfcode.htm): [ie:{ fl:( br:> va:) ls:\[ fo:) rl:( n4:# ss:) de:\] js:| ch:{ sh:| mo:} zu:|](http://www.peter.in-berlin.de/projekte/selfcode/?code=ie%3A%7B+fl%3A%28+br%3A%3E+va%3A%29+ls%3A%5B+fo%3A%29+rl%3A%28+n4%3A%23+ss%3A%29+de%3A%5D+js%3A%7C+ch%3A%7B+sh%3A%7C+mo%3A%7D+zu%3A%7C)  
  
[Weblog](http://riehle-web.com/blog/): [Comboboxen mit Javascript](http://riehle-web.com/blog/log.php/2007/03/comboboxen-mit-javascript) - [apr_dbd_mysql unter Debian - so geht es doch \\[Update](http://riehle-web.com/blog/log.php/2007/03/apr_dbd_mysql-unter-debian)]