GROUP BY - nur zufällig richtig?

[Kalle_B]

Hallöle,

MySQL 5: wenn ich mehrere Einträge mit GROUP BY zusammenfasse und die Einzelfelder (ohne min/max usw.) ausgebe, bekomme ich den chronologisch zuletzt eingegeben Datensatz (id mit auto_increment).

Den möchte ich auch haben.

Doch mich wundert, warum bei einer Zusammenfassung von Sätzen ein einzelner gültig ist.

Kann ich mich darauf verlassen, dass es der letzte ist oder ist das nur zufällig so?

Lieben Gruß, Kalle

[hotti]

hi,

das kenne ich, guck mal: hier

Hotte

[Kalle_B]

hallo, Hotte,

das kenne ich, guck mal: hier

danke für den Link. Die darin enthaltene Aussage "... nimmt mysql eben einen zufälligen wert der in der jeweiligen gruppierung enthalten ist" klingt nicht nach Systematik.

Da brauche ich also einen neuen Ansatz. Meine Problemstellung:

In einen Veranstaltungskalender geben viele User Termine ein. Ich möchte die 10 zuletzt eingegebenen Veranstaltungen anzeigen, aber pro User nur einen.

Im Klartext: Wenn User 4711 zehn Termine kurz nacheinander eingibt, soll nur sein zuletzt eingegebener angezeigt werden.

An zweiter Stelle der Liste steht der vorletze User mit seinem letzten Termin usw.

Ich hatte das mit einem Subselect gelöst, zwar korrekt, aber schlecht für die Performance:

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
# DIE LETZTEN 10 NEUERFASSUNGEN  
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
SELECT  
 trm1.id         trm_id  
,trm1.titel      trm_titel  
,trm1.tag        trm_tag  
,trm1.uhr        trm_uhr  
,trm1.zeit_neu   trm_zeit_neu  
,ort1.id         ort_id  
,ort1.plz        ort_plz  
,ort1.name       ort_name  
FROM     ( ".$db[0]['termine']." trm1  
          ,".$db[0]['orte']."    ort1)  
LEFT JOIN (".$db[0]['adressen']." adr1)  
ON        (adr1.id = trm1.veranstalter_id)  
  
WHERE     trm1.owner_id  = ".$owner_id."  
AND       trm1.intern_kz = 0  
AND       ort1.id = trm1.ort_id  
AND       trm1.zeit_neu =  
(  
  SELECT  
   max( trm2.zeit_neu )  
  FROM      ".$db[0]['termine']." trm2  
  WHERE trm2.veranstalter_id = trm1.veranstalter_id  
)  
ORDER BY trm1.zeit_neu DESC  
LIMIT  0,10  

Nun habe ich mit einem schnellen SQL zwar dasselbe Ergebnis, aber eben nur zufällig:

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
# DIE LETZTEN 10 NEUERFASSUNGEN  
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
SELECT  
 trm1.id            trm_id  
,trm1.titel         trm_titel  
,trm1.tag           trm_tag  
,trm1.uhr           trm_uhr  
,min(trm1.zeit_neu) trm_zeit_neu  
,ort1.id            ort_id  
,ort1.plz           ort_plz  
,ort1.name          ort_name  
  
FROM      ".$db[0]['termine']." trm1  
  
LEFT JOIN ".$db[0]['orte']." ort1  
ON        ort1.id = trm1.ort_id  
  
WHERE     trm1.owner_id       = ".$owner_id."  
AND       trm1.veranstalter_id > 0  
AND       trm1.intern_kz = 0  
  
GROUP BY  trm1.veranstalter_id  
ORDER BY  trm1.zeit_neu DESC  
LIMIT  0,10  

Brauche mal einen Anstoss, stehe im Moment auf dem Schlauch.

Kalle

[hotti]

hallo, Hotte,

hi Kalle,

»» das kenne ich, guck mal: hier

danke für den Link. Die darin enthaltene Aussage "... nimmt mysql eben einen zufälligen wert der in der jeweiligen gruppierung enthalten ist" klingt nicht nach Systematik.

nunja, in meinem Fall bestand das Herstellen der Systematik in der Anwendung der AggregatFunktion max('Zeitstempel') über GROUP BY url.

Da brauche ich also einen neuen Ansatz. Meine Problemstellung:

In einen Veranstaltungskalender geben viele User Termine ein. Ich möchte die 10 zuletzt eingegebenen Veranstaltungen anzeigen, aber pro User nur einen.

??

Im Klartext: Wenn User 4711 zehn Termine kurz nacheinander eingibt, soll nur sein zuletzt eingegebener angezeigt werden.

