Bewegungs Durchschnitt Orakel Funktion

Wenn Sie diese Nachricht sehen, hat Ihr Browser entweder deaktiviert oder unterstützt JavaScript nicht. Um die vollständigen Funktionen dieses Hilfesystems zu nutzen, wie zB die Suche, muss Ihr Browser JavaScript-Unterstützung aktiviert haben. Gewichtete Bewegungsdurchschnitte bei einfacher gleitender Durchschn. Wird jeder Datenwert in der Windowquot, in dem die Berechnung durchgeführt wird, eine gleiche Bedeutung oder Gewicht gegeben. Es ist oft der Fall, vor allem in der Finanzpreisdatenanalyse, dass chronologisch neuere Daten ein größeres Gewicht tragen sollten. In diesen Fällen wird Weighted Moving Average (oder Exponential Moving Average - siehe folgendes Thema) die Funktionalität oft bevorzugt. Betrachten Sie die gleiche Tabelle der Verkaufsdatenwerte für zwölf Monate: Um einen gewichteten beweglichen Durchschnitt zu berechnen: Berechnen Sie, wie viele Intervalle von Daten an der Moving Average-Berechnung teilnehmen (d. h. die Größe der Berechnung quotwindowquot). Wenn das Berechnungsfenster n ist, dann wird der aktuellste Datenwert im Fenster mit n multipliziert, der nächstletzte multipliziert mit n-1, der Wert vor dem multipliziert mit n-2 und so weiter für alle Werte im Fenster. Teilen Sie die Summe aller multiplizierten Werte durch die Summe der Gewichte, um den gewichteten beweglichen Durchschnitt über dieses Fenster zu geben. Platzieren Sie den gewichteten Moving Average Wert in einer neuen Spalte entsprechend der oben beschriebenen übergeordneten Mittelwertpositionierung. Um diese Schritte zu veranschaulichen, ist zu prüfen, ob im Dezember ein 3-monatiger gewichteter Umsatzkurs im Dezember erforderlich ist (unter Verwendung der obigen Tabelle der Verkaufswerte). Der Begriff quot3-monthquot impliziert, dass die Berechnung quotwindowquot 3 ist, daher sollte der gewichtete Moving Average Berechnungsalgorithmus für diesen Fall sein: Oder wenn ein 3-Monats-gewichteter Moving Average über den gesamten ursprünglichen Datenbereich ausgewertet wurde, wäre das Ergebnis : 3-Monats-gewichtetes Moving AveragePreviously diskutierten wir, wie man rollende Mittelwerte 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 kann MA200 leicht berechnen. Meine aktuelle Abfrage sieht so etwas wie die Basis aus (wählen Sie ROWNUMBER () OVER (Partition von STOCKCODE ORDER BY pxDate) rowno a. Ab Preis a), base2 as (wählen Sie MA200160160160160160 Fall, wenn rowno gt 200 dann avg (schließen) OVER ( Partition von STOCKCODE ORDER BY pxDate ROWS ZWISCHEN 200 VORHERIGEN UND AKTUELLEN REIHEN) sonst null Ende als MA200, MA100160160160160160 Fall, wenn rowno gt 100 dann avg (schließen) OVER (Partition von STOCKCODE ORDER BY pxDate ROWS ZWISCHEN 100 PRECEDING und CURRENT ROW) sonst null Ende Als MA100, MA50160160160160160 Fall, wenn rowno gt 50 dann avg (schließen) OVER (Partition von STOCKCODE ORDER BY pxDate ROWS ZWISCHEN 50 VORHERIGEN UND CURRENT ROW) sonst null Ende als MA50, b von Basis b) wählen Sie aus base2. : 6 Jede Funktion, um den exponentiellen gleitenden Durchschnitt im Orakel zu berechnen. Nicht dass ich wüsste. Bei der Berechnung einer 5-Periode EMA ist so etwas wie unten, wäre es einfach genug, um ein Skript zurückzukehren, um den Code zurückzukehren, um EMA 200 Regards zu berechnen Bearbeitet von: Etbin am 30.5.2010 18:29 Wenn die jüngsten Werte ein größeres Gewicht haben sollten, (1 -: Faktor), n) sollte umgekehrt angewendet werden Entschuldigung, ich verstehe nicht, was deine Frage ist. Wann immer Sie ein Problem haben, posten Sie ein paar Beispieldaten (CREATE TABLE und INSERT Anweisungen) und die Ergebnisse, die Sie von diesen Daten wünschen. Immer erwähnen, welche Version von Oracle youre mit. Vereinfachen Sie so viel wie möglich. Zum Beispiel, anstatt durchschnittlich über 200 Zeilen, Post ein Problem, wo Sie durchschnittlich über vielleicht 3 Zeilen. Datum schließen stockname SMA (in der Lage avg zu verwenden, Partition Abfrage und Windowing einfache gleitende avg zu berechnen) 03-MAY-101601601601601603.69160160160160160CAPITALAND LIMITED null 04-MAY-101601601601601603.66160160160160160CAPITALAND LIMITED null 05-MAY-101601601601601603.63160160160160160CAPITALAND LIMITED null 06-MAY-101601601601601603.6160160160160160CAPITALAND LIMITED null 07-MAY-101601601601601603.63160160160160160CAPITALAND LIMITED 3,642 10-MAY-101601601601601603.7160160160160160CAPITALAND LIMITED 3,644 03-MAY-101601601601601601.68160160160160160YANLORD LAND GROUP LIMITED null 04-MAY-101601601601601601.64160160160160160YANLORD LAND GROUP LIMITED null 05-MAY-101601601601601601.61160160160160160YANLORD LAND GROUP LIMITED null 06- MAY-10160160160160160160YANLORD LAND GROUP LIMITED null 07-MAY-10160160160160160161160160160160160YANLORD LAND GRUPPE LIMITED 1.628 10-MAY-101601601601601601.67160160160160160YANLORD LAND GRUPPE LIMITED 1.626 Angesichts 2 Aktienkapital und Yanlord für den Zeitraum zwischen Mai 3 bis 10. Mai kann ich einfache 5 Tage berechnen Gleitender Durchschnitt mit avg-Funktion. Das Problem ist, dass ich Schwierigkeiten habe, den 5-tägigen exponentiellen gleitenden Durchschnitt zu finden. Anmerkung: Meine Datenbank hat derzeit 1000 über Aktien Daten für einige Jahre und ich habe verschiedene Periode des exponentiellen gleitenden Durchschnittes berechnet werden, das ist, warum ich frage, ob es eine Funktion zur Verfügung, die ich nicht bewusst bin. Es gibt keine Standardfunktion, um den exponentiellen gleitenden Durchschnitt zu berechnen. Aber das ist die Art der Berechnung, bei der die Modellklausel wirklich leuchtet: Ich habe die Formel wie hier beschrieben verwendet: download. oracledocscdE1203201docepm.921htmlirstudioframeset. htmdocscdE1203201docepm.921htmlirstudioirstudio-15-74.html 1 quot (0) (0) wow. Das ist cool. Ich wusste nicht, dass das so genannte Modell war. Ich werde mehr Forschung darüber und sehen, ob ich es umsetzen kann. Danke viel Los SQL SQL für Analyse und Reporting Behandlung von NULLs als Eingabe von Fensterfunktionen Fensterfunktionen Die NULL Semantik entspricht der NULL Semantik für SQL Aggregate Funktionen. Andere Semantik kann durch benutzerdefinierte Funktionen oder durch Verwendung des DECODE oder eines CASE-Ausdrucks innerhalb der Fensterfunktion erhalten werden. Window-Funktionen mit logischem Offset Ein logischer Offset kann mit Konstanten wie RANGE 10 PRECEDING spezifiziert werden. Oder einen Ausdruck, der eine Konstante auswertet, oder durch eine Intervall-Spezifikation wie RANGE INTERVAL N DAY MONTH YEAR PRECEDING oder einen Ausdruck, der ein Intervall auswertet. Bei logischem Offset kann es nur einen Ausdruck in der ORDER BY-Ausdrucksliste in der Funktion geben, wobei der Typ kompatibel zu NUMERIC 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 Kunden-ID um Viertel im Jahr 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. Verschachtelte SUMs werden in diesem Beispiel benötigt, da wir einen SUM über einen Wert ausführen, der selbst ein SUM ist. Verschachtelte Aggregationen werden sehr häufig in analytischen Aggregatfunktionen verwendet. Beispiel 21-8 Verschieben der Aggregatfunktion Dieses Beispiel eines zeitbasierten Fensters zeigt für einen Kunden den gleitenden Durchschnitt des Umsatzes für den aktuellen Monat und die vorangegangenen zwei Monate an: 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 nicht über die von der Abfrage abgerufenen Daten hinausgehen kann. Sie müssen die verschiedenen Fenstergrößen an den Grenzen der Ergebnismengen berücksichtigen. Mit anderen Worten, Sie müssen möglicherweise die Abfrage ändern, um genau das zu enthalten, was Sie wollen. Zentrierte Aggregatfunktion Die Berechnung der Fenstersystemfunktionen, die um die aktuelle Zeile zentriert sind, ist einfach. Dieses Beispiel berechnet für alle Kunden einen zweistelligen gleitenden Durchschnitt des Umsatzes für eine Woche Ende Dezember 1999. Es findet einen Durchschnitt der Umsatzsumme für den einen Tag vor der aktuellen Zeile und einen Tag nach der aktuellen Zeile einschließlich der aktuellen Zeile auch. Beispiel 21-9 Zentrierte Aggregate Die Start - 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 erreichen kann. Benutzer müssen die verschiedenen Fenstergrößen berücksichtigen, die an den Grenzen der Ergebnismengen gefunden werden: Die Abfrage muss möglicherweise angepasst werden. Windowing Aggregate-Funktionen in Anwesenheit von Duplikaten Das folgende Beispiel veranschaulicht, wie die Fensteraggregat-Funktionen Werte berechnen, wenn es Duplikate gibt, dh wenn mehrere Zeilen für einen einzelnen Bestellwert zurückgegeben werden. Die Abfrage ruft die an mehrere Kunden verkaufte Menge während eines bestimmten Zeitraums 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 Bewegtfenster, das vom Datum der aktuellen Zeile bis 10 Tage früher läuft. Habe das RANGE-Schlüsselwort Wird verwendet, um die Fensterklausel dieses Beispiels zu definieren. Dies bedeutet, dass das Fenster potenziell viele Zeilen für jeden Wert im Bereich halten kann. In diesem Fall gibt es drei Paare von Zeilen mit doppelten Datumswerten. Beispiel 21-10 Zusammenfassen von Aggregatfunktionen mit logischen Offsets In der Ausgabe dieses Beispiels werden alle Daten außer dem 6. Mai und 12. Mai zwei Zeilen mit doppelten Daten zurückgeben. Untersuche 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 dann zutrifft, wenn Sie das RANGE-Schlüsselwort anstelle des ROWS-Schlüsselworts verwenden. Es ist auch wichtig, sich daran zu erinnern, dass mit RANGE. Sie können nur 1 ORDER BY-Ausdruck in der analytischen Funktionen ORDER BY-Klausel verwenden. Mit dem ROWS-Schlüsselwort können Sie in der analytischen Funktion ORDER BY-Klausel mehrere Aufträge nach Ausdrücken verwenden. Unterschiedliche Fenstergröße für jede Zeile Es gibt Situationen, in denen es nützlich ist, die Größe eines Fensters für jede Zeile zu variieren, basierend auf einer bestimmten Bedingung. Zum Beispiel können Sie das Fenster für bestimmte Termine größer machen und für andere kleiner machen. Angenommen, Sie wollen den gleitenden Durchschnitt des Aktienkurses über drei Werktage berechnen. Wenn Sie für alle Arbeitstage eine gleiche Anzahl von Zeilen für jeden Tag haben und keine nicht arbeitenden Tage gespeichert sind, können Sie eine physikalische Fensterfunktion verwenden. Wenn jedoch die angegebenen Bedingungen 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 verschiedenen Quellen erstellt werden. Der Ausdruck könnte 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 auf der Grundlage von Werten in der aktuellen Zeile zurückgibt. Die folgende Aussage 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 PLSQL-Funktion mit der folgenden Spezifikation sein: 4 Wenn ttimekey Montag, Dienstag ist Wenn irgendwelche der vorherigen Tage Feiertage sind, passt es die Zählung entsprechend an. Beachten Sie, dass, wenn das Fenster mit einer Zahl in einer Fensterfunktion mit ORDER BY an einer Datumssäule angegeben wird, dann wird es umgewandelt, um die Anzahl der Tage zu bedeuten. Sie könnten auch die Intervall-Literal-Conversion-Funktion verwendet haben, da NUMTODSINTERVAL (fn (ttimekey), DAY) anstatt nur fn (ttimekey) das Gleiche bedeutet. Sie können auch eine PLSQL-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 Bestellausdrücke eindeutig sein, um deterministische Ergebnisse zu erzeugen. Zum Beispiel ist die folgende Abfrage nicht deterministisch, weil timeid in dieser Ergebnismenge nicht eindeutig ist. Beispiel 21-11 Windowing Aggregate-Funktionen mit physischen Offsets Ein Weg, um dieses Problem zu behandeln wäre, um die prodid Spalte zu der Ergebnismenge hinzuzufügen und auf sowohl timeid als auch prodid zu bestellen. FIRSTVALUE und LASTVALUE Funktionen Mit den Funktionen FIRSTVALUE und LASTVALUE können Sie die ersten und letzten Zeilen aus einem Fenster auswählen. Diese Reihen sind besonders wertvoll, weil sie oft als Basislinien in Berechnungen verwendet werden. Zum Beispiel, mit einer Partition mit Verkaufsdaten nach Tag bestellt, könnten Sie fragen, wie viel war jeder Tag Umsatz im Vergleich zu den ersten Verkaufstag (FIRSTVALUE) der Periode Oder möchten Sie vielleicht wissen, für eine Reihe von Zeilen in steigenden Kundenauftrag , Was war die prozentuale Größe jedes Verkaufs in der Region im Vergleich zum größten Verkauf (LASTVALUE) in der Region Wenn die IGNORE NULLS Option mit FIRSTVALUE verwendet wird. Es wird den ersten Nicht-Nullwert in der Menge zurückgeben, oder NULL, wenn alle Werte NULL sind. Wenn IGNORE NULLS mit LASTVALUE verwendet wird. Es wird den letzten Nicht-Nullwert in der Menge zurückgeben, oder NULL, wenn alle Werte NULL sind. Die IGNORE NULLS-Option ist besonders nützlich, um eine Inventartabelle ordnungsgemäß zu belegen. Reporting Aggregate Functions Nachdem eine Abfrage verarbeitet wurde, können aggregierte Werte wie die Anzahl der resultierenden Zeilen oder ein Mittelwert in einer Spalte innerhalb einer Partition leicht berechnet und anderen Reporting-Funktionen zur Verfügung gestellt werden. Das Sammeln von Aggregatfunktionen gibt denselben Aggregatwert für jede Zeile in einer Partition zurück. Ihr Verhalten in Bezug auf NULLs ist das gleiche wie die SQL-Aggregat-Funktionen. Die Syntax ist: Zusätzlich gelten folgende Bedingungen: Ein Asterisk () ist nur in COUNT erlaubt () DISTINCT wird nur unterstützt, wenn entsprechende Aggregatfunktionen den Wert expression1 erlauben 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 sollen. 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 erscheinen. Der Hauptvorteil von Reporting-Funktionen ist ihre Fähigkeit, mehrere Datenpässe in einem einzigen Abfrage-Block zu tun und die Abfrageleistung zu beschleunigen. Abfragen wie zählt die Anzahl der Verkäufer mit Verkäufen mehr als 10 der Stadtverkäufe erfordern keine Verknüpfungen zwischen separaten Abfrageblöcken. Zum Beispiel, betrachten Sie die Frage Für jede Produktkategorie, finden Sie die Region, in der es maximale Verkäufe hatte. Die äquivalente SQL-Abfrage mit der MAX-Reporting-Aggregatfunktion wäre: Die innere Abfrage mit der Reporting-Aggregatfunktion MAX (SUM (subsold)) gibt zurück: Die vollständigen Abfrageergebnisse sind: Beispiel 21-12 Reporting Aggregate Beispiel Reporting Aggregate kombiniert mit verschachtelten Abfragen ermöglichen Sie können komplexe Abfragen effizient beantworten. Zum Beispiel, was, wenn Sie wissen wollen, die meistverkauften Produkte in Ihrem wichtigsten Produkt-Unterkategorien Das folgende ist eine Abfrage, die die 5 Top-Selling-Produkte für jede Produkt-Subkategorie, die mehr als 20 der Umsatz in seiner Produktkategorie: RATIOTOREPORT beiträgt Funktion Die Funktion RATIOTOREPORT berechnet das Verhältnis eines Wertes zur Summe eines Satzes von Werten. Wenn der Ausdruck Ausdruck Ausdruck auf NULL auswertet. RATIOTOREPORT bewertet auch NULL. Aber es wird als Null für die Berechnung der Summe der Werte für den Nenner behandelt. Seine Syntax ist: Dabei gilt: expr kann ein 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. Um RATIOTOREPORT des Umsatzes für jeden Kanal zu berechnen, können Sie die folgende Syntax verwenden: LAGLEAD-Funktionen Die LAG - und LEAD-Funktionen sind nützlich, um Werte zu vergleichen, wenn die relativen Positionen von Zeilen zuverlässig bekannt sind. Sie arbeiten, indem sie die Anzahl der Zeilen angeben, die die Zielreihe von der aktuellen Zeile trennen. Da die Funktionen den Zugriff auf mehr als eine Zeile einer Tabelle zur gleichen Zeit ohne Selbstverknüpfung ermöglichen, können sie die Verarbeitungsgeschwindigkeit verbessern. Die LAG-Funktion ermöglicht den 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. LAGLEAD Syntax Diese Funktionen haben folgende Syntax: Offset ist ein optionaler Parameter und standardmäßig 1. default ist ein optionaler Parameter und ist der Wert, der zurückgegeben wird, wenn Offset außerhalb der Grenzen der Tabelle oder Partition liegt. Weitere Informationen zur Verwendung der LAG LEAD-Funktionen finden Sie unter Datenverzerrung für die Berichterstattung, um die Vergleichsabfragen für spärliche Daten zu verwenden. FIRSTLAST-Funktionen Die FIRSTLAST-Aggregatfunktionen erlauben es Ihnen, einen Datensatz zu ordnen und mit seinen hochrangigen oder untergeordneten Zeilen zu arbeiten. Nach dem Finden der oberen oder unteren Rangreihen wird eine Aggregatfunktion auf jede gewünschte Spalte angewendet. Das heißt, ERSTES LETZT läßt Sie auf Spalte A stehen, aber geben Sie das Ergebnis eines Aggregats zurück, das auf den ersten oder letzten Rangreihen der Spalte B angewendet wird. Dies ist wertvoll, weil es die Notwendigkeit einer Selbstverknüpfung oder Unterabfrage vermeidet verbessernde Leistung. Diese Funktionssyntax beginnt mit einer regulären Aggregatfunktion (MIN. MAX. SUM. AVG. COUNT. VARIANCE STDDEV), die einen einzelnen Rückgabewert pro Gruppe erzeugt. Um das verwendete Ranking anzugeben, fügen die FIRST LAST-Funktionen eine neue Klausel hinzu, die mit dem Wort KEEP beginnt. FIRSTLAST Syntax Diese Funktionen haben folgende Syntax: Beachten Sie, dass die ORDER BY-Klausel mehrere Ausdrücke ausführen kann. FIRSTLAST Als reguläre Aggregate können Sie die FIRST LAST Familie von Aggregaten als reguläre Aggregatfunktionen verwenden. Beispiel 21-15 FIRSTLAST Beispiel 1 Die folgende Abfrage lässt uns den Mindestpreis und den Listenpreis unserer Produkte vergleichen. Für jede Produktunterkategorie innerhalb der Herrenbekleidungskategorie ergibt sich folgendes: Listenpreis des Produktes mit dem niedrigsten Mindestpreis Niedrigster Mindestpreis Listenpreis des Produktes mit dem höchsten Mindestpreis Höchster Mindestpreis FIRSTLAST Als Reporting Aggregate Sie können auch die ERSTE LETZTE Familie von Aggregaten als Berichtsaggregatfunktionen. Ein Beispiel ist die Berechnung, welche Monate die größte und geringste Zunahme der Kopfzahl während des Jahres hatte. Die Syntax für diese Funktionen ähnelt der Syntax für alle anderen Berichtsaggregate. Betrachten wir das Beispiel in Beispiel 21-15 für FIRSTLAST. Was wäre, wenn wir die Listenpreise einzelner Produkte finden und mit den Listenpreisen der Produkte in ihrer Unterkategorie vergleichen würden, die die höchsten und niedrigsten Mindestpreise hatten. Mit der folgenden Abfrage können wir diese Informationen für die Unterkategorie Unterlagen finden, indem wir FIRSTLAST als Berichterstattung verwenden Aggregate Beispiel 21-16 FIRSTLAST Beispiel 2 Mit den FIRST - und LAST-Funktionen als Reporting-Aggregate ist es einfach, die Ergebnisse in Berechnungen wie Gehalt in Prozent des höchsten Gehalts einzubeziehen. Inverse Percentile-Funktionen Mit der CUMEDIST-Funktion finden Sie die kumulative Verteilung (Perzentil) eines Satzes von Werten. Die umgekehrte Operation (die Feststellung, welcher Wert zu einem bestimmten Perzentil berechnet wird) ist jedoch nicht leicht zu erledigen und effizient zu berechnen. Um diese Schwierigkeit zu überwinden, wurden die PERCENTILECONT - und PERCENTILEDISC-Funktionen eingeführt. Diese können sowohl als Fensterberichtsfunktionen 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 sie als normale Aggregatfunktion verwendet wird, gibt sie für jeden geordneten Satz einen einzelnen Wert zurück. PERCENTILECONT Die eine durch Interpolation berechnete kontinuierliche Funktion ist und PERCENTILEDISC. Das ist eine Schrittfunktion, die diskrete Werte annimmt. Wie andere Aggregate arbeiten PERCENTILECONT und PERCENTILEDISC auf einer Gruppe von Zeilen in einer gruppierten Abfrage, aber mit den folgenden Unterschieden: Sie benötigen einen Parameter zwischen 0 und 1 (inklusive). Ein aus diesem Bereich vorgegebener 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 einzigen Ausdruck. Mehrere Ausdrücke sind nicht erlaubt. Normal Aggregate Syntax Inverse Percentile Beispiel Basis 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 nach dem Scannen der ersten, Oder gleich x. Wobei x der angegebene Perzentilwert ist. Für die Beispielabfrage, wo PERCENTILEDISC (0.5), ergibt sich das Ergebnis 5.000, wie die folgenden veranschaulicht: Das Ergebnis von PERCENTILECONT wird durch lineare Interpolation zwischen Zeilen nach der Bestellung berechnet. Zur Berechnung von PERCENTILECONT (x). Zuerst berechnen wir die Zeilennummer RN (1x (n-1)), wobei n die Anzahl der Zeilen in der Gruppe und x der angegebene Perzentilwert ist. Das endgültige Ergebnis der Aggregatfunktion wird durch lineare Interpolation zwischen den Werten aus Zeilen in Zeilennummern CRN CEIL (RN) und FRN FLOOR (RN) berechnet. Das endgültige Ergebnis ist: PERCENTILECONT (X) if (CRN FRN RN), dann (Wert des Ausdrucks aus 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 Zeilennummer RN (1 0,5 (n-1)) 9 für beide Gruppen. Wenn wir dies in die Formel setzen (FRNCRN9), geben wir den Wert aus Zeile 9 als Ergebnis zurück. Ein anderes Beispiel ist, wenn Sie PERCENTILECONT (0.66) berechnen möchten. 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 Aggregat-Funktionen können in der HAVING-Klausel einer Abfrage wie andere vorhandene aggregierte Funktionen erscheinen. Als Reporting Aggregate können Sie auch die Aggregatfunktionen PERCENTILECONT verwenden. PERCENTILEDISC als Berichtsaggregatfunktionen. Bei der Verwendung als Reporting-Aggregat-Funktionen ist die Syntax ähnlich wie bei anderen Reportaggregaten. Diese Abfrage berechnet die gleiche Sache (Median Credit Limit für Kunden in diesem Ergebnis gesetzt, aber meldet das Ergebnis für jede Zeile in der Ergebnismenge, wie in der folgenden Ausgabe gezeigt: Inverse Percentile Einschränkungen Für PERCENTILEDISC der Ausdruck in der ORDER BY-Klausel kann Von jedem Datentyp, den Sie sortieren können (numerisch, String, Datum usw.). Allerdings muss der Ausdruck in der ORDER BY-Klausel ein numerischer oder datetime-Typ sein (einschließlich Intervallen), da eine 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 bis zur nächsten Sekunde (INTERVAL DAY TO SECOND) Oder in den Monat (INTERVAL JAHR ZUM MONAT) Wie andere Aggregate ignorieren die inversen Perzentilfunktionen NULLs bei der Auswertung des Ergebnisses. Wenn du zum Beispiel den Medianwert in einem Satz finden willst, ignoriert Oracle Database die NULLs und findet den Median Unter den Nicht-Null-Werten. Sie können die Option NULLS FIRST NULLS LAST in der ORDER BY-Klausel verwenden, aber sie werden ignoriert, da NULLs ignoriert werden. Hypothetische Rang - und Verteilungsfunktionen Diese Funktionen bieten Funktionalität für die Analyse. Als Beispiel, was wäre der Rang einer Zeile, wenn die Zeile hypothetisch in eine Reihe von anderen Zeilen eingefügt wurde Diese Familie von Aggregaten nimmt ein oder mehrere Argumente einer hypothetischen Zeile und einer geordneten Gruppe von Zeilen, die RANG zurückzugeben. DENSERANK PERCENTRANK oder CUMEDIST der Reihe, als wäre sie hypothetisch in die Gruppe eingefügt. Hypothetischer Rang und Verteilungssyntax Hier bezieht sich der konstante Ausdruck auf einen Ausdruck, der eine Konstante auswertet, und es können mehr als eine solche Ausdrücke geben, 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 im ORDER BY zur Verfügung. Beispiel 21-17 Hypothetischer Rang und Verteilungsbeispiel 1 Unter Verwendung der Listenpreisdaten aus der in diesem Abschnitt verwendeten Produkttabelle können Sie die RANG berechnen. PERCENTRANK und CUMEDIST für einen hypothetischen 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 gleich sein und die Argumente müssen konstante Ausdrücke des gleichen oder kompatiblen Typs an den 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 wie andere Aggregatfunktionen erscheinen. Sie können nicht als Reporting aggregate Funktionen oder Fenster-Aggregat-Funktionen verwendet werden. Lineare Regressionsfunktionen Die Regressionsfunktionen unterstützen die Anpassung einer ordentlichen kleinsten Regressionslinie an einen Satz von Zahlenpaaren. Sie können sie sowohl als aggregierte Funktionen als auch als Fenster - oder Reporting-Funktionen verwenden. Die Funktionen sind wie folgt: Oracle wendet die Funktion auf die Menge der (e1. E2) Paare an, nachdem alle Paare eliminiert wurden, für die entweder e1 oder e2 null ist. E1 wird als Wert der abhängigen Variablen (ein y-Wert) interpretiert und e2 wird als 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-Nummernpaare zurück, die für die Regressionsgerade verwendet wurden. Wenn es auf einen leeren Satz angewendet wird (oder wenn es keine (e1, e2) Paare gibt, bei denen keiner von e1 oder e2 null ist), gibt die Funktion 0 zurück. REGRAVGY und REGRAVGX Funktionen REGRAVGY und REGRAVGX berechnen die Mittelwerte der abhängigen Variablen und der unabhängigen Variable der Regressionsgerade. REGRAVGY berechnet den Mittelwert seines ersten Arguments (e1) nach der Eliminierung von (e1. E2) Paaren, wobei entweder e1 oder e2 Null ist. Ähnlich berechnet REGRAVGX den Durchschnitt seines zweiten Arguments (e2) nach der Null-Eliminierung. Beide Funktionen geben NULL zurück, wenn sie auf einen leeren Satz angewendet werden. REGRSLOPE - und REGRINTERCEPT-Funktionen Die REGRSLOPE-Funktion berechnet die Steilheit der Regressionslinie, die an Nicht-Null (e1. E2) - Paaren angepasst ist. Die Funktion REGRINTERCEPT berechnet den y-Intercept der Regressionsgerade. REGRINTERCEPT gibt NULL zurück, wenn die Steigung oder die Regressionsdurchschnitte NULL sind. REGRR2-Funktion Die Funktion REGRR2 berechnet den Bestimmungskoeffizienten (üblicherweise R-Quadrat oder Güte der Anpassung) für die Regressionsgerade. REGRR2 gibt Werte zwischen 0 und 1 zurück, wenn die Regressionslinie definiert ist (Slope der Zeile ist nicht null), und es gibt NULL ansonsten zurück. Je näher der Wert 1 ist, desto besser ist die Regressionsgerade für die Daten. REGRSXX, REGRSYY und REGRSXY Funktionen REGRSXX. REGRSYY - und REGRSXY-Funktionen werden bei der Berechnung verschiedener Diagnosestatistiken für die Regressionsanalyse verwendet. Nach der Eliminierung von (e1. E2) - Paaren, bei denen entweder e1 oder e2 null ist, bilden diese Funktionen die folgenden Berechnungen: Lineare Regressionsstatistiken Beispiele Einige gemeinsame Diagnosestatistiken, die die lineare Regressionsanalyse begleiten, sind in Tabelle 21-2, Common Diagnostic Statistics und Their angegeben Ausdrücke Beachten Sie, dass dies neue Funktionen freigibt, damit Sie alle diese berechnen können. Tabelle 21-2 Gemeinsame Diagnostikstatistik und ihre Ausdrücke Probe Lineare Regressionsberechnung In diesem Beispiel berechnen wir eine ordentliche Reklamationslinie der kleinsten Quadrate, die die verkaufte Menge eines Produkts als lineare Funktion des Produktlistenpreises ausdrückt. Die Berechnungen werden nach dem Vertriebskanal gruppiert. Die Werte SLOPE. INTCPT. RSQR sind Steilheit, Abfangen und Bestimmungskoeffizient der Regressionsgerade. Der (ganzzahlige) Wert COUNT ist die Anzahl der Produkte in jedem Kanal, für die beide Mengen verkauft und Listenpreisdaten verfügbar sind. Häufige Itemsets Anstatt zu zählen, wie oft ein bestimmtes Ereignis auftritt (z. B. wie oft jemand Milch im Lebensmittelgeschäft gekauft hat), bieten häufige Itemsets einen Mechanismus zum Zählen, wie oft mehrere Ereignisse zusammen auftreten (zB wie oft jemand Milch gekauft hat Und Getreide zusammen im Supermarkt). Die Eingabe für die Operation "häufige Itemsets" ist ein Satz von Daten, die Sammlungen von Elementen (Itemsets) repräsentieren. Some examples of itemsets could be all of the products that a given customer purchased in a single trip to the grocery store (commonly called a market basket), the web-pages that a user accessed in a single session, or the financial services that a given customer utilizes. The notion of a frequent itemset is to find those itemsets that occur most often. If you apply the frequent-itemset operator to a grocery stores point-of-sale data, you might, for example, discover that milk and bananas are the most commonly bought pair of items. Frequent itemsets have thus been used in business intelligence environments for many years, with the most common one being for market basket analysis in the retail industry. Frequent itemsets are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides a couple of key benefits: Applications that previously relied on frequent itemset operations now benefit from significantly improved performance as well as simpler implementation. SQL-based applications that did not previously use frequent itemsets can now be easily extended to take advantage of this functionality. Frequent itemsets analysis is performed with the PLSQL package DBMSFREQUENTITEMSETS. See PLSQL Packages and Types Reference for more information. Other Statistical Functions Oracle introduces a set of SQL statistical functions and a statistics package, DBMSSTATFUNCS. This section lists some of the new functions along with basic syntax. See PLSQL Packages and Types Reference for detailed information about the DBMSSTATFUNCS package and Oracle Database SQL Reference for syntax and semantics. Descriptive Statistics You can calculate the following descriptive statistics: Median of a Data Set Mode of a Data Set You can calculate the following parametric statistics: Spearmans rho Coefficient Kendalls tau-b Coefficient In addition to the functions, this release has a new PLSQL package, DBMSSTATFUNCS. It contains the descriptive statistical function SUMMARY along with functions to support distribution fitting. The SUMMARY function summarizes a numerical column of a table with a variety of descriptive statistics. The five distribution fitting functions support normal, uniform, Weibull, Poisson, and exponential distributions. WIDTHBUCKET Function For a given expression, the WIDTHBUCKET function returns the bucket number that the result of this expression will be assigned after it is evaluated. You can generate equiwidth histograms with this function. Equiwidth histograms divide data sets into buckets whose interval size (highest value to lowest value) is equal. The number of rows held by each bucket will vary. A related function, NTILE. creates equiheight buckets. Equiwidth histograms can be generated only for numeric, date or datetime types. So the first three parameters should be all numeric expressions or all date expressions. Other types of expressions are not allowed. If the first parameter is NULL. the result is NULL. If the second or the third parameter is NULL. an error message is returned, as a NULL value cannot denote any end point (or any point) for a range in a date or numeric value dimension. The last parameter (number of buckets) should be a numeric expression that evaluates to a positive integer value 0, NULL. or a negative value will result in an error. Buckets are numbered from 0 to ( n 1). Bucket 0 holds the count of values less than the minimum. Bucket( n 1) holds the count of values greater than or equal to the maximum specified value. WIDTHBUCKET Syntax The WIDTHBUCKET takes four expressions as parameters. The first parameter is the expression that the equiwidth histogram is for. The second and third parameters are expressions that denote the end points of the acceptable range for the first parameter. The fourth parameter denotes the number of buckets. Consider the following data from table customers. that shows the credit limits of 17 customers. This data is gathered in the query shown in Example 21-19 . In the table customers. the column custcreditlimit contains values between 1500 and 15000, and we can assign the values to four equiwidth buckets, numbered from 1 to 4, by using WIDTHBUCKET (custcreditlimit, 0, 20000, 4). Ideally each bucket is a closed-open interval of the real number line, for example, bucket number 2 is assigned to scores between 5000.0000 and 9999.9999. sometimes denoted 5000, 10000) to indicate that 5,000 is included in the interval and 10,000 is excluded. To accommodate values outside the range 0, 20,000), values less than 0 are assigned to a designated underflow bucket which is numbered 0, and values greater than or equal to 20,000 are assigned to a designated overflow bucket which is numbered 5 (num buckets 1 in general). See Figure 21-3 for a graphical illustration of how the buckets are assigned. You can specify the bounds in the reverse order, for example, WIDTHBUCKET ( custcreditlimit. 20000. 0. 4 ). When the bounds are reversed, the buckets will be open-closed intervals. In this example, bucket number 1 is ( 15000,20000 , bucket number 2 is ( 10000,15000 , and bucket number 4, is ( 0 ,5000. The overflow bucket will be numbered 0 ( 20000. infinity ), and the underflow bucket will be numbered 5 (- infinity. 0 . It is an error if the bucket count parameter is 0 or negative. The followin g query shows the bucket numbers for the credit limits in the customers table for both cases where the boundaries are specified in regular or reverse order. We use a range of 0 to 20,000. User-Defined Aggregate Functions Oracle offers a facility for creating your own functions, called user-defined aggregate functions. These functions are written in programming languages such as PLSQL, Java, and C, and can be used as analytic functions or aggregates in materialized views. See Oracle Data Cartridge Developers Guide for further information regarding syntax and restrictions. The advantages of these functions are: Highly complex functions can be programmed using a fully procedural language. Higher scalability than other techniques when user-defined functions are programmed for parallel processing. Object datatypes can be processed. As a simple example of a user-defined aggregate function, consider the skew statistic. This calculation measures if a data set has a lopsided distribution about its mean. It will tell you if one tail of the distribution is significantly larger than the other. If you created a user-defined aggregate called udskew and applied it to the credit limit data in the prior example, the SQL statement and results might look like this: Before building user-defined aggregate functions, you should consider if your needs can be met in regular SQL. Many complex calculations are possible directly in SQL, particularly by using the CASE expression. Staying with regular SQL will enable simpler development, and many query operations are already well-parallelized in SQL. Even the earlier example, the skew statistic, can be created using standard, albeit lengthy, SQL. CASE Expressions Oracle now supports simple and searched CASE statements. CASE statements are similar in purpose to the DECODE statement, but they offer more flexibility and logical power. They are also easier to read than traditional DECODE statements, and offer better performance as well. They are commonly used when breaking categories into buckets like age (for example, 20-29, 30-39, and so on). The syntax for simple statements is: The syntax for searched statements is: You can specify only 255 arguments and each WHEN. THEN pair counts as two arguments. For a workaround to this limit, see Oracle Database SQL Reference . Suppose you wanted to find the average salary of all employees in the company. If an employees salary is less than 2000, you want the query to use 2000 instead. Without a CASE statement, you would have to write this query as follows, In this, foo is a function that returns its input if the input is greater than 2000, and returns 2000 otherwise. The query has performance implications because it needs to invoke a function for each row. Writing custom functions can also add to the development load. Using CASE expressions in the database without PLSQL, this query can be rewritten as: Using a CASE expression lets you avoid developing custom functions and can also perform faster. Creating Histograms With User-Defined Buckets You can use the CASE statement when you want to obtain histograms with user-defined buckets (both in number of buckets and width of each bucket). The following are two examples of histograms created with CASE statements. In the first example, the histogram totals are shown in multiple columns and a single row is returned. In the second example, the histogram is shown with a label column and a single column for totals, and multiple rows are returned. Example 21-21 Histogram Example 1 Example 21-22 Histogram Example 2 Data Densification for Reporting Data is normally stored in sparse form. That is, if no value exists for a given combination of dimension values, no row exists in the fact table. However, you may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. For example, if a product did not sell during a particular time period, you may still want to see the product for that time period with zero sales value next to it. Moreover, time series calculations can be performed most easily when data is dense along the time dimension. This is because dense data will fill a consistent number of rows for each period, which in turn makes it simple to use the analytic windowing functions with physical offsets. Data densification is the process of converting spare data into dense form. To overcome the problem of sparsity, you can use a partitioned outer join to fill the gaps in a time series or any other dimension. Such a join extends the conventional outer join syntax by applying the outer join to each logical partition defined in a query. Oracle logically partitions the rows in your query based on the expression you specify in the PARTITION BY clause. The result of a partitioned outer join is a UNION of the outer joins of each of the partitions in the logically partitioned table with the table on the other side of the join. Note that you can use this type of join to fill the gaps in any dimension, not just the time dimension. Most of the examples here focus on the time dimension because it is the dimension most frequently used as a basis for comparisons. Partition Join Syntax The syntax for partitioned outer join extends the ANSI SQL JOIN clause with the phrase PARTITION BY followed by an expression list. The expressions in the list specify the group to which the outer join is applied. The following are the two forms of syntax normally used for partitioned outer join: Note that FULL OUTER JOIN is not supported with a partitioned outer join. Sample of Sparse Data A typi cal situation with a sparse dimension is shown in the following example, which computes the weekly sales and year-to-date sales for the product Bounce for weeks 20-30 in 2000 and 2001: In this example, we would expect 22 rows of data (11 weeks each from 2 years) if the data were dense. However we get only 18 rows because weeks 25 and 26 are missing in 2000, and weeks 26 and 28 in 2001. Filling Gaps in Data We can take the sparse data of the preceding query and do a partitioned outer join with a dense set of time data. In the following query, we alias our original query as v and we select data from the times table, which we alias as t. Here we retrieve 22 rows because there are no gaps in the series. The four added rows each have 0 as their Sales value set to 0 by using the NVL function. Note that in this query, a WHERE condition was placed for weeks between 20 and 30 in the inline view for the time dimension. This was introduced to keep the result set small. Filling Gaps in Two Dimensions N-dimensional data is typically displayed as a dense 2-dimensional cross tab of (n - 2) page dimensions. This requires that all dimension values for the two dimensions appearing in the cross tab be filled in. The following is another example where the partitioned outer join capability can be used for filling the gaps on two dimensions: In this query, the WITH sub-query factoring clause v1. summarizes sales data at the product, country, and year level. This result is sparse but users may want to see all the country, year combinations for each product. To achieve this, we take each partition of v1 based on product values and outer join it on the country dimension first. This will give us all values of country for each product. We then take that result and partition it on product and country values and then outer join it on time dimension. This will give us all time values for each product and country combination. Filling Gaps in an Inventory Table An inventory table typically tracks quantity of units available for various products. This table is sparse: it only stores a row for a product when there is an event. For a sales table, the event is a sale, and for the inventory table, the event is a change in quantity available for a product. For example, consider the following inventory table: The inventory table now has the following rows: For reporting purposes, users may want to see this inventory data differently. For example, they may want to see all values of time for each product. This can be accomplished using partitioned outer join. In addition, for the newly inserted rows of missing time periods, users may want to see the values for quantity of units column to be carried over from the most recent existing time period. The latter can be accomplished using analytic window function LASTVALUE value. Here is the query and the desired output: The inner query computes a partitioned outer join on time within each product. The inner query densifies the data on the time dimension (meaning the time dimension will now have a row for each day of the week). However, the measure column quantity will have nulls for the newly added rows (see the output in the column quantity in the following results. The outer query uses the analytic function LASTVALUE. Applying this function partitions the data by product and orders the data on the time dimension column ( timeid ). For each row, the function finds the last non-null value in the window due to the option IGNORE NULLS. which you can use with both LASTVALUE and FIRSTVALUE. We see the desired output in the column repeatedquantity in the following output: Computing Data Values to Fill Gaps Examples in previous section illustrate how to use partitioned outer join to fill gaps in one or more dimensions. However, the result sets produced by partitioned outer join have null values for columns that are not included in the PARTITION BY list. Typically, these are measure columns. Users can make use of analytic SQL functions to replace those null values with a non-null value. For example, the following query computes monthly totals for products 64MB Memory card and DVD-R Discs (product IDs 122 and 136) for the year 2000. It uses partitioned outer join to densify data for all months. For the missing months, it then uses the analytic SQL function AVG to compute the sales and units to be the average of the months when the product was sold. If working in SQLPlus, the following two commands will wrap the column headings for greater readability of results: Time Series Calculations on Densified Data Densificatio n is not just for reporting purpose. It also enables certain types of calculations, especially, time series calculations. Time series calculations are easier when data is dense along the time dimension. Dense data has a consistent number of rows for each time periods which in turn make it simple to use analytic window functions with physical offsets. To illustrate, lets first take the example on Filling Gaps in Data. and lets add an analytic function to that query. In the following enhanced version, we calculate weekly year-to-date sales alongside the weekly sales. The NULL values that the partitioned outer join inserts in making the time series dense are handled in the usual way: the SUM function treats them as 0s. Period-to-Period Comparison for One Time Level: Example How do we use this feature to compare values across time periods Specifically, how do we calculate a year-over-year sales comparison at the week level The following query returns on the same row, for each product, the year-to-date sales for each week of 2001 with that of 2000. Note that in this example we start with a WITH clause. This improves readability of the query and lets us focus on the partitioned outer join. If working in SQLPlus, the following command will wrap the column headings for greater readability of results: In the FROM clause of the in-line view densesales. we use a partitioned outer join of aggregate view v and time view t to fill gaps in the sales data along the time dimension. The output of the partitioned outer join is then processed by the analytic function SUM. OVER to compute the weekly year-to-date sales (the weeklyytdsales column). Thus, the view densesales computes the year-to-date sales data for each week, including those missing in the aggregate view s. The in-line view yearoveryearsales then computes the year ago weekly year-to-date sales using the LAG function. The LAG function labeled weeklyytdsalesprioryear specifies a PARTITION BY clause that pairs rows for the same week of years 2000 and 2001 into a single partition. We then pass an offset of 1 to the LAG function to get the weekly year to date sales for the prior year. The outermost query block selects data from yearoveryearsales with the condition yr 2001, and thus the query returns, for each product, its weekly year-to-date sales in the specified weeks of years 2001 and 2000. Period-to-Period Comparison for Multiple Time Levels: Example While the prior example shows us a way to create comparisons for a single time level, it would be even more useful to handle multiple time levels in a single query. For example, we could compare sales versus the prior period at the year, quarter, month and day levels. How can we create a query which performs a year-over-year comparison of year-to-date sales for all levels of our time hierarchy We will take several steps to perform this task. The goal is a single query with comparisons at the day, week, month, quarter, and year level. The steps are as follows: We will create a view called cubeprodtime. which holds a hierarchical cube of sales aggregated across times and products . Then we will create a view of the time dimension to use as an edge of the cube. The time edge, which holds a complete set of dates, will be partitioned outer joined to the sparse data in the view cubeprodtime . Finally, for maximum performance, we will create a materialized view, mvprodtime. built using the same definition as cubeprodtime . For more information regarding hierarchical cubes, see Chapter 20, SQL for Aggregation in Data Warehouses. The materialized view is defined using the following statement: Step 1 Create the hierarchical cube view The materialized view shown in the following may already exist in your system if not, create it now. If you must generate it, please note that we limit the query to just two products to keep processing time short: Because this view is limited to two products, it returns just over 2200 rows. Note that the column HierarchicalTime contains string representations of time from all levels of the time hierarchy. The CASE expression used for the HierarchicalTime column appends a marker (0, 1. ) to each date string to denote the time level of the value. A 0 represents the year level, 1 is quarters, 2 is months, and 3 is day. Note that the GROUP BY clause is a concatenated ROLLUP which specifies the rollup hierarchy for the time and product dimensions. The GROUP BY clause is what determines the hierarchical cube contents. Step 2 Create the view edgetime, which is a complete set of date values edgetime is the source for filling time gaps in the hierarchical cube using a partitioned outer join. The column HierarchicalTime in edgetime will be used in a partitioned join with the HierarchicalTime column in the view cubeprodtime. The following statement defines edgetime : Step 3 Create the materialized view mvprodtime to support faster performance The materialized view definition is a duplicate of the view cubeprodtime defined earlier. Because it is a duplicate query, references to cubeprodtime will be rewritten to use the mvprodtime materialized view. The following materialized may already exist in your system if not, create it now. If you must generate it, please note that we limit the query to just two products to keep processing time short. Step 4 Create the comparison query We have now set the stage for our comparison query. We can obtain period-to-period comparison calculations at all time levels. It requires applying analytic functions to a hierarchical cube with dense data along the time dimension. Some of the calculations we can achieve for each time level are: Sum of sales for prior period at all levels of time. Variance in sales over prior period. Sum of sales in the same period a year ago at all levels of time. Variance in sales over the same period last year. The following example performs all four of these calculations. It uses a partitioned outer join of the views cubeprodtime and edgetime to create an in-line view of dense data called densecubeprodtime. The query then uses the LAG function in the same way as the prior single-level example. The outer WHERE clause specifies time at three levels: the days of August 2001, the entire month, and the entire third quarter of 2001. Note that the last two rows of the results contain the month level and quarter level aggregations. Note: To make the results easier to read if you are using SQLPlus, the column headings should be adjusted with the following commands. The commands will fold the column headings to reduce line length: Here is the query comparing current sales to prior and year ago sales: The first LAG function ( salespriorperiod ) partitions the data on gidp. cat. subcat. prod. gidt and orders the rows on all the time dimension columns. It gets the sales value of the prior period by passing an offset of 1. The second LAG function ( salessameperiodprioryear ) partitions the data on additional columns qtrnum. monnum. and daynum and orders it on yr so that, with an offset of 1, it can compute the year ago sales for the same period. The outermost SELECT clause computes the variances. Creating a Custom Member in a Dimension: Example In many OLAP tasks, it is helpful to define custom members in a dimension. For instance, you might define a specialized time period for analyses. You can use a partitioned outer join to temporarily add a member to a dimension. Note that the new SQL MODEL clause is suitable for creating more complex scenarios involving new members in dimensions. See Chapter 22, SQL for Modeling for more information on this topic. As an example of a task, what if we want to define a new member for our time dimension We want to create a 13th member of the Month level in our time dimension. This 13th month is defined as the summation of the sales for each product in the first month of each quarter of year 2001. The solution has two steps. Note that we will build this solution using the views and tables created in the prior example. Two steps are required. First, create a view with the new member added to the appropriate dimension. The view uses a UNION ALL operation to add the new member. To query using the custom member, use a CASE expression and a partitioned outer join. Our new member for the time dimension is created with the following view: In this statement, the view timec is defined by performing a UNION ALL of the edgetime view (defined in the prior example) and the user-defined 13th month. The gidt value of 8 was chosen to differentiate the custom member from the standard members. The UNION ALL specifies the attributes for a 13th month member by doing a SELECT from the DUAL table. Note that the grouping id, column gidt. is set to 8, and the quarter number is set to 5. Then, the second step is to use an inline view of the query to perform a partitioned outer join of cubeprodtime with timec. This step creates sales data for the 13th month at each level of product aggregation. In the main query, the analytic function SUM is used with a CASE expression to compute the 13th month, which is defined as the summation of the first months sales of each quarter. The SUM function uses a CASE to limit the data to months 1, 4, 7, and 10 within each year. Due to the tiny data set, with just 2 products, the rollup values of the results are necessarily repetitions of lower level aggregations. For more realistic set of rollup values, you can include more products from the Game Console and Y Box Games subcategories in the underlying materialized view.