Gleitende Durchschnittsberechnung In Sql

Ich arbeite mit SQL Server 2008 R2 und versuche, einen gleitenden Durchschnitt zu berechnen. Für jeden Datensatz meiner Ansicht möchte ich die Werte der 250 vorherigen Datensätze sammeln und dann den Durchschnitt für diese Selektion berechnen. Meine Ansichtsspalten sind wie folgt: TransactionID ist eindeutig. Für jede TransactionID. Ich möchte den Durchschnitt für Spaltenwert über 250 Datensätze berechnen. So für die TransactionID 300, sammeln Sie alle Werte aus früheren 250 Zeilen (Ansicht wird absteigend nach TransactionID sortiert) und dann in Spalte MovAvg das Ergebnis des Mittelwerts dieser Werte schreiben. Ich bin auf der Suche, um Daten in einer Reihe von Datensätzen zu sammeln. Gefragt Oktober 28 14 um 20: 58Derzeit haben wir diskutiert, wie man rollende 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. Postgres: Zeilen zwischen 3 vorherigen und 3 folgenden MySql: zwischen signups. date - 3 und signups. date 3 in MySQL SQL Server: zwischen dateadd (Tag, -3, signups. (Tag, 3, signups. date) Gewichteter gleitender Durchschnitt in T-SQL In einem früheren Beitrag zeigte ich die Berechnung von gleitenden Durchschnitten in T-SQL. Allerdings gibt es einen großen Nachteil mit einfachen gleitenden Durchschnitten. Preisänderungen am Anfang der Periode haben die gleiche Bedeutung wie die jüngsten Preisänderungen. Irgendwie haben Sie gerne die verschiedenen Gewichte zu den Preisänderungen zuweisen, so dass die jüngsten Änderungen das meiste Gewicht bekommen. Dazu kann ein Weighted Moving Average (WMA) berechnet werden. In diesem Blog-Beitrag werde ich zeigen zwei verschiedene Methoden, um WMA zu berechnen, eine, die auf SQL Server 2005 und höher verwendet werden kann, und die andere Version ist für SQL Server-Versionen früher als 2005. Um das relative Gewicht jeder Preisänderung zu berechnen, Müssen die Position jeder Preisänderung relativ zum berechneten Tag kennen. Daher kann eine Fensterfunktion nicht verwendet werden. Es ist nicht möglich, Informationen aus den einzelnen Zeilen im Fenster zu erhalten. Im folgenden Beispiel wird der gewichtete gleitende Durchschnitt für 9 Tage (WMA9) berechnet. Das Beispiel verwendet das TAdb. Ein Skript zur Erstellung von TAdb finden Sie hier. Unabhängig von der SQL Server-Version, für jede Zeile müssen wir auf die 8 vorherigen Zeilen, 9 mit der aktuellen Zeile enthalten. Diese 9 Zeilen sind das Fenster, das unsere Preisänderungen enthält. Jeder Zeile in diesem Fenster wird ein lineares Gewicht zugewiesen, das mit dem gleichen Betrag für jede Zeile bis zur aktuellen Zeile zunimmt. Das Gewicht für jede Zeile wird berechnet, indem die Fensterreihenposition relativ zur aktuellen Zeile verwendet wird. Let8217s sagen, dass wir WMA für die 9-te Zeile berechnen wollen (Zitate von TAdb StockId 1): 1 30,02 30,02 2 30,33 60,66 3 30,33 90,99 4 30,44 121,76 5 30,24 151,20 6 30,27 181,62 7 29,87 209,09 8 30,00 240,00 9 30,02 270,18 Die Summe der obigen ist 1355,52. Dies wird dann durch die Summe der Gewichte dividiert, das heißt 123456789 45. Das WMA9 für Zeile 9 ist 1355,52 45 30,12. Wenn Sie WMA ausser für 9 Tage berechnen möchten, verwenden Sie die folgende T-SQL (erhalten Sie hier die GetNums2-Funktion), um den Divisor für die Periodenlänge (zB 45 für 9 Tage WMA) zu erhalten: Gewichtete Moving Average (WMA) Divisoren Gewichteter gleitender Durchschnitt SQL Server 2005 und höher Diese Version verwendet einen CTE, um die WMA zu berechnen: Ergebnis für 9 Tage Gewichteter gleitender Durchschnitt (WMA9) In den obigen Ergebnissen können Sie sehen, dass das WMA9 für Zeile 9 30,12 ist, wie zuvor berechnet. Weighted Moving Average vor SQL Server 2005 Der einzige Unterschied zwischen der SQL Server 2005-Version und dieser ist die Verwendung eines gemeinsamen Tabellenausdrucks. Die Vor 2005-Version verwendet tatsächliche Tabellen anstelle von CTE8217s: Leistung Wenn Sie einen einfachen gleitenden Durchschnitt berechnen und SQL Server 2012 oder höher verwenden, kann eine riesige Verbesserung in der Leistung gesehen werden, wenn Fensterfunktionen im Vergleich zu den alternativen Metohods verwendet werden, die in älteren Versionen von SQL Server verwendet werden . Berechnungen von gewichteten gleitenden Durchschnitten können jedoch Fensterfunktionen auf dieselbe Weise verwenden. Ein Vergleich zwischen der SQL Server 2005-Version von WMA zeigt eine kleine Verbesserung gegenüber den Versionen, die in älteren Versionen von SQL Server verwendet werden: T-SQL WMA SQL Server 2005 verglichen mit Version 2005. Wegen der teuren Berechnungen in WMA beteiligt, könnte es eine gute Idee, um die Ergebnisse bestehen. WMA werden in der Trendanalyse genauso wie SMA verwendet. WMA hat mehr Gewicht auf die jüngsten Preisänderungen jedoch. Dieser Blog-Beitrag ist Teil einer Serie über technische Analyse, TA, in SQL Server. Siehe die anderen Beiträge hier. Geschrieben von Tomas Lind Tomas Lind - Consulting als SQL Server DBA und Datenbankentwickler bei High Coast Database Solutions AB.