Ich arbeite mit SQL Server 2008 R2 und versuche, einen gleitenden Durchschnitt zu berechnen. Für jeden Datensatz meiner Ansicht nach möchte ich die Werte der 250 vorherigen Aufzeichnungen sammeln und dann den Durchschnitt für diese Auswahl berechnen. Meine Ansichtspalten sind wie folgt: TransactionID ist eindeutig. Für jede TransactionID. Ich möchte den Durchschnitt für Spaltenwert berechnen, über vorherige 250 Datensätze. Also für TransactionID 300, sammle alle Werte aus vorherigen 250 Zeilen (Ansicht wird absteigend von TransactionID sortiert) und dann in Spalte MovAvg das Ergebnis des Mittelwertes dieser Werte schreiben. Ich bin auf der Suche nach Daten in einer Reihe von Datensätzen zu sammeln. Gefragt 28. Oktober 14 bei 20: 58Exponential gleitenden Durchschnitt in T-SQL Exponentielle gleitende Durchschnitte sind ähnlich wie gewichtete Bewegungsdurchschnitte, in denen sie weniger Gewicht zu Änderungen vor langer Zeit und mehr Gewicht auf die jüngsten Änderungen zuweisen. Gewichtete Bewegungsdurchschnitte sind linear, aber exponentielle gleitende Durchschnitte sind exponentiell. Das heißt, das Gewicht kann als Kurve ausgedrückt werden: Es gibt eine großartige Möglichkeit, exponentielle gleitende Durchschnitte in T-SQL zu berechnen, indem ein undokumentiertes Merkmal über Variablen und laufende Summen in SQL Server verwendet wird. In diesem Blog-Post werde ich zeigen, wie man diese Methode verwendet, um exponentiellen gleitenden Durchschnitt in T-SQL zu berechnen, aber ich werde auch eine Methode, die Standard-Features in SQL Server verwendet. Leider bedeutet das mit einer Schleife. In den Beispielen berechne ich einen 9 Tage exponentiellen gleitenden Durchschnitt. Die Beispiele verwenden die Datenbank TAdb. Ein Skript zum Erstellen von TAdb finden Sie hier. Exponential Moving Average (EMA): Laufen Totals-Methode Die Theorie hinter den laufenden Total-Features in Updates wird ausführlich von Jeff Moden in seinem Artikel beschrieben, der die laufenden Total - und Ordinal-Rank-Probleme löscht. Andere Ressourcen, die mit dieser Methode beschreiben, um EMA zu berechnen, sind der Blog-Beitrag, der die Umschlagsdurchschnitte mit T-SQL von Gabriel Priester berechnet und die Forumspost Exponential Moving Average Challenge. Sowohl auf SQL Server Central. Grundsätzlich können Sie in T-SQL Variablen sowie Spalten in einer Update-Anweisung aktualisieren. Die Updates werden Zeile für Zeile intern von SQL Server ausgeführt. Dieses Zeilen-zu-Zeilen-Verhalten macht das Berechnen einer laufenden Summe möglich. Dieses Beispiel zeigt, wie es funktioniert: Beachten Sie, dass 8220ColumnRunningTotal8221 eine laufende Summe von 8220ColumnToSum8221 ist. Mit dieser Methode können wir EMA9 mit diesem T-SQL berechnen: Die Berechnung von EMA ist ziemlich einfach. Wir verwenden die aktuelle Zeile und die vorherige, aber mit mehr Gewicht auf die aktuelle Zeile. Das Gewicht wird nach der Formel 2 (19) berechnet, wobei 822098221 der Parameter für die Länge der EMA ist. Um die EMA9 für Zeile 10 oben zu berechnen, ist die Berechnung: In diesem Fall erhält die aktuelle Zeile 20 des Gewichts (2 (19) 0,2) und die vorherige Zeile erhält 80 des Gewichts (1-2 (19) 0,8). Sie finden diese Berechnungen in der obigen Anweisung in der CASE-Anweisung: Exponential Moving Average (EMA): Looping-Methode Soweit ich weiß, mit Ausnahme der laufenden Summen-Methode, die oben beschrieben wurde, gibt es keine Möglichkeit, EMA mit einer setbasierten SQL-Anweisung zu berechnen . Daher verwendet das T-SQL unten eine while-Schleife, um EMA9 zu berechnen: Die Ergebnisse sind die gleichen wie im laufenden Summenbeispiel oben. Leistung Wie erwartet, ist die setbasierte laufende Summenversion viel schneller als die Loop-Version. Auf meiner Maschine war die Set-basierte Lösung ca. 300 ms, im Vergleich zu etwa 1200 mit der Loop-Version. Die Loop-Version entspricht eher den SQL-Standards. Also die Wahl zwischen den Methoden hängt davon ab, was8217s wichtigste für Sie, Leistung oder Standards. Der exponentielle gleitende Durchschnitt kann in der Trendanalyse verwendet werden, wie bei den anderen Arten von gleitenden Durchschnitten, Simple Moving Average (SMA) und Weighted Gleitender Durchschnitt (WMA). Es gibt auch andere Berechnungen in der technischen Analyse, die die EMA, MACD zum Beispiel verwendet. Dieser Blog-Post ist Teil einer Serie über technische Analyse, TA, in SQL Server. Sehen Sie die anderen Beiträge hier. Geschrieben von Tomas Lind Tomas Lind - Beratungsdienste als SQL Server DBA und Datenbankentwickler bei High Coast Database Solutions AB. Zunächst haben wir diskutiert, wie man rollende Durchschnittswerte in Postgres schreibt. Durch die beliebte Nachfrage zeigten Sie, wie Sie das gleiche in MySQL und SQL Server zu tun. Nun, wie man kommentiert laute Charts wie folgt: Mit einer 7-Tage-Vor-durchschnittliche Zeile wie folgt: Die große Idee Unsere erste Grafik oben ist ziemlich laut und schwer zu nützlichen Informationen aus. Wir können es glätten, indem wir einen 7-tägigen Durchschnitt über die zugrunde liegenden Daten zeichnen. Dies geschieht mit Fensterfunktionen, Selbstverknüpfungen oder korrelierten Unterabfragen - gut decken die ersten beiden. Grasen Sie mit einem vorangegangenen Durchschnitt, was bedeutet, dass der durchschnittliche Punkt am 7. des Monats der Durchschnitt der ersten sieben Tage ist. Optisch verschiebt dies die Spikes in der Grafik nach rechts, da eine große Spitze in den folgenden sieben Tagen gemittelt wird. Zuerst eine Zwischenzählertabelle erstellen Wir wollen einen Durchschnitt ü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 Signatur-Tabelle wie folgt erstellen: In Postgres und SQL Server können Sie dies als CTE verwenden. In MySQL kannst du es als temporäre Tabelle speichern. Postgres Rolling Average Glücklicherweise hat Postgres Fensterfunktionen, die die einfachste Möglichkeit sind, einen laufenden Durchschnitt zu berechnen. Diese Abfrage geht davon aus, dass die Termine keine Lücken aufweisen. Die Abfrage ist durchschnittlich in den letzten sieben Reihen, nicht die letzten sieben Termine. Wenn Ihre Daten Lücken haben, füllen Sie sie mit generateseries oder verbinden sich gegen eine Tabelle mit dichten Datumszeilen. MySQL Rolling Average MySQL fehlt Fensterfunktionen, aber wir können eine ähnliche Berechnung mit Selbstverknüpfungen machen. Für jede Zeile in unserem Zählertisch, kommen wir zu jeder Zeile, die in den letzten sieben Tagen war und nehmen Sie den Durchschnitt. Diese Abfrage behandelt automatisch Datumslücken, da wir Zeilen innerhalb eines Datumsbereichs anstatt der vorhergehenden N Zeilen betrachten. SQL Server Rolling Average SQL Server hat Fenster-Funktionen, so dass die Berechnung der rollenden Durchschnitt kann entweder in der Postgres-Stil oder MySQL-Stil durchgeführt werden. Für die Einfachheit, waren die Verwendung der MySQL-Version mit einem Selbst-Join. Das ist konzeptionell das gleiche wie bei MySQL. Die einzigen Übersetzungen sind die dateadd-Funktion und explizit benannte Gruppe nach Spalten. Andere Mittelwerte Wir konzentrierten uns auf den 7-tägigen nachlaufenden Durchschnitt in diesem Beitrag. Wenn wir den 7-tägigen Vorquartal betrachten wollten, war es so einfach wie das Sortieren der Daten in die andere Richtung. Wenn wir einen zentrierten Durchschnitt betrachten wollten, verwenden wir: postgres: Zeilen 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) Ich bin noch nicht genau mit SQL vertraut: Ich antwortete zu schnell leid. Ich habe versucht, Ihren Code zu ändern, um einen neuen movering Durchschnitt hinzuzufügen, aber ich scheiterte, könnten Sie einen Blick darauf haben und mir sagen, was fehlt mit MA AS (SELECT ma. TradeDate, ma. Opening, ROWNUMBER () OVER (ORDER BY TradeDate ) AS Bestellung von EurostoxxBase1 ma) SELECT ma. TradeDate, ma. Opening, ma2.Opening, ma3.Opening, ((oaOinging 43 ma2.Opening) 2) AS MovingAverage2, ((ma. Opening 43 ma2.Opening 43 ma3.Opening) 3) AS MovingAverage3, (ma. Opening 43 ma2.Opening 43 ma3.Opening) 43 ma4.Opening) 4) AS MovingAverage4 VON MA MA LEFT OUTER JOIN MA ma2 ON ma. Order ma2.Order 43 1 LINKER OUTER JOIN MA ma3 ON ma2.Order ma3.Order 43 1 LINKS OUTER JOIN MA ma4 ON ma3.Order ma4.Order 43 1 Wenn ich dein Skript laufe, schaffe es eine temporäre Tabelle, was ich möchte, ist, diese neuen Spalten hinzuzufügen (MovingAverage1 MovingAverage2 . ) an den Tisch. Wie kann ich diese Moving Averages zu meinem Tisch hinzufügen Dienstag, 27. April 2010 um 11:33 Uhr Was Sie für einen 4 Tage gleitenden Durchschnitt haben, sieht gut aus, außer Sie haben eine extra links Klammer nach quot43 ma3.Opening) 43 ma4.Opening) Nehmen Sie das eine nach ma3.Opening. Um bewegte Durchschnitte in deiner Basistabelle zu haben, müssen Trigger beteiligt sein, es sei denn, jemand anderes hat eine andere Idee - anders als du könntest eine Ansicht erstellen, um die select-Anweisung einzukapseln und dann die Ansicht anstelle der Basistabelle zu verwenden. Dienstag, 27. April 2010 13:29 Könnten Sie mir mehr darüber erzählen, wie man in diesem Fall Trigger benutzt. Ich muss die verschiedenen Moving Averages in meinem Tisch haben, Kranke muss etwas berechnen. Ich muss diese Moving-Mittel in meinen Tisch hinzufügen. Dienstag, 27. April 2010 um 14:14 Uhr Trigger werden in einer großen Menge von Overhead hinzufügen. Zum Beispiel, wenn Sie 4 Tage im Umzug in Ihrer Basistabelle behalten würden, wenn ein Element eingefügt oder aktualisiert wurde, würde das Insertupdate auftreten und dann bis zu 4 weitere Updates pro Element, also 5 insgesamt für ein einzelnes insertupdate. Für ein paar Artikel, die Sie nicht viel Konkurrenz haben, aber wenn es viele Insertupdates ein secondminute dies könnte dazu führen, dass viele Fragen enthalten Deadlocking. Ich rate nicht, berechnete Werte in Basistabellen zu halten, sie sollten in Ansichten gehalten oder mit Hilfe von Analysedienstleistungen behandelt werden. Ich würde die Architektur der Anwendung ändern, bevor die anderen Probleme kriechen und später Chaos verursachen. Allerdings können Sie über Trigger HIER lesen. Dienstag, 27. April 2010 14:32 Der gespeicherte Wert ändert sich aus keinem Grund. Sie sind historische Daten. Umzugsdurchschnitte werden sich auch nicht ändern. Was ich denke, ist, eine zweite Tabelle (nicht vorübergehend) zu kisten und dann meine erste Tabelle mit historischen Daten und die Tabelle mit berechneten Moving-Mitteln zu verbinden. Alle diese Daten sind fest, kein Update, kein Einfügen, kein Löschen. Das einzige Problem, das ich mir vorstellen kann, ist, wann ich noch mehr Daten hinzufügen muss, zum Beispiel muss ich April 2010 in den ersten Mai-Tagen hinzufügen, ich möchte nicht den ganzen Prozess neu ausführen, sondern nur für das spezifische hinzufügen Werte und ich denke ich kann das schaffen. Was denkst du, Bin ich völlig falsch an diesen 2 Tischen anschließen Dienstag, 27. April 2010 um 14:46 Uhr Aber es klingt dort ist ein Problem mit dem Tisch, den ich erstellt habe: CREATE TABLE MovingAverage (TradeDateAvg SMALLdatetime, OpeningAvg FLOAT, B1MAvg2 FLOAT, B1MAvg3 FLOAT, B1MAvg4 FLOAT MIT MA AS (SELECT ma. TradeDate, ma. Opening, ROWNUMBER () OVER (ORDER BY TradeDate) AS Bestellung von EurostoxxBase1 ma) SELECT ma. TradeDate, ma. Opening, ma2.Opening, ma3.Opening, ma4.Opening, (ma. Opening 43 ma2.Opening) 2) AS B1MAvg2, ((ma. Opening 43 ma2.Opening 43 ma3.Opening) 3) AS B1MAvg3 von MA ma LINK OUTER JOIN MA ma2 ON ma. Order ma2. Bestellen 43 1 LINK OUTER JOIN MA ma3 ON ma2.Order ma3.Order 43 1 LINK OUTER JOIN MA ma4 ON ma3.Order ma4.Order Es funktioniert perfekt, ich kann alle Werte sehen Aber der Tisch ist leer, ich meine, wenn ich laufe. Wählen Sie aus MovingAverage Es gibt mir nur die Spaltennamen, kein Wert überhaupt Eine Idee, was ich falsch gemacht habe Vielen Dank, ich habe meinen Code geändert: CREATE TABLE MovingAverage (TradeDateAvg SMALLdatetime, OpeningAvg FLOAT, B1MAvg2 FLOAT, B1MAvg3 FLOAT, B1MAvg4 FLOAT MIT MA AS (SELECT ma. TradeDate, ma. Opening, ROWNUMBER () OVER (ORDER BY TradeDate) AS Auftrag von EurostoxxBase1 ma) INSERT IN in MovingAverage (TradeDateAvg, OpeningAvg, B1MAvg2, B1MAvg3, B1MAvg4) SELECT ma. TradeDate, ma. Opening, ( (Ma. Opening 43 ma2.Opening) 2) AS B1MAvg2, ((Na. Opening 43 ma2.Opening 43 ma3.Opening) 3) AS B1MAvg3 ((ma. Opening 43 ma2.Opening 43 ma3.Opening 43 ma4.Opening ) 4) AS B1Mavg4 VON MA MA LEFT OUTER JOIN MA ma2 ON ma. Order ma2.Order 43 1 LINK OUTER JOIN MA ma3 ON ma2.Order ma3.Order 43 1 LINK OUTER JOIN MA ma4 ON ma3.Order ma4.Order 43 1 Aber nichts passiert Die Anfrage ist noch im Gange, keine Fehlermeldung, aber es scheint, es macht nichts. Irgendeine Idee was ich wieder vermisse
No comments:
Post a Comment