Methode bei der Suche nach Lücken in den Zeitreihen, Daten in MySQL?
Können sagen, wir haben eine Datenbank-Tabelle mit zwei Spalten, entry_time und Wert. entry_time ist timestamp während der Wert kann einen beliebigen anderen Datentyp. Die Datensätze sind relativ konsistent, eingegeben in etwa in x-minütigen Abständen. Für viele x 's Zeit, aber ein Eintrag kann nicht gemacht werden, so dass eine "Lücke" in den Daten.
In Bezug auf die Effizienz, was ist der beste Weg zu gehen über die Suche nach diesen Lücken von mindestens Zeit Y (neuen und alten) mit einer Abfrage?
Wie definieren Sie eine Lücke? Sie haben ein hartes limit, wie viel Zeit darf vergehen zwischen den Eingängen?
Eine variable Y. Vergaß, um anzugeben, dass.
Eine variable Y. Vergaß, um anzugeben, dass.
InformationsquelleAutor TheDog | 2012-06-18
Du musst angemeldet sein, um einen Kommentar abzugeben.
So starten Sie mit, lassen Sie uns zusammenfassen, die Anzahl der Einträge pro Stunde in der Tabelle.
Nun, wenn Sie sich etwas alle sechs Minuten (zehn mal eine Stunde) alle Ihre samplecount Werte sollten zehn. Dieser Ausdruck:
CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME)
sieht haarig, aber es einfach kürzt Ihre Zeitstempel zu der Stunde, in der Sie auftreten, durch unwiderrufliche löschen die minute und Sekunde.Dies ist einigermaßen effizient, und erhalten Sie begann. Es ist sehr effizient, wenn Sie können, legen Sie einen index für Ihre entry_time Spalte und beschränken Sie die Abfrage um, sagen wir mal, den gestrigen Proben, wie hier gezeigt.
Aber es ist nicht sehr gut bei der Erkennung ganzen Stunden, die sich mit fehlenden samples. Es ist auch ein wenig empfindlich auf jitter in Ihre sampling. Das ist, wenn Sie Ihre top-von-the-hour-Probe wird manchmal eine halbe Sekunde zu früh (10:59:30) und manchmal eine halbe Sekunde zu spät (11:00:30) Ihre stundenübersicht zählt, wird ausgeschaltet. So ist diese Stunde Zusammenfassung, was (oder den Tag-Zusammenfassung, - oder Minuten-Inhaltsangabe, etc) ist nicht kugelsicher.
Benötigen Sie einen selbst-join-Abfrage Zeug zu bekommen, völlig Recht; es ist ein bisschen mehr von einem Haarballen und nicht annähernd so effizient.
Lassen Sie uns beginnen Sie, indem Sie uns eine virtuelle Tabelle (Unterabfrage) mit Nummerierung der Proben. (Dies ist ein Schmerz in MySQL; einige andere teure DBMS, die es leichter machen. Egal.)
Diese kleine virtuelle Tabelle gibt entry_num, entry_time, Wert.
Nächsten Schritt, wir machen es selbst.
Diese Linien den Tabellen der nächsten zwei einander ausgeglichen durch einen einzigen Eintrag, geregelt durch die ON-Klausel von der JOIN.
Schließlich wählen wir die Werte aus dieser Tabelle mit einem
interval
größer als der Schwellenwert ist, und es gibt die Zeiten für die Proben unmittelbar vor dem fehlenden.Dem über alle self-join-Abfrage ist. Ich sagte Ihnen, es war ein Haarballen.
Wenn Sie haben, dies zu tun in der Produktion auf eine große Tabelle, möchten Sie vielleicht, es zu tun für eine Teilmenge Ihrer Daten. Zum Beispiel, könnten Sie tun es jeden Tag für den letzten zwei Tagen " Proben. Das wäre anständig, effiziente, und würde auch sicher, dass Sie nicht übersehen werden fehlende samples direkt um Mitternacht. Um dies zu tun, Ihre wenig rownumbered virtuelle Tabellen würde so Aussehen.
Diese
@sample
variable verfolgt die Nummer der Zeile. Beachten Sie, dass es bei der Initialisierung im(SELECT @sample:=0)
inkrementiert und für jede Zeile der Tabelle. Wenn Sie Zehntausende von Dollar zu zahlen für Oracle, Sie könnte einfach sagen, ROWNUM, aber das ist der MySQL-hack um das gleiche zu tun. Arcane, eh?+1 Schritt-für-Schritt Erklärung
InformationsquelleAutor O. Jones
Einen sehr effizienten Weg, dies zu tun ist mit einer gespeicherten Prozedur mithilfe von Cursorn. Ich denke, das ist einfacher und effizienter als die anderen Antworten.
Diese Prozedur erstellt einen cursor und iteriert durch die datetime-Datensätze, die Sie überprüfen möchten. Wenn es jemals eine Lücke von mehr als das, was Sie angeben, schreibt er die Lücke beginnen und Ende einer Tabelle.
In diesem Fall wird davon ausgegangen, dass 'tmp_table' existiert. Sie könnten leicht definieren, dies als eine TEMPORÄRE Tabelle in der Prozedur, aber ich ließ es aus diesem Beispiel.
InformationsquelleAutor Alden W.