MySQL Doubletten gruppieren

[polarfox]

Hallo an alle.

Ich habe folgendes Problem mit meiner MySQL Datenbank. Dieses Forum hat mir schon ein ganzes Stück weit geholfen, aber nun komme ich leider nicht weiter.

Meine Datenbank ist recht gross. 2.5 Mio Datensätze mit jeweils 27 Spalten. Hier der Aufbau vereinfacht dargestellt.

Ticketnr     Status       open_date     update_time     close_time
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open-linked  01/01/2008    02/01/2008      00/00/0000
AD0012346    open         01/01/2008    02/01/2008      00/00/0000
AD0012346    open-linked  01/01/2008    02/01/2008      00/00/0000
AD0012346    closed       01/01/2008    02/01/2008      02/01/2008
AD0012347    open         01/01/2008    02/01/2008      00/00/0000
AD0012347    closed       01/01/2008    02/01/2008      03/00/2008

Ich muss die Daten zwecks Reporting auswerten. Hierfür möchte ich nur einen Datensatz pro Ticketnr haben. Dies habe ich bereits mit einem 'GROUP BY' statement hinbekommen.

Nun möchte ich allerdings, dass die Einträge nicht nur anhand der ticketnr zusammengefasst werden, sondern falls ein ticket "closed" ist, nur noch dieser Eintrag angezeigt wird. Aktuell gruppiert SQL zwar wie gewünscht nach der Ticketnr, fasst dann aber alles unter dem jeweils ersten Eintrag zusammen. Das resultat ist, dass alle tickets auf 'open' stehen, obwohl diese schon "closed" sind.

Kurz gesagt: Ich möchte jede Ticketnummer nur einmal haben, muss aber obendrein auch wissen, ob das Ticket nun 'open' oder 'closed' ist.

[Ilja]

yo,

Kurz gesagt: Ich möchte jede Ticketnummer nur einmal haben, muss aber obendrein auch wissen, ob das Ticket nun 'open' oder 'closed' ist.

klnigt nach dem typischen mysql bug bei gruppierungen. ich vermute mal, du hast eine abfrage gebastelt, in der du spalten mit ausgibst, über du den weder gruppiert hast noch aggregat-funktion sind. aber das ist erst mal nur eine vermutung.

um dir helfen zu wollen, musst du uns erst mal verraten, wie deine ergebnismenge aussehen soll und zwar inklusive aller spalten, die du ausgeben willst. dazu kannst du deine beispieldaten nehmen, die du hier gepostet hast und sagst uns, welches ergebnis du genau haben willst und warum.

in denke mal, dein zauberwort werde korrelierte unterabfragen sien, aber das kann man dir genau sagen, wenn du uns die ergebnismenge aufzeigst.

Ilja

[polarfox]

yo,

Kurz gesagt: Ich möchte jede Ticketnummer nur einmal haben, muss aber obendrein auch wissen, ob das Ticket nun 'open' oder 'closed' ist.

klnigt nach dem typischen mysql bug bei gruppierungen. ich vermute mal, du hast eine abfrage gebastelt, in der du spalten mit ausgibst, über du den weder gruppiert hast noch aggregat-funktion sind. aber das ist erst mal nur eine vermutung.

um dir helfen zu wollen, musst du uns erst mal verraten, wie deine ergebnismenge aussehen soll und zwar inklusive aller spalten, die du ausgeben willst. dazu kannst du deine beispieldaten nehmen, die du hier gepostet hast und sagst uns, welches ergebnis du genau haben willst und warum.

in denke mal, dein zauberwort werde korrelierte unterabfragen sien, aber das kann man dir genau sagen, wenn du uns die ergebnismenge aufzeigst.

Ilja

Danke für die Antwort.

Danke für die Antwort Ilja.

Im Prinzip ist was ich erreichen möchte einfach:

Jedes Ticket steht mehrfach in der Tabelle. Jedesmal wenn ein Ticket z.B. geupdated wurde, wird ein Eintrag generiert mit Zeitstempel. Diese Daten sind für mich irrelevant.

Ich muss am Ende eine Tabelle haben, in der jede ticketnr nur einmal steht, und zwar mit dem letzten Status den das Ticket hatte.

Beispiel:

Ticketnr     Status       open_date     update_time     close_time
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open-linked  01/01/2008    02/01/2008      00/00/0000
AD0012346    open         01/01/2008    02/01/2008      00/00/0000
AD0012346    open-linked  01/01/2008    02/01/2008      00/00/0000
AD0012346    closed       01/01/2008    02/01/2008      02/01/2008
AD0012347    open         01/01/2008    02/01/2008      00/00/0000
AD0012347    closed       01/01/2008    02/01/2008      03/00/2008

