21 SQL für Analyse und Reporting Um diese Operationen auszuführen, fügen die analytischen Funktionen der SQL-Verarbeitung mehrere neue Elemente hinzu. Diese Elemente bauen auf vorhandenen SQL auf, um flexible und leistungsfähige Berechnungsausdrücke zu ermöglichen. Mit wenigen Ausnahmen haben die analytischen Funktionen diese neuen Elemente. Der Verarbeitungsablauf ist in Abbildung 21-1 dargestellt. Abbildung 21-1 Verarbeitungsauftrag Die wesentlichen Konzepte, die in analytischen Funktionen verwendet werden, sind: Die Abfrageverarbeitung mit Hilfe von analytischen Funktionen erfolgt in drei Stufen. Erstens, alle Joins, WHERE. GROUP BY und HAVING-Klauseln durchgeführt. Zweitens wird die Ergebnismenge den analytischen Funktionen zur Verfügung gestellt, und alle ihre Berechnungen erfolgen. Drittens, wenn die Abfrage eine ORDER BY-Klausel an ihrem Ende hat, wird die ORDER BY verarbeitet, um eine präzise Ausgabeordnung zu ermöglichen. Die Verarbeitungsreihenfolge ist in Abbildung 21-1 dargestellt. Ergebnismengenpartitionen Die analytischen Funktionen ermöglichen es Benutzern, Abfrageergebnismengen in Gruppen von Zeilen, die als Partitionen bezeichnet werden, zu teilen. Beachten Sie, dass der Begriff Partitionen, die mit analytischen Funktionen verwendet werden, nicht mit dem Tabellenpartitionsmerkmal verknüpft ist. In diesem Kapitel bezieht sich der Begriff Partitionen nur auf die Bedeutung der analytischen Funktionen. Partitionen werden nach den Gruppen definiert, die mit GROUP BY-Klauseln definiert sind, sodass sie für alle aggregierten Ergebnisse wie Summen und Durchschnittswerte verfügbar sind. Partitionsbereiche können auf beliebigen Spalten oder Ausdrücken basieren. Eine Abfrageergebnismenge kann in nur eine Partition partitioniert werden, die alle Zeilen, einige große Partitionen oder viele kleine Partitionen mit nur wenigen Zeilen enthält. Für jede Zeile in einer Partition können Sie ein Schiebefenster von Daten definieren. Dieses Fenster bestimmt den Bereich der Zeilen, die für die Berechnung der aktuellen Zeile verwendet werden. Fenstergrößen können entweder auf eine physikalische Anzahl von Zeilen oder ein logisches Intervall wie Zeit basieren. Das Fenster hat eine Anfangszeile und eine Endzeile. Je nach ihrer Definition kann sich das Fenster an einem oder beiden Enden bewegen. Beispielsweise würde ein Fenster, das für eine kumulative Summenfunktion definiert ist, seine Startzeile in der ersten Zeile seiner Partition festgelegt, und seine Endzeile würde vom Anfangspunkt bis zur letzten Zeile der Partition verschoben. Im Gegensatz dazu würde ein Fenster, das für einen gleitenden Durchschnitt definiert ist, sowohl seine Anfangs - als auch seine Endpunkte gleiten lassen, so daß sie einen konstanten physikalischen oder logischen Bereich beibehalten. Ein Fenster kann so groß sein wie alle Zeilen in einer Partition oder nur ein Schiebefenster einer Zeile innerhalb einer Partition. Wenn ein Fenster in der Nähe eines Rahmens ist, gibt die Funktion Ergebnisse nur für die verfügbaren Zeilen zurück, anstatt Sie zu warnen, dass die Ergebnisse nicht das sind, was Sie wollen. Bei der Verwendung von Fensterfunktionen ist die aktuelle Zeile während der Berechnungen enthalten, so dass Sie nur n (n) angeben sollten, wenn Sie mit n Elementen zu tun haben. Jede Berechnung, die mit einer analytischen Funktion durchgeführt wird, basiert auf einer aktuellen Zeile innerhalb einer Partition. Die aktuelle Zeile dient als Referenzpunkt, der den Anfang und das Ende des Fensters bestimmt. Beispielsweise könnte eine zentrierte gleitende Durchschnittsberechnung mit einem Fenster definiert werden, das die aktuelle Zeile, die sechs vorhergehenden Zeilen und die folgenden sechs Zeilen enthält. Dies würde ein Schiebefenster von 13 Reihen erzeugen (siehe Abbildung 21-2). Abbildung 21-2 Schiebefenster Beispiel Rangierfunktionen Eine Rangfolgefunktion berechnet den Rang eines Datensatzes im Vergleich zu anderen Datensätzen im Datensatz anhand der Werte eines Satzes von Maßnahmen. Die Typen der Ranking-Funktion sind: Sample Linear Regression Berechnung In diesem Beispiel berechnen wir eine Regressionsgerade, die die Menge eines Produktes als lineare Funktion des Produktlistenpreises ausdrückt. Die Berechnungen werden nach Vertriebskanal gruppiert. Die Werte SLOPE. INTCPT. RSQR sind Steigung, Intercept und Bestimmungskoeffizient der Regressionslinie. Der (ganzzahlige) Wert COUNT ist die Anzahl der Produkte in jedem Kanal, für den sowohl die verkauften Mengen als auch die Listenpreisdaten verfügbar sind. Lineare Algebra Lineare Algebra ist ein Zweig der Mathematik mit einer breiten Palette von praktischen Anwendungen. Viele Bereiche haben Aufgaben, die durch lineare Algebra ausgedrückt werden können, und hier einige Beispiele aus verschiedenen Bereichen: Statistik (multiple lineare Regression und prinzipielle Komponentenanalyse), Data Mining (Clustering und Klassifikation), Bioinformatik (Analyse von Microarray-Daten), Operations Research (Supply Chain und andere Optimierungsprobleme), Ökonometrie (Analyse der Verbraucherbedarfsdaten) und Finanzen (Asset Allocation). Verschiedene Bibliotheken für lineare Algebra sind frei verfügbar für jedermann zu verwenden. Oracles UTLNLA-Paket stellt Matrix-PLSQL-Datentypen und Wrapper-PLSQL-Unterprogramme für zwei der beliebtesten und robustesten dieser Bibliotheken BLAS und LAPACK zur Verfügung. Lineare Algebra hängt von der Matrixmanipulation ab. Die Durchführung von Matrixmanipulationen in PLSQL in der Vergangenheit erforderte das Erfinden einer Matrixdarstellung basierend auf PLSQLs nativen Datentypen und dann das Schreiben von Matrixmanipulationsroutinen von Grund auf neu. Dies erforderte einen erheblichen Programmieraufwand und die Leistung der resultierenden Implementierung war begrenzt. Wenn Entwickler wählten, Daten an externe Pakete zur Verarbeitung zu senden, anstatt ihre eigenen Routinen zu erstellen, könnte eine Datenübertragung hin und her zeitraubend sein. Mit dem UTLNLA-Paket können Daten in Oracle bleiben, der Programmieraufwand entfernt und eine schnelle Implementierung ermöglicht werden. Beispiel 21-19 Lineare Algebra Hier ist ein Beispiel dafür, wie Oracles lineare Algebra-Unterstützung für Business Analysis verwendet werden könnte. Es ruft eine multiple lineare Regressionsanwendung auf, die mit dem UTLNLA-Paket erstellt wurde. Die Multiregressionsanwendung wird in einem Objekt namens OLSRegression implementiert. Beachten Sie, dass Beispieldateien für das OLS-Regression-Objekt in ORACLEHOMEplsqldemo gefunden werden können. Betrachten Sie das Szenario eines Einzelhändlers, der die Wirksamkeit seines Marketingprogramms analysiert. Jeder seiner Filialen verteilt sein Marketingbudget über folgende mögliche Programme: Medienwerbung (Medien), Promotions (promo), Rabattgutscheine (disct) und Direct Mailer (dmail). Die Regressionsanalyse bildet eine lineare Beziehung zwischen der Menge der Verkäufe, die ein durchschnittliches Geschäft in einem gegebenen Jahr (Verkäufe) und den Ausgaben für die vier Bestandteile des Marketing-Programms hat. Angenommen, die Marketingdaten werden in der folgenden Tabelle gespeichert: Dann können Sie das folgende lineare Verkaufsmodell mit Hilfe von Koeffizienten erstellen: Dieses Modell kann als die folgende Sicht implementiert werden, die sich auf das OLS-Regressionsobjekt bezieht: Mit dieser Ansicht ein Marketing Programm-Manager kann eine Analyse durchführen, wie Ist dieses Vertriebs-Marketing-Modell vernünftig für das Jahr 2004 Daten Das heißt, ist die Mehrfachkorrelation größer als einige akzeptable Wert, sagen, 0,9 Die SQL für eine solche Abfrage könnte wie folgt sein: Sie könnten auch Fragen wie z. B. Was ist der erwartete Umsatz Umsatz eines Filialen ohne Marketing-Programme im Jahr 2003 oder Welche Komponente des Marketing-Programm war die effektivste in 2004 Das heißt, ein Dollar-Anstieg in dem Programm produziert die größte erwartete Zunahme in Sales Weitere Informationen zur Verwendung des UTLNLA-Pakets und der linearen Algebra finden Sie unter Oracle Database PLSQL Packages and Types Reference. Häufige Itemsets Anstatt zu zählen, wie oft ein bestimmtes Ereignis auftritt (z. B. wie oft jemand Milch im Lebensmittelgeschäft gekauft hat), kann es nützlich sein, zu zählen, wie oft mehrere Ereignisse zusammen auftreten (z. B. wie oft jemand beide Milch gekauft hat) Und Getreide zusammen an der Supermarkt). Sie können diese mehrere Ereignisse mit einem so genannten "häufigen Itemset" zählen, das, wie der Name schon sagt, eine Menge von Elementen ist. Einige Beispiele für Postenets könnten alle Produkte sein, die ein bestimmter Kunde in einer einzigen Reise zum Lebensmittelgeschäft gekauft hat (üblicherweise als Marktkorb bezeichnet), die Webseiten, auf die ein Benutzer in einer einzigen Sitzung zugreift, oder die Finanzdienstleistungen, Kunde nutzt. Die praktische Motivation für die Verwendung eines häufigen Itemset ist es, diejenigen Itemsets zu finden, die am häufigsten auftreten. Wenn Sie analysieren, ein Lebensmittelgeschäfte Point-of-Sale-Daten, könnten Sie zum Beispiel entdecken, dass Milch und Bananen sind die am häufigsten gekauft Paar von Elementen. Häufige Postenets sind damit seit vielen Jahren in Business-Intelligence-Umgebungen verwendet worden, wobei die gebräuchlichste für die Marktkorb-Analyse im Einzelhandel ist. Häufige Itemset-Berechnungen werden mit der Datenbank integriert, die über relationale Tabellen verfügt und über SQL aufgerufen wird. Diese Integration bietet die folgenden wesentlichen Vorteile: Anwendungen, die bisher auf häufige Itemset-Operationen angewiesen waren, profitieren nun von einer deutlich verbesserten Performance sowie einer einfacheren Implementierung. SQL-basierte Anwendungen, die bisher keine häufigen Postenets verwendet haben, können nun leicht erweitert werden, um diese Funktionalität nutzen zu können. Häufige Itemsets-Analyse wird mit dem PLSQL-Paket DBMSFREQUENTITEMSETS durchgeführt. Weitere Informationen finden Sie unter Oracle Database PLSQL Packages and Types Reference. Darüber hinaus gibt es ein Beispiel für häufige Postenets Verwendung in Häufigen Postenets. Weitere statistische Funktionen Oracle stellt eine Reihe statistischer SQL-Funktionen und ein Statistikpaket, DBMSSTATFUNCS, vor. In diesem Abschnitt werden einige der neuen Funktionen zusammen mit der grundlegenden Syntax aufgelistet. Beschreibende Statistik Sie können folgende deskriptive Statistik berechnen: Median eines Datensatzes Sie können folgende parametrische Statistik berechnen: Spearmans rho Koeffizient Kendalls tau-b Koeffizient Zusätzlich zu den Funktionen verfügt diese Version über ein neues PLSQL-Paket, DBMSSTATFUNCS. Es enthält die beschreibende statistische Funktion ZUSAMMENFASSUNG zusammen mit Funktionen zur Unterstützung der Verteilungsanpassung. Die SUMMARY-Funktion fasst eine numerische Spalte einer Tabelle mit einer Vielzahl von deskriptiven Statistiken zusammen. Die fünf Verteilungsanpassungsfunktionen unterstützen normale, einheitliche, Weibull-, Poisson - und Exponentialverteilungen. WIDTHBUCKET-Funktion Die WIDTHBUCKET-Funktion gibt für einen gegebenen Ausdruck die Bucket-Nummer zurück, nach der das Ergebnis dieses Ausdrucks zugewiesen wird. Sie können mit dieser Funktion Gleichheitshistogramme erzeugen. Equiwidth-Histogramme unterteilen Datensätze in Buckets, deren Intervallgröße (höchster Wert zum niedrigsten Wert) gleich ist. Die Anzahl der Zeilen, die von jedem Eimer gehalten werden, variiert. Eine verwandte Funktion, NTILE. Schafft gleich große Schaufeln. Equiwidth-Histogramme können nur für numerische, Datums - oder Datetime-Typen erzeugt werden. Die ersten drei Parameter sollten also alle numerischen Ausdrücke oder alle Datumsausdrücke sein. Andere Ausdrücke sind nicht zulässig. Wenn der erste Parameter NULL ist. Das Ergebnis ist NULL. Wenn der zweite oder dritte Parameter NULL ist. Wird eine Fehlermeldung zurückgegeben, da ein NULL-Wert keinen Endpunkt (oder einen beliebigen Punkt) für einen Bereich in einer Datums - oder numerischen Wertdimension angibt. Der letzte Parameter (Anzahl der Buckets) sollte ein numerischer Ausdruck sein, der einen positiven Integerwert 0, NULL auswertet. Oder ein negativer Wert führt zu einem Fehler. Die Buckets sind von 0 bis (n 1) nummeriert. Bucket 0 enthält die Anzahl der Werte, die kleiner als das Minimum sind. Bucket (n 1) enthält die Anzahl der Werte, die größer oder gleich dem maximalen angegebenen Wert sind. WIDTHBUCKET Syntax Das WIDTHBUCKET nimmt vier Ausdrücke als Parameter an. Der erste Parameter ist der Ausdruck, für den das equiwidth-Histogramm gilt. Der zweite und dritte Parameter sind Ausdrücke, die die Endpunkte des akzeptablen Bereichs für den ersten Parameter bezeichnen. Der vierte Parameter bezeichnet die Anzahl der Buckets. Betrachten Sie die folgenden Daten von Tischkunden. Dass die Kreditlimiten von 17 Kunden zeigt. Diese Daten werden in der Abfrage in Beispiel 21-20 gesammelt. In der Tabelle Kunden. Die Spalte custcreditlimit enthält Werte zwischen 1500 und 15000, und wir können die Werte zu vier Equiwidth Buckets, nummeriert von 1 bis 4, mit WIDTHBUCKET (custcreditlimit, 0, 20000, 4) zuweisen. Idealerweise ist jede Schaufel ein geschlossenes Intervall der reellen Zahlenlinie, z. B. ist die Schaufelzahl 2 Scores zwischen 5000.0000 und 9999.9999 zugeordnet. Manchmal mit 5000, 10000 bezeichnet), um anzuzeigen, daß 5.000 in dem Intervall enthalten sind und 10.000 ausgeschlossen sind. Um Werte außerhalb des Bereichs 0, 20.000 zu erfassen, werden Werte kleiner als 0 einer bezeichneten Unterlaufschaufel mit der Nummer 0 zugewiesen und Werte größer oder gleich 20.000 einer bezeichneten Überlaufschaufel zugeordnet, die mit 5 (num Schaufeln 1 Im Algemeinen). Siehe Abbildung 21-3 für eine grafische Darstellung, wie die Buckets zugeordnet sind. Abbildung 21-3 Bucket-Zuweisungen Sie können die Schranken in umgekehrter Reihenfolge angeben, zB WIDTHBUCKET (custcreditlimit 20000. 0. 4). Wenn die Schranken umgekehrt werden, sind die Buckets offen-geschlossene Intervalle. In diesem Beispiel ist die Schaufelzahl 1 (15000,20000, die Schaufelzahl 2 ist (10000,15000 und die Schaufelzahl 4, (0, 5000. Die Überlaufschaufel wird mit 0 (20000. unendlich) und die Unterlaufschaufel numeriert Beispiel 21-20 WIDTHBUCKET In der folgenden Abfrage werden die Bucket-Nummern für die Kreditlimiten in der Kundentabelle für beide Fälle angezeigt, in denen die Option " Benutzerdefinierte Aggregatfunktionen Oracle bietet eine Möglichkeit zur Erstellung eigener Funktionen, sogenannte benutzerdefinierte Aggregatfunktionen, die in Programmiersprachen wie PLSQL geschrieben werden , Java und C und können als analytische Funktionen oder Aggregate in materialisierten Ansichten verwendet werden Weitere Informationen zu Syntax und Einschränkungen finden Sie im Oracle Database Data Cartridge Developers Guide Die Vorteile dieser Funktionen sind: Hochkomplexe Funktionen können mit einer vollständigen Programmierung programmiert werden Verfahrenssprache. Höhere Skalierbarkeit als andere Techniken, wenn benutzerdefinierte Funktionen für die Parallelverarbeitung programmiert werden. Objektdatentypen können verarbeitet werden. Als einfaches Beispiel für eine benutzerdefinierte Aggregatfunktion ist die Skew-Statistik zu betrachten. Diese Berechnung mißt, wenn ein Datensatz eine schiefe Verteilung um seinen Mittelwert hat. Es wird Ihnen sagen, wenn ein Schwanz der Verteilung ist deutlich größer als die anderen. Wenn Sie ein benutzerdefiniertes Aggregat namens udskew erstellt und auf die Kreditlimitdaten des vorherigen Beispiels angewendet haben, können die SQL-Anweisung und die Ergebnisse wie folgt aussehen: Bevor Sie benutzerdefinierte Aggregatfunktionen erstellen, sollten Sie prüfen, ob Ihre Anforderungen erfüllt werden können In regelmäßigen SQL. Viele komplexe Berechnungen sind direkt in SQL möglich, insbesondere durch Verwendung des CASE-Ausdrucks. Der Aufenthalt mit regulärem SQL ermöglicht eine einfachere Entwicklung, und viele Abfrageoperationen sind in SQL bereits gut parallelisiert. Selbst das frühere Beispiel, die Skew-Statistik, kann mit Standard, wenn auch langwierig, SQL erstellt werden. CASE-Ausdrücke Oracle unterstützt jetzt einfache und durchsuchte CASE-Anweisungen. CASE-Anweisungen sind ähnlich im Sinne der DECODE-Anweisung, aber sie bieten mehr Flexibilität und logische Macht. Sie sind auch leichter zu lesen als herkömmliche DECODE-Anweisungen und bieten auch eine bessere Leistung. Sie werden häufig beim Brechen von Kategorien in Eimer wie Alter (z. B. 20-29, 30-39, und so weiter) verwendet. Die Syntax für einfache CASE-Anweisungen lautet: Einfache CASE-Ausdrücke testen, ob der expr - Wert gleich dem comparisonexpr ist. Die Syntax für gesuchte CASE-Anweisungen lautet: Sie können jede Art von Bedingung in einem gesuchten CASE-Ausdruck verwenden, nicht nur einen Gleichheitstest. Sie können nur 255 Argumente und jedes WHEN angeben. DANN Paar zählt als zwei Argumente. Um diese Grenze zu überschreiten, können Sie CASE-Ausdrücke verschachteln, so dass das returnexpr selbst ein CASE-Ausdruck ist. Beispiel 21-21 CASE Angenommen, Sie wollten das durchschnittliche Gehalt aller Mitarbeiter im Unternehmen finden. Wenn ein Mitarbeitergehalt weniger als 2000 ist, möchten Sie, dass die Abfrage 2000 stattdessen verwendet. Ohne CASE-Anweisung können Sie diese Abfrage folgendermaßen schreiben: Beachten Sie, dass dies gegen das hr-Beispielschema läuft. In diesem Fall ist foo eine Funktion, die ihre Eingabe zurückgibt, wenn die Eingabe größer als 2000 ist und gibt ansonsten 2000 zurück. Die Abfrage hat Auswirkungen auf die Leistung, da sie eine Funktion für jede Zeile aufrufen muss. Das Schreiben von benutzerdefinierten Funktionen kann auch die Entwicklungslast hinzufügen. Mit CASE-Ausdrücken in der Datenbank ohne PLSQL kann diese Abfrage umgeschrieben werden als: Mit einem CASE-Ausdruck können Sie vermeiden, benutzerdefinierte Funktionen zu entwickeln und können auch schneller ausgeführt werden. Beispiel 21-22 CASE für die Aggregation von Independent Subsets Die Verwendung von CASE innerhalb von Aggregatfunktionen ist eine bequeme Möglichkeit, Aggregate auf mehreren Teilmengen von Daten auszuführen, wenn eine einfache GROUP BY nicht ausreicht. Beispielsweise könnte das vorhergehende Beispiel mehrere AVG-Spalten in seiner SELECT-Liste enthalten haben, jedes mit seinem eigenen CASE-Ausdruck. Vielleicht haben wir eine Abfrage finden die durchschnittliche Gehalt für alle Mitarbeiter in den Gehaltsspannen 0-2000 und 2000-5000 gehabt haben. Es würde folgendermaßen aussehen: Obwohl diese Abfrage die Aggregate unabhängiger Untermengen-Daten in separate Spalten platziert, können wir die Aggregate als Zeilen einer einzelnen Spalte anzeigen, indem wir der GROUP BY-Klausel einen CASE-Ausdruck hinzufügen. Der nächste Abschnitt zeigt die Flexibilität dieses Ansatzes mit zwei Ansätzen zur Erstellung von Histogrammen mit CASE. Erstellen von Histogrammen mit benutzerdefinierten Buckets Sie können die CASE-Anweisung verwenden, wenn Sie Histogramme mit benutzerdefinierten Buckets (sowohl in der Anzahl der Buckets als auch in der Breite der einzelnen Bucket) erhalten möchten. Es folgen zwei Beispiele für Histogramme, die mit CASE-Anweisungen erstellt wurden. Im ersten Beispiel werden die Histogramm-Summen in mehreren Spalten angezeigt und eine einzelne Zeile wird zurückgegeben. Im zweiten Beispiel wird das Histogramm mit einer Label-Spalte und einer einzelnen Spalte für Summen dargestellt, und mehrere Zeilen werden zurückgegeben. Beispiel 21-23 Histogramm Beispiel 1 Beispiel 21-24 Histogramm Beispiel 2 Datenverdichtung für die Meldung Daten werden normalerweise in spärlicher Form gespeichert. Das heißt, wenn kein Wert für eine gegebene Kombination von Dimensionswerten existiert, existiert keine Zeile in der Faktentabelle. Sie können die Daten jedoch in dichter Form anzeigen, wobei Zeilen für alle Kombinationen von Bemaßungswerten angezeigt werden, auch wenn keine Faktendaten für sie vorhanden sind. Wenn beispielsweise ein Produkt während eines bestimmten Zeitraums nicht verkauft wurde, können Sie das Produkt weiterhin für diesen Zeitraum sehen, wenn der Verkaufswert daneben liegt. Darüber hinaus können Zeitreihenberechnungen am einfachsten durchgeführt werden, wenn Daten entlang der Zeitdimension dicht sind. Dies liegt daran, dass dichte Daten eine konsistente Anzahl von Zeilen für jede Periode füllen, was wiederum es einfach macht, die analytischen Fensterfunktionen mit physischen Offsets zu verwenden. Datenverdichtung ist der Prozess der Umwandlung von spärlichen Daten in dichte Form. Um das Problem der Sparsität zu überwinden, können Sie eine partitionierte äußere Verknüpfung verwenden, um die Lücken in einer Zeitreihe oder einer anderen Dimension zu füllen. Eine solche Verknüpfung erweitert die herkömmliche äußere Verknüpfungssyntax, indem die äußere Verknüpfung auf jede in einer Abfrage definierte logische Partition angewendet wird. Oracle partitioniert die Zeilen in Ihrer Abfrage auf der Grundlage des in der PARTITION BY-Klausel angegebenen Ausdrucks logisch. Das Ergebnis einer partitionierten äußeren Verknüpfung ist eine UNION der äußeren Verknüpfungen von jeder der Partitionen in der logisch partitionierten Tabelle mit der Tabelle auf der anderen Seite der Verknüpfung. Beachten Sie, dass Sie diese Art von Verknüpfung verwenden können, um die Lücken in beliebigen zu füllen Dimension, nicht nur die Zeitdimension. Die meisten Beispiele beziehen sich hier auf die Zeitdimension, da sie die am häufigsten verwendete Dimension für Vergleiche ist. Partition Join Syntax Die Syntax für partitionierte äußere Verknüpfung erweitert die ANSI SQL JOIN-Klausel um den Ausdruck PARTITION BY, gefolgt von einer Ausdrucksliste. Die Ausdrücke in der Liste geben die Gruppe an, auf die die äußere Verknüpfung angewendet wird. Im Folgenden finden Sie die beiden Syntaxformen, die normalerweise für partitionierte äußere Verknüpfungen verwendet werden: Beachten Sie, dass FULL OUTER JOIN nicht mit einer partitionierten äußeren Verknüpfung unterstützt wird. Beispiel von Sparse-Daten Eine typische Situation mit einer spärlichen Dimension wird im folgenden Beispiel gezeigt, das die wöchentlichen Verkäufe und den Jahresabschluss für das Produkt Bounce für die Wochen 20-30 in den Jahren 2000 und 2001 berechnet Erwarten würde 22 Reihen von Daten (11 Wochen jeweils von 2 Jahren), wenn die Daten waren dicht. Allerdings erhalten wir nur 18 Zeilen, weil die Wochen 25 und 26 im Jahr 2000 und die Wochen 26 und 28 im Jahr 2001 fehlen. Füllen von Datenlücken Wir können die spärlichen Daten der vorhergehenden Abfrage nehmen und eine partitionierte äußere Verknüpfung mit einem dichten Satz von Zeit durchführen Daten. In der folgenden Abfrage übergeben wir unsere ursprüngliche Abfrage als v und wir wählen Daten aus der Zeittabelle, die wir alias als t. Hier werden 22 Zeilen abgerufen, da es keine Lücken in der Reihe gibt. Die vier hinzugefügten Zeilen haben jeweils 0, wenn der Verkaufswert auf 0 gesetzt wird, indem die NVL-Funktion verwendet wird. Beachten Sie, dass in dieser Abfrage eine WHERE-Bedingung für Wochen zwischen 20 und 30 in der Inline-Ansicht für die Zeitdimension platziert wurde. Dies wurde eingeführt, um die Ergebnismenge klein zu halten. Füllen von Lücken in zwei Dimensionen N-dimensionale Daten werden typischerweise als ein dichter zweidimensionaler Querstreifen von (n - 2) Seitenabmessungen angezeigt. Dies erfordert, dass alle Bemaßungswerte für die beiden Dimensionen, die in der Quer-Registerkarte angezeigt werden, eingegeben werden. Im Folgenden ist ein anderes Beispiel, bei dem die partitionierte äußere Verknüpfungsfunktion zum Füllen der Lücken auf zwei Dimensionen verwendet werden kann: In dieser Abfrage die WITH-Unterabfrage Factoring-Klausel V1 fasst die Umsatzdaten auf Produkt-, Länder - und Jahresebene zusammen. Dieses Ergebnis ist spärlich, aber Benutzer können alle Länder-, Jahr-Kombinationen für jedes Produkt sehen. Um dies zu erreichen, nehmen wir jede Partition von v1 basierend auf Produktwerten und äußere verbinden sie auf der Land-Dimension zuerst. Dieses gibt uns alle Werte des Landes für jedes Produkt. Wir nehmen dann dieses Ergebnis und partitionieren es auf Produkt-und Landeswerte und dann äußere verbinden sie auf Zeitdimension. Damit erhalten Sie für jede Produkt - und Länderkombination alle Zeitwerte. Füllen von Lücken in einer Inventartabelle In einer Inventartabelle wird typischerweise eine Menge von Einheiten angezeigt, die für verschiedene Produkte verfügbar sind. Diese Tabelle ist spärlich: sie speichert nur eine Zeile für ein Produkt, wenn es ein Ereignis gibt. Für eine Verkaufstabelle ist die Veranstaltung ein Verkauf, und für die Inventartabelle ist das Ereignis eine Mengenänderung, die für ein Produkt verfügbar ist. Beachten Sie zum Beispiel die folgende Inventartabelle: Die Inventartabelle verfügt nun über die folgenden Zeilen: Für Berichtszwecke können Benutzer diese Inventardaten anders sehen. Sie können z. B. alle Werte der Zeit für jedes Produkt sehen. Dies kann durch partitionierte äußere Verknüpfung erreicht werden. Außerdem können Benutzer für die neu eingefügten Zeilen fehlender Zeitperioden die Werte für Mengeneinheitsspalten sehen, die von der aktuellsten vorhandenen Zeitdauer übertragen werden sollen. Letzteres kann mit dem analytischen Fensterfunktion-LASTVALUE-Wert erreicht werden. Hier ist die Abfrage und die gewünschte Ausgabe: Die innere Abfrage berechnet eine partitionierte äußere Verknüpfung auf Zeit innerhalb jedes Produkts. Die innere Abfrage verdichtet die Daten auf der Zeitdimension (dh die Zeitdimension hat nun eine Zeile für jeden Wochentag). Für die neu hinzugefügten Zeilen hat die Maßsäulenmenge jedoch Nullen (siehe die Ausgabe in der Spaltenmenge in den folgenden Ergebnissen: Die äußere Abfrage verwendet die analytische Funktion LASTVALUE.) Bei Anwendung dieser Funktion werden die Daten nach Produkt partitioniert und die Daten auf der (Timeid) Für jede Zeile findet die Funktion aufgrund der Option IGNORE NULLS, die Sie sowohl mit LASTVALUE als auch mit FIRSTVALUE verwenden können, den letzten Nicht-Nullwert im Fenster. In der Spalte repequentity sehen wir die gewünschte Ausgabe Die folgende Ausgabe: Berechnen von Datenwerten zum Füllen von Lücken Beispiele im vorherigen Abschnitt veranschaulichen, wie partitionierte äußere Verknüpfung verwendet wird, um Lücken in einer oder mehreren Dimensionen zu füllen. Die Ergebnismengen, die durch partitionierte äußere Verknüpfung erzeugt werden, haben jedoch Nullwerte für Spalten, die nicht in enthalten sind Die PARTITION BY-Liste, die normalerweise analytische SQL-Funktionen verwenden, um diese Nullwerte durch einen Nicht-Nullwert zu ersetzen. Beispielsweise berechnet die folgende Abfrage monatliche Summen für Produkte 64 MB Speicherkarte und DVD-R Discs (Produkt-IDs 122 und 136) für das Jahr 2000. Es verwendet partitionierte äußere Verknüpfung, um Daten für alle Monate zu verdichten. Für die fehlenden Monate wird dann die analytische SQL-Funktion AVG verwendet, um die Umsätze und Einheiten als den Durchschnitt der Monate zu errechnen, in denen das Produkt verkauft wurde. Wenn Sie in SQLPlus arbeiten, werden die folgenden beiden Befehle die Spaltenüberschriften umreißen, um die Ergebnisse besser lesbar zu machen: Zeitreihenberechnungen für Densified Data Densificatio n ist nicht nur für Berichtszwecke gedacht. Es ermöglicht auch bestimmte Berechnungsarten, insbesondere Zeitreihenberechnungen. Zeitreihenberechnungen sind einfacher, wenn Daten entlang der Zeitdimension dicht sind. Dichte Daten weisen eine konsistente Anzahl von Zeilen für jede Zeitperiode auf, die es wiederum einfach machen, analytische Fensterfunktionen mit physikalischen Offsets zu verwenden. Um dies zu illustrieren, nehmen wir zunächst das Beispiel zum Füllen von Lücken in Daten. Und fügen Sie eine analytische Funktion zu dieser Abfrage hinzu. In der folgenden erweiterten Version berechnen wir wöchentlich jährliche Verkäufe neben dem wöchentlichen Verkauf. Die NULL-Werte, die die partitionierte äußere Verknüpfung bei der Herstellung der Zeitreihen dicht einfügt, werden auf die übliche Weise behandelt: Die SUM-Funktion behandelt sie als 0s. Period-to-Period-Vergleich für eine Zeitstufe: Beispiel Wie verwenden wir diese Funktion, um Werte über Zeiträume hinweg zu vergleichen. Im Einzelnen: Wie berechnen wir einen Vergleichsvergleich im Vergleich zum Vorjahr auf der Wochesebene Die folgende Abfrage gibt dieselbe Zeile zurück , Für jedes Produkt den Jahresabschluss für jede Woche von 2001 mit dem von 2000. Beachten Sie, dass wir in diesem Beispiel mit einer WITH-Klausel beginnen. Dies verbessert die Lesbarkeit der Abfrage und lässt uns auf den partitionierten äußeren Join fokussieren. Wenn Sie in SQLPlus arbeiten, wickelt der folgende Befehl die Spaltenüberschriften für eine bessere Lesbarkeit der Ergebnisse: In der FROM-Klausel der Inline-Ansicht densesales. Verwenden wir eine partitionierte äußere Verknüpfung der Aggregatansicht v und der Zeitansicht t, um Lücken in den Verkaufsdaten entlang der Zeitdimension zu füllen. Die Ausgabe der partitionierten äußeren Verknüpfung wird dann durch die analytische Funktion SUM verarbeitet. OVER, um den wöchentlichen Jahresabschluss zu berechnen (die weeklyytdsales Spalte). So berechnet die Ansicht densesales die Jahresabschlussdaten für jede Woche, einschließlich der fehlenden Summenansichten. Die Inline-Ansicht yearoveryearsales berechnet dann den vorjährigen wöchentlichen Jahresabschluss mit der LAG-Funktion. Die LAG-Funktion weeklyytdsalesprioryear gibt eine PARTITION BY-Klausel an, die Zeilen für die gleiche Woche der Jahre 2000 und 2001 in einer einzigen Partition paaren. Wir übergeben dann einen Offset von 1 an die LAG-Funktion, um das wöchentliche Jahr zu erhalten, bis dato Verkäufe für das vorherige Jahr. Der äußerste Abfrageblock wählt Daten von yearoveryearsales mit der Bedingung yr 2001. und so gibt die Abfrage für jedes Produkt seine wöchentliche zurück Jahr-to-date-Umsatz in den angegebenen Wochen der Jahre 2001 und 2000. Period-to-Period Vergleich für mehrere Zeitstufen: Beispiel Während das vorherige Beispiel zeigt uns einen Weg, um Vergleiche für ein einziges Zeitniveau zu schaffen, wäre es noch mehr Nützlich, um mehrere Zeitniveaus in einer einzelnen Abfrage zu behandeln. Zum Beispiel könnten wir den Umsatz gegenüber dem Vorjahreszeitraum auf Jahr, Quartal, Monat und Tagestufe vergleichen. Wie können wir eine Abfrage erstellen, die einen jährlichen Vergleich des Jahresabschlusses für alle Ebenen unserer Zeithierarchie durchführt Wir werden mehrere Schritte zur Ausführung dieser Aufgabe durchführen. Das Ziel ist eine einzelne Abfrage mit Vergleichen am Tag, Woche, Monat, Quartal und Jahr. Die Schritte sind wie folgt: Wir erstellen eine Ansicht namens cubeprodtime. Die einen hierarchischen Würfel von Umsatz aggregiert über Zeiten und Produkte hält. Dann erstellen wir eine Ansicht der Zeitdimension, die als Kante des Würfels verwendet werden soll. Die Zeitkante, die einen vollständigen Satz von Daten enthält, wird partitioniert mit den spärlichen Daten in der View cubeprodtime verbunden. Schließlich, für maximale Leistung, erstellen wir eine materialisierte Ansicht, mvprodtime. Gebaut mit der gleichen Definition wie cubeprodtime. Weitere Informationen zu hierarchischen Cubes finden Sie unter Kapitel 20, SQL für Aggregation in Data Warehouses. Die materialisierte Ansicht wird in Schritt 1 im folgenden Abschnitt definiert. Schritt 1 Erstellen Sie die hierarchische Cube-Ansicht Die materialisierte Ansicht, die im Folgenden dargestellt wird, kann in Ihrem System vorhanden sein, wenn nicht, erstellen Sie sie jetzt. Wenn Sie es generieren müssen, beachten Sie, dass wir die Abfrage auf nur zwei Produkte beschränken, um die Bearbeitungszeit kurz zu halten: Da diese Ansicht auf zwei Produkte beschränkt ist, gibt sie knapp über 2200 Zeilen zurück. Beachten Sie, dass die Spalte HierarchicalTime Stringdarstellungen von Zeit aus allen Ebenen der Zeithierarchie enthält. Der CASE-Ausdruck, der für die HierarchicalTime-Spalte verwendet wird, fügt eine Markierung (0, 1.) jeder Datumszeichenfolge hinzu, um die Zeitstufe des Wertes anzugeben. A 0 steht für das Jahr, 1 für Quartale, 2 für Monate und 3 für Tag. Beachten Sie, dass die GROUP BY-Klausel eine verkettete ROLLUP ist, die die Rollup-Hierarchie für die Zeit - und Produktdimensionen angibt. Die GROUP BY-Klausel bestimmt den hierarchischen Cube-Inhalt. Schritt 2 Erstellen Sie die Ansicht edgetime, die eine vollständige Menge von Datumswerten ist Edgetime ist die Quelle für das Ausfüllen von Zeitlücken im hierarchischen Cube mit einem partitionierten äußeren Join. Die Spalte HierarchicalTime in edgetime wird in einer partitionierten Verknüpfung mit der Spalte HierarchicalTime in der View cubeprodtime verwendet. Die folgende Anweisung definiert edgetime: Schritt 3 Erstellen der materialisierten Ansicht mvprodtime zur Unterstützung schnellerer Performance Die materialisierte Viewdefinition ist ein Duplikat der zuvor definierten View cubeprodtime. Da es sich um eine doppelte Abfrage handelt, werden Verweise auf cubeprodtime neu geschrieben, um die materialisierte Ansicht mvprodtime zu verwenden. Die folgenden Materialisierungen können in Ihrem System vorhanden sein, wenn nicht, erstellen Sie es jetzt. Wenn Sie es generieren müssen, beachten Sie bitte, dass wir die Abfrage auf nur zwei Produkte beschränken, um die Bearbeitungszeit kurz zu halten. Schritt 4 Erstellen der Vergleichsabfrage Wir haben nun die Stufe für unsere Vergleichsabfrage gesetzt. Wir können Periodenvergleichsberechnungen auf allen Zeitniveaus erhalten. Es erfordert das Anwenden von analytischen Funktionen auf einen hierarchischen Würfel mit dichten Daten entlang der Zeitdimension. Einige der Berechnungen, die wir für jedes Zeitniveau erreichen können, sind: Summe der Verkäufe für vorherigen Zeitraum auf allen Zeitniveaus. Umsatzabweichung gegenüber Vorjahr. Summe des Umsatzes im gleichen Zeitraum vor einem Jahr auf allen Ebenen der Zeit. Umsatzveränderung im Vergleich zum Vorjahreszeitraum. Im folgenden Beispiel werden alle vier Berechnungen durchgeführt. Es verwendet eine partitionierte äußere Verknüpfung der Ansichten cubeprodtime und edgetime, um eine Inline-Ansicht von dichten Daten namens densecubeprodtime zu erstellen. Die Abfrage verwendet dann die LAG-Funktion auf dieselbe Weise wie das vorherige einstufige Beispiel. Die äußere WHERE-Klausel gibt die Zeit auf drei Ebenen an: die Tage August 2001, den gesamten Monat und das gesamte dritte Quartal 2001. Beachten Sie, dass die letzten beiden Zeilen der Ergebnisse die Monats - und Quartalsniveau-Aggregationen enthalten Die Ergebnisse leichter zu lesen, wenn Sie SQLPlus verwenden, sollten die Spaltenüberschriften mit den folgenden Befehlen angepasst werden. 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. Scripting on this page enhances content navigation, but does not change the content in any way.22 SQL for Analysis and Reporting Oracle has enhanced SQLs analytical processing capabilities by introducing a new family of analytic SQL functions. These analytic functions enable you to calculate: Rankings and percentiles Moving window calculations Linear regression statistics Ranking functions include cumulative distributions, percent rank, and N-tiles. Moving window calculations allow you to find moving and cumulative aggregations, such as sums and averages. Laglead analysis enables direct inter-row references so you can calculate period-to-period changes. Firstlast analysis enables you to find the first or last value in an ordered group. Other enhancements to SQL include the CASE expression and partitioned outer join. CASE expressions provide if-then logic useful in many situations. Partitioned outer join is an extension to ANSI outer join syntax that allows users to selectively densify certain dimensions while keeping others sparse. This allows reporting tools to selectively densify dimensions, for example, the ones that appear in their cross-tabular reports while keeping others sparse. To enhance performance, analytic functions can be parallelized: multiple processes can simultaneously execute all of these statements. These capabilities make calculations easier and more efficient, thereby enhancing database performance, scalability, and simplicity. Analytic functions are classified as described in Table 22-1 . Table 22-1 Analytic Functions and Their Uses To perform these operations, the analytic functions add several new elements to SQL processing. These elements build on existing SQL to allow flexible and powerful calculation expressions. With just a few exceptions, the analytic functions have these new elements. The processing flow is represented in Figure 22-1 . Figure 22-1 Processing Order The essential concepts used in analytic functions are: Query processing using analytic functions takes place in three stages. First, all joins, WHERE. GROUP BY and HAVING clauses are performed. Second, the result set is made available to the analytic functions, and all their calculations take place. Third, if the query has an ORDER BY clause at its end, the ORDER BY is processed to allow for precise output ordering. The processing order is shown in Figure 22-1 . Result set partitions The analytic functions allow users to divide query result sets into groups of rows called partitions. Note that the term partitions used with analytic functions is unrelated to the table partitions feature. Throughout this chapter, the term partitions refers to only the meaning related to analytic functions. Partitions are created after the groups defined with GROUP BY clauses, so they are available to any aggregate results such as sums and averages. Partition divisions may be based upon any desired columns or expressions. A query result set may be partitioned into just one partition holding all the rows, a few large partitions, or many small partitions holding just a few rows each. For each row in a partition, you can define a sliding window of data. This window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time. The window has a starting row and an ending row. Depending on its definition, the window may move at one or both ends. For instance, a window defined for a cumulative sum function would have its starting row fixed at the first row of its partition, and its ending row would slide from the starting point all the way to the last row of the partition. In contrast, a window defined for a moving average would have both its starting and end points slide so that they maintain a constant physical or logical range. A window can be set as large as all the rows in a partition or just a sliding window of one row within a partition. When a window is near a border, the function returns results for only the available rows, rather than warning you that the results are not what you want. When using window functions, the current row is included during calculations, so you should only specify ( n -1) when you are dealing with n items. Each calculation performed with an analytic function is based on a current row within a partition. The current row serves as the reference point determining the start and end of the window. For instance, a centered moving average calculation could be defined with a window that holds the current row, the six preceding rows, and the following six rows. This would create a sliding window of 13 rows, as shown in Figure 22-2 . Figure 22-2 Sliding Window Example Ranking, Windowing, and Reporting Functions This section illustrates the basic analytic functions for ranking, windowing, and reporting. Sample Linear Regression Calculation In this example, we compute an ordinary-least-squares regression line that expresses the quantity sold of a product as a linear function of the products list price. The calculations are grouped by sales channel. The values SLOPE. INTCPT. RSQR are slope, intercept, and coefficient of determination of the regression line, respectively. The (integer) value COUNT is the number of products in each channel for whom both quantity sold and list price data are available. Statistical Aggregates Oracle provides a set of SQL statistical functions and a statistics package, DBMSSTATFUNCS. This section lists some of the new functions along with basic syntax. 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 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. User-Defined Aggregates 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 Database 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 data types 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. Pivoting Operations The d ata returned by business intelligence queries is often most usable if presented in a crosstabular format. The pivotclause of the SELECT statement lets you write crosstabulation queries that rotate rows into columns, aggregating data in the process of the rotation. Pivoting is a key technique in data warehouses. In it, you transform multiple rows of input into fewer and generally wider rows in the data warehouse. When pivoting, an aggregation operator is applied for each item in the pivot column value list. The pivot column cannot contain an arbitrary expression. If you need to pivot on an expression, then you should alias the expression in a view before the PIVOT operation. The basic syntax is as follows: To illustrate the use of pivoting, create the following view as a basis for later examples: Example: Pivoting The following statement illustrates a typical pivot on the channel column: Note that the output has created four new aliased columns, DIRECTSALES. INTERNETSALES. CATALOGSALES. and TELESALES. one for each of the pivot values. The output is a sum. If no alias is provided, the column heading will be the values of the IN - list. Pivoting on Multiple Columns You can pivot on more than one column. The following statement illustrates a typical multiple column pivot: Note that this example specifies a multi-column IN - list with column headings designed to match the IN - list members. Pivoting: Multiple Aggregates You can pivot with multiple aggregates, as shown in the following example: Note that the query creates column headings by concatenating the pivot values (or alias) with the alias of the aggregate function, plus an underscore. Distinguishing PIVOT-Generated Nulls from Nulls in Source Data You can distinguish between null values that are generated from the use of PIVOT and those that exist in the source data. The following example illustrates nulls that PIVOT generates. The following query returns rows with 5 columns, column prodid. and pivot resulting columns Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. For each unique value of prodid. Q1COUNTTOTAL returns the total number of rows whose qtr value is Q1. that is, and Q2COUNTTOTAL returns the total number of rows whose qtr value is Q2 . Assume we have a table sales2 of the following structure: From the result, we know that for prodid 100, there are 2 sales rows for quarter Q1. and 1 sales row for quarter Q2 for prodid 200, there is 1 sales row for quarter Q1. and no sales row for quarter Q2.So, in Q2COUNTTOTAL. you can identify that NULLlt1gt comes from a row in the original table whose measure is of null value, while NULLlt2gt is due to no row being present in the original table for prodid 200 in quarter Q2 . Unpivoting Operations An unpivot does not reverse a PIVOT operation. Instead, it rotates data from columns into rows. If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means. To illustrate unpivoting, first create a pivoted table that includes four columns, for quarters of the year: The tables contents resemble the following: The following UNPIVOT operation rotates the quarter columns into rows. For each product, there will be four rows, one for each quarter. Note the use of INCLUDE NULLS in this example. You can also use EXCLUDE NULLS. which is the default setting. In addition, you can also unpivot using two columns, as in the following: Wildcard and Subquery Pivoting with XML Operations If you want to use a wildcard argument or subquery in your pivoting columns, you can do so with PIVOT XML syntax. With PIVOT XML, the output of the operation is properly formatted XML. The following example illustrates using the wildcard keyword, ANY. It outputs XML that includes all channel values in salesview : Note that the keyword ANY is available in PIVOT operations only as part of an XML operation. This output includes data for cases where the channel exists in the data set. Also note that aggregation functions must specify a GROUP BY clause to return multiple values, yet the pivotclause does not contain an explicit GROUP BY clause. Instead, the pivotclause performs an implicit GROUP BY . The following example illustrates using a subquery. It outputs XML that includes all channel values and the sales data corresponding to each channel: The output densifies the data to include all possible channels for each product. 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 sparse 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 subquery 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 wraps 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, let us 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 wraps the column headings for greater readability of results: In the FROM clause of the inline 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 inline 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 21, SQL for Aggregation in Data Warehouses. The materialized view is defined in Step 1 in the following section. 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, 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, 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 inline 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 that 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 analytical SQL 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 23, 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. Miscellaneous Analysis and Reporting Capabilities This section illustrates the following additional analytic capabilities: 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 22-24 . 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 22-3 for a graphical illustration of how the buckets are assigned. Figure 22-3 Bucket Assignments 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. Example 22-24 WIDTHBUCKET 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. Linear Algebra Linear algebra is a branch of mathematics with a wide range of practical applications. Many areas have tasks that can be expressed using linear algebra, and here are some examples from several fields: statistics (multiple linear regression and principle components analysis), data mining (clustering and classification), bioinformatics (analysis of microarray data), operations research (supply chain and other optimization problems), econometrics (analysis of consumer demand data), and finance (asset allocation problems). Various libraries for linear algebra are freely available for anyone to use. Oracles UTLNLA package exposes matrix PLSQL data types and wrapper PLSQL subprograms for two of the most popular and robust of these libraries, BLAS and LAPACK. Linear algebra depends on matrix manipulation. Performing matrix manipulation in PLSQL in the past required inventing a matrix representation based on PLSQLs native data types and then writing matrix manipulation routines from scratch. This required substantial programming effort and the performance of the resulting implementation was limited. If developers chose to send data to external packages for processing rather than create their own routines, data transfer back and forth could be time consuming. Using the UTLNLA package lets data stay within Oracle, removes the programming effort, and delivers a fast implementation. Example 22-25 Linear Algebra Here is an example of how Oracles linear algebra support could be used for business analysis. It invokes a multiple linear regression application built using the UTLNLA package. The multiple regression application is implemented in an object called OLSRegression. Note that sample files for the OLS Regression object can be found in ORACLEHOMEplsqldemo . Consider the scenario of a retailer analyzing the effectiveness of its marketing program. Each of its stores allocates its marketing budget over the following possible programs: media advertisements ( media ), promotions ( promo ), discount coupons ( disct ), and direct mailers ( dmail ). The regression analysis builds a linear relationship between the amount of sales that an average store has in a given year ( sales ) and the spending on the four components of the marketing program. Suppose that the marketing data is stored in the following table: Then you can build the following sales-marketing linear model using coefficients: This model can be implemented as the following view, which refers to the OLS regression object: Using this view, a marketing program manager can perform an analysis such as Is this sales-marketing model reasonable for year 2004 data That is, is the multiple-correlation greater than some acceptable value, say, 0.9 The SQL for such a query might be as follows: You could also solve questions such as What is the expected base-line sales revenue of a store without any marketing programs in 2003 or Which component of the marketing program was the most effective in 2004 That is, a dollar increase in which program produced the greatest expected increase in sales See Oracle Database PLSQL Packages and Types Reference for further information regarding the use of the UTLNLA package and linear algebra. 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 CASE statements is: Simple CASE expressions test if the expr value equals the comparisonexpr . The syntax for searched CASE statements is: You can use any kind of condition in a searched CASE expression, not just an equality test. You can specify only 65,535 arguments and each WHEN. THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the returnexpr itself is a CASE expression. Example 22-26 CASE 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 might choose to write this query as follows: Note that this runs against the hr sample schema. 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. Example 22-27 CASE for Aggregating Independent Subsets Using CASE inside aggregate functions is a convenient way to perform aggregates on multiple subsets of data when a plain GROUP BY will not suffice. For instance, the preceding example could have included multiple AVG columns in its SELECT list, each with its own CASE expression. We might have had a query find the average salary for all employees in the salary ranges 0-2000 and 2000-5000. It would look like: Although this query places the aggregates of independent subsets data into separate columns, by adding a CASE expression to the GROUP BY clause we can display the aggregates as the rows of a single column. The next section shows the flexibility of this approach with two approaches to creating histograms with CASE . Creating Histograms 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 22-28 Histogram Example 1 Example 22-29 Histogram Example 2 Frequent Itemsets Instead of counting how often a given event occurs (for example, how often someone has purchased milk at the grocery), you may find it useful to count how often multiple events occur together (for example, how often someone has purchased both milk and cereal together at the grocery store). You can count these multiple events using what is called a frequent itemset, which is, as the name implies, a set of items. 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 practical motivation for using a frequent itemset is to find those itemsets that occur most often. If you analyze 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 itemset calculations are integrated with the database, operating on top of relational tables and accessed through SQL. This integration provides the following 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 Oracle Database PLSQL Packages and Types Reference for more information. In addition, there is an example of frequent itemset usage in Frequent itemsets . Scripting on this page enhances content navigation, but does not change the content in any way.
Comments
Post a Comment