gulchi: Ähnliche Einträge filtern

Hallo zusammen,

ich verwende MySQL 5.5.13. Mein Problem ist das Folgende:

Ich habe einen Tabelle mit folgenden Aufbau

uhrzeit  |  kennung  | ...
----------------------------
13:23:00 | abc            //Dieser Eintrag soll herausgefiltert werden
13:23:01 | abc            //
15:00:00 | def
17:30:00 | abc

In der Ausgabe sollen nun Einträge bei denen sich die Uhrzeit um maximal 10 Sekunden unterscheiden, nur einfach angezeigt werden. Die Ausgabe soll also folgendermaßen aussehen:

uhrzeit  | kennung  | ...
---------------------------
13:23:01 | abc
15:00:00 | def
17:30:00 | abc

Ich habe schon alles mögliche versucht, aber ich schaffe es einfach nicht.
Wäre wirklich sehr dankbar für eure Hilfe.

  1. Ich habe schon alles mögliche versucht, aber ich schaffe es einfach nicht.

    Dann wirds schwer, denn wenn alles mögliche nicht funktioniert und das unmögliche per Definition unmöglich ist, wie soll das gehen?

    Du könntest aber mal alles mögliche aufzählen und uns sagen, was du genau probiert hast, evtl. finden wir dann das Problem.

  2. Hi!

    In der Ausgabe sollen nun Einträge bei denen sich die Uhrzeit um maximal 10 Sekunden unterscheiden, nur einfach angezeigt werden.

    Das wird nicht einfach. Es gibt beim Filtern mit WHERE keinen Bezug auf einen Vorgänger oder Nachfolger. Und das schon deshalb nicht, weil die Sortierung erst später stattfindet. Um also zwei Datensätze zu vergleichen, muss man joinen. Das müsste jedoch in deinem Fall um einen Datensatz versetzt stattfinden. Da die Menge jedoch unsortiert ist und vermutlich keinen fortlaufenden, garantiert lückenlosen Zähler besitzt, kannst du nicht x mit x+1 joinen sondern müsstest auf größer oder kleiner als setzen und dann aber auf 1 limitieren, was gleich gar icht beim Joinen geht. Vielleicht übersehe ich auch etwas, aber eine einfache Lösung wirst du auf diese Weise nicht hinbekommen, denke ich.

    Du könntest nun im abfragenden Programm ein Array aufbauen. Der erste Datensatz wird abgelegt. Wenn der nächste kleiner als 10 Sekunden entfernt ist, wird der vorhergehende überschrieben, ansonsten wird es hinten drangehängt.

    Wenn die Abfrage schon im DBMS passieren soll, weil die Datenmenge wegen der doppelten Datensätze zu groß zum Übertragen wird, dann würde ich eine Stored Procedure schreiben. Allerdings braucht die zum Arbeiten auch temporären Speicher, was für den Server vielleicht zu einem Problem werden kann. Jedenfalls braucht es eine temporäre Tabelle, die das Ergebnis sammelt. Es gibt leider kein Array-Konstrukt oder etwas anderes, um einen Datensatz zwischenzuspeichern, außer einer weiteren temporären Tabelle. Benötigt wird jedoch ein Zwischenspeicher für einen Datensatz, weil der erst dann zur Ergebnis-Temp-Tabelle hinzugefügt werden kann, wenn eine genügend hohe Differenz zum nachfolgenden Datensatz festgestellt wurde. Ansonsten muss der Nachfolger in den Zwischenspeicher. Der Zwischenspeicher müsste also eine Handvoll Variablen sein oder bei größeren Mengen eben eine weiter temp. Tabelle. Bleibt nun noch, wie man sich durch die eigentliche Datenmenge bewegt: dazu kannst du dir einen so genannten Cursor erstellen. Der bekommt ein Select-Statement übergeben, welches die Daten aus deiner Tabelle mit allen weiteren gewünschten Bedingungen und in einer definierten Reihenfolgen zieht. Am Ende wird die temporäre Ergebnis-Tabelle mit einem normalen SELECT ausgegeben und erzeugt so die Ergebnismenge der Stored Procedure, die im abfragenden Programm wie bei einem einfachen SELECT behandelt wird.

    Nochmal zusammenfassend den Inhalt der Stored Procedure:

    • Temporäre Tabelle für das Ergebnis erstellen
    • Variablen oder weitere temporäre Tabelle für den Datensatzzwischenspeicher
    • Cursor für die Abfrage
    • Schleife
      -- vom Cursor einen Datensatz holen
      -- mit dem Zwischenspeicher vergleichen
      -- ggf. Zwischenspeicher ans Ergebnis hängen
      -- aktuellen Datensatz in Zwischenspeicher legen
    • Temporäre Tabelle für das Ergebnis ausgeben

    Die Syntax für die Elemente der Stored Procedure gibts im Kapitel SQL Statement Syntax.

    Lo!

    1. Hi,

      In der Ausgabe sollen nun Einträge bei denen sich die Uhrzeit um maximal 10 Sekunden unterscheiden, nur einfach angezeigt werden.

      Das wird nicht einfach. Es gibt beim Filtern mit WHERE keinen Bezug auf einen Vorgänger oder Nachfolger. Und das schon deshalb nicht, weil die Sortierung erst später stattfindet. Um also zwei Datensätze zu vergleichen, muss man joinen.

      … oder eine Subquery verwenden.

      Das müsste jedoch in deinem Fall um einen Datensatz versetzt stattfinden. Da die Menge jedoch unsortiert ist und vermutlich keinen fortlaufenden, garantiert lückenlosen Zähler besitzt, kannst du nicht x mit x+1 joinen sondern müsstest auf größer oder kleiner als setzen und dann aber auf 1 limitieren, was gleich gar icht beim Joinen geht.

      Bei der Subquery aber schon.

      Mir schwebt in etwa sowas vor:

      SELECT * FROM tabelle t1  
      WHERE IFNULL(  
        TIME_TO_SEC(  -- Zeit der Differenz in Sekunden umwandeln  
          TIMEDIFF(                            -- Differenz bilden  
            (  
              SELECT uhrzeit FROM tabelle t2   -- zwischen Uhrzeit des nächsten  
              WHERE t2.uhrzeit > t1.uhrzeit  
              ORDER BY uhrzeit ASC LIMIT 1  
            ),  
            t1.uhrzeit                         --  und des aktuellen Datensatzes  
          )  
        ),  
        11  -- oder, wenn es keinen nächsten Datensatz gibt, eine Anzahl von 11 Sekunden zurückliefern  
      ) > 10  -- nur die Datensätze selektieren, bei denen die Differenz zum nächsten Datensatz > 10 Sekunden ist  
      
      
      • In meinem Test mit ein paar Dutzend generierten Testdatensätzen scheint das auf den ersten Blick das gewünschte zu liefern.

      • IFNULL mit alternativer Auswahl von 11 Sekunden, damit es auch für den letzten Datensatz noch eine Differenz zum „Nachfolger“ gibt; Wert 11, damit er auch in der Ergebnismenge landet

      • Ob der OP noch eine Gruppierung nach seiner Spalte „kennung“ haben wollte, konnte ich aus der Fragestellung und den Beispieldaten nicht zweifelsfrei herauslesen; die müsste man ggf. noch mit einbauen beim Selektieren des jeweils „nächsten“ Datensatzes.

      • Wie das von der Performance her aussehen mag bei größeren Datenbeständen, darüber möchte ich nicht spekulieren; das war jetzt eher als “proof–of–concept” gedacht ;-)

      MfG ChrisB

      --
      RGB is totally confusing - I mean, at least #C0FFEE should be brown, right?
      1. Hi!

        … oder eine Subquery verwenden.

        Gute Idee, irgendwie war ich zu sehr in der Self-Join-Idee gefangen.

        [...] und dann aber auf 1 limitieren, was gleich gar icht beim Joinen geht.
        Bei der Subquery aber schon.

        Wobei in meiner Erinnerung ist, dass Limitieren in Subquerys nicht in allen Versionen/Situationen in MySQL geht. Muss man also mal probieren.

        Lo!

  3. Danke schon mal für eure Antworten.

    Mit Subuerys hab ichs schon versucht. Ich kann mich leider nicht mehr an meine Abfrage errinnern.

    Ich habe mir folgenden Workaround gebastelt:

    SELECT from_unixtime(floor(unix_timestamp(uhrzeit)/(20))*(20)) AS u, uhrzeit, kennung
    FROM table
    GROUP BY u,kennung

    Ich runde meine Uhrzeit in 20 Sekunden Schritten und sorge dann mit GROUP BY das doppelte gleiche (gerundete) Uhrzeiten nicht angezeigt werden.
    Natürlich unterdrücke ich damit nicht jeden doppelten Wer, da
    13:00:14 auf 13:00:00 gerundet wird und
    13:00:15 auf 13:00:20 gerundet wird.
    Nicht perfekt aber immerhin etwas.

    Ich würde es gerne nochmal mit Subuerys versuchen, hab damit aber leider keine Erfahrung.
    Tipps in dieser Richtung würden mir also sehr helfen.

    1. Hi!

      Ich habe mir folgenden Workaround gebastelt:
      SELECT from_unixtime(floor(unix_timestamp(uhrzeit)/(20))*(20)) AS u, uhrzeit, kennung
      FROM table
      GROUP BY u,kennung

      Das geht syntaktisch, aber nur mit MySQL. Beachte, dass in den Datensätzen einer Gruppe unterschiedliche Werte stehen können. Wenn du nun Felder abfragst, die nicht zur Gruppierung gehören oder in Aggregatefunktionen verwendet werden, dann bekommst du die Daten eines zufälligen Datensatzes geliefert. Wenn die in den betreffenden Feldern ungleich sind, hast du den Salat. Andere DBMSe lassen sich gleich gar nicht auf dieses Spielchen ein.

      Ich runde meine Uhrzeit in 20 Sekunden Schritten und sorge dann mit GROUP BY das doppelte gleiche (gerundete) Uhrzeiten nicht angezeigt werden.
      Natürlich unterdrücke ich damit nicht jeden doppelten Wer, da
      13:00:14 auf 13:00:00 gerundet wird und
      13:00:15 auf 13:00:20 gerundet wird.
      Nicht perfekt aber immerhin etwas.

      Wenn du dich damit zufriedengibst, ist das in der Syntax einfacher als die Subquery- oder gar die Stored-Procedure-Lösung. Anzumerken ist nur, dass deine Rechnung bei jeder Abfrage für jeden Datensatz durchgeführt werden muss. Wenn du das öfter brauchst und eine schlechte Performance bemerkst, wäre vielleicht eine andere Lösung besser. Ich denke da an ein Kopieren der zusammengefassten Datensätze in eine weitere Tabelle. Das ist natürlich nicht mehr praktikabel, wenn die Tabelle ständig weiter gefüllt wird.

      Ich würde es gerne nochmal mit Subuerys versuchen, hab damit aber leider keine Erfahrung.
      Tipps in dieser Richtung würden mir also sehr helfen.

      Was konkret möchtest du wissen? Für deinen Fall hat ChrisB ja schon eine Lösung vorgeschlagen.

      Lo!