An zweiter Stelle der Liste steht der vorletze User mit seinem letzten Termin usw.

Das ist doch genau dasselbe wie bei mir.

Also
GROUP BY user
aggregatfunktion('letztes Datum')

Die aggregatfunktion() hängt ab vom Feldtyp, falls das bei Dir ein Datumstyp ist, siehe Dokumentation.

Hotte

[Kalle_B]

hallo, Hotte,

Also
GROUP BY user
aggregatfunktion('letztes Datum')

Die aggregatfunktion() hängt ab vom Feldtyp, falls das bei Dir ein Datumstyp ist, siehe Dokumentation.

Dein Vorschlag springt zu kurz. Ich brauche nicht nur das Feld aggregatfunktion('letztes Datum'), sondern zu diesem Datensatz auch weitere Angaben wie Titel, Datum (das ist natürlich nicht das Eingabe-Datum) und Ort der Veranstaltung.

Kalle

[hotti]

hallo, Hotte,

hi Kalle,

»» Also
»» GROUP BY user
»» aggregatfunktion('letztes Datum')

»» Die aggregatfunktion() hängt ab vom Feldtyp, falls das bei Dir ein Datumstyp ist, siehe Dokumentation.

Dein Vorschlag springt zu kurz. Ich brauche nicht nur das Feld aggregatfunktion('letztes Datum'), sondern zu diesem Datensatz auch weitere Angaben wie Titel, Datum (das ist natürlich nicht das Eingabe-Datum) und Ort der Veranstaltung.

Das kannst Du alles in Dein SELECT einbauen:
SELECT title, last(date) as current, user, count(user) [,...] from table GROUP BY user ORDER BY current DESC

last(date) ist nur meine Umschreibung, kann sein, dass die Aggr.Funktion wirklich so heißt.

Hotte

[Harlequin]

Yerf!

Das kannst Du alles in Dein SELECT einbauen:
SELECT title, last(date) as current, user, count(user) [,...] from table GROUP BY user ORDER BY current DESC

Ehm, nein. Bei "title" ist jetzt wieder der fall gegeben, dass nicht gruppiert wird, aber auch keine Aggregatsfunktion verwendet wird. Leider gibt es für diesen Fall keine passende Aggregatsfunktion, da hier eine Abhängigkeit zum Datum besteht. Dies lässt sich nur per Subselect oder ähnlichem lösen.

Gruß,

Harlequin

[Ilja]

yo,

Dein Vorschlag springt zu kurz. Ich brauche nicht nur das Feld aggregatfunktion('letztes Datum'), sondern zu diesem Datensatz auch weitere Angaben wie Titel, Datum (das ist natürlich nicht das Eingabe-Datum) und Ort der Veranstaltung.

dann benutze anstelle von einer gruppierung korrelierte unterabfragen. wichtig ist aber dabei, genau zu wisen, welche tabellen du hast, welchen datenbestand und was du raus haben willst.

Ilja

[Kalle_B]

dann benutze anstelle von einer gruppierung korrelierte unterabfragen. wichtig ist aber dabei, genau zu wisen, welche tabellen du hast, welchen datenbestand und was du raus haben willst.

Ich zähle grundsätzlich die Zeit auf dem PHP-Server, beginnend mit dem Aufruf bis zur letzten HTML- Ausgabe.

Statistische Abfragen mit Subselect dauerten 14 sec und sind bei remso.de (Anzeige der Zeit unten auf der Seite) nicht akzeptabel !!!

Nach Auskommentieren dieser Abfragen war die Zeit 0,2 sec.

Nun möchte ich die Statistik mit neuen SQLs aufbauen.

Kalle

[Ilja]

yo,

es ist mühsam, das daten-design aus vorhanden abfragen auszulesen, zumal dort auch noch php variablen vorkommen. also her mit den tabellen inklusive der user und deren verbindung zu den veranstaltungen und notfalls auch alle anderen relevanten tabelle. auf die beispieldaten verzichte ich erst einmal, kann sein, dass wir sie später aber noch brauchen.

also her damit udn dann kann dir auch geholfen werden...

Ilja

[Kalle_B]

Hallo, Ilja,

also her damit udn dann kann dir auch geholfen werden...

Die Zeit des Seitenaufbaus zähle ich vom Beginn des Programmaufrufs bis zur vorletzten HTML-Ausgabe, die letzte Zeile ist dann die Zeitanzeige.

Es differiert etwas von Aufruf zu Aufruf, obwohl remso allein auf dem Server ist (Serverhousing, Debian-Linux, 1 Switchport 100MBit).

