Mysql Moving Average Berechnung

Als ich ein ähnliches Problem hatte, landete ich mit temporären Tabellen für eine Vielzahl von Gründen, aber es machte dies viel einfacher, was ich tat, um sehr ähnlich sieht, was du zu tun, so weit das Schema geht. Machen Sie das Schema so etwas wie ID identity, startdate, enddate, value. Wenn Sie auswählen, führen Sie einen Subselect-Durchschnitt der vorherigen 20 basierend auf der Identitäts-ID aus. Nur tun, wenn Sie sich bereits mit Temp-Tabellen aus anderen Gründen aber (Ich traf die gleichen Zeilen immer und immer für verschiedene Metriken, so war es hilfreich, die kleinen Dataset haben). Nach meiner Erfahrung tendiert Mysql ab 5.5.x nicht dazu, Indizes auf abhängige selects, ob eine Unterabfrage oder join, zu verwenden. Dies kann eine erhebliche Auswirkung auf die Leistung haben, wenn sich die abhängigen Auswahlkriterien bei jeder Zeile ändern. Der gleitende Durchschnitt ist ein Beispiel für eine Abfrage, die in diese Kategorie fällt. Die Ausführungszeit kann mit dem Quadrat der Zeilen ansteigen. Um dies zu vermeiden, wählen Sie eine Datenbank-Engine, die indizierte Lookups auf abhängige selects durchführen können. Ich finde postgres wirkt effektiv für dieses Problem. beantwortet 14. Juli 2, um 8:01 Ihre Antwort 2017 Stapel Wechsel, IncMetrics Maven: Ein gleitender Durchschnitt in PostgreSQL In unserer Metrics Maven Serie Rechnen, können Komponiert Daten Wissenschaftler Aktien Datenbank-Features, Tipps, Tricks und Code, den Sie die Metriken erhalten Sie verwenden, um Benötigen Sie Ihre Daten. Lesen Sie in diesem Artikel, wie Sie einen gleitenden Durchschnitt in PostgreSQL berechnen. Dieser Artikel basiert auf unseren beiden letzten Artikeln über Fensterfunktionen und Fensterrahmen in PostgreSQL. Nun nutzen Sie die Fenster-Funktionen weve zuvor diskutiert, um einen gleitenden Durchschnitt zu berechnen und auch auf eine alternative Methode. Was ist ein gleitender Durchschnitt Ein gleitender Durchschnitt ist genau das, was es klingt wie - ein Durchschnitt, der ständig auf der Grundlage der sich ändernden Eingabe bewegt. Beispielsweise möchten Sie vielleicht den Durchschnitt von einem Wert für die Top 100 Einträge oder für die letzten 30 Tage zu nehmen. Da Sie neue Einträge in Ihre Datenbank erhalten oder weil jeder neue Tag ein anderes Datum ist, ändert sich der Durchschnitt. Der Begriff gleitenden Durchschnitt ist auch gleichbedeutend mit rollenden durchschnittlichen oder laufenden Durchschnitt, aber es gibt ein paar verschiedene Arten von gleitenden Durchschnitten. In diesem Artikel wurden auf den einfachen gleitenden Durchschnitt konzentrieren, um unsere Füße nass und gut auch kurz den kumulativen gleitenden Durchschnitt am Ende des Artikels. Ein zukünftiger Artikel deckt gewichtete und exponentielle gleitende Durchschnitte. Der Grund, einen gleitenden Durchschnitt für Ihre Metriken zu verwenden, besteht darin, die Erfassung von Trends zu erleichtern. Es ist eine häufig verwendete Technik in der Finanz-und Business-Analytik zu glätten Dips und Spikes, die in den Daten auftreten, so dass echte Trends über die sich ändernde Serie identifiziert werden können. Herauszufinden, wie die Berechnung als die Datenänderungen durchführen kann ein wenig entmutigend, aber wenn Sie es nie getan haben. Sobald Sie eine Methode, die Sie mögen lernen, obwohl, (gut decken zwei) seine leicht zu tun und youll finden viele Verwendungen für sie in Ihrem Tracking und Berichte. Lasst uns anfangen. Erste Dinge zuerst: gut brauchen eine Tabelle, die die Werte, die wir wollen durchschnittlich enthält. In der Praxis bei Compose, finden wir oft, dass die Basisdaten, die wir benötigen, nicht bereits ordentlich in einer Tabelle definiert sind. Aus diesem Grund haben wir ein paar Aggregat-Tabellen, die die Daten, die wir zusammen brauchen zu ziehen. Dies sind die Basistabellen, auf die wir fortgeschrittenere Berechnungen anwenden, wie einen gleitenden Durchschnitt. In einigen Fällen handelt es sich dabei um abgeleitete Tabellen, die vorübergehend für die Ausführung der Hauptabfrage existieren. In anderen Fällen können wir eine Ansicht oder eine materialisierte Ansicht verwenden. Also, wie Sie es bekommen, benötigen Sie eine Tabelle mit den Werten, die Sie durchschnittlich möchten und welche Dimension (en) Sie die Daten durch bestellen möchten. Für unser Beispiel, sagen wir weve wurde gebeten, einen 30-Tage-rollenden Durchschnitt für App-Downloads von Beispiel zu erstellen. Die App-Download-Daten wird täglich auf eine Tabelle mit dem Namen appdownloadsbydate und der jüngste Teil davon sieht so aussehen: In diesem Beispiel, Wird die Reihenfolge nach Datum wichtig sein, da wir einen 30-tägigen Rollendurchschnitt über die vorangegangenen Datenreihen berechnen wollen. Aus diesem Grund ist es wichtig, dass wir für jedes Datum eine Zeile haben. In unserem Fall haben wir, aber wenn Sie Lücken in Ihrer Daten haben, wo es keine Werte für bestimmte Termine sind, können Sie generateseries verwenden, wenn Ihre Basistabelle konstruieren, um sicherzustellen, bekam youve alle Zeilen youll müssen. Beachten Sie, wie diese Reihe von Daten enthält fluctuating App-Downloads von 35 bis 7. Sein sehr schwierig, einen Trend aus diesen Daten zu sehen: Geben Sie den gleitenden Durchschnitt. Verwenden von Fensterrahmen für einen einfachen gleitenden Durchschnitt Wenn Sie aus unserem vorherigen Artikel in dieser Serie zu erinnern. Werden Fensterrahmen verwendet, um die Anzahl der Zeilen um die aktuelle Zeile anzuzeigen, die die Fensterfunktion enthalten soll. Sie erstellen eine Teilmenge von Daten für die Fenster-Funktion zu bedienen. Abhängig von Ihren Daten und Ihren Anforderungen kann Ihre gleitende Durchschnittsberechnung Zeilen vor und nach der aktuellen Zeile enthalten, aber für unsere Zwecke wird unser gleitender Durchschnitt die vorherigen Zeilen und die aktuelle Zeile verwenden, weil wir einen neuen gleitenden Mittelwert für generieren möchten Jedes neue Datum. Unsere Abfrage sieht folgendermaßen aus: Wurden ORDER BY auf unserem Datumsfeld verwendet, um sicherzustellen, dass unsere Daten in der von uns erwarteten Reihenfolge sein werden und wir haben angegebene ROWS ZWISCHEN 29 PRECEDING und CURRENT ROW, um den Fensterrahmen für die AVG-Berechnung einzustellen. Da der Fensterrahmen für jedes Datum fortschreitet, werden nur die vorherigen 29 Zeilen und die aktuelle (30 Tage) für die Berechnung verwendet: Da wir in diesem Fall die Daten in unserer Basistabelle vor dem 26. Mai für dieses Beispiel zeigen, Der Ergebnisse zu den Daten, an denen wir Ihnen die 29 vorhergehenden Zeilen gezeigt haben. Nehmen wir zum Beispiel den 30. Juni. Unser Fensterrahmen fokussiert unsere AVG-Aggregation auf die App-Downloads ab Juni nur diesen Teil unserer Basistabelle: So, jetzt, wenn wir das Rolling Average Weve berechnet, können wir sehen, dass die Daten geglättet und es gibt einen Aufwärtstrend Durch die erste Woche im Juni, dann eine volatilere Abwärtstrend nach, dass: Da dies zeigt nur einen Monat Daten ist es nicht sehr für einen analytischen Bericht zu erzählen, aber hoffentlich hilft es Ihnen zu verstehen, wie die Berechnung eines gleitenden Durchschnitt kann nützlich für die Wirtschaft sein Analyse. Ein Tipp auf nicht einschließlich der aktuellen Zeile Wenn aus irgendeinem Grund Sie nicht möchten, dass die aktuelle Zeile für Ihre Fenster-Funktion und youre mit nur PRECEDING oder nur FOLLOWING-Einstellungen für Ihren Fensterrahmen, eine einfache Möglichkeit zu tun, die zu verwenden ist x PRECEDING oder Y FOLGEN Sie zweimal in Ihren ROWS ZWISCHEN. Klausel. Zum Beispiel sagen wir, wir wollten 30 Zeilen vor unserer aktuellen Zeile verwenden, aber nicht die aktuelle Zeile in den Fensterrahmen. Wir könnten schreiben, dass Klausel wie folgt: ZEITEN ZWISCHEN 30 PRECEDING UND 1 PRECEDING. Ebenso können wir die aktuelle Zeile ausschließen, jedoch 30 Zeilen wie folgt ausführen: ROWS BETWEEN 1 FOLLOWING und 30 FOLLOWING. Eine alternative Methode für einen einfachen gleitenden Durchschnitt Vor PostgreSQL 9.0, hatten wir nicht die x PRECEDING oder y FOLLOWING Fensterrahmen Optionen zur Verfügung. Um einen gleitenden Durchschnitt ohne Verwendung eines Fensterrahmens zu berechnen, können wir stattdessen zwei Tabellenaliasnamen aus unserer Basistabelle verwenden. Nun verwenden Sie einen Alias ​​zu bedienen über die andere mit einem Datum Intervall. Check it out: Mit dieser Methode können wir die gleichen Ergebnisse erzielen, wie oben mit dem Fensterrahmen beschrieben. Wenn Sie über große Datenmengen arbeiten, wird die Fensterrahmenoption effizienter, aber diese Alternative ist vorhanden, wenn Sie sie verwenden möchten. Berechnung eines kumulativen gleitenden Durchschnitt Nun, dass Weve überprüft ein paar Methoden, wie man einen einfachen gleitenden Durchschnitt zu berechnen, gut schalten Sie unsere Fensterrahmen Beispiel, um zu zeigen, wie Sie auch einen kumulativen gleitenden Durchschnitt. Die gleichen Prinzipien gelten, aber anstatt mit einem ständig wechselnden Fensterrahmen für ein Intervall, erstreckt sich der Fensterrahmen einfach. Zum Beispiel, anstatt zu tun, einen 30-Tage-Rolling Durchschnitt, würde ein Jahr-to-date gleitenden Durchschnitt zu berechnen. Für jeden neuen Tag ist sein Wert einfach in der Durchschnittsberechnung von allen vorherigen Daten enthalten. Sehen wir uns dieses Beispiel an: Weil unsere Basistabelle ab dem 1. Januar für das laufende Jahr beginnt, wurde UNBOUNDED PRECEDING verwendet, um unseren Fensterrahmen zu setzen. Die Ergebnisse, die wir für diese kumulative Berechnung erhalten, sehen folgendermaßen aus: Wenn wir diese Ergebnisse darstellen, können Sie sehen, dass der Vorteil des kumulativen gleitenden Durchschnitts eine weitere Glättung der Daten ist, so dass sich nur signifikante Datenänderungen als Trends zeigen. Wir sehen jetzt, dass es einen leichten Aufwärtstrend Jahr-to-date: Wrapping Nun, da Sie wissen, ein paar verschiedene Arten von gleitenden Durchschnitten können Sie und ein paar verschiedene Methoden, um sie zu berechnen, können Sie mehr aufschlussreiche Analyse und mehr zu schaffen Wirksame Berichte. In unserem nächsten Metriken Maven Artikel, auch auf einige Optionen, wie Sie Daten ziemlich so, dass statt der Werte wie 20.4184782608695652, siehe auch 20.42. Bis zum nächsten Mal Kopie 2017 ComposeVorher haben wir diskutiert, wie Rolling-Durchschnitte in Postgres schreiben. Durch die populäre Nachfrage zeigten Ihnen, wie das gleiche in MySQL und SQL Server zu tun. Nun zu decken, wie kommentieren laute Charts wie folgt: Mit einem 7-Tage vorherigen durchschnittlichen Zeile wie folgt: Die große Idee Unsere erste Grafik oben ist ziemlich laut und schwer zu nützlichen Informationen zu bekommen. Wir können es glätten, indem wir einen 7-Tage-Durchschnitt auf die zugrundeliegenden Daten zeichnen. Dies kann durch Fensterfunktionen, Self-Joins oder korrelierte Unterabfragen erfolgen - decken die ersten beiden ab. Nun beginnen mit einem vorangegangenen Durchschnitt, was bedeutet, dass der durchschnittliche Punkt am 7. des Monats der Durchschnitt der ersten sieben Tage ist. Optisch verschiebt sich die Spitze im Graphen nach rechts, da eine große Spitze über die nächsten sieben Tage gemittelt wird. Erstens eine Zwischenzahltabelle erstellen Wir wollen einen Durchschnittswert über die gesamten Anmeldungen für jeden Tag berechnen. Angenommen, wir haben eine typische Benutzer-Tabelle mit einer Zeile pro neuen Benutzer und einem Zeitstempel erstellt, können wir unsere aggregate unsere Signups-Tabelle wie folgt erstellen: In Postgres und SQL Server können Sie diese als CTE verwenden. In MySQL können Sie sie als temporäre Tabelle speichern. Postgres Rolling Average Glücklicherweise hat Postgres Fenster-Funktionen, die der einfachste Weg, um einen laufenden Durchschnitt zu berechnen sind. Diese Abfrage setzt voraus, dass die Daten keine Lücken aufweisen. Die Abfrage ist Mittelung über die letzten sieben Zeilen, nicht die letzten sieben Termine. Wenn Ihre Daten Lücken aufweisen, füllen Sie sie mit generateseries oder Beitritt gegen eine Tabelle mit dichten Datumszeilen. MySQL Rolling Average MySQL fehlt Fenster-Funktionen, aber wir können eine ähnliche Berechnung mit Self-Joins zu tun. Für jede Zeile in unserem Zählungstabelle verbinden wir jede Zeile, die innerhalb der letzten sieben Tage war, und nehmen den Durchschnitt. Diese Abfrage behandelt automatisch Datumslücken, da wir Zeilen innerhalb eines Datumsbereichs anstelle der vorhergehenden N Zeilen betrachten. SQL Server Rolling Average SQL Server verfügt über Fensterfunktionen, so dass die Berechnung der rollenden Durchschnitt kann entweder in der Postgres-Stil oder MySQL-Stil erfolgen. Zur Vereinfachung wurden die MySQL-Version mit einem Self-Join. Dies ist konzeptionell das gleiche wie in MySQL. Die einzigen Übersetzungen sind die dateadd Funktion und explizit benannt Gruppe nach Spalten. Andere Mittelwerte Wir konzentrierten uns auf den 7-tägigen nachlaufenden Durchschnitt in diesem Beitrag. Wenn wir uns den 7-Tage-Leitdurchschnitt anschauen wollten, so einfach wie die Daten in die andere Richtung sortieren. Wenn wir bei einem zentrierten Durchschnitt, wed Verwendung aussehen wollte: Postgres: Reihen zwischen 3 vor und 3 nach MySql: zwischen signups. date - 3 und signups. date 3 in MySQL SQL Server: zwischen dateadd (Tag, -3, signups. Datum) und dateadd (Tag, 3, signups. date)