Es gab eine schöne Frage auf OTN heute darüber, ob es eine Standard-Oracle-Funktion, um die exponentiellen gleitenden Durchschnitt zu berechnen. Die Antwort ist, dass es keine solche Funktion, aber mit der Modell-Klausel, können Sie es sehr einfach zu berechnen. Und seine ein großes Beispiel dessen, was ich meine mit variablen Anzahl von Berechnungen auf der Grundlage berechneter Werte, geschrieben in meinem dritten Teil des Modells Klausel Tutorial. Vor heute, ich didnt sogar wissen, was ein exponentieller gleitender Durchschnitt genau war. Mehr dazu findet ihr hier auf Wikipedia oder hier mit einem schönen Beispiel. Aus dem ersten Link: Ein exponentieller gleitender Durchschnitt (EMA) wendet Gewichtungsfaktoren an, die exponentiell abnehmen. Die Gewichtung für jeden älteren Datenpunkt nimmt exponentiell ab, was den neueren Beobachtungen viel mehr Bedeutung verleiht, während ältere Beobachtungen nicht vollständig vernachlässigt werden. Aus dem zweiten Link: Die Formel für die Berechnung eines Exponential Moving Average (EMA) ist: X Aktueller EMA (dh zu berechnender EMA) C Aktueller Originaldatenwert K Glättung Konstante P Vorherige EMA (Die erste EMA im berechneten Bereich ist K Smoothing Constant 2 (1 n) Auf diese Formel folgt ein Beispiel, das ich ein Bit erweitert habe, und zwar mit dieser Tabelle: Die Datensätze von Produkt A Ich habe die Zahlen aus Produkt B. Hier ist die Modell-Klausel Abfrage, die die Formel implementiert. Beachten Sie, wie die Formel direkt in die einzige Regel der Modell-Klausel zu übersetzen. Die Glättungskonstante K ist gesetzt Auf 5, basierend auf einem Fenster von Werten (n) gleich 3. Challenge: versuchen Sie dies ohne die Modell-Klausel und sehen, ob Sie kommen können mit etwas umfangreicher. 5 Kommentare: 11.2 Funktionen in Verwendung mit dat as (select 39A39 product Datum 392009-01-0139 Monat, 10 Betrag aus Dual Union alle auswählen 39A39, Datum 392009-02-0139, 15 aus Dual Union alle auswählen 39A39, Datum 392009-03-0139, 17 aus Dual Union alle wählen 39A39, Datum 392009 -04-0139, 20 aus Dual Union alle auswählen 39A39, Datum 392009-05-0139, 22 aus Dual Union alle auswählen 39A39, Datum 392009-06-0139, 20 aus Dual Union alle auswählen 39A39, Datum 392009-07-0139, 25 aus Dual Union alle auswählen 39A39, Datum 392009-08-0139, 27 aus Dual Union alle auswählen 39A39, Datum 392009-09-0139, 30 aus Dual Union alle auswählen 39A39, Datum 392009-10-0139, 35 aus Dual Union alle Wählen Sie 39A39, Datum 392009-11-0139, 37 aus Dual Union alle auswählen 39A39, Datum 392009-12-0139, 40 aus Dual Union alle 39B39 wählen, Datum 392009-01-0139, 0 aus Dual Union alle wählen 39B39, Datum 392009 -02-0139, 50 aus Dual Union alle auswählen 39B39, Datum 392009-03-0139, 10 aus Dual Union alle auswählen 39B39, Datum 392009-04-0139, 40 aus Dual Union alle auswählen 39B39, Datum 392009-05-0139, 15 aus Dual Union alle auswählen 39B39, Datum 392009-06-0139, 35 aus Dual Union alle auswählen 39B39, Datum 392009-07-0139, 30 aus Dual Union alle wählen 39B39, Datum 392009-08-0139, 30 aus Dual Union alle Auswählen 39B39, Datum 392009-09-0139, 20 aus Dual Union alle auswählen 39B39, Datum 392009-10-0139, 20 aus Dual Union alle 39B39 auswählen, Datum 392009-11-0139, 20 aus Dual Union alle wählen 39B39, Datum 392009 -12-0139, 20 von dual), rns as (select dat. . Rownumber () over (Partition nach Produkt Reihenfolge nach Monat) rn -. 2 (1count () über (Teilung durch Produkt)) k. 0.5 k von dat), res (Produkt, Monat, Betrag, rn, x) als (wählen Sie r. product, r. month, r. amount, r. rn, r. amount x aus rns r, wobei rn 1 union alle auswählen (Ns. amount - es. x) es. xx von rns ns, res es, wo ns. rn es. rn 1 und ns. product es Produkt, Monat, Betrag, rn, Runde (x, 3) EMA aus res Reihenfolge nach Produkt, Monat nach der Berechnung der geschlossenen Form Ich kam mit dem folgenden Code, dass mehr wie eine Verschleierung als alles umfassende. Die Idee ist, laufende Multiples mit einer Stringverkettung und der xml-eval-Funktionalität zu erstellen. Die geschlossenen Formen der Sonderfälle brauchen nur laufende Summen. Es gibt einen allgemeinen Fall und zwei spezielle Fälle, die viel einfacher sind: mit t1 als (Produkt, Monat, Betrag, Menge ci, rownumber () über (Teilung durch Produkt Reihenfolge nach Monat) rn, --2 (1 rownumber () über (Produktauswahl, Monat, Betrag, (Fall bei rn 1 und 1 sonst ki end ci) ai, Fall bei rn 1 und 1 sonst (1 - Ki) ende bi von t1), t3 als (SELECT-Produkt, MONTH, Menge, ai, xmlquery (REPLACE (wmconcat (bi) über (PARTITION BY Produkt ORDER BY MONTH Zeilen ZWISCHEN unbeschränkte vorhergehende AND CURRENT ROW), 39,39, 3939 ) RETURNING-Inhalt).getnumberval () mi FROM t2), t4 as (Produkt, Monat, Betrag, mi, (ai mi) xi aus t3 auswählen) Produkt, MONTH, Menge, Runde (mi SUM (xi) over (PARTITION BY Produkt ORDER BY MONTH Zeilen ZWISCHEN unbeschränkte vorhergehende AND CURRENT ROW), 3) ema FROM t4 Spezialfall K 0.5: mit t1 als (Produkt, Monat, Betrag, Rownumber () auswählen (Partition nach Produkt Reihenfolge pro Monat) rn, Betrag (1, 0), 1)) ci vom Verkauf) Produkt, Monat, Betrag, Runde (sum (ci) über (Partition von (2, rn), 3) ema von t1 Sonderfall K 2 (1 i): mit t1 als (Produkt, Monat, Betrag, Rownumber () wählen (Partition Nach Produkt sortiert nach Monat) rn, Menge rownumber () über (Teilung nach Produkt Sortierung nach Monat) ci vom Verkauf) Produkt, Monat, Betrag, Umlauf (Summe (ci) Und die aktuelle Zeile) 2 (rn (rn 1)), 3) ema von t1 I39ll den Beweis der geschlossenen Form, wenn irgendjemand daran interessiert ist. Dies ist ein großes Beispiel für quotfun mit SQLquot :-) Eine Kombination von XMLQuery, die undokumentierte wmconcat und analytische Funktionen mit der windowing-Klausel. Ich mag das. Obwohl es nicht so umfassend ist wie die Modellklauselvariante und die Rafu39s rekursiv mit einem, wie Sie selbst sagten. Und sicher sehen wir den Beweis der geschlossenen Gestalt. Ich habe eine Frage gestellt: wie man die Glättungskonstante SELECT k - Glättungskonstante optimiert. Ms - mittlerer quadratischer Fehler FROM (SELECT FROM sales MODEL DIMENSION BY (Produkt: ROWNUMBER () OVER (PARTITION BY Produkt ORDER BY Monat ASC) rn) MASSNAHMEN (Betrag - Verkaufsmenge Monat Monat AS S - quadrierter Fehler - - Arbeitszeile und Attribute - a) Arbeitszeile ist Produkt 39X39, rn 1 - b) Arbeitsattribute sind wie folgt:. 0 AS SSE - Summe SE für alle ProdukteMonate. 0 AS MSE - bedeuten SSE für alle ProdukteMonate. 0 AS k - für alle ProdukteMonate. 0 AS PreMSE - vor k39s MSE für alle productsmonths. 0 AS diff - zwischen aktuellem MSE und vorherigen. 0,1 AS Delta - Anfangsschritt. 0 AS priorpt - anfänglicher Startpunkt -) RULES ITERATE (99) UNTIL (abs (diff39A39,1) lt 0,00010) (Cany, rn amountcv (), cv () K39A39,1 priorpt39A39,1 delta39A39,1 Xany , Rn ORDER BY Produkt, rn ASC COALESCE (K39A39,1 Ccv (), cv () (1-K39A39,1) Xcv (), cv () - 1, Ccv (), cv ()).Produkt, rn Xcv (), Cv () - 1. SEproduct, rn POWER (Ccv (), cv () - Xcv (), cv () - 1, 2) SSE39A39,1 SUM (SE) beliebig (SE) beliebige 24. diff39A39,1 CASE-Iterationsnummer WHEN 0 dann NULL ELSE preMSE39A39,1 MSE39A39,1 delta39A39,1 CASE WHEN diff39A39,1 lt 0 THEN - abs (delta39A39, 12) ELSE abs (delta39A39,1) END. Priorpt39A39,1 K39A39,1)), wobei das Produkt 39A39 und rn 1 K MSE ---------- ---------- .599999237 174.016094 Ihre Gruppe durch ist, was Ihre durchschnittlichen aggregiert, und es ist Gruppierung durch die ganze Tabelle (Ich gehe davon aus, dass Sie dies getan haben, um die Auswahl für alles erlauben) Gerade bewegen Sie Ihre avg in eine andere Unterabfrage, entfernen Sie die übergeordnete Gruppe durch und das sollte es lösen. Wenn Sie die grundlegende SELECT AVG (Kosten) - Anweisung ausführen, wird sie natürlich durch die angegebene Spalte (Kosten in diesem Fall) so gruppiert, wie das ist, was Sie anfordern. Ich würde vorschlagen, lesen Sie mehr auf GROUP BY und Aggregate, um ein besseres Verständnis für das Konzept. Das sollte Ihnen helfen, mehr als nur eine einfache Lösung. Die Antwort unten ist eigentlich von Davids Antwort. Es nutzt die analytischen Funktionen. Grundsätzlich ist, was passiert, dass bei jedem AVG-Aufruf, sagen Sie dem Motor, was für die Funktion (in diesem Fall nichts) zu verwenden. Eine anständige Schreiben auf analytische Funktionen finden Sie hier und hier und mehr mit einem Google auf die Angelegenheit. Allerdings, wenn Ihre SQL-Engine für Variablen erlaubt, können Sie genauso einfach die folgende Antwort. Ich bevorzuge diese für zukünftige Wartbarkeit. Der Grund ist, dass eine Variable mit einem guten Namen kann sehr beschreibend für zukünftige Leser des Codes, im Vergleich zu einer analytischen Funktion, die ein wenig mehr Arbeit zu lesen braucht (vor allem, wenn Sie nicht verstehen, die über-Funktion). Auch diese Lösung dupliziert die gleiche Abfrage zweimal, so könnte es sich lohnt, speichern Sie Ihren Durchschnitt in einer SQL-Variable. Dann können Sie Ihre Aussage ändern, um einfach diesen globalen Durchschnitt verwenden Dies sind Variablen in SQL-Server (Sie müssen es für Ihre eigene Instanz von SQL anpassen) Diese Lösung wird viel sauberer zu den zukünftigen Lesern Ihrer SQL zu lesen, zu I39m ziemlich Sicher David39s Abfrage ist mindestens so effizient und erfordert nicht die Verwendung eines PLSQL-Blocks, die Sie das Ergebnis der SELECT in Variablen setzen müssen - es wird nicht angezeigt werden, wie Sie es schrieb (eigentlich denke ich, es wouldn39t sogar laufen) . Außerdem wird die doppelte Abfrage nur einmal von Oracle ausgeführt. I39m ziemlich sicher, dass der Optimierer ist intelligent genug, um das zu erkennen. Ndash ahorsewithnoname Ich schätze Ihre Erklärung, aber die PLSQL-Block ist einfach falsch. Dieser Ansatz wird nie in Oracle funktionieren. Wenn Sie die avg () - Abfrage nicht wiederholen möchten, verwenden Sie den Ansatz mit der analytischen Funktion oder der Cross-Join-Lösung. Ndash ahorsewithnonameMit einem einfachen gleitenden Durchschnitt, um Daten zu glätten ist eine ziemlich beliebte Technik. Es ist zu schlecht das primäre Beispiel in der SQL Anywhere-Hilfe ist weit von einfach: Was macht dieses Beispiel so komplex Neben der Problem-Anweisung, das heißt: Berechnen Sie den gleitenden Durchschnitt aller Produktverkäufe, nach Monat, im Jahr 2000. Heres, was macht Es komplex: zwei Verweise auf die AVG () - Funktion, eine GROUP BY (die alle von sich selbst macht fast jede SELECT ein Kopf-Scratcher),. Eine Stealth-WINDOW-Klausel eine WINDOW-Klausel, die nicht sogar das WINDOW-Schlüsselwort verwendet. So dass die Uneingeweihte (die Leute, die Beispiele brauchen mehr als jeder andere) ist es nicht offensichtlich, dass ein WINDOW beteiligt ist überhaupt. Nicht nur eine WINDOW-Klausel, denken Sie daran, aber eine, die jede einzelne Komponente enthält, können Sie in einem WINDOW: eine PARTITION BY, eine RANGE-Klausel. Nicht eine einfache ROWS-Klausel, sondern eine vollwertige RANGE-Klausel, die eine intime Beziehung mit dem ORDER BY hat. Ich weiß, was eine Zeile ist, aber was die redacted ist ein RANGE Aber warten, theres mehr: Die Auswahl von RANGE über ROWS in diesem Beispiel ist entscheidend für die korrekte Operation der Abfrage. (Für eine ausführlichere Erörterung dieses speziellen Beispiels siehe Beispiel 23 - Berechnen eines bewegten Durchschnitts in Glenn Paulleys exzellentem OLAP-Weißbuch.) Jetzt können wir wieder auf den richtigen Weg zurückkehren: Ein wirklich einfacher gleitender Durchschnitt Das folgende Beispiel zeigt einen Wert von 10 Tagen an Daten zusammen mit dem gleitenden Durchschnitt von heutigem Wert und gestern: Die WINDOW-Klausel auf den Zeilen 21 bis 23 definiert ein sich bewegendes Fenster, das zwei Zeilen enthält: die heutige Zeile (CURRENT ROW) und die gestern Zeile (1 PRECEDING): die WINDOW ORDER BY-Klausel bestimmt was PRECEDING-Mittel (die vorhergehende Zeile durch t. entrydate) und die ROWS-Klausel bestimmt die Größe des Fensters (immer zwei Zeilen). Der Ausdruck AVG (t. value) OVER twodays auf Zeile 19 bezieht sich auf die WINDOW-Klausel nach Namen und weist SQL Anywhere an, den Durchschnitt der beiden Werte von t. value zu berechnen, die in dem 2-reihigen Schiebefenster vorhanden sind Zeile in der Ergebnismenge. Also, für 2012-02-02 der Durchschnitt von 10 und 20 ist 15.000000, für 2012-02-03 der Durchschnitt von 20 und 10 ist 15.000000, für 2012-02-04 der Durchschnitt von 10 und 30 ist 20.000000, für 2012- 02-10 der Durchschnitt von 10 und 60 ist 35.000000. Hoppla, was ist mit der ersten Zeile Die 2012-02-01 Zeile hat nicht eine PRECEDING Zeile, also was ist der Durchschnitt über das sich bewegende Fenster Laut Glenn Paulleys Weißbuch im Falle eines sich bewegenden Fensters, wird davon ausgegangen, dass Zeilen mit Null Werte existieren vor der ersten Zeile und nach der letzten Zeile in der Eingabe. Das bedeutet, wenn das sich bewegende Fenster 2012-02-01 als CURRENT ROW hat, enthält die Zeile 1 PRECEDING NULL-Werte. Und wenn SQL Anywhere ein AVG () berechnet, das einen NULL-Wert enthält, zählt es den NULL überhaupt nicht. Nicht im Zähler oder im Nenner bei der Berechnung des Durchschnitts. Heres Beweis: Thats, warum twodayaverage 10.000000 für die erste Reihe 2012-02-01. Geschrieben von Breck Carter um 15:47 Uhr
Comments
Post a Comment