Hier die benötigten *Felder und die Keys:

CREATE TABLE bia\_termine (
  *id int(11) NOT NULL auto_increment,
  *owner\_id int(11) NOT NULL default '0',
  *intern\_kz int(1) NOT NULL default '0',
  *veranstalter\_id int(11) NOT NULL default '0',
  *ort\_id int(11) NOT NULL default '0',
  *tag date NOT NULL default '2001-01-01',
  *titel varchar(50) collate utf8_unicode_ci default NULL,
  *zeit\_neu int(11) default NULL,
  PRIMARY KEY  (id)
)
CREATE TABLE bia\_orte (
  *id int(11) NOT NULL auto_increment,
  *name varchar(50) collate utf8_unicode_ci NOT NULL,
  *plz varchar(10) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (id),
  KEY plz (plz)
)

Meine jetzige, zeitlich akzeptable Lösung (0.3 sec für die ganze Seite) hat zwei SQLs:

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DIE LETZTEN 10 NEUERFASSUNGEN

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT
 max(trm1.zeit_neu) letzte_eingabe
FROM      bia_termine trm1
WHERE     trm1.owner_id       = 1
AND       trm1.veranstalter_id > 0
AND       trm1.intern_kz = 0
GROUP BY  trm1.veranstalter_id
ORDER BY  max(trm1.zeit_neu) DESC
LIMIT 0,10

  $letzte_10 = '';  
  while ( $row_letzte_10_neu = mysql_fetch_array( $res_letzte_10_neu ))  
  {  
    $letzte_10 .= $row_letzte_10_neu['letzte_eingabe'].',';  
  }  
  $letzte_10 .= '0'; // wg. Komma  

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DATEN ZU DEN LETZTEN 10 NEUERFASSUNGEN

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT
 trm1.id            trm_id
,trm1.titel         trm_titel
,trm1.tag           trm_tag
#trm1.uhr           trm_uhr
#trm1.zeit_neu      trm_zeit_neu
#ort1.id            ort_id
,ort1.plz           ort_plz
,ort1.name          ort_name

FROM      bia_termine trm1

LEFT JOIN bia_orte ort1
ON        ort1.id = trm1.ort_id

WHERE trm1.zeit_neu IN (1238673496,1238593540,1238589841,1238571267,1238539977,1238534205,1238483656,1238410565,1238347082,1238321964,0)
ORDER BY  trm1.zeit_neu DESC

Das erste SQL hatte ich testweise als Subquery in das zweite eingesetzt. Da kam eine Fehlermeldung, die GROUP, ORDER oder LIMIT (weiss nicht mehr so genau) im Subquery nicht mochte.

Und hier zum Vergleich das alte SQL (heute morgen 7.5 sec Seitenaufbau):

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DIE LETZTEN 10 NEUERFASSUNGEN

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT
trm1.id         trm_id
,trm1.titel      trm_titel
,trm1.tag        trm_tag
,trm1.uhr        trm_uhr
,trm1.zeit_neu   trm_zeit_neu
,ort1.id         ort_id
,ort1.plz        ort_plz
,ort1.name       ort_name
FROM     ( bia_termine trm1
    ,bia_orte    ort1)
LEFT JOIN (bia_adressen adr1)
ON        (adr1.id = trm1.veranstalter_id)

WHERE     trm1.owner_id  = 1
AND       trm1.intern_kz = 0
AND       ort1.id = trm1.ort_id
AND       trm1.zeit_neu =
(
SELECT
max( trm2.zeit_neu )
FROM      bia_termine trm2
WHERE trm2.veranstalter_id = trm1.veranstalter_id
)
ORDER BY trm1.zeit_neu DESC
LIMIT  0,10

Merkwürdig ist die Zeit von 0.0001 sec, wenn ich das alte SQL im phpMyAdmin laufen lasse.

Gruß, Kalle

[Kalle_B]

Merkwürdig ist die Zeit von 0.0001 sec, wenn ich das alte SQL im phpMyAdmin laufen lasse.

Ähhh - das scheint nur ein Spezialfall zu sein, wenn dasselbe SQL wiederholt wird.

Jetzt sind es wieder 7,5 sec allein für das alte SQL im PHPMyAdmin

[Struppi]

Jetzt sind es wieder 7,5 sec allein für das alte SQL im PHPMyAdmin

Um Performanceprobleme zu ermitteln bietet mysql den Befehl EXPLAIN. In phpmyadmin nennt sich das 'SQL erklären', damit kannst du herausfinden, was dein Befehl so langsam macht.

Struppi.

[Sven Rautenberg]

Moin!

»» Merkwürdig ist die Zeit von 0.0001 sec, wenn ich das alte SQL im phpMyAdmin laufen lasse.

Ähhh - das scheint nur ein Spezialfall zu sein, wenn dasselbe SQL wiederholt wird.

MySQL Query Cache! Sollte man ausnutzen, wenn Performanceprobleme zu lösen sind, ist aber kein Allheilmittel.

Insbesondere ist zu beachten: Schreibzugriffe auf die beteiligten Tabellen invalidieren den Cache logischerweise, aber auch die Anwendung von Funktionen, die dynamische Ergebnisse zurückliefern könnten, wie beispielsweise NOW(). Ideal ist, wenn man die Suchoperation nur als Vergleich statischer Werte mit Tabelleninhalt gestaltet.

- Sven Rautenberg

[Ilja]

yo Kalle,

Hier die benötigten *Felder und die Keys:

du willst doch die top 10 der veranstaltungen von 10 unterschiedlichen "owner" haben. dann gebe mir doch bitte auch mal die tabelle dazu. vielleicht brauchen wir sie gar nicht, aber gib mir noch mal die info.

Ilja

[Ilja]

yo,

MySQL 5: wenn ich mehrere Einträge mit GROUP BY zusammenfasse und die Einzelfelder (ohne min/max usw.) ausgebe, bekomme ich den chronologisch zuletzt eingegeben Datensatz (id mit auto_increment).

wie schon des öfteren hier gesagt, ich würde davon abraten den mysql-bug zu benutzen. gebe nur spalten aus, über die die auch gruppiert hast oder benutze aggregat-funktionen. dann bist du immer auf der sicheren seite, auch was die portierbarkeit angeht.

Kann ich mich darauf verlassen, dass es der letzte ist oder ist das nur zufällig so?

rein zufällig, darauf kann man sich nie verlassen.

Ilja

[Kalle_B]

Hallo, Ilja,

... dann bist du immer auf der sicheren seite, auch was die portierbarkeit angeht.

Stichwort: Portierbarkeit

Du meinst, auf neue/alte MySQL- Versionen oder auf andere Datenbanken?

Was glaubst du, wie oft ich testen muss, damit SQL das richtige Ergebnis liefert. Und ich gehe davon aus, dass das dann *nur* für die momentane DB-Version funktioniert.

Portierbarkeit habe ich mir im Laufe der Jahre längst abgeschminkt. Wenn du nämlich nach Handbuch programmierst (ohne Test am vorhandenen Objekt) klappt eigentlich nichts.

Sei es, dass das Handbuch ungenau ist, sei es, dass der Programmierer Aussagen falsch versteht, sei es, dass Kombinationen nirgendwo beschrieben sind.

Kalle

[Kalle_B]

heute morgen (2.4.) hatte ich 907 Veranstaltungstermine in remso.de, der letzte Eintrag stammte von der DLRG Hemsbach.

Jetzt (2.4. 14:50) sind es 984 Veranstaltungen, und ich kann nicht erkennen, wer die neuesten eingegeben hat.

Mein Statistik-SQL, basierend auf dem MySQL-Bug, ist wohl nicht zuverlässig.

Kalle

[Vinzenz Mai]

Hallo Kalle,

Jetzt (2.4. 14:50) sind es 984 Veranstaltungen, und ich kann nicht erkennen, wer die neuesten eingegeben hat.
Mein Statistik-SQL, basierend auf dem MySQL-Bug, ist wohl nicht zuverlässig.

selbstverständlich nicht. So steht es im Handbuch, ich zitiere:

<zitat>
    Do not use this feature if the columns you omit from the GROUP BY part are
    not constant in the group. The server is free to return any value from the
    group, so the results are indeterminate unless all values are the same.
</zitat>

Deine Spalten weisen innerhalb der Gruppe keinen konstanten Wert auf.
Der Server darf daher jeden beliebigen Wert aus der Gruppe zurückliefern.
Nein, er wird der nicht zuverlässig den von Dir gewünschten Wert liefern.

Andere DBMS sind konsequent und weisen solche Abfragen einfach als syntaktisch falsch zurück - was nicht nur ich gut finde. So steht es in vielen Forumsbeiträgen hier. Ja, das haben auch andere einsehen müssen, hier ein Beispiel: </archiv/2006/8/t134684/#m873861>.

Wenn Du zuverlässige Ergebnisse benötigst, dann benötigst Du die von Ilja angesprochenen korrelierten Unterabfragen. Eine Optimierung könntest Du vielleicht über eine Stored Procedure in Verbindung mit Sperren hinbekommen.

Freundliche Grüße

Vinzenz