Moving Average Query 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: 58Dies ist eine Evergreen Joe Celko Frage. Ich ignoriere, welche DBMS-Plattform verwendet wird. Aber auf jeden Fall Joe war in der Lage, mehr als 10 Jahren mit Standard-SQL zu beantworten. Joe Celko SQL-Puzzles und Antworten Zitat: Der letzte Update-Versuch deutet darauf hin, dass wir das Prädikat verwenden können, um eine Abfrage, die uns einen gleitenden Durchschnitt geben würde: Ist die zusätzliche Spalte oder die Abfrage Ansatz besser Die Abfrage ist technisch besser, weil die UPDATE-Ansatz wird Denormalisierung der Datenbank. Wenn jedoch die historischen Daten, die aufgezeichnet werden, sich nicht ändern und die Berechnung des gleitenden Durchschnitts kostspielig ist, könnten Sie die Verwendung des Spaltenansatzes in Erwägung ziehen. SQL Puzzle-Abfrage: mit allen Mitteln einheitlich. Sie werfen nur auf den entsprechenden Gewichtskorb je nach Entfernung vom aktuellen Zeitpunkt. Zum Beispiel quottake Gewicht1 für Datenpunkte innerhalb von 24 Stunden von aktuellen Datenpunkt Gewicht0,5 für Datenpunkte innerhalb von 48hrsquot. In diesem Fall ist es wichtig, wieviel aufeinander folgende Datenpunkte (wie 6:12 Uhr und 11:48 Uhr) voneinander entfernt sind. Ein Anwendungsfall, den ich mir vorstellen kann, wäre ein Versuch, das Histogramm zu glätten, wo Datenpunkte nicht dicht genug sind. Ndash msciwoj Mai 27 15 at 22:22 Im nicht sicher, dass Ihr erwarteten Ergebnis (Ausgang) zeigt klassische einfache bewegen (rolling) Durchschnitt für 3 Tage. Denn zum Beispiel gibt das erste Dreibettzimmer von Zahlen per Definition: aber man erwartet 4.360 und seine Verwirrung. Trotzdem schlage ich die folgende Lösung vor, die die Fensterfunktion AVG verwendet. Dieser Ansatz ist viel effizienter (klarer und weniger ressourcenintensiv) als SELF-JOIN in anderen Antworten eingeführt (und ich bin überrascht, dass niemand eine bessere Lösung gegeben hat). Sie sehen, dass AVG wird mit Fall verpackt, wenn rownum gt p. days dann zu zwingen, NULL s in ersten Zeilen, wo 3 Tage Moving Average ist sinnlos. Beantwortet Joe Celkos dirty linken äußeren Join-Methode (wie zitiert von Diego Scaravaggi) auf die Frage zu beantworten, wie es gefragt wurde. Generiert die angeforderte Ausgabe: answered Jan 9 16 at 0:33 Deine Antwort 2017 Stack Exchange, Inc Berechnen von Werten in einem rollenden Fenster in Transact SQL Dwain Camps Berechnen von Werten in einem Rolling Window in SQL Jedes Mal, wenn Sie Werte über mehrere Zeilen kombinieren müssen SQL, das Problem kann eine Herausforderung sein, vor allem, wenn es um Leistung geht. Wir konzentrieren uns auf das rollende zwölfmonatige Gesamtproblem, aber unsere Methoden können auf jedes Zeitfenster (z. B. 3 Monate) oder auf Durchschnittswerte und andere Aggregationen über diese Zeitfenster angewendet werden. Eine rollende Summe für einen Monat ist die Summe für diesen Monat plus die vorherigen Monate im Zeitfenster oder NULL, wenn Sie die Werte für alle vorherigen Monate innerhalb des Zeitfensters haben. In früheren Versionen von SQL Server mussten Sie durch einige Reifen springen, um eine Methode zu finden, die gut funktioniert, aber SQL 2012 bietet einige neue Features, die es einfacher machen. In beiden Fällen gibt es mehrere gültige Lösungen. Was ist die schnellste und effizienteste We8217ll versuchen, diese Frage in diesem Artikel zu beantworten. Wir werden in SQL 2012 arbeiten. Wenn Sie möchten, folgen Sie, können Sie die Sample Queries. sql-Ressource you8217ll finden Sie beigefügt. Daten-Setup und Erklärung des Business-Problems Häufig finden Sie sich mit vielen Transaktionen innerhalb eines Monats, aber in unserem Fall gehen wir davon aus, you8217ve bereits gruppiert Ihre Transaktionen für jeden Monat. We8217ll weisen unseren PRIMARY KEY einem DATE-Datentyp zu und enthalten einige Werte, über die wir rollende zwölfmonatige Summen akkumulieren möchten. Dies erzeugt auch eine geringfügig andere Abfrage-Plan, so dass wir interessiert sein, um zu sehen, wie seine Performance-Ergebnisse im Vergleich zu anderen Lösungen vorgeschlagen, so weit. So viel zu traditionellen Lösungen, und meine Entschuldigungen, wenn ich zufällig zu einem Ihrer Favoriten übersehen, aber fühlen Sie sich frei, es Code und fügen Sie es auf die Performance-Test-Kabelbaum we8217ll später zu sehen, wie es Tarife. Lösung 5: Verwenden Sie ein schrulliges Update Wenn you8217ve noch nie von der Quirky Update (QU) gehört und wie es auf Probleme wie das Ausführen von Summen angewendet werden kann, empfehle ich Ihnen dringend, lesen Sie diese herausragenden Artikel von SQL MVP Jeff Moden. Berechtigung, die laufenden Gesamt - und Ordinal-Rangprobleme zu lösen. Bevor wir fortfahren, sollten wir beachten, dass es diejenigen gibt, die darauf bestehen, dass die QU-Methode ein undokumentiertes Verhalten von SQL Server darstellt und so nicht vertrauenswürdig ist. Wir können sagen, dass die Syntax eindeutig durch den Eintrag MS Books On Line für die UPDATE-Anweisung für SQL-Versionen 2005, 2008 und 2012 beschrieben wird. Tatsächlich geht es weiter zurück. Ich habe es erfolgreich in SQL Server 2000 verwendet, aber es wurde von Sybase geerbt und war in der ersten SQL Server-Version, die jemals veröffentlicht wurde. Zu den Neinsagen I8217ll sagen, dass das Verhalten 8220undocumented8221 ist zumindest konsistent in allen Versionen und es gibt wahrscheinlich wenig Grund zu vermuten, dass es abgelehnt wird oder ändern in zukünftigen Versionen von MS SQL. Betrachten Sie sich selbst gewarnt Wenn Sie jemals in Erwägung ziehen, eine QU verwenden, um jedes Problem zu lösen, müssen Sie sorgfältig beachten Sie die vielen Regeln, die gelten (auch enthalten in der referenzierten Artikel von Jeff). Die wichtigsten, die in dieser Abfrage behandelt werden, können wie folgt zusammengefasst werden: Die Tabelle muss über einen gruppierten Index verfügen, der die Reihenfolge der Quellzeilen für den Zeitraum angibt, den Sie durchlaufen möchten. Die Tabelle muss eine Spalte haben, in die Sie die aggregierte laufende Summe einfügen können. Wenn Sie die Aktualisierung durchführen, müssen Sie die Tabelle mit dem TABLOCKX-Abfragehinweis sperren, um sicherzustellen, dass niemand sonst in einem INSERT s, DELETE s oder UPDATE s vor you8217re durch bekommt. Sie müssen verhindern, dass SQL versucht, die Abfrage mit dem Hinweis OPTION (MAXDOP 1) zu parallelisieren. Da ein rollierender Zwölfmonatsdurchschnitt einfach eine laufende Summe in Verkleidung ist, können wir eine Spalte zu unserer Tabelle hinzufügen und eine QU-Anfrage anwenden, um unsere Berechnung durchzuführen. Ich muss gestehen, dass dies ein wenig chaotisch aussieht, mit all den Variablen, die Sie brauchen, um DECLARE. Grundsätzlich, was wir tun, ist, den Überblick über die letzten zwölf (lagging) Werte zu halten, um die 12. ein (wo die Rolling12Months Spalte zugeordnet ist) von dem, was sonst ein QU läuft insgesamt zu entfernen, wie in Jeff8217s Artikel beschrieben. Wir haben große Hoffnungen für seine Geschwindigkeit, da es bekannt, dass die schnellste Methode zur Lösung der laufenden Summen Problem ist. Wieder einmal sollten Sie sich davon überzeugen, dass die Ergebnisse im Einklang mit früheren Lösungen stehen und ja diese Lösung immer noch das gleiche in SQL 2012 verhält. Wenn you8217re mit mir so weit, können Sie auch fragen sich 8220what geschieht, wenn ich mehrere Laufzeit zu berechnen Zwölf Monate Summen über verschiedene partitions8221 Dies ist relativ einfach für alle anderen Lösungen vorgestellt, aber vorschlagen, ein bisschen eine Herausforderung mit der QU. Die Antwort dazu finden Sie in der angehängten Ressourcendatei: Quirky Update Partitioned. sql. SQL 2012-Lösungen Bis jetzt wird alles, was wir getan haben, in SQL 2008 funktionieren. Die einzige Sache, die wir getan haben, die nicht in SQL 2005 unterstützt wird, sind die Initialisierungen der Variablen, die wir im QU-Ansatz deklarieren. Nun let8217s sehen, was neue Features SQL 2012 hat, die auf dieses Problem angewendet werden können. Lösung 6: Verwenden eines Fensterrahmens Unsere erste SQL 2012-Lösung (6) zeigt, wie ein Fensterrahmen verwendet wird, der 11 Zeilen vor der aktuellen Zeile beginnt, über die aktuelle Zeile zu SUM unsere gewünschten Ergebnisse. Noch einmal sind die zurückgegebenen Ergebnisse die gleichen, aber der Abfrageplan ist ganz anders als für die vorherige SQL 2012-Lösung aber wir8217re nicht besonders optimistisch, dass dieser Ansatz wird eine vernünftig durchführende Alternative, weil der Anzahl der 8220look-backs8221 benötigt, um es funktionieren . Performance-Vergleich der Methoden Der eigentliche Test, um zu sehen, wie mehrere Lösungen durchführen, ist die tatsächlichen Ausführungszeiten in einem ruhigen Server mit einem Testkabelbaum mit vielen Zeilen zu überprüfen. Unser Testkabelbaum wird zusammen mit den Modifikationen von Solution 1 und 2 angezeigt (siehe Kommentare im Code), um: die Ergebnisse in eine temporäre Tabelle einzufügen, um den Zeitaufwand für die Rückgabe der Zeilen auf die Ergebnisse von SQL Server Management Studio8217 zu vermeiden Gitter. Entfernen Sie die DATE-Arithmetik, da es bei der Erstellung von Multi-Million-Zeilen-Testkabeln schwierig ist, viele einzelne Monate zu generieren, sodass die Spalte Datumstabelle zu einem BIGINT-Datentyp überarbeitet wurde. Für die verbleibenden Lösungen (2 8211 6) haben wir die CPU und die abgelaufene Zeit von 1M mit 4M Zeilen gezeichnet. Interpretation der Ergebnisse vergangen und CPU-Zeiten scheinen über die verschiedenen Methoden in Bezug auf ihre Bestellung konsistent sein. Alle sind linear skaliert. Das schrullige Update, vorausgesetzt, Sie können es verstehen und alle zugehörigen Regeln, scheint die schnellste verfügbare Lösung für die Lösung dieses Problems sein, auch unter Berücksichtigung der neuen Funktionen in SQL 2012. In SQL 2012 ist der Fensterrahmen Ansatz sicherlich ordentlich, Kompakt und elegant, aber leicht Spuren der Quirky Update-Lösung über die Zeilen, die wir getestet haben. Diese Testergebnisse scheinen zu einem früheren Test auf Laufende Summen in SQL 8220Denali8221 CTP3 durch Microsoft Certified Master Wayne Sheffield in seinem Blog entsprechen. Wenn Sie mit einer früheren Version von SQL (2005 oder 2008) stecken, und aus irgendeinem Grund können Sie sich mit einem Quirky Update (z. B. wenn Sie dieses undokumentierte Verhalten nicht vertrauen), sind die schnellsten Lösungen, die Ihnen zur Verfügung stehen, entweder die CROSS APPLY TOP oder Mit einer korrelierten Unterabfrage, da beide von ihnen schien in einer engen Bindung an der ganzen Linie. Es scheint, dass das 8220traditional8221 INNER JOIN etwas zu vermeiden ist. Es wird wahrscheinlich nur noch schlimmer, wenn Sie Datum Arithmetik innerhalb der JOIN8217s ON-Klausel tun müssen. Ebenso, mit einem Tally-Tabelle oder mehrere LAGs (SQL 2012) war sicherlich nicht der Weg zu gehen. Wir haben nicht CURSOR-basierte Lösungen erforscht, aber Sie können zurück zu dem Artikel, der auf laufenden Summen verwiesen wird, zurückkehren, um eine Vorstellung davon zu erhalten, wie sie in diesem Fall durchführen können. I8217ve sahen auch einige Lösungen, die eine rekursive Common Table Expression (rCTE) verwenden, aber ich sicherlich wouldn8217t auf ihre Leistung im Vergleich zu den QU-oder Fensterrahmen-Lösungen wetten. Es gibt viele Möglichkeiten, um Werte innerhalb eines rollenden Fensters in SQL zu berechnen und es gibt einige klare Leistung Gewinner unter ihnen. Wir hoffen, dass Sie diesen Leitfaden für die verfügbaren Methoden interessant und informativ gefunden haben. Gesamt: 31 Durchschnitt: 4.65 Dwain Camps ist seit vielen Jahren Projektleiter. Weil Leistung von Anwendungen ein kritischer Erfolgsfaktor für Projekte sein kann, hat er Evangelisierung auf der Notwendigkeit, leistungsstarke SQL zu entwickeln. Durch Mentoring und Authoring Artikel auf SQL, hofft er, eine zukünftige Generation von Software-Ingenieure auf die richtige und falsche Wege, um SQL-Code liefern zu trainieren. Er hat auch ein besonderes Interesse an der Entwicklung von Lösungen für komplexe, datenintensive Probleme mit Hochleistungs-SQL, weil die deklarativen Charakter von SQL ermöglicht die Entwicklung von algorithmisch einzigartigen Lösungen, die Verfahrenssprachen nicht in der Lage sein. Folgen Sie Dwain auf Twitter Sehr gut Großer Artikel Ich war überrascht, dass LAG () so schlecht. Ich denke, jeder Aufruf erfolgt separat, anstatt ausgewertet und optimiert wie ein Fenster. Große Erklärung Ich stimme zu, das ist eine große Erklärung der verschiedenen Möglichkeiten, um Werte in einem rollenden Fenster zu berechnen. Wenn Sie diese Beispiele auf SQL 2012 testen, müssen Sie MyTable mit RollingTotalsExample ändern. Vielen Dank, Mr. Camps Tally-Methode Hallo Dwain, bemerkte ich, dass Ihre Tally-Tabelle Abfrage verursachte einen Tabellenspool-Operator und dachte, Sie könnten in Erwägung ziehen, die Tally Teil einer Dates-Tabelle wie folgt: SELECT GroupingDate, ValueMAX (CASE GroupingDate WHEN Datum THEN a. Value END), Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDER BY GroupingDate) lt 12 DANN NULL ELSE SUM (Wert) END INTO ErgebnisseSoln2 FROM RollingTotalsBeispiel ein CROSS APPLY (mdash Entfernen Sie die DATE arithmetischen Werte (Datum), (Date1) (Datum2), (Datum3), (Datum4), (Datum5), (Datum6), (Datum7), (Datum8), (Datum9), (Datum10), (Datum11) c (GroupingDate) GROUP BY GroupingDate HAVING GroupingDate lt MAX (Datum) ORDER BY GroupingDate (Entschuldigt, wenn Formatierung ist schlecht ndash keine Vorschau) Diese Änderung noch wouldnrsquot machen es ein Kandidat, aber macht eine massive Verbesserung, dass queryhellip Danke für die Kommentare Danke Joe und Nic. Irsquom froh, dass Sie den Artikel interessant fanden. Joe: Auch ich war überrascht von den Ergebnissen der LAG und es macht mich fragen, was der Break-even-Punkt wäre. Vielleicht 3 Monate vielleicht nicht so schlimm, aber es ist immer noch schwer zu glauben, es könnte schneller als die QU. Tally Tables MM: Aus irgendeinem Grund habe ich eine persönliche Vorliebe für Inline-Tally-Tabellen, aber Ihre Ergebnisse sind interessant, wenn nur für andere Fälle zu berücksichtigen. Assistance mit Moving Annual Total Mein erster Beitrag. Ich muss die Moving Annual Summe für den obigen Wert für die vorangegangenen 12 Monate berechnen, wobei dieser Monat Monat 12 ist. Ich muss dann die Moving Annual Summe für die 12 Monate davor erhalten. Mit der Idee zu vergleichen MAT für diesen Monat mit dem entsprechenden Monat im letzten Jahr, und für jeden vorangegangenen Monat. Mein Versuch hat mir Folgendes gegeben: SELECT rNum ROWNUMBER () Over (Reihenfolge nach Datum) Datum Rolling12MonthsCASE WHEN ROWNUMBER () OVER (ORDER BY Date) gt 11 THEN SUM (Value) OVER (ORDER BY Date ROWS BETWEEN 11 VORWÄRTS - UND STROMREIHE) ENDE FROM RollingTotalsExample) Wählen Sie Von cte, (Wählen Sie mRNum max (rNum) aus cte) deMax Wo rNum zwischen mRNum ndash 23 und mRNum Mit der Möglichkeit, die Were-Anweisung zu ändern, um zu reflektieren, ob ich dieses Jahr oder die vorheriges Jahr. Meine echten Daten hat das Datum wie in Integer 201409, die ich glaube, wird das Leben für mich leichter machen, wie ich subtrahieren kann 100, um das Vorjahr zu bekommen. Ausgezeichneter Artikel und jede mögliche Hilfe würde geschätzt. Dies ist meine Arbeitslösung (mit einigen Rauschen) mdash Rolling 12 Monate Summen mit SQL 2012 und ein Fensterrahmen IF OBJECTID (lsquotempdb..PreviousYearrsquo) IST NICHT NULL DROP TABELLE PreviousYear Mit cte as (SELECT rNum ROWNUMBER () Über (Reihenfolge nach Datum (ORDER BY Date) gt 11 DANN SUM (Wert) OVER (ORDER BY Datum ROWS ZWISCHEN 11 VORWÄRTS - UND STROMREIHE) END FROM RollingTotalsBeispiel) Wählen Sie pyRowNum ROWNUMBER () Over (order by mRNum ). . SStart mRNum ndash 24. eend MRNUM ndash 12 in previousYear Von CTE (Select MRNUM max (rnum) von CTE) DeMax Wo rnum zwischen MRNUM ndash 23 und MRNUM ndash 12 mdash Rollen 12 Monate Summen mit SQL 2012 und einen Fensterrahmen IF OBJECTID (lsquotempdb..ThisYearrsquo) IS NOT NULL DROP TABLE ThisYear CTE als (rnum ROWNUMBER () Über (Reihenfolge nach Datum SELECT). Datum. Wert. Rolling12MonthsCASE WENN ROWNUMBER () OVER (ORDER BY Datum) gt 11 THEN SUM (Wert) OVER (ORDER BY Datum ROWS ZWISCHEN 11 PRECEDING und CURRENT ROW) END FROM RollingTotalsExample) Wählen Sie tyRowNum ROWNUMBER () Over (order by mRNum). . SStart mRNum ndash 11. eend MRNUM in ThisYear Von CTE (Select MRNUM max (rnum) von CTE) DeMax Wo rnum zwischen MRNUM ndash 11 und MRNUM Wählen Sie aus ThisYear ty Links previousYear py auf ty. tyRowNum py. pyRowNum Join Diese können Irsquom arbeiten nicht in der Nähe eines comp mit Sql-Zugriff jetzt, so dass ich canrsquot Test es (es kann einige typossyntax Fehler). SELECT T. DateKey, AVG (T. ValueField) OVER (ODER VON T. DateKey ASC ZWISCHEN 365 PRECEDING AND AND CURRENT ROW) AS YMAValueField VON Table AS T ORDER BY T. DateKey ASC Falls AVG eine der Aggregatfunktionen nicht unterstützt Mit BETWEEN Bereich (Ich weiß, SUM unterstützt wird). SELECT T. DateKey, SUM (T. ValueField) OVER (ODER VON T. DateKey ASC ZWISCHEN 365 PRECEDING AND AND CURRENT ROW) CASE WHEN DATEDIFF (Tag, Startdatum, T. DateKey) lt 365 THEN DATEDIFF (Tag, Startdatum, T. DateKey) ELSE 365 END AS YMAValueField VON Table AS T ORDER BY T. DateKey ASC Einfache Diskussion Profis Melden über 200.000 Microsoft Join, und erhalten vollen, freien Zugang zu technischen Artikeln, unsere zweimal monatlich Einfache Diskussion Newsletter und kostenlose SQL-Tools. Besuchen Sie unsere Bibliothek mit Artikeln, um die Muster und Praktiken zu entdecken, die Sie benötigen, um zu agilen Methoden der Datenbanklieferung zu gelangen. Erfahren Sie, wie Sie den Prozess der Erstellung, Prüfung und Bereitstellung Ihrer Datenbankänderungen automatisieren, um das Risiko zu reduzieren und den Lieferzyklus zu beschleunigen. Top bewertete aktuelle Artikel in T-SQL-Programmierung Verwandte Artikel Auch in SQL Mit dem Aufstieg von NoSQL-Datenbanken, die Aspekte der SQL für die Abfrage ausnutzen und umfassende Transaktionalität umfassen, besteht die Gefahr, dass die Daten-Dokument-Modelle hierarchische Natur eine grundlegende verursachen Konflikt mit relationaler Theorie Wir forderten unseren relationalen Experten, Hugh Bin-Haad, einen schwierigen Bereich für die Datenbank theorists. hellip Read more Auch in SQL Server Jeder SQL Server Database-Programmierer muss mit den Systemfunktionen vertraut sein. Diese reichen von der sublime (wie Zeilenzahl oder Identität), um die lächerliche (IsNumeric ()) Robert Sheldon bietet einen Überblick über die am häufigsten von ihnen verwendet. hellip Lesen Sie mehr Auch in T-SQL-Programmierung Um in vollem Umfang nutzen zu können Im Systemkatalog, um mehr über eine Datenbank zu erfahren, müssen Sie mit den Metadaten-Funktionen vertraut sein. Sie sparen viel Zeit und Tippen beim Abfragen der Metadaten. Sobald Sie den Hang dieser Funktionen erhalten, ist der Systemkatalog plötzlich einfach zu bedienen, wie Robert Sheldon in diesem Artikel demonstriert. hellip Lesen Sie mehr Auch in T-SQL-Programmierung Manchmal können Sie eine unangenehme Frage über Daten, die nicht ganz passen Mit dem SQL-Denken. Obwohl Fenster-Funktionen haben SQL mehr vielseitig, gibt es Zeiten, wenn Sie für Ihr Lieblingsbuch von Algorithmen, um Ihre Art und Weise um eine ungewöhnliche Berichterstattung taskhellip Code mehr lesen Kopie 2005 - 2017 Red Gate Software Ltd Was halten Sie von der neuen Simple denken Sprechen Sie uns Ihre Meinung