Das sollte nachher so aussehen:

Ticketnr     Status       open_date     update_time     close_time
AD0012345    open-linked  01/01/2008    02/01/2008      00/00/0000
AD0012346    closed       01/01/2008    02/01/2008      02/01/2008
AD0012347    closed       01/01/2008    02/01/2008      03/00/2008

Hoffe damit wird klar was ich erreichen möchte. Danke im Vorraus für jede Hilfe : )

[Ilja]

yo,

Ich muss am Ende eine Tabelle haben, in der jede ticketnr nur einmal steht, und zwar mit dem letzten Status den das Ticket hatte.

mal davon abgesehen, dass das daten-design diskusionsbedürftig ist, woran erkennst du den den "letzten" eintrag, wenn die zeiten in deinem beispiel für ein ticket (AD0012345) alle gleich sind oder hast du uns da die zeit des zeitstempels unterschlagen ?

Ilja

[polarfox]

yo,

Ich muss am Ende eine Tabelle haben, in der jede ticketnr nur einmal steht, und zwar mit dem letzten Status den das Ticket hatte.

mal davon abgesehen, dass das daten-design diskusionsbedürftig ist, woran erkennst du den den "letzten" eintrag, wenn die zeiten in deinem beispiel für ein ticket (AD0012345) alle gleich sind oder hast du uns da die zeit des zeitstempels unterschlagen ?

Ilja

Die Daten haben kein Design. Das ist ein raw-dump einer antiken P4 flatfile database.

Die Tabelle ist nach Ticketnummern sortiert. Ferner existieren 3 Spalten für Zeitstempel. Die erste Zeit-Spalte ist die Zeit in der das Ticket geöffnet wurde. Die Zweite ist die Zeit wann das Ticket zum letzten mal geupdated wurde. Die dritte und letzte Zeit Spalte wird nur gesetzt wenn das Ticket geschlossen wurde.

Ich brauche für jedes Ticket nur einen Eintrag, unabhängig davon ob es nun zu ist oder noch nicht.

Ich habe mal einen Screenshot gemacht zur Verdeutlichung.

[Ilja]

yo,

Die Daten haben kein Design. Das ist ein raw-dump einer antiken P4 flatfile database.

ok, das erklärt es.

Die Tabelle ist nach Ticketnummern sortiert.

tabellen sind immer unsortiert, ist eben eine menge. spielt für deinen fall nicht wirklich eine rolle, sollte man aber immer im hinterkopf behalten.

Ferner existieren 3 Spalten für Zeitstempel. Die erste Zeit-Spalte ist die Zeit in der das Ticket geöffnet wurde. Die Zweite ist die Zeit wann das Ticket zum letzten mal geupdated wurde. Die dritte und letzte Zeit Spalte wird nur gesetzt wenn das Ticket geschlossen wurde.

die abfrage selbst, ist technisch gesehen recht einfach umzusetzen, ich sprach es schon mal an, korrelierte unterabfragen eignen sich dafür am besten, eventuell in verbindung mit der case funktionalität. group by ist hierfür eher ungeeignet. das problem besteht in der fachlichkeit, sprich was genau der letzte datensatz eines jeweiligen tickets bedeutet. ich hatte es schon mal versucht anzusprechen, machen es aber noch mal deutlicher. hier sind beispieldaten von dir für ein ticket:

Ticketnr     Status       open_date     update_time     close_time
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open-linked  01/01/2008    02/01/2008      00/00/0000

bei allen drei von dir erwähnten spalten ist die zeit genau gleich. wenn ein ticket geschlossen ist, könnten man offentsichtlich die close_time heran ziehen, um den letzten datensatz eines tickets zu bestimmen. aber in diesem falle gibt es keine close_time. woran also soll man bei diesem ticket festmachen, welches der "letzte" datensatz ist, dessen status-wert relevant ist ? dafür bräuchten wir noch ein paar mehr infos von dir. die angebliche sortierung der tabelle kann dafür nicht verwendet werden.

Ich habe mal einen Screenshot gemacht zur Verdeutlichung.

den kann ich nicht sehen den link.....

Ilja

[polarfox]

yo,

Die Daten haben kein Design. Das ist ein raw-dump einer antiken P4 flatfile database.

ok, das erklärt es.

Die Tabelle ist nach Ticketnummern sortiert.

tabellen sind immer unsortiert, ist eben eine menge. spielt für deinen fall nicht wirklich eine rolle, sollte man aber immer im hinterkopf behalten.

Ferner existieren 3 Spalten für Zeitstempel. Die erste Zeit-Spalte ist die Zeit in der das Ticket geöffnet wurde. Die Zweite ist die Zeit wann das Ticket zum letzten mal geupdated wurde. Die dritte und letzte Zeit Spalte wird nur gesetzt wenn das Ticket geschlossen wurde.

die abfrage selbst, ist technisch gesehen recht einfach umzusetzen, ich sprach es schon mal an, korrelierte unterabfragen eignen sich dafür am besten, eventuell in verbindung mit der case funktionalität. group by ist hierfür eher ungeeignet. das problem besteht in der fachlichkeit, sprich was genau der letzte datensatz eines jeweiligen tickets bedeutet. ich hatte es schon mal versucht anzusprechen, machen es aber noch mal deutlicher. hier sind beispieldaten von dir für ein ticket:

Ticketnr     Status       open_date     update_time     close_time
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open-linked  01/01/2008    02/01/2008      00/00/0000

bei allen drei von dir erwähnten spalten ist die zeit genau gleich. wenn ein ticket geschlossen ist, könnten man offentsichtlich die close_time heran ziehen, um den letzten datensatz eines tickets zu bestimmen. aber in diesem falle gibt es keine close_time. woran also soll man bei diesem ticket festmachen, welches der "letzte" datensatz ist, dessen status-wert relevant ist ? dafür bräuchten wir noch ein paar mehr infos von dir. die angebliche sortierung der tabelle kann dafür nicht verwendet werden.

Ich habe mal einen Screenshot gemacht zur Verdeutlichung.

den kann ich nicht sehen den link.....

Ilja

Sorry hier nochmal der Link zum Screenshot.

http://spear-head.org/sql.jpg

[Ilja]

yo,

Sorry hier nochmal der Link zum Screenshot.

dort sehe ich auf den ersten blick nur zwei datumspalten und dann auch noch mit sehr unglücklichen spaltennamen. kannst du das noch mal deutlicher machen, welche spalte was ist und alle drei anzeigen ?

noch ein tipp, du brauchst nicht immer alles komplett zu zitieren in deinen antworten. beziehe dich nur auf relevante textstellen.

Ilja

[Ilja]

ok, die dritte habe ich gefunden, denke mal close_time ist die spalte f11, aber mach bitte noch mal deutlich, welche spalte was ist.

Ilja

[polarfox]

ok, die dritte habe ich gefunden, denke mal close_time ist die spalte f11, aber mach bitte noch mal deutlich, welche spalte was ist.

Ilja

Wie ich sagte: Die erste ist die "open_time" die zweite Zeit-spalte ist die "update_time" und die dritte Spalte ist die "close_time"
F11 ist demnach die "closed_time" : )

Ich bin inzwischen bereits ein Stück weiter:

• Ich habe über einen Filter erst einmal alle Tickets rausgezogen, die einen "Closed" Zeitstempel haben. Das sind - für Dezember - genau 6351 Tickets.

Im Prinzip muss ich jetzt nur noch irgendwie die noch offenen herausfiltern und dann die Listen abgleichen. Das Problem ist, dass es kein Feld gibt, dass mir definitiv sagt ob ein Ticket aktuell noch offen ist. Das sehe ich nur daran, dass der letzte Eintrag einer Ticketnummer keinen "closed" Zeitstempel hat.

Im Prinzip habe ich schon alles so wie ich es brauche. Nur die Doubletten verhindern eine sinnvolle Auswertung.

[Ilja]

yo,

Im Prinzip habe ich schon alles so wie ich es brauche. Nur die Doubletten verhindern eine sinnvolle Auswertung.

wie sagte doch die schlange ka im dschungelbuch, vertraue mir...dein freund sind korrelierte unterabfragen.

aber um ganz sicher zu gehen, müssen wir das problem auflösen, dass durch deine ersten beispieldaten enstanden ist. dort haben deine datumswerte KEINE zeitangaben: ich zitiere das relevante ticket noch mal, in der hoffnung, das es diesmal von dir aufgegriffen wird.

Ticketnr     Status       open_date     update_time     close_time
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open-linked  01/01/2008    02/01/2008      00/00/0000

bei den beispieldaten dieses tickets sieht es so aus, als wenn die zeitwerte der drei datensätze alle gleich sind. meine vermutung ist aber, du hast uns einfach nur die zeitwerte unterschlagen, dein link bekräftigt meine vermutung. aber gewissheit wäre schöner. also zeige doch noch mal für dieses ticket AD0012345 explizit die werte der drei spalten auf und wenn vorhanden MIT vollständiger zeitangabe.

Ilja

[polarfox]

yo,

Im Prinzip habe ich schon alles so wie ich es brauche. Nur die Doubletten verhindern eine sinnvolle Auswertung.

wie sagte doch die schlange ka im dschungelbuch, vertraue mir...dein freund sind korrelierte unterabfragen.

aber um ganz sicher zu gehen, müssen wir das problem auflösen, dass durch deine ersten beispieldaten enstanden ist. dort haben deine datumswerte KEINE zeitangaben: ich zitiere das relevante ticket noch mal, in der hoffnung, das es diesmal von dir aufgegriffen wird.

Ticketnr     Status       open_date     update_time     close_time
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open         01/01/2008    02/01/2008      00/00/0000
AD0012345    open-linked  01/01/2008    02/01/2008      00/00/0000

bei den beispieldaten dieses tickets sieht es so aus, als wenn die zeitwerte der drei datensätze alle gleich sind. meine vermutung ist aber, du hast uns einfach nur die zeitwerte unterschlagen, dein link bekräftigt meine vermutung. aber gewissheit wäre schöner. also zeige doch noch mal für dieses ticket AD0012345 explizit die werte der drei spalten auf und wenn vorhanden MIT vollständiger zeitangabe.

Ilja

Danke für's Feedback.

Bitte vergiss das Beispiel und schau in die Tabelle die ich angehangen habe als Screenshot. Natürlich haben die Zeitstempel unterschiedliche Werte. Ausnahme wäre, wenn 2 Tickets auf die Sekunde genau gleichzeitig geschlossen werden. Dann wären natürlich auch die "closed_time" Zeitstempel gleich. Wie im Screenshot ersichtlich haben die DATETIME Stempel natürlich auch hh:mm:ss angaben.

Falls es dir das ganze erleichtert, erstelle ich gerne auch hier eine Beispieltabelle mit vollständigen Daten.

[Ilja]

yo,

Bitte vergiss das Beispiel und schau in die Tabelle die ich angehangen habe als Screenshot.

ok, das ist doch mal eine aussage. und wenn ich den screenshot richtig interpretiere brauchen wir auch nur eine relevante spalte, nämlich die zweite für die updates, weil die ja bei den "closed" datensätzen ebenfalls gesetzt wird. ich nenne diese update spalte einfach mal datumsspalte2, kannst ihr dann ja den richten namen gegeben, wenn du die abfrage ausprobierst.

Natürlich haben die Zeitstempel unterschiedliche Werte. Ausnahme wäre, wenn 2 Tickets auf die Sekunde genau gleichzeitig geschlossen werden.

die abfrage setzt vorraus, dass es keine gleichen maximalen zeitstempel für ein und dasselbe ticket gibt. sollte das der fall sein, muss man noch mal nachlegen. ob das der fall ist, wird leicht mit eine gruppierung über die ticketnummer und having count(*) > 1 zu kontrollieren sein. aber hier erst mal deine abfrage.

SELECT t1.ticketnummer, t1.status, t1.weitere_spalten
FROM tabelle t1
WHERE t1.datumsspalte2 = (SELECT MAX(t2.datumsspalte2)
                          FROM tabelle t2
                          WHERE t2.ticketnummer = t1.ticketnummer
                         )
;

Ilja

[polarfox]

aber hier erst mal deine abfrage.

SELECT t1.ticketnummer, t1.status, t1.weitere_spalten
FROM tabelle t1
WHERE t1.datumsspalte2 = (SELECT MAX(t2.datumsspalte2)
                          FROM tabelle t2
                          WHERE t2.ticketnummer = t1.ticketnummer
                         )
;

Ilja

Die Idee ist genial. Einfach immer nur den Datensatz pro Ticket raussuchen, die den höchsten Wert in der update_time Spalte hat. Werde mich gleich mal ransetzen, und später Bericht erstatten.

Cheers !

[polarfox]

Sooo .. hier ist die Abfrage die ich aus deiner abgeleitet habe.

SELECT *
FROM problem_2008_12_uhd t1
WHERE t1.update_time = (SELECT MAX(t2.update_time)
                          FROM problem_2008_12_uhd t2
                          WHERE t2.incident = t1.incident
                         );

Erst habe ich die Abfrage über ca. 890.000 Zeilen laufen zu lassen. Und dann nach ca 3 Stunden abgebrochen. Dann habe ich es mit 230.000 versucht. Ebenfalls nach mehr als 2 Stunden abgebrochen. Momentan läuft die Query über ca 18.000 Zeilen. Leider auch schon eine ganze Weile.

Die Sache scheint sehr Zeitintensiv zu sein. Server ist aber nur so zu 50% ausgelastet. Mache ich etwas falsch ?

[polarfox]

Okay. Hatte kaum zu ende geschrieben, da kamen die results.

Sieht wirklich gut aus auf den ersten Blick. Damit habe ich eine grosse Hürde genommen. Danke Ilja !

[Ilja]

yo,

Okay. Hatte kaum zu ende geschrieben, da kamen die results.

leg mal einen index über die spalte "incident" an.

Ilja