Oracle Durchschnittliche Abfrage

Gleitender Durchschnitt in T-SQL Eine gängige Berechnung in der Trendanalyse ist der gleitende (oder rollende) Durchschnitt. Ein gleitender Durchschnitt ist der Durchschnitt der letzten 10 Zeilen. Der gleitende Durchschnitt zeigt eine glattere Kurve als die tatsächlichen Werte, mehr also mit einer längeren Periode für den gleitenden Durchschnitt, was es zu einem guten Werkzeug für die Trendanalyse macht. Dieser Blogpfosten zeigt, wie man den gleitenden Durchschnitt in T-SQL berechnet. Abhängig von der Version von SQL Server werden unterschiedliche Methoden verwendet. Die nachstehende Tabelle zeigt den Glättungseffekt (rote Linie) mit einem 200 Tage gleitenden Durchschnitt. Die Aktienkurse sind die blaue Linie. Der langfristige Trend ist deutlich sichtbar. T-SQL Moving Avergage 200 Tage Die folgende Demonstration benötigt die TAdb-Datenbank, die mit dem hier befindlichen Skript erstellt werden kann. Im nächsten Beispiel wird ein gleitender Durchschnitt für die letzten 20 Tage berechnet. Abhängig von der Version von SQL Server gibt es eine andere Methode, um die Berechnung durchzuführen. Und, wie wir später sehen werden, haben die neueren Versionen von SQL Server Funktionen, die eine viel effektivere Berechnung ermöglichen. SQL Server 2012 und höher Moving Average Diese Version verwendet eine aggregierte Fensterfunktion. Was ist neu in SQL 2012 ist die Möglichkeit, die Größe des Fensters zu beschränken, indem Sie angeben, wie viele Zeilen vor dem Fenster enthalten sollten: Zeilen vorangegangen ist 19, weil wir die aktuelle Zeile auch in die Berechnung enthalten. Wie Sie sehen können, ist die Berechnung der gleitenden Durchschnitt in SQL Server 2012 ziemlich einfach. Die Abbildung unten zeigt das Fensterprinzip. Die aktuelle Zeile ist mit gelb markiert. Das Fenster ist blau markiert. Der gleitende Durchschnitt ist einfach der Durchschnitt von QuoteClose in den blauen Linien: T-SQL Moving Average Fenster. Die Ergebnisse der Berechnungen in älteren Versionen von SQL Server sind identisch, so dass sie nicht erneut angezeigt werden. SQL Server 2005 8211 2008R2 Moving Average Diese Version verwendet einen gemeinsamen Tabellenausdruck. Der CTE wird selbst referenziert, um die letzten 20 Zeilen für jede Zeile zu erhalten: Moving Average vor SQL Server 2005 Die pre 2005-Version wird eine linke äußere Verknüpfung zu der gleichen Tabelle verwenden, um die letzten 20 Zeilen zu erhalten. Die äußere Tabelle kann gesagt werden, um das Fenster, das wir wollen, um einen Durchschnitt zu berechnen: Leistungsvergleich Wenn wir die drei verschiedenen Methoden gleichzeitig ausführen und überprüfen Sie die resultierende Ausführung Plan gibt es einen dramatischen Leistungsunterschied zwischen den Methoden: Vergleich von drei Verschiedene Methoden, um den gleitenden Durchschnitt zu berechnen Wie Sie sehen können, macht die Verbesserung der Fensterfunktion in SQL 2012 einen großen Unterschied in der Leistung. Verwendung Wie zu Beginn dieses Beitrags erwähnt, sind gleitende Durchschnitte als Werkzeug zur Veranschaulichung Trends verwendet. Ein gemeinsamer Ansatz ist gleitende Durchschnitte mit unterschiedlichen Längen zu kombinieren, um jeweils Änderungen in der kurz-, mittel - und langfristige Trends zu erkennen. Von besonderem Interesse sind die Übergänge der Trendlinien. Zum Beispiel, wenn der kurze Trend über die lange oder mittlere Trend bewegt, kann dies als Kaufsignal in der technischen Analyse interpretiert werden. Und wenn der kurze Trend unter einer längeren Trendlinie bewegt, kann dies als Verkaufssignal interpretiert werden. Die folgende Tabelle zeigt Quotes, Ma20, Ma50 und Ma200. T-SQL Ma20, Ma50, Ma200 kaufen und verkaufen Signale. Dieser Blog-Beitrag ist Teil einer Serie über technische Analyse, TA, in SQL Server. Siehe die anderen Beiträge hier. Geschrieben von Tomas LindHow eine SQL zu ermitteln, ohne einen Cursor aktualisieren Moving Average: Wenn Sie mit den neuesten Versionen von SQL Server arbeiten, können Sie die Windowing-Funktionen nutzen zu können, das Gleiche zu erreichen. Ich habe den aktualisierten Code am Ende der Post. Für dieses Video, Ich mag immer noch den Gedanken Prozess der Verankerung zu einem Datum. Video: 3-Tage-Moving-Average in SQL Eine effiziente Methode, um einen gleitenden Durchschnitt in SQL mit Hilfe einiger Tricks zu berechnen, um Datum-Anker festzulegen. Es gibt Debatten über den besten Weg, um einen SQL Moving Average in SQL Server zu tun. Einige Leute denken, es gibt Zeiten, wenn ein Cursor am effizientesten ist. Andere denken, dass Sie alles in einer Set-basierte Weise ohne den Cursor tun können. Neulich wollte ich einen gleitenden Durchschnitt berechnen und mein erster Gedanke war, einen Cursor zu benutzen. Ich habe einige schnelle Forschung und fand dieses Forum Frage: Moving Average in TSQL Es gibt einen Beitrag, der eine Unterabfrage mit einem Anker Datum, um zu finden, die 1 und 2-Tage-Offset zeigt. Hier ist das Skript, das Sie verwenden können, um die 3 Tage SQL Moving Average Endresultat zu testen. Hier ist die abschließende Frage. Hier ist die Abfrage, die Sie mit SQL Server 2012 verwenden würden. Share this: Drei kleine Hive-UDFs: Teil 3 Von dan. mcclary am 7. April 2013 Einleitung In der letzten Tranche in unserer Serie auf Hive-UDFs, ging es um die am wenigsten intuitiv anzugehen Der drei Typen: die benutzerdefinierte Aggregationsfunktion. Während sie anspruchsvoll zu implementieren sind, sind UDAFs notwendig, wenn wir Funktionen wünschen, für die die Unterscheidung von kartenseitigen v-reduzierenden Seitenoperationen für den Benutzer undurchsichtig ist. Wenn ein Benutzer eine Abfrage schreibt, würden die meisten es vorziehen, sich auf die Daten zu konzentrieren, die sie zu berechnen versuchen, nicht welcher Teil des Plans eine bestimmte Funktion ausführt. Das UDAF bietet auch eine wertvolle Gelegenheit, einige der Nuancen der verteilten Programmierung und der parallelen Datenbankoperationen zu berücksichtigen. Da jede Task in einem MapReduce-Job in einem Bit eines Vakuums arbeitet (z. B. Map Task A weiß nicht, welche Daten Map Task B hat), muss ein UDAF explizit mehr Betriebszustände berücksichtigen als ein einfaches UDF. Nun wieder auf die Vorstellung einer einfachen Moving Average-Funktion. Aber fragen Sie sich: Wie berechnen wir einen gleitenden Durchschnitt, wenn wir nicht haben Zustand oder Ordnung um die Daten Wie zuvor ist der Code auf Github verfügbar. Aber gut auszugleichen die wichtigen Teile hier. Präfix Summe: Gleitender Durchschnitt ohne Zustand Um einen gleitenden Durchschnitt ohne Zustand zu berechnen, benötigte man einen speziellen parallelen Algorithmus. Für den gleitenden Durchschnitt ist der Quottrickquot eine Präfix-Summe zu verwenden. Effektiv halten eine Tabelle der laufenden Summen für die schnelle Berechnung (und Neuberechnung) unserer gleitenden Durchschnitt. Eine vollständige Diskussion der Präfix-Summen für bewegte Durchschnitte ist jenseits der Länge eines Blog-Post, aber John Jenq bietet eine ausgezeichnete Diskussion über die Technik, wie auf CUDA-Implementierungen angewendet. Was gut decken hier ist die notwendige Implementierung eines Paares von Klassen zu speichern und zu betreiben auf unsere Präfix Summe Eintrag innerhalb der UDAF. Hier haben wir die Definition unserer gleitenden Mittelklasse und die statische innere Klasse, die als Eingang in unsere Tabelle dient. Was hier wichtig ist, sind einige der Variablen, die wir für jeden Eintrag in der Tabelle definieren: den Zeitindex oder die Periode des Wertes (dessen Reihenfolge), den Wert selbst, die Präfix-Summe, die Untersequenz-Summe und den gleitenden Durchschnitt. Jeder Eintrag in unserer Tabelle erfordert nicht nur den aktuellen Wert, um den gleitenden Durchschnitt zu berechnen, sondern auch die Summe der Einträge in unserem gleitenden Durchschnittsfenster. Es ist das Paar dieser beiden Werte, die Prefix Summe Methoden, ihre Magie zu arbeiten erlaubt. Die obigen sind einfache Initialisierungsroutinen: ein Konstruktor, eine Methode, um die Tabelle zurückzusetzen, und eine boolesche Methode, ob das Objekt eine Präfix-Summentabelle besitzt oder nicht, auf welcher zu arbeiten ist. Von hier aus gibt es 3 wichtige Methoden zu untersuchen: Hinzufügen, Zusammenführen und Serialisieren. Das erste ist intuitiv, da wir Zeilen in Hive scannen, wollen wir sie zu unserer Präfix-Summentabelle hinzufügen. Die zweite sind wegen der partiellen Aggregation wichtig. Wir können nicht sagen, vor der Zeit, wo diese UDAF laufen wird, und teilweise Aggregation erforderlich sein kann. Das heißt, es ist durchaus möglich, dass einige Werte durch den UDAF während einer Karten-Task ausgeführt werden können, aber dann an eine reduzierte Aufgabe weitergegeben werden, um mit anderen Werten kombiniert werden. Die Methode serialize ermöglicht es Hive, die Teilergebnisse von der Kartenseite zur reduzierten Seite zu übergeben. Die Zusammenführungsmethode ermöglicht Reduzierern, die Ergebnisse von Teilaggregationen aus den Kartenaufgaben zu kombinieren. Der erste Teil der Add-Aufgabe ist einfach: Wir fügen das Element zur Liste hinzu und aktualisieren unsere Tabellenpräfix-Summen. In der zweiten Hälfte der add-Funktion berechnen wir unsere gleitenden Mittelwerte auf Basis der Präfix-Summen. Sein hier sehen Sie das Scharnier, auf dem der Algorithmus schwingt: thisEntry. prefixSum - backEntry. prefixSum - dieser Versatz zwischen dem gegenwärtigen Tabelleneintrag und seinem n-ten Vorgänger macht die ganze Arbeit. Die Serialisierungsmethode muss die Ergebnisse unseres Algorithmus verpacken, um an eine andere Instanz des gleichen Algorithmus weiterzugeben, und es muss dies in einem Typ tun, den Hadoop serialisieren kann. Im Falle einer Methode wie Summe wäre dies relativ einfach: Wir müssten die Summe nur bis zu diesem Punkt übergeben. Da wir jedoch nicht sicher sein können, ob diese Instanz unseres Algorithmus alle Werte gesehen oder in der richtigen Reihenfolge gesehen hat, müssen wir tatsächlich die gesamte Tabelle serialisieren. Dazu erstellen wir eine Liste von DoubleWritables. Packen Sie die Fenstergröße an seinem Kopf, und dann jede Periode und Wert. Dies gibt uns eine Struktur, die einfach zu entpacken und verschmelzen mit anderen Listen mit der gleichen Konstruktion. Mischen Ergebnisse ist vielleicht die komplizierteste Sache, die wir behandeln müssen. Zuerst prüfen wir den Fall, in dem es kein Teilergebnis gegeben hat - einfach zurückkehren und fortfahren. Zweitens überprüfen wir, ob diese Instanz von PrefixSumMovingAverage bereits eine Tabelle enthält. Wenn es nicht, können wir einfach auspacken das serialisierte Ergebnis und behandeln sie als unser Fenster. Der dritte Fall ist der nicht-triviale: Wenn diese Instanz eine Tabelle hat und eine serialisierte Tabelle erhält, müssen wir sie zusammenführen. Betrachten Sie eine Aufgabe reduzieren: Da sie Ausgänge aus mehreren Map-Tasks empfängt, muss sie alle zusammenführen, um eine größere Tabelle zu bilden. Somit wird merge mehrmals aufgerufen, um diese Ergebnisse hinzuzufügen und eine größere Zeitreihe wieder zusammenzusetzen. Dieser Teil sollte vertraut aussehen, es ist genau wie die Add-Methode. Jetzt, da wir neue Einträge in unserer Tabelle haben, müssen wir nach Periode sortieren und die gleitenden Durchschnitte neu berechnen. In der Tat ist der Rest der Merge-Methode genau wie die Add-Methode, so könnten wir in Erwägung ziehen, Sortierung und Neuberechnung in einer separaten Methode. Orchestrating Partial Aggregation Weve hat einen cleveren kleinen Algorithmus für die Berechnung der gleitenden Durchschnitt parallel, aber Hive kann nicht alles mit ihm zu tun, wenn wir eine UDAF, die wie unsere Algorithmus zu verstehen versteht. An dieser Stelle müssen wir beginnen, einige echte UDAF-Code schreiben. Nach wie vor erweitern wir eine generische Klasse, in diesem Fall GenericUDAFEvaluator. Wie im Fall eines UDTF erstellen wir ObjectInspectors, um die Typprüfung durchzuführen. Beachten Sie jedoch, dass wir Inspektoren für verschiedene Staaten haben: PARTIAL1, PARTIAL2, COMPLETE und FINAL. Diese entsprechen den verschiedenen Zuständen, in denen unsere UDAF ausgeführt werden kann. Da unsere Serialized Prefix Summe Tabelle nicht die gleiche Eingabetyp wie die Werte unserer Add-Methode dauert, brauchen wir unterschiedliche Typ-Überprüfung für jeden. Hier ist der Anfang unserer überschriebenen Initialisierungsfunktion. Wir überprüfen die Parameter für die beiden Modi PARTIAL1 und COMPLETE. Hier nehmen wir an, dass die Argumente für unsere UDAF die gleichen sind, wie der Benutzer in einer Abfrage übergibt: den Zeitraum, die Eingabe und die Größe des Fensters. Wenn die UDAF-Instanz die Ergebnisse unserer Teilaggregation verbraucht, benötigen wir einen anderen ObjectInspector. Genauer gesagt, diese: Ähnlich wie die UDTF, müssen wir auch Typ-Prüfung auf die Ausgabetypen - aber für die partielle und volle Aggregation. Im Falle der partiellen Aggregation waren die Listen von DoubleWritables: Aber im Falle von FINAL oder COMPLETE, befassten sich die Typen, die an den Hive-Benutzer zurückgegeben werden, also müssen wir eine andere Ausgabe zurückgeben. Würde eine Liste von Strukturen zurückgeben, die den Zeitraum, den gleitenden Durchschnitt und die Residuen enthalten (da sie billig zu berechnen sind). Als nächstes kommen Methoden, um zu steuern, was passiert, wenn eine Map - oder Reduce-Aufgabe mit ihren Daten beendet ist. Im Falle der partiellen Aggregation müssen wir die Daten serialisieren. Im Fall der Vollaggregation müssen wir das Ergebnis für Hive-Benutzer verpacken. Wir müssen auch Anweisungen geben, wie Hive die Ergebnisse der partiellen Aggregation zusammenführen sollte. Zum Glück haben wir dies bereits in unserer PrefixSumMovingAverage-Klasse abgewickelt, so dass wir das einfach anrufen können. Natürlich ist Verschmelzung und Serialisierung ist nicht sehr nützlich, wenn die UDAF hat Logik für iterating über Werte. Die iterate-Methode behandelt diese und - wie man erwarten würde - ganz auf die PrefixSumMovingAverage-Klasse, die wir erstellt haben. Aggregation Puffer: Verbinden von Algorithmen mit Execution Man könnte feststellen, dass der Code für unsere UDAF ein Objekt vom Typ AggregationBuffer ziemlich viel referenziert. Dies liegt daran, dass der AggregationBuffer die Schnittstelle ist, die es uns ermöglicht, unsere benutzerdefinierte PrefixSumMovingAverage-Klasse mit dem Hives-Ausführungsrahmen zu verbinden. Während es doesnt sehr viel Code, sein Leim, der unsere Logik zu Hives Ausführung Framework bindet. Wir implementieren es als solches: Verwendung der UDAF Das Ziel eines guten UDAF ist, dass, egal wie kompliziert es war für uns zu implementieren, seine, dass es einfach für unsere Benutzer. Für alle, die Code und Parallele Denken, Nutzung der UDAF ist sehr einfach: Hier wurden die Anwendung der UDAF, um die gleitende Durchschnitt der Ankunft Verzögerung von einem bestimmten Flug zu bekommen. Es ist eine wirklich einfache Abfrage für all die Arbeit, die wir darunter getan haben. Wir können ein bisschen mehr und nutzen Hives Fähigkeiten, um komplexe Typen als Spalten behandeln, heres eine Abfrage, die eine Tabelle von timeseries als Arrays erstellt. WHERE TailNum LIKE N GRUPPE DURCH TailNum Zusammenfassung Weve abgedeckt alle Arten von UDFs: von einfachen Klassenerweiterungen, die sehr leicht geschrieben werden können, zu sehr komplizierten UDAFs, die uns zu denken, über verteilte Ausführung und Planung Orchestrierung von Abfrage-Motoren getan denken müssen. Mit jedem Glück hat die Diskussion Ihnen das Vertrauen zu gehen und zu implementieren Ihre eigenen UDFs - oder zumindest einige Aufmerksamkeit auf die Komplexität der, die in Gebrauch jeden Tag. Kategorie: Big Data21 SQL für Analyse und Reporting Behandlung von NULLs als Eingabe von Fensterfunktionen Fensterfunktionen NULL-Semantik stimmt mit den NULL-Semantiken für SQL-Aggregatfunktionen überein. Andere Semantiken können durch benutzerdefinierte Funktionen oder durch Verwendung des DECODE - oder CASE-Ausdrucks innerhalb der Fensterfunktion erhalten werden. Windowing-Funktionen mit logischem Offset Ein logischer Offset kann mit Konstanten wie RANGE 10 PRECEDING angegeben werden. Oder einen Ausdruck, der eine Konstante oder eine Intervallspezifikation wie RANGE INTERVAL N DAY / MONTH / YEAR PRECEDING oder einen Ausdruck, der ein Intervall auswertet, auswertet. Bei logischem Offset kann es nur einen Ausdruck in der Ausprägungsliste ORDER BY in der Funktion geben, wobei der Typ NUMERIC kompatibel ist, wenn der Offset numerisch ist, oder DATE, wenn ein Intervall angegeben ist. Beispiel 21-7 Kumulative Aggregatfunktion Nachfolgend ein Beispiel für kumulative Beträge nach Kundennummer pro Quartal 1999: In diesem Beispiel definiert die analytische Funktion SUM für jede Zeile ein Fenster, das am Anfang der Partition beginnt (UNBOUNDED PRECEDING ) Und endet standardmäßig in der aktuellen Zeile. Nested SUM s werden in diesem Beispiel benötigt, da wir einen SUM über einen Wert ausführen, der selbst ein SUM ist. Verschachtelte Aggregationen werden sehr oft in analytischen Aggregatfunktionen verwendet. Beispiel 21-8 Moving Aggregate-Funktion In diesem Beispiel eines zeitbasierten Fensters wird für einen Kunden der gleitende Durchschnitt des Umsatzes für den aktuellen Monat und die vorangegangenen zwei Monate angezeigt: Beachten Sie, dass die ersten beiden Zeilen für die dreimonatige gleitende Durchschnittsberechnung im Ausgabedaten basieren auf einer kleineren Intervallgröße als angegeben, da die Fensterberechnung die von der Abfrage abgerufenen Daten nicht überschreiten kann. Sie müssen die verschiedenen Fenstergrößen berücksichtigen, die an den Rändern der Ergebnismengen gefunden werden. Mit anderen Worten, müssen Sie die Abfrage ändern, um genau das, was Sie wollen, zu ändern. Zentrierte Aggregatfunktion Die Berechnung von Fenster-Aggregatfunktionen, die um die aktuelle Zeile zentriert sind, ist einfach. In diesem Beispiel wird für alle Kunden ein zentrierter gleitender Durchschnitt des Umsatzes für eine Woche Ende Dezember 1999 berechnet. Er ermittelt einen Durchschnitt der Verkaufssumme für den einen Tag vor der aktuellen Zeile und einen Tag nach der aktuellen Zeile einschließlich der aktuellen Zeile. Beispiel 21-9 Zentriertes Aggregat Die Anfangs - und Endzeilen für jede produktzentrierte gleitende Durchschnittsberechnung in den Ausgabedaten basieren auf nur zwei Tagen, da die Fensterberechnung die von der Abfrage abgerufenen Daten nicht überschreiten kann. Benutzer müssen die verschiedenen Fenstergrößen berücksichtigen, die an den Rändern der Ergebnismengen gefunden werden: die Abfrage muss möglicherweise angepasst werden. Aggregatfunktionen in Anwesenheit von Duplikaten visualisieren Im folgenden Beispiel wird veranschaulicht, wie Fensteraggregatfunktionen Werte berechnen, wenn Duplikate vorhanden sind, dh wenn mehrere Zeilen für einen einzelnen Auftragswert zurückgegeben werden. Die Abfrage ruft die in einem bestimmten Zeitraum an mehrere Kunden verkaufte Menge ab. (Obwohl wir eine Inline-Ansicht verwenden, um unseren Basisdatensatz zu definieren, hat er keine besondere Bedeutung und kann ignoriert werden.) Die Abfrage definiert ein sich bewegendes Fenster, das vom Datum der aktuellen Zeile bis zu 10 Tagen früher läuft. Beachten Sie das Schlüsselwort RANGE Wird verwendet, um die windowing-Klausel dieses Beispiels zu definieren. Dies bedeutet, dass das Fenster potenziell viele Zeilen für jeden Wert in dem Bereich halten kann. In diesem Fall gibt es drei Paare von Zeilen mit doppelten Datumswerten. Beispiel 21-10 Aktivieren von Aggregatfunktionen mit logischen Offsets In der Ausgabe dieses Beispiels geben alle Daten außer dem 6. Mai und 12. Mai zwei Zeilen mit doppelten Daten zurück. Untersuchen Sie die kommentierten Zahlen rechts neben der Ausgabe, um zu sehen, wie die Werte berechnet werden. Beachten Sie, dass jede Gruppe in Klammern die Werte darstellt, die für einen einzelnen Tag zurückgegeben werden. Beachten Sie, dass dieses Beispiel nur angewendet wird, wenn Sie das Schlüsselwort RANGE anstelle des ROWS-Schlüsselwort verwenden. Es ist auch wichtig, sich daran zu erinnern, dass mit RANGE. Können Sie nur 1 ORDER BY-Ausdruck in der analytischen Funktionen ORDER BY-Klausel verwenden. Mit dem ROWS-Schlüsselwort können Sie mehrere Ausdrücke in der analytischen Funktion ORDER BY verwenden. Variable Fenstergröße für jede Zeile Es gibt Situationen, in denen es sinnvoll ist, die Größe eines Fensters für jede Zeile abhängig von einer bestimmten Bedingung zu ändern. Zum Beispiel möchten Sie vielleicht das Fenster größer für bestimmte Termine und kleiner für andere machen. Angenommen, Sie wollen den gleitenden Durchschnitt des Aktienkurses über drei Werktage berechnen. Wenn Sie für jeden Arbeitstag eine gleiche Anzahl von Zeilen für jeden Arbeitstag haben und keine arbeitsfreien Tage gespeichert sind, können Sie eine physikalische Fensterfunktion verwenden. Wenn die angegebenen Bedingungen jedoch nicht erfüllt sind, können Sie trotzdem einen gleitenden Durchschnitt berechnen, indem Sie einen Ausdruck in den Fenstergrößenparametern verwenden. Ausdrücke in einer Fenstergrößenangabe können in mehreren verschiedenen Quellen gemacht werden. Könnte der Ausdruck ein Verweis auf eine Spalte in einer Tabelle sein, z. B. eine Zeittabelle. Es könnte auch eine Funktion sein, die die entsprechende Grenze für das Fenster basierend auf Werten in der aktuellen Zeile zurückgibt. Die folgende Anweisung für eine hypothetische Aktienkursdatenbank verwendet eine benutzerdefinierte Funktion in ihrer RANGE-Klausel, um die Fenstergröße festzulegen: In dieser Anweisung ist ttimekey ein Datumsfeld. Hier könnte fn eine PL / SQL-Funktion mit der folgenden Spezifikation sein: 4 if ttimekey is Monday, Tuesday Wenn einer der vorherigen Tage Urlaube ist, passt er die Zählung entsprechend an. Beachten Sie, dass, wenn Fenster mit einer Zahl in einer Fensterfunktion mit ORDER BY an einer Datumsspalte angegeben wird, dann wird es konvertiert, um die Anzahl der Tage zu bedeuten. Sie könnten auch die Intervall-Literal-Konvertierungsfunktion verwendet haben, da NUMTODSINTERVAL (fn (ttimekey), DAY) statt nur fn (ttimekey) das gleiche bedeutet. Sie können auch eine PL / SQL-Funktion schreiben, die einen INTERVAL-Datentypwert zurückgibt. Windowing Aggregate-Funktionen mit physischen Offsets Für Fenster, die in Zeilen ausgedrückt werden, sollten die Ordnungsausdrücke eindeutig sein, um deterministische Ergebnisse zu erzeugen. Beispielsweise ist die folgende Abfrage nicht deterministisch, weil timeid in dieser Ergebnismenge nicht eindeutig ist. Beispiel 21-11 Aktivieren von Aggregatfunktionen mit physischen Offsets Eine Möglichkeit, dieses Problem zu umgehen, wäre, die prodid-Spalte der Ergebnismenge und der Reihenfolge auf timeid und prodid hinzuzufügen. FIRSTVALUE - und LASTVALUE-Funktionen Mit den FIRSTVALUE - und LASTVALUE-Funktionen können Sie die erste und die letzte Zeile eines Fensters auswählen. Diese Zeilen sind besonders wertvoll, weil sie häufig als die Basislinien in Berechnungen verwendet werden. Beispielsweise könnten Sie bei einer Partitionsholding-Verkaufsdaten, die nach dem Tag geordnet sind, fragen, wie viele Tage die Verkäufe im Vergleich zum ersten Verkaufstag (FIRSTVALUE) der Periode oder vielleicht auch für einen Satz von Zeilen im steigenden Kundenauftrag waren , Was war die prozentuale Größe jedes Verkaufs in der Region im Vergleich zum größten Verkauf (LASTVALUE) in der Region Wenn die Option IGNORE NULLS mit FIRSTVALUE verwendet wird. Wird es den ersten Nicht-Nullwert in der Menge zurückgeben, oder NULL, wenn alle Werte NULL sind. Wenn IGNORE NULLS mit LASTVALUE verwendet wird. Gibt es den letzten Nicht-Nullwert in der Menge oder NULL zurück, wenn alle Werte NULL sind. Die Option IGNORE NULLS ist besonders nützlich, wenn Sie eine Inventartabelle ordnungsgemäß speichern. Reporting Aggregate-Funktionen Nachdem eine Abfrage verarbeitet wurde, können Aggregatwerte wie die Anzahl der resultierenden Zeilen oder ein Durchschnittswert in einer Spalte innerhalb einer Partition einfach berechnet und anderen Berichtsfunktionen zur Verfügung gestellt werden. Die Berichtsaggregatfunktionen geben für jede Zeile in einer Partition denselben Aggregatwert zurück. Ihr Verhalten in Bezug auf NULLs ist das gleiche wie die SQL-Aggregatfunktionen. Die Syntax lautet: Darüber hinaus gelten folgende Bedingungen: Ein Asterisk () ist nur in COUNT erlaubt () DISTINCT wird nur unterstützt, wenn entsprechende Aggregatfunktionen den Wert expression1 zulassen und value expression2 ein beliebiger gültiger Ausdruck mit Spaltenreferenzen oder Aggregaten sein kann. Die PARTITION BY-Klausel definiert die Gruppen, auf denen die Fensterfunktionen berechnet werden. Wenn die PARTITION BY-Klausel nicht vorhanden ist, wird die Funktion über die gesamte Abfrageergebnismenge berechnet. Reporting-Funktionen können nur in der SELECT-Klausel oder der ORDER BY-Klausel angezeigt werden. Der Hauptvorteil der Berichtsfunktionen ist ihre Fähigkeit, mehrere Durchläufe von Daten in einem einzelnen Abfrageblock zu tun und die Abfrageleistung zu beschleunigen. Abfragen wie zählen die Anzahl der Verkäufer mit Verkäufen mehr als 10 der Stadtverkäufe erfordern keine Verknüpfungen zwischen getrennten Abfrageblöcken. Betrachten Sie beispielsweise die Frage Für jede Produktkategorie, finden Sie die Region, in der es maximalen Umsatz hatte. Die äquivalente SQL-Abfrage mit der MAX-Berichtsaggregatfunktion wäre: Die innere Abfrage mit der Berichtsaggregatfunktion MAX (SUM (Beträge)) gibt zurück: Die vollständigen Abfrageergebnisse sind: Beispiel 21-12 Reporting Aggregate Beispiel Reporting Aggregate kombiniert mit geschachtelten Abfragen aktivieren Komplexe Abfragen effizient zu beantworten. Zum Beispiel, was, wenn Sie wissen möchten, die meistverkauften Produkte in Ihrer wichtigsten Produkt-Unterkategorien Das Folgende ist eine Abfrage, die die 5 meistverkauften Produkte für jede Produkt-Subkategorie, die mehr als 20 der Verkäufe innerhalb seiner Produktkategorie beiträgt: RATIOTOREPORT Funktion Die Funktion RATIOTOREPORT berechnet das Verhältnis eines Wertes zur Summe eines Wertsatzes. Wenn der Ausdruckswertausdruck zu NULL ausgewertet wird. RATIOTOREPORT wertet auch NULL aus. Aber es wird als Null für die Berechnung der Summe von Werten für den Nenner behandelt. Seine Syntax lautet: Dabei gilt: expr kann ein beliebiger gültiger Ausdruck mit Spaltenreferenzen oder Aggregaten sein. Die PARTITION BY-Klausel definiert die Gruppen, auf denen die RATIOTOREPORT-Funktion berechnet werden soll. Wenn die PARTITION BY-Klausel nicht vorhanden ist, wird die Funktion über die gesamte Abfrageergebnismenge berechnet. Für die Berechnung von RATIOTOREPORT der Verkäufe für jeden Kanal können Sie die folgende Syntax verwenden: LAG / LEAD-Funktionen Die LAG - und LEAD-Funktionen sind nützlich, um Werte zu vergleichen, wenn die relativen Positionen von Zeilen zuverlässig bekannt sein können. Sie arbeiten, indem sie die Anzahl der Zeilen angeben, die die Zielzeile von der aktuellen Zeile trennen. Da die Funktionen den Zugriff auf mehr als eine Zeile einer Tabelle zur gleichen Zeit ohne eine Selbstverknüpfung ermöglichen, können sie die Verarbeitungsgeschwindigkeit erhöhen. Die LAG-Funktion bietet Zugriff auf eine Zeile bei einem gegebenen Offset vor der aktuellen Position und die LEAD-Funktion bietet Zugriff auf eine Zeile bei einem gegebenen Offset nach der aktuellen Position. LAG / LEAD-Syntax Diese Funktionen haben folgende Syntax: offset ist ein optionaler Parameter und standardmäßig auf 1. default ist ein optionaler Parameter und ist der zurückgegebene Wert, wenn offset außerhalb der Grenzen der Tabelle oder Partition liegt. Informationen zur Verwendung der LAG / LEAD-Funktionen zum Durchführen von Vergleichsabfragen für Periodenperioden bei spärlichen Daten finden Sie unter Data Densification for Reporting. FIRST / LAST-Funktionen Die FIRST / LAST-Aggregatfunktionen ermöglichen es Ihnen, einen Datensatz zu ordnen und mit seinen obersten oder untersten Zeilen zu arbeiten. Nach dem Finden der obersten oder untersten Zeilen wird eine Aggregatfunktion auf jede gewünschte Spalte angewendet. Das heißt, FIRST / LAST lässt Sie auf Spalte A rangieren, aber das Ergebnis eines Aggregats zurückgeben, das auf die ersten oder letzten Zeilen der Spalte B angewendet wird. Dies ist wertvoll, weil es die Notwendigkeit für eine Selbstverknüpfung oder Unterabfrage vermeidet, Wodurch die Leistungsfähigkeit verbessert wird. Diese Funktionssyntax beginnt mit einer regulären Aggregatfunktion (MIN, MAX, AVG, COUNT VARIANCE, STDDEV), die einen einzelnen Rückgabewert pro Gruppe erzeugt. Um das angegebene Ranking anzugeben, fügen die FIRST / LAST-Funktionen eine neue Klausel hinzu, die mit dem Wort KEEP beginnt. FIRST / LAST Syntax Diese Funktionen haben folgende Syntax: Beachten Sie, dass die ORDER BY-Klausel mehrere Ausdrücke annehmen kann. FIRST / LAST als reguläre Aggregate Sie können die Aggregate der FIRST / LAST-Aggregate als reguläre Aggregatfunktionen verwenden. Beispiel 21-15 FIRST / LAST Beispiel 1 Die folgende Abfrage erlaubt uns, den Mindestpreis und den Listenpreis unserer Produkte zu vergleichen. Für jedes Produkt Unterkategorie innerhalb der Mens Kleidung Kategorie, gibt es die folgenden: Listenpreis des Produkts mit dem niedrigsten Mindestpreis Niedrigster Mindestpreis Listenpreis des Produkts mit dem höchsten Mindestpreis Höchster Mindestpreis FIRST / LAST Als Reporting Aggregates Sie können auch Verwenden Sie die FIRST / LAST-Familie von Aggregaten als Berichtsaggregatfunktionen. Ein Beispiel ist die Berechnung, welche Monate die größte und geringste Zunahme der Kopfzahl das ganze Jahr über. Die Syntax für diese Funktionen ähnelt der Syntax für jedes andere Berichtsaggregat. Betrachten Sie das Beispiel in Beispiel 21-15 für FIRST / LAST. Was wäre, wenn wir die Listenpreise einzelner Produkte finden und sie mit den Listenpreisen der Produkte in ihrer Unterkategorie vergleichen wollten, die den höchsten und den niedrigsten Mindestpreis hatten? Die folgende Abfrage läßt uns die Informationen für die Unterkategorie Documentation mit FIRST / LAST finden Als Berichtsaggregate. Beispiel 21-16 ERSTES / LETZTES Beispiel 2 Die Verwendung der FIRST - und LAST-Funktionen als Berichtsaggregate macht es einfach, die Ergebnisse in Berechnungen wie Gehalt als Prozentsatz des höchsten Gehalts einzubeziehen. Inverse Percentile-Funktionen Mit Hilfe der CUMEDIST-Funktion finden Sie die kumulative Verteilung (percentile) eines Wertsatzes. Allerdings ist die inverse Operation (Feststellung, welcher Wert zu einem bestimmten Perzentil berechnet) weder einfach noch effizient zu berechnen. Um diese Schwierigkeiten zu überwinden, wurden die Funktionen PERCENTILECONT und PERCENTILEDISC eingeführt. Diese können sowohl als Fensterberichterstattungsfunktionen als auch als normale Aggregatfunktionen verwendet werden. Diese Funktionen benötigen eine Sortierspezifikation und einen Parameter, der einen Perzentilwert zwischen 0 und 1 annimmt. Die Sortierspezifikation wird mit einer ORDER BY-Klausel mit einem Ausdruck behandelt. Wenn es als normale Aggregatfunktion verwendet wird, gibt es einen einzelnen Wert für jeden bestellten Satz zurück. PERCENTILECONT. Die eine durch Interpolation berechnete kontinuierliche Funktion und PERCENTILEDISC ist. Die eine Schrittfunktion ist, die diskrete Werte annimmt. Wie andere Aggregate arbeiten PERCENTILECONT und PERCENTILEDISC auf einer Gruppe von Zeilen in einer gruppierten Abfrage, jedoch mit den folgenden Unterschieden: Sie benötigen einen Parameter zwischen 0 und 1 (einschließlich). Ein aus diesem Bereich spezifizierter Parameter führt zu einem Fehler. Dieser Parameter sollte als Ausdruck angegeben werden, der eine Konstante auswertet. Sie benötigen eine Sortierung. Diese Sortierspezifikation ist eine ORDER BY-Klausel mit einem einzelnen Ausdruck. Mehrere Ausdrücke sind nicht erlaubt. Normal-Aggregat-Syntax Inverse Perzentil-Beispielbasis Wir verwenden die folgende Abfrage, um die 17 Zeilen der Daten zurückzugeben, die in den Beispielen dieses Abschnitts verwendet werden: PERCENTILEDISC (x) wird berechnet, indem die CUMEDIST-Werte in jeder Gruppe gescannt werden, bis die erste größer ist Oder gleich x ist. Wobei x der angegebene Perzentilwert ist. Für die Beispielabfrage mit PERCENTILEDISC (0,5) ergibt sich ein Ergebnis von 5.000, wie folgendes illustriert: Das Ergebnis von PERCENTILECONT wird durch lineare Interpolation zwischen Zeilen nach der Bestellung berechnet. Berechnen von PERCENTILECONT (x). Berechnen wir zunächst die Zeilennummer RN (1x (n-1)), wobei n die Anzahl der Zeilen in der Gruppe und x der angegebene Perzentilwert ist. Das Endresultat der Aggregatfunktion wird durch lineare Interpolation zwischen den Werten aus Zeilen der Zeilennummern CRN CEIL (RN) und FRN FLOOR (RN) berechnet. Das endgültige Ergebnis ist: PERCENTILECONT (X) if (CRN FRN RN), dann (Wert des Ausdrucks von Zeile bei RN) sonst (CRN - RN) (Wert des Ausdrucks für Zeile bei FRN) (RN - FRN) (Wert von Ausdruck für Zeile bei CRN). Betrachten wir die vorherige Beispielabfrage, wo wir PERCENTILECONT (0.5) berechnen. Hier ist n 17. Die Reihennummer RN (1 0,5 (n-1)) 9 für beide Gruppen. Setzen wir dies in die Formel (FRNCRN9), so geben wir den Wert aus Zeile 9 als Ergebnis zurück. Ein anderes Beispiel ist, wenn Sie PERCENTILECONT (0.66) berechnen wollen. Die berechnete Zeilennummer RN (1 0,66 (n -1)) (1 0,6616) 11,67. PERCENTILECONT (0,66) (12-11,67) (Wert der Zeile 11) (11,67-11) (Wert der Zeile 12). Diese Ergebnisse sind: Inverse-Perzentil-Aggregatfunktionen können in der HAVING-Klausel einer Abfrage wie andere vorhandene Aggregatfunktionen auftreten. Als Reporting Aggregates Sie können auch die Aggregatfunktionen PERCENTILECONT nutzen. PERCENTILEDISC als Berichtsaggregatfunktionen. Bei der Verwendung als Berichtsaggregatfunktionen ist die Syntax ähnlich wie bei anderen Berichtsaggregaten. Diese Abfrage berechnet das gleiche Ergebnis (Median-Kreditlimit für Kunden in dieser Ergebnismenge, meldet jedoch das Ergebnis für jede Zeile in der Ergebnismenge, wie in der folgenden Ausgabe gezeigt: Inverse Percentile Restrictions Für PERCENTILEDISC kann der Ausdruck in der ORDER BY-Klausel verwendet werden (Numeric, string, date usw.) Der Ausdruck in der ORDER BY-Klausel muss jedoch ein numerischer oder datetime-Typ sein (einschließlich Intervalle), da lineare Interpolation zur Auswertung von PERCENTILECONT verwendet wird. Wenn der Ausdruck vom Typ DATE ist, wird das interpolierte Ergebnis auf die kleinste Einheit für den Typ gerundet Für einen DATE-Typ wird der interpolierte Wert auf die nächste Sekunde gerundet, für Intervalltypen auf die nächste Sekunde (INTERVAL DAY TO SECOND) Oder bis zum Monat (INTERVAL YEAR TO MONTH) Wie andere Aggregate ignorieren die inversen Perzentilfunktionen NULLs beim Auswerten des Ergebnisses. Wenn Sie beispielsweise den Medianwert in einer Menge finden möchten, ignoriert Oracle Database die NULLs und findet den Median Unter den Nicht-Nullwerten. Sie können die Option NULLS FIRST / NULLS LAST in der ORDER BY-Klausel verwenden, werden jedoch ignoriert, da NULLs ignoriert werden. Hypothetische Rang - und Verteilungsfunktionen Diese Funktionen bieten Funktionalitäten, die für eine Was-wäre-Analyse nützlich sind. Ein Beispiel wäre der Rang einer Zeile, wenn die Zeile hypothetisch in einen Satz von anderen Zeilen eingefügt wurde. Diese Aggregatgruppe nimmt ein oder mehrere Argumente einer hypothetischen Zeile und einer geordneten Gruppe von Zeilen an und gibt die RANK zurück. DENSERANK. PERCENTRANK oder CUMEDIST der Zeile, als ob sie hypothetisch in die Gruppe eingefügt wurde. Hypothetische Rang - und Verteilungssyntax Hier bezieht sich konstanter Ausdruck auf einen Ausdruck, der eine Konstante auswertet, und es können mehr als eine solche Ausdrücke vorhanden sein, die als Argumente an die Funktion übergeben werden. Die ORDER BY-Klausel kann einen oder mehrere Ausdrücke enthalten, die die Sortierreihenfolge definieren, auf der das Ranking basiert. ASC. DESC. NULLS ERSTE. NULLS LAST-Optionen stehen für jeden Ausdruck in der ORDER BY zur Verfügung. Beispiel 21-17 Hypothetisches Rang - und Verteilungsbeispiel 1 Mit Hilfe der Listenpreisdaten aus der in diesem Abschnitt verwendeten Produkttabelle können Sie den RANK berechnen. PERCENTRANK und CUMEDIST für eine hypothetische Pullover mit einem Preis von 50 für wie es passt in jede der Pullover Unterkategorien. Die Abfrage und die Ergebnisse sind: Im Gegensatz zu den inversen Perzentilaggregaten kann die ORDER BY-Klausel in der Sortierspezifikation für hypothetische Rang - und Verteilungsfunktionen mehrere Ausdrücke annehmen. Die Anzahl der Argumente und die Ausdrücke in der ORDER BY-Klausel sollten dieselben sein und die Argumente müssen konstante Ausdrücke desselben oder kompatiblen Typs zu dem entsprechenden ORDER BY-Ausdruck sein. Das folgende ist ein Beispiel mit zwei Argumenten in mehreren hypothetischen Ranking-Funktionen. Beispiel 21-18 Hypothetischer Rang und Verteilungsbeispiel 2 Diese Funktionen können in der HAVING-Klausel einer Abfrage ebenso wie andere Aggregatfunktionen erscheinen. Sie können nicht als Berichtsaggregatfunktionen oder als Fensteraggregatfunktionen verwendet werden. Lineare Regressionsfunktionen Die Regressionsfunktionen unterstützen die Anpassung einer Regressionsgerade auf eine Reihe von Zahlenpaaren. Sie können sie sowohl als Aggregatfunktionen als auch als Fenster - oder Reportingfunktionen verwenden. Die Funktionen sind wie folgt: Oracle wendet die Funktion auf den Satz von (e1.e2) Paaren an, nachdem alle Paare eliminiert wurden, für die entweder e1 oder e2 null ist. E1 wird als ein Wert der abhängigen Variablen (ein y-Wert) interpretiert, und e2 wird als ein Wert der unabhängigen Variablen (ein x-Wert) interpretiert. Beide Ausdrücke müssen Zahlen sein. Die Regressionsfunktionen werden alle gleichzeitig während eines einzigen Durchlaufs der Daten berechnet. Sie werden häufig mit dem COVARPOP kombiniert. COVARSAMP. Und CORR-Funktionen. REGRCOUNT Funktion REGRCOUNT gibt die Anzahl der Nicht-Null-Zahlenpaare zurück, die für die Regressionsgerade verwendet wurden. Wenn auf einen leeren Satz angewendet wird (oder wenn es keine (e1, e2) Paare gibt, in denen weder e1 noch e2 null ist), gibt die Funktion 0 zurück. REGRAVGY und REGRAVGX Die Funktionen REGRAVGY und REGRAVGX berechnen die Mittelwerte der abhängigen Variablen und der unabhängigen Variable der Regressionsgeraden. REGRAVGY berechnet den Durchschnitt seines ersten Arguments (e1) nach dem Eliminieren von (e1.e2) Paaren, bei denen entweder e1 oder e2 Null ist. In ähnlicher Weise berechnet REGRAVGX den Durchschnitt seines zweiten Arguments (e2) nach einer Nulleliminierung. Beide Funktionen geben NULL zurück, wenn sie auf einen leeren Satz angewendet werden. REGRSLOPE - und REGRINTERCEPT-Funktionen Die REGRSLOPE-Funktion berechnet die Steigung der Regressionsgerade, die an Nicht-Null-Paare (e1.e2) angepasst ist. Die Funktion REGRINTERCEPT berechnet den y-Achsenabschnitt der Regressionsgeraden. REGRINTERCEPT gibt NULL zurück, wenn Slope oder die Regressionsmittelwerte NULL sind. REGRR2 Funktion Die Funktion REGRR2 berechnet den Bestimmungskoeffizienten (üblicherweise R-Quadrat oder Gütegrad) für die Regressionsgeraden. REGRR2 gibt Werte zwischen 0 und 1 zurück, wenn die Regressionslinie definiert ist (Steigung der Linie ist nicht Null) und es gibt sonst NULL zurück. Je näher der Wert auf 1 ist, desto besser passt die Regressionslinie zu den Daten. REGRSXX, REGRSYY und REGRSXY Funktionen REGRSXX. REGRSYY - und REGRSXY-Funktionen werden bei der Berechnung verschiedener diagnostischer Statistiken für die Regressionsanalyse verwendet. Schafft gleich große Schaufeln.