Exponentiell Gleitender Durchschnitt Sql

6 Formatieren von SQLPlus-Reports Sie müssen den Bindestrich in Anführungszeichen einfügen. Andernfalls interpretiert SQLPlus den Bindestrich als Bindestrich, der angibt, dass Sie den Befehl auf einer anderen Zeile fortsetzen möchten. Formatieren von NUMBER-Spalten Wenn Sie NUMBER-Spalten anzeigen, können Sie entweder die standardmäßige Anzeigebreite von SQLPlus akzeptieren oder diese mit dem Befehl COLUMN ändern. Spätere Abschnitte beschreiben die Standardanzeige und wie können Sie sie mit dem Befehl COLUMN ändern. Das Formatmodell bleibt solange aktiv, bis Sie ein neues eingeben, das Spaltenformat mit dem SQLPlus-Format zurücksetzen oder diesen verlassen. Standardanzeige Eine NUMBER-Spaltenbreite entspricht der Breite der Überschrift oder der Breite des FORMAT plus ein Leerzeichen für das Vorzeichen. der größere Wert gilt. Wenn Sie nicht explizit FORMAT verwenden, dann ist die Spaltenbreite immer mindestens der Wert von SET NUMWIDTH. SQLPlus zeigt normalerweise Nummern mit so vielen Ziffern an, wie sie für die Genauigkeit erforderlich sind, bis zu einer Standardanzeigebreite, die durch den Wert der NUMWIDTH-Variablen des SET-Befehls (normalerweise 10) bestimmt wird. Wenn eine Zahl größer als der Wert von SET NUMWIDTH ist, runden SQLPlus die Zahl nach oben oder unten auf die maximal zulässige Anzahl von Zeichen, wenn möglich, oder zeigt Hashes an, wenn die Nummer zu groß ist. Sie können ein anderes Format für jede NUMBER-Spalte wählen, indem Sie ein Formatmodell in einem COLUMN-Befehl verwenden. Ein Formatmodell ist eine Darstellung der Art und Weise, wie die Ziffern in der Spalte erscheinen sollen, mit 9s, um Ziffern darzustellen. Ändern der Standardanzeige Der Befehl COLUMN identifiziert die Spalte, die Sie formatieren möchten, und das Modell, das Sie verwenden möchten, wie gezeigt: Verwenden Sie Formatmodelle, um Kommas, Dollarzeichen, spitze Klammern (um negative Werte) und führende Nullen zu Zahlen in einem hinzuzufügen Gegebene Spalte. Sie können auch die Werte auf eine vorgegebene Anzahl von Dezimalstellen runden, Minuszeichen rechts neben negativen Werten anzeigen (anstatt nach links) und Werte in exponentieller Schreibweise anzeigen. Um mehr als ein Formatmodell für eine einzelne Spalte zu verwenden, kombiniere die gewünschten Modelle in einem COLUMN-Befehl (siehe Beispiel 6-4). Siehe COLUMN für eine vollständige Liste der Formatmodelle und weitere Details. Beispiel 6-4 Formatieren einer NUMBER-Spalte Um SALARY mit einem Dollarzeichen anzuzeigen. ein Komma. Und die Ziffer Null anstelle eines Leerzeichens für beliebige Nullwerte, geben Sie den folgenden Befehl ein: Führen Sie nun die aktuelle Abfrage aus: Standardanzeige Die Standardbreite der Datentypspalten ist die Breite der Spalte in der Datenbank. Die Spaltenbreite eines LONG, CLOB, NCLOB oder XMLType ist standardmäßig auf den Wert von SET LONGCHUNKSIZE oder SET LONG, je nachdem, welcher Wert kleiner ist. Die Standardbreite und das Format der unformatierten DATE-Spalten in SQLPlus wird durch den Parameter NLSDATEFORMAT-Parameter bestimmt. Andernfalls ist die Standardformatbreite A9. Weitere Informationen zum Formatieren von DATE-Spalten finden Sie in der FORMAT-Klausel des COLUMN-Befehls. Linke Begründung ist die Voreinstellung für Datentypen. Ändern der Standardanzeige Sie können die angezeigte Breite eines Datentyps oder DATE ändern, indem Sie den Befehl COLUMN mit einem Formatmodell verwenden, das aus dem Buchstaben A (alphanumerisch) besteht, gefolgt von einer Zahl, die die Breite der Spalte in Zeichen darstellt. Wählen Sie im COLUMN-Befehl die Spalte, die Sie formatieren möchten, und das Modell, das Sie verwenden möchten: Wenn Sie eine Breite angeben, die kürzer als die Spaltenüberschrift ist. SQLPlus schneidet die Überschrift ab. Weitere Informationen finden Sie im COLUMN-Befehl. Beispiel 6-5 Formatieren einer Zeichensäule Um die Breite der Spalte LASTNAME auf vier Zeichen zu setzen und die aktuelle Abfrage erneut auszuführen, geben Sie ein. Wenn die WRAP-Variable des SET-Befehls auf ON gesetzt ist (der Standardwert), werden die Namen des Mitarbeiters an die Nächste Zeile nach dem vierten Zeichen, wie in Beispiel 6-5 gezeigt, Formatieren einer Zeichensäule. Wenn WRAP auf OFF gesetzt ist, werden die Namen nach dem vierten Zeichen abgeschnitten (abgeschnitten). Die Systemvariable WRAP steuert alle Spalten, die Sie die Einstellung von WRAP für eine gegebene Spalte über die WRAPPED überschreiben können. WORDWRAPPED Und TRUNCATED-Klauseln des COLUMN-Befehls. Weitere Informationen zu diesen Klauseln finden Sie im COLUMN-Befehl. Sie verwenden die WORDWRAPPED-Klausel von COLUMN später in diesem Kapitel. NCLOB - oder Multibyte-CLOB-Spalten können nicht mit der Option WORDWRAPPED formatiert werden. Wenn Sie eine NCLOB - oder Multibyte-CLOB-Spalte mit COLUMN WORDWRAPPED formatieren, verhalten sich die Spaltendaten so, als ob COLUMN WRAPPED stattdessen angewendet wurde. Die Spaltenüberschrift wird unabhängig von der Einstellung von WRAP oder beliebigen COLUMN-Befehlsklauseln abgeschnitten. Geben Sie nun die Spalte in das vorherige Format zurück: Beispiel 6-6 Formatieren einer XMLType-Spalte Bevor Sie eine XMLType-Spalte formatieren, müssen Sie eine Tabelle mit einer XMLType-Spaltendefinition erstellen und einige Daten in die Tabelle einfügen. Sie können eine XMLType-Spalte wie jede andere benutzerdefinierte Spalte erstellen. Um eine Tabelle mit einer XMLType-Spalte zu erstellen, geben Sie ein, um einen neuen Datensatz mit warehouseid - und Warehousespec-Werten in die neue Warehouse-Tabelle einzufügen. Geben Sie Folgendes ein, um die Spaltebreite der XMLType-Spalte auf 20 Zeichen festzulegen und anschließend die Spalte XMLType auszuwählen Sie können die Anzeigeattribute, die Sie eine bestimmte Spalte angegeben haben, unterdrücken und wiederherstellen. Um eine Spaltenanzeigeattribute zu unterdrücken, geben Sie einen COLUMN-Befehl in der folgenden Form ein: OFF teilt SQLPlus mit, die Standardanzeigeattribute für die Spalte zu verwenden, aber entfernt die Attribute, die Sie über den Befehl COLUMN definiert haben. Um die von COLUMN definierten Attribute wiederherzustellen. Verwenden Sie die ON-Klausel: Drucken einer Zeile von Zeichen nach eingewickelten Spaltenwerten Wie Sie gesehen haben, wendet SQLPlus standardmäßig Spaltenwerte auf zusätzliche Zeilen, wenn der Wert nicht der Spaltenbreite entspricht. Wenn Sie nach jeder eingewickelten Ausgabezeile (oder nach jeder Zeile) einen Datensatztrenner (eine Zeichenzeile oder eine Leerzeile) einfügen möchten, verwenden Sie die RECSEP - und RECSEPCHAR-Variablen des SET-Befehls. RECSEP legt fest, wann die Zeichenzeile gedruckt wird. RECSEP auf JED einstellen, um nach jeder Zeile zu drucken, nach WRAPPED zu drucken, um nach gewickelten Zeilen zu drucken, und auf OFF, um den Druck zu unterdrücken. Die Voreinstellung von RECSEP ist WRAPPED. RECSEPCHAR setzt das Zeichen in jeder Zeile. Sie können RECSEPCHAR auf ein beliebiges Zeichen setzen. Vielleicht möchten Sie ganze Wörter auf zusätzliche Zeilen wickeln, wenn ein Spaltenwert zu zusätzlichen Zeilen umschließt. Verwenden Sie dazu die WORDWRAPPED-Klausel des COLUMN-Befehls wie folgt: Beispiel 6-9 Drucken einer Zeichenzeile nach eingewickelten Spaltenwerten Um eine Zeile der Bindestriche nach jedem eingewickelten Spaltenwert zu drucken, geben Sie die Befehle ein: Schließlich geben Sie die folgende Abfrage ein : Jetzt beschränken Sie die Breite der Spalte JOBTITLE und sagen Sie SQLPlus, um ganze Wörter auf zusätzliche Zeilen zu verschieben, wenn nötig: Wenn Sie RECSEP auf JED setzen, druckt SQLPlus eine Zeile von Zeichen nach jeder Zeile (nach jeder Abteilung für das obige Beispiel). Bevor Sie fortfahren, setzen Sie RECSEP auf OFF, um das Drucken von Datensatztrennzeichen zu unterdrücken: Klären Sie Ihren Bericht mit Abstands - und Zusammenfassungszeilen Wenn Sie eine ORDER BY-Klausel in Ihrem SQL SELECT-Befehl verwenden, sind Zeilen mit demselben Wert in der geordneten Spalte (oder Ausdruck) Zusammen in Ihrer Ausgabe angezeigt. Sie können diese Ausgabe für den Benutzer sinnvoller machen, indem Sie die Befehle SQLPlus BREAK und COMPUTE verwenden, um Untermengen von Datensätzen zu erstellen und Platz - oder Zusammenfassungszeilen nach jeder Teilmenge hinzuzufügen. Die Spalte, die Sie in einem BREAK-Befehl angeben, wird als Pausenspalte bezeichnet. Indem Sie die Pausenspalte in Ihre ORDER BY-Klausel einbinden, erstellen Sie sinnvolle Untermengen von Datensätzen in Ihrer Ausgabe. Sie können dann den Untermengen innerhalb des gleichen BREAK-Befehls eine Formatierung hinzufügen und eine Zusammenfassungszeile hinzufügen (mit Summen, Mittelwerten usw.), indem Sie die Unterbrechungsspalte in einem COMPUTE-Befehl angeben. Um diesen Bericht nützlicher zu machen, würden Sie BREAK verwenden, um DEPARTMENTID als Pausenspalte einzurichten. Durch BREAK kannst du doppelte Werte in DEPARTMENTID unterdrücken und leere Zeilen platzieren oder eine neue Seite zwischen den Abteilungen beginnen. Sie können BREAK in Verbindung mit COMPUTE verwenden, um zusammenfassende Zeilen zu berechnen und zu drucken, die das Gesamtgehalt für jede Abteilung und für alle Abteilungen enthalten. Sie können auch Zusammenfassungszeilen drucken, die die durchschnittliche, maximale, minimale, Standardabweichung, Varianz oder Zeilenzahl enthalten. Unterdrücken von doppelten Werten in Unterbrechungssäulen Der Befehl BREAK unterdrückt in der Spalte oder dem Ausdruck, den Sie benennen, standardmäßig doppelte Werte. Um die doppelten Werte in einer Spalte, die in einer ORDER BY-Klausel angegeben ist, zu unterdrücken, verwenden Sie den BREAK-Befehl in seiner einfachsten Form: Wann immer Sie eine Spalte oder einen Ausdruck in einem BREAK-Befehl angeben, verwenden Sie eine ORDER BY-Klausel, die dieselbe Spalte oder einen Ausdruck angibt. Wenn Sie dies nicht tun, treten bei jedem Wechsel des Spaltenwerts Pausen auf. Beispiel 6-10 Unterdrücken von doppelten Werten in einer Bruchspalte Um die Anzeige der doppelten Abteilungsnummern in den gezeigten Abfrageergebnissen zu unterdrücken, geben Sie die folgenden Befehle ein: Für die folgende Abfrage (die aktuelle Abfrage im Puffer gespeichert): Vielleicht möchten Sie Leere Zeilen oder eine leere Seite nach jeder Zeile einfügen. Um n Zeilen nach jeder Zeile zu überspringen, verwenden Sie BREAK in der folgenden Form: Um eine Seite nach jeder Zeile zu überspringen, verwenden Sie SKIP PAGE nicht, dass ein physikalisches Seitenumbruchzeichen erzeugt wird, es sei denn, Sie haben auch NEWPAGE 0 angegeben. Verwenden Sie mehrere Spacing Techniques Angenommen Sie haben mehr als eine Spalte in Ihrer ORDER BY-Klausel und möchten Platz einfügen, wenn sich jeder Spaltenwert ändert. Jeder BREAK-Befehl, den Sie eingeben, ersetzt den vorherigen. Wenn Sie also in einem Bericht unterschiedliche Abstandstechniken verwenden oder Platz einfügen möchten, nachdem die Wertänderungen in mehr als einer geordneten Spalte geändert wurden, müssen Sie mehrere Spalten und Aktionen in einem einzigen BREAK-Befehl angeben. Beispiel 6-12 Kombinieren von Abstandstechniken Geben Sie folgendes ein: Um eine Seite zu überspringen, wenn sich der Wert von DEPARTMENTID ändert und eine Zeile, wenn sich der Wert von JOBID ändert, geben Sie den folgenden Befehl ein: Um zu zeigen, dass SKIP PAGE wirksam geworden ist, erstellen Sie einen TTITLE Mit einer Seitennummer: Führen Sie die neue Abfrage aus, um die Ergebnisse zu sehen: Listing und Entfernen von Break-Definitionen Bevor Sie fortfahren, schalten Sie die Top-Titel-Anzeige aus, ohne ihre Definition zu ändern: Sie können Ihre aktuelle Break-Definition auflisten, indem Sie den BREAK-Befehl ohne Klauseln eingeben: Sie Kann die aktuelle break-Definition durch Eingabe des CLEAR-Befehls mit der BREAKS-Klausel entfernen: Möglicherweise möchten Sie den Befehl CLEAR BREAKS am Anfang jedes Skripts platzieren, um sicherzustellen, dass zuvor eingegebene BREAK-Befehle keine Abfragen betreffen, die in einer bestimmten Datei ausgeführt werden. Berechnen von Zusammenfassungszeilen, wenn ein Bruchspaltenwertänderungen Wenn Sie die Zeilen eines Berichts in Teilmengen mit dem Befehl BREAK organisieren, können Sie verschiedene Berechnungen in den Zeilen in jeder Teilmenge ausführen. Sie tun dies mit den Funktionen des Befehls SQLPlus COMPUTE. Verwenden Sie die Befehle BREAK und COMPUTE zusammen in den folgenden Formularen: Sie können mehrere Pausenspalten und Aktionen wie zB Überspringen von Zeilen im Befehl BREAK einfügen, solange die Spalte, die Sie nach dem Befehl im COMPUTE-Namen benennen, auch nach EIN im BREAK erscheint Befehl. Um mehrere Pausenspalten und Aktionen in BREAK einzuschließen, wenn sie in Verbindung mit COMPUTE verwendet werden, verwenden Sie diese Befehle in den folgenden Formularen: Der COMPUTE-Befehl hat keine Wirkung ohne entsprechenden BREAK-Befehl. Sie können COMPUTE auf NUMBER Spalten und in bestimmten Fällen auf alle Arten von Spalten. Weitere Informationen finden Sie im COMPUTE-Befehl. Die folgende Tabelle listet Berechnungsfunktionen und deren Auswirkungen auf. Tabelle 6-1 Berechnen von Funktionen Die Funktion, die Sie im COMPUTE-Befehl angeben, gilt für alle Spalten, die Sie nach OF und vor EIN eingeben. Die berechneten Werte werden auf einer separaten Zeile gedruckt, wenn sich der Wert der geordneten Spalte ändert. Etiketten für ON REPORT und ON ROW Berechnungen erscheinen in der ersten Spalte ansonsten erscheinen sie in der Spalte, die in der ON-Klausel angegeben ist. Sie können das Compute Label mit COMPUTE LABEL ändern. Wenn Sie keine Beschriftung für den berechneten Wert definieren, druckt SQLPlus das Schlüsselwort "Unverkürzte Funktion". Das Compute-Label kann unter Verwendung der NOPRINT-Option des COLUMN-Befehls in der Pausenspalte unterdrückt werden. Weitere Informationen finden Sie im COMPUTE-Befehl. Wenn Sie die Option NOPRINT für die Spalte verwenden, auf der das COMPUTE ausgeführt wird, wird auch das COMPUTE-Ergebnis unterdrückt. Beispiel 6-13 Rechnen und Drucken von Zwischensummen Um die Summe von SALARY nach der Abteilung zu berechnen, listet man zuerst die aktuelle BREAK-Definition auf: die aktuelle BREAK-Definitionen enthält: Geben Sie nun den folgenden COMPUTE-Befehl ein und führen Sie die aktuelle Abfrage aus: Um die Summe der Gehälter für Abteilungen zu berechnen 10 und 20 ohne das Rechenetikett zu drucken: Um die Gehälter am Ende des Berichts zu berechnen: Wenn Sie das Format einer NUMBER-Spalte festlegen, müssen Sie die Größe der im Bericht enthaltenen Beträge zulassen. Berechnen von Zusammenfassungszeilen am Ende des Berichts Sie können zusammenfassende Zeilen basierend auf allen Werten in einer Spalte berechnen und drucken, indem Sie BREAK und COMPUTE in den folgenden Formularen verwenden: Beispiel 6-14 Berechnen und Drucken einer Großgesamtmenge Um die Gesamtsumme zu berechnen und zu drucken Der Gehälter für alle Vertriebsmitarbeiter und ändern Sie das Compute-Label, geben Sie zunächst die folgenden BREAK - und COMPUTE-Befehle ein: Als nächstes geben Sie eine neue Abfrage ein und führen Sie eine neue Abfrage aus: Um eine Gesamtsumme (oder Großdurchschnitt, Großes Maximum und so weiter) zusätzlich zu drucken Zwischensummen (oder Teilmittelwerte usw.) enthalten eine Pausenspalte und eine ON REPORT-Klausel in deinem BREAK-Befehl. Geben Sie dann einen COMPUTE-Befehl für die Pausenspalte ein und einen anderen, um ON REPORT zu berechnen: Mehrere Zusammenfassungswerte und Linien berechnen Sie können denselben Typ von Summary-Wert auf verschiedenen Spalten berechnen und ausdrucken. Geben Sie dazu für jede Spalte einen separaten COMPUTE-Befehl ein. Beispiel 6-15 Berechnen des gleichen Typs des Summary-Wertes in verschiedenen Spalten Um die Summe der Gehälter und Provisionen für alle Vertriebsmitarbeiter zu drucken, geben Sie zuerst den folgenden COMPUTE-Befehl ein: Sie müssen keinen BREAK-Befehl eingeben, den BREAK, den Sie in Beispiel 6 eingegeben haben -14, Computing und Printing ein Grand Total ist noch in Kraft. Ändern Sie nun die erste Zeile der select-Abfrage, um COMMISSIONPCT einzuschließen: Führen Sie schließlich die überarbeitete Abfrage aus, um die Ergebnisse zu sehen: Sie können auch mehrere Zusammenfassungszeilen in derselben Spalte ausdrucken. Dazu müssen Sie die Funktion für jede Zusammenfassungszeile im COMPUTE-Befehl wie folgt einfügen: Wenn Sie mehrere Spalten nach OF und vor ON einfügen, berechnet und druckt COMPUTE Werte für jede Spalte, die Sie angeben. Beispiel 6-16 Mehrere Zusammenfassungslinien auf der gleichen Bruchspalte berechnen Um den Durchschnitt und die Summe der Gehälter für die Verkaufsabteilung zu berechnen, geben Sie zunächst die folgenden BREAK - und COMPUTE-Befehle ein: Geben Sie nun die folgende Abfrage ein und führen Sie die folgende Abfrage aus: Listing und Entfernen von COMPUTE-Definitionen Sie Können Sie Ihre aktuellen COMPUTE-Definitionen auflisten, indem Sie den COMPUTE-Befehl ohne Klauseln eingeben: Beispiel 6-17 COMPUTE-Definitionen entfernen Um alle COMPUTE-Definitionen und die dazugehörige BREAK-Definition zu entfernen, geben Sie die folgenden Befehle ein: Sie können die Befehle CLEAR BREAKS und CLEAR COMPUTES platzieren Am Anfang jedes Skripts, um sicherzustellen, dass zuvor eingegebene BREAK - und COMPUTE-Befehle keine Abfragen betreffen, die Sie in einer bestimmten Datei ausführen. Definieren von Seiten - und Berichts-Titeln und Dimensionen Die Wort-Seite bezieht sich auf einen Bildschirm voller Informationen auf Ihrem Display oder eine Seite eines gespoolten (gedruckten) Berichts. Sie können Ober - und Untertitel auf jeder Seite platzieren, die Anzahl der Zeilen pro Seite festlegen und die Breite jeder Zeile bestimmen. Der Wortbericht bezieht sich auf die vollständigen Ergebnisse einer Abfrage. Sie können auch Kopf - und Fußzeilen auf jedem Bericht platzieren und sie auf die gleiche Weise formatieren wie die oberen und unteren Titel auf Seiten. Einstellen der oberen und unteren Titel und Kopfzeilen und Fußzeilen Wie Sie bereits gesehen haben, können Sie einen Titel festlegen, der am Anfang jeder Seite eines Berichts angezeigt wird. Sie können auch einen Titel festlegen, der am unteren Rand jeder Seite angezeigt wird. Der TTITLE-Befehl definiert den oberen Titel, den der BTITLE-Befehl den unteren Titel definiert. Sie können auch eine Kopf - und Fußzeile für jeden Bericht festlegen. Der REPHEADER-Befehl definiert den Berichtsheader, den der REPFOOTER-Befehl die Berichtsfußzeile definiert. Ein Befehl TTITLE, BTITLE, REPHEADER oder REPFOOTER besteht aus dem Befehlsnamen, gefolgt von einer oder mehreren Klauseln, die eine Position oder ein Format und einen CHAR-Wert angeben, den Sie in dieser Position platzieren oder dieses Format angeben möchten. Sie können mehrere Sätze von Klauseln und CHAR-Werten enthalten: Für Beschreibungen aller TTITLE-, BTITLE-, REPHEADER - und REPFOOTER-Klauseln siehe den Befehl TTITLE und den Befehl REPHEADER. Beispiel 6-18 Platzieren eines Top - und Bottom-Titels auf einer Seite Um Titeln oben und unten auf jeder Seite eines Berichts einzugeben, geben Sie nun die aktuelle Abfrage ein: Um den Report-Header zu unterdrücken, ohne seine Definition zu ändern, geben Sie Positioning Title Elements ein Bericht in den vorangegangenen Übungen könnte attraktiver aussehen, wenn Sie den Firmennamen mehr Wert legen und legen Sie die Art des Berichts und den Abteilungsnamen an jedem Ende einer separaten Zeile. Es kann auch dazu beitragen, die Liniengröße zu reduzieren und damit die Titel näher um die Daten zu zentrieren. Sie können diese Änderungen durch Hinzufügen einiger Klauseln zum TTITLE-Befehl und durch Zurücksetzen der Systemvariablen LINESIZE durchführen. Wie das folgende Beispiel zeigt. Sie können Berichtsheader und - fußzeilen wie BTITLE und TTITLE mit den Befehlen REPHEADER und REPFOOTER formatieren. Beispiel 6-20 Positionierung von Titel-Elementen Um den Personalbericht mit einem neu positionierten Top-Titel neu anzuzeigen, geben Sie folgende Befehle ein: LINKS. RECHT. Und CENTER-Klauseln legen die folgenden Werte am Anfang, Ende und Mitte der Zeile fest. Die SKIP-Klausel teilt SQLPlus mit, dass er eine oder mehrere Zeilen herunterzieht. Beachten Sie, dass zwischen der letzten Zeile der Ergebnisse und dem unteren Titel kein Leerzeichen mehr vorhanden ist. Die letzte Zeile des unteren Titels druckt auf der letzten Zeile der Seite. Der Abstand zwischen der letzten Zeile des Berichts und dem unteren Titel hängt von der Gesamtseitengröße, der Anzahl der Zeilen, die von dem Toptitel belegt wurden, und die Anzahl der Zeilen in einer gegebenen Seite. Im obigen Beispiel belegt der obere Titel drei weitere Zeilen als der oberste Titel im vorherigen Beispiel. Sie werden lernen, die Anzahl der Zeilen pro Seite später in diesem Kapitel festzulegen. Um immer n leere Zeilen vor dem unteren Titel zu drucken, verwenden Sie die SKIP n-Klausel am Anfang des BTITLE-Befehls. Zum Beispiel, um eine Zeile vor dem unteren Titel im obigen Beispiel zu überspringen, könntest du den folgenden Befehl eingeben: Eingeben eines Titel-Elements Sie können die COL-Klausel in TTITLE oder BTITLE verwenden, um dem title-Element eine bestimmte Anzahl von Leerzeichen einzugeben. Zum Beispiel stellt COL 1 die folgenden Werte in die erste Zeichenposition ein und ist also gleich LINKS oder ein Einzug von Null. COL 15 platziert das Titelelement in die 15. Zeichenposition und gibt es 14 Räume ein. Beispiel 6-21 Eingeben eines Titel-Elements Um den Firmennamen linksbündig mit dem Berichtsnamen zu drucken, werden in der nächsten Zeile fünf Leerzeichen eingefügt. Geben Sie nun die aktuelle Abfrage erneut ein, um die Ergebnisse zu sehen: Auflistung, Unterdrückung und Wiederherstellung von Seitentitel Definitionen Zur Auflistung Eine Seitentiteldefinition, geben Sie den entsprechenden Titelbefehl ohne Klauseln ein: Um eine Titeldefinition zu unterdrücken, geben Sie Folgendes ein: Diese Befehle führen dazu, dass SQLPlus die Anzeige von Titeln auf Berichten aufhört, aber die aktuellen Definitionen der Titel nicht löschen. Sie können die aktuellen Definitionen wiederherstellen, indem Sie Folgendes eingeben: Spaltenwerte in Titeln anzeigen Sie können einen Masterdetail-Bericht erstellen, der einen veränderten Masterspaltenwert am Anfang jeder Seite mit den Detailabfrageergebnissen für diesen Wert anzeigt. Sie können einen Spaltenwert in einem Top-Titel verweisen, indem Sie den gewünschten Wert in einer Variablen speichern und die Variable in einem TTITLE-Befehl referenzieren. Verwenden Sie die folgende Form des Befehls COLUMN, um die Variable zu definieren: Sie müssen die Masterspalte in einer ORDER BY-Klausel und in einem BREAK-Befehl mit der SKIP PAGE-Klausel enthalten. Beispiel 6-24 Erstellen eines MasterDetail-Reports Angenommen, Sie möchten einen Bericht erstellen, der zwei verschiedene Manager-Mitarbeiter-Nummern anzeigt, die jeweils an der Oberseite einer separaten Seite stehen und die Personen, die dem Manager auf derselben Seite berichten, wie die Manager-Mitarbeiter-Nummer. Zuerst erstellen Sie eine Variable, MGRVAR, um den Wert der aktuellen Manager-Mitarbeiter-Nummer zu halten: Weil Sie nur die Manager-Mitarbeiter-Nummern im Titel anzeigen, wollen Sie nicht, dass sie als Teil des Details drucken. Die NOPRINT-Klausel, die Sie oben eingegeben haben, teilt SQLPlus mit, die Spalte MANAGERID nicht zu drucken. Als nächstes fügen Sie ein Label und den Wert in Ihren Seitentitel ein, geben Sie den richtigen BREAK-Befehl ein und unterdrücken den unteren Titel aus dem letzten Beispiel: Schließlich geben Sie die folgende Abfrage ein und führen Sie sie aus: Wenn Sie den Wert einer Spalte am Unten auf der Seite können Sie den Befehl COLUMN in der folgenden Form verwenden: SQLPlus druckt den unteren Titel als Teil des Prozesses des Brechens auf einen neuen pagemdashafter, der den neuen Wert für die Masterspalte findet. Wenn du also einfach die NEWVALUE der Masterspalte referenzierst, würdest du den Wert für den nächsten Satz von Details erhalten. OLDVALUE erinnert sich an den Wert der Masterspalte, der vor dem Beginn des Seitenumbruchs wirksam war. Anzeigen des aktuellen Datums in Titeln Sie können natürlich Ihre Berichte durch einfaches Eingeben eines Wertes im Titel abgeben. Dies ist für Ad-hoc-Berichte zufriedenstellend, aber wenn Sie den gleichen Bericht wiederholt ausführen möchten, würden Sie es vorziehen, dass das Datum automatisch angezeigt wird, wenn der Bericht ausgeführt wird. Sie können dies tun, indem Sie eine Variable erstellen, um das aktuelle Datum zu halten. Sie können auf die vordefinierte Substitutionsvariable DATE verweisen, um das aktuelle Datum in einem Titel anzuzeigen, wie es jede andere Variable wäre. Das Datumsformatmodell, das Sie in Ihre LOGIN-Datei oder in Ihre SELECT-Anweisung aufnehmen, bestimmt das Format, in dem SQLPlus das Datum anzeigt. Weitere Informationen zu Datumsformatmodellen finden Sie in Ihrer Oracle-Datenbank-SQL-Referenz. Weitere Informationen zur LOGIN-Datei finden Sie unter Ändern der LOGIN-Datei. Sie können diese Befehle auch interaktiv eingeben. Siehe COLUMN für weitere Informationen. Einstellen von Seitenabmessungen Normalerweise enthält eine Seite eines Berichts die Anzahl der Leerzeilen, die in der Variable NEWPAGE des SET-Befehls festgelegt sind, einen Top-Titel, Spaltenüberschriften, Ihre Abfrageergebnisse und einen unteren Titel. SQLPlus zeigt einen Bericht an, der zu lang ist, um auf einer Seite auf mehreren aufeinanderfolgenden Seiten zu passen, jeweils mit eigenen Titeln und Spaltenüberschriften. Die Datenmenge, die SQLPlus auf jeder Seite anzeigt, hängt von den aktuellen Seitenabmessungen ab. Die von SQLPlus verwendeten Standard-Seitenabmessungen werden darunter angezeigt: Anzahl der Zeilen vor dem Top-Titel: 1 Anzahl Zeilen pro Seite, vom oberen Titel bis zum Ende der Seite: 14 Anzahl Zeichen pro Zeile: 80 Sie können diese Einstellungen ändern Um die Größe Ihres Computerbildschirms anzupassen, oder, zum Drucken, die Größe eines Blattes Papier. Sie können die Seitenlänge mit der Systemvariablen PAGESIZE ändern. Zum Beispiel können Sie dies wünschen, wenn Sie einen Bericht drucken. Um die Anzahl der Zeilen zwischen dem Anfang jeder Seite und dem oberen Titel festzulegen. Verwenden Sie die Variable NEWPAGE des SET-Befehls: Wenn Sie NEWPAGE auf Null setzen, überspringt SQLPlus Nullzeilen und zeigt und druckt ein Formular-Zeichen, um eine neue Seite zu starten. Bei den meisten Arten von Computerbildschirmen löscht das Formular-Zeichen den Bildschirm und verschiebt den Cursor zum Anfang der ersten Zeile. Wenn Sie einen Bericht drucken, macht das Formular-Zeichen den Drucker an die Oberseite eines neuen Papiers, auch wenn die Gesamtseitenlänge geringer ist als die des Papiers. Wenn Sie NEWPAGE auf NONE setzen, druckt SQLPlus keine Leerzeile oder einen Formulareintrag zwischen Berichtseiten. Um die Anzahl der Zeilen auf einer Seite festzulegen, verwenden Sie die Variable PAGESIZE des SET-Befehls: Sie können die Zeilengröße verkleinern, um einen Titel ordnungsgemäß über Ihre Ausgabe zu zentrieren, oder Sie möchten die Zeilengröße für den Druck auf Breitpapier erhöhen. Sie können die Zeilenbreite mit der LINESIZE-Variablen des SET-Befehls ändern: Beispiel 6-25 Einstellen der Seitenabmessungen Um die Seitengröße auf 66 Zeilen einzustellen, löschen Sie den Bildschirm (oder den Drucker auf ein neues Blatt Papier) zu Beginn von Jede Seite und legen Sie die Zeilengröße auf 70, geben Sie die folgenden Befehle ein: Geben Sie nun die folgenden Befehle ein, um die Ergebnisse zu sehen: Führen Sie nun die folgende Abfrage aus: Setzen Sie nun PAGESIZE, NEWPAGE und LINESIZE auf ihre Standardwerte zurück: Aktuelle Werte dieser Variablen verwenden Sie den Befehl SHOW: Über den SQLPlus-Befehl SPOOL. Sie können Ihre Abfrageergebnisse in einer Datei speichern oder auf dem Computerdrucker drucken. Speichern und Drucken von Abfrageergebnissen Senden Sie Ihre Abfrageergebnisse an eine Datei, wenn Sie sie mit einem Textverarbeitungsprogramm bearbeiten möchten, bevor Sie sie drucken oder in einen Brief, eine E-Mail oder ein anderes Dokument einfügen. Um die Ergebnisse einer Abfrage in einem filemdashand zu speichern, werden sie auf dem screenmdashenter den SPOOL-Befehl in der folgenden Form angezeigt: Wenn du den Dateinamen nicht mit einer Periode und einer Erweiterung verfolgst, fügt SPOOL dem Dateinamen eine Standard-Dateierweiterung hinzu, um sie zu identifizieren Als Ausgabedatei. Die Voreinstellung variiert mit dem Betriebssystem auf den meisten Hosts ist es LST oder LIS. Die Erweiterung wird nicht angehängt, wenn Sie zu systemgenerierten Dateien wie devnull und devstderr spulen. Weitere Informationen finden Sie in der plattformspezifischen Oracle-Dokumentation für Ihr Betriebssystem. SQLPlus fährt fort, Informationen in die Datei zu spulen, bis Sie das Spoolen ausschalten, indem Sie die folgende Form von SPOOL verwenden: Erstellen einer Flat File Beim Verschieben von Daten zwischen verschiedenen Softwareprodukten ist es manchmal notwendig, eine Flat-Datei zu verwenden (eine Betriebssystemdatei ohne Escape Zeichen, Überschriften oder zusätzliche Zeichen eingebettet). Wenn Sie zum Beispiel kein Oracle Net haben, müssen Sie eine Flat-Datei für die Verwendung mit SQLLoader erstellen, wenn Sie Daten von Oracle9 i in Oracle Database 10 g verschieben. Um eine Flat-Datei mit SQLPlus zu erstellen, müssen Sie zuerst die folgenden SET-Befehle eingeben: Nach der Eingabe dieser Befehle verwenden Sie den Befehl SPOOL, wie im vorherigen Abschnitt gezeigt, um die Flat-Datei zu erstellen. Der Befehl SET COLSEP kann nützlich sein, um die Spalten abzugrenzen. Weitere Informationen finden Sie im SET-Befehl. Senden von Ergebnissen an eine Datei Um die Ergebnisse einer Abfrage in einem filemdashand zu speichern, werden sie auf dem screenmdashenter den SPOOL-Befehl in der folgenden Form angezeigt: SQLPlus speichert alle auf dem Bildschirm angezeigten Informationen, nachdem Sie den Befehl SPOOL in der von Ihnen angegebenen Datei eingegeben haben. Senden von Ergebnissen an einen Drucker So drucken Sie Abfrageergebnisse aus. Spulen Sie sie zu einer Datei, wie im vorherigen Abschnitt beschrieben. Dann geben Sie statt der Verwendung von SPOOL OFF den Befehl in der folgenden Form ein: SQLPlus stoppt das Spoolen und kopiert den Inhalt der Spool-Datei auf den Computer-Standard (Standard) - Drucker. SPOOL OUT löscht die Spool-Datei nach dem Drucken nicht. Beispiel 6-26 Senden von Abfrageergebnissen an einen Drucker Um einen Abschlussbericht zu erstellen und die Ergebnisse zu filtern und zu drucken, erstellen Sie ein Skript mit dem Namen EMPRPT mit den folgenden Befehlen. Verwenden Sie zuerst EDIT, um das Skript mit Ihrem Betriebssystem-Texteditor zu erstellen. Als nächstes geben Sie die folgenden Befehle in die Datei ein, indem Sie Ihren Texteditor verwenden: Wenn Sie die Ausgabe auf Ihrem Bildschirm nicht sehen möchten, können Sie auch SET TERMOUT OFF zum Anfang der Datei hinzufügen und SET TERMOUT ON am Ende des Datei. Speichern und schließen Sie die Datei in Ihrem Texteditor (Sie kehren automatisch zu SQLPlus zurück). Führen Sie nun das Skript aus EMPRPT: SQLPlus zeigt die Ausgabe auf Ihrem Bildschirm an (es sei denn, Sie setzen TERMOUT auf OFF) und spulen es in die Datei TEMP: Einleitung Der vorherige Artikel untersuchte, welche gleitenden Durchschnitte sind und wie man sie berechnet. Dieser Artikel schaut jetzt, wie man diese in Web Intelligence implementiert. Die hier verwendete Formel ist mit der XIr3-Version von SAP BOE kompatibel, aber einige Formel kann in früheren Versionen funktionieren, falls verfügbar. Wir beginnen mit dem Betrachten, wie man einen einfachen gleitenden Durchschnitt berechnet, bevor man gewichtete und exponentielle Formen betrachtet. Bearbeitete Beispiele Die nachfolgenden Beispiele verwenden den gleichen Datensatz, der aus Aktienkursdaten in einer Excel-Datei besteht, die Sie herunterladen können. Die erste Spalte in der Datei ist der Tag des Aktienkurses und dann Spalten des Eröffnungskurses, höchster Preis am Tag, niedrigster Preis, Schlusskurs, Volumen und angepaßter Schlusskurs. Wir verwenden den Schlusskurs in unserer Analyse unten zusammen mit dem Date-Objekt. Simple Moving Average Es gibt ein paar Möglichkeiten, mit denen wir einfache gleitende Durchschnitte berechnen können. Eine Option besteht darin, die Vorherige Funktion zu verwenden, um den Wert einer vorherigen Zeile zu erhalten. Zum Beispiel berechnet die folgende Formel einen gleitenden Durchschnitt auf unserem Schlusskurs für einen gleitenden durchschnittlichen Datensatz von Größe 3, das ist eine ganz einfache Formel aber es ist offensichtlich, dass es nicht praktisch ist, wenn wir eine große Anzahl von Perioden haben, die wir hier machen können Verwendung von RunningSum Formel und für einen Datensatz von Größe N haben wir endlich haben wir eine 3. Technik, die zwar komplizierter ist, kann es eine bessere Leistung haben, da es den neuen Wert auf der Grundlage des vorherigen Wertes anstatt zwei laufende Summen über die vollständigen Daten berechnet Set. Diese Formel funktioniert jedoch nur nach dem N-ten Punkt im Gesamtdatensatz und da sie sich auf einen vorherigen Wert bezieht, müssen wir auch einen Startwert setzen. Unten ist die volle Formel für unsere Aktienkursanalyse verwendet, wo unsere gleitende durchschnittliche Periode 15 Tage ist, das Datum 1252010 ist der 15. Datenpunkt in unserem Datensatz und so für diesen Punkt berechnen wir einen normalen Durchschnitt mit dem RunningSum. Für alle Termine jenseits dieses Wertes verwenden wir unsere SMA-Formel und wir lassen alle Termine vor diesem Datum leer. Abbildung 1 unten ist ein Diagramm in Web Intelligence, das unsere Aktienkursdaten mit einem einfachen gleitenden Durchschnitt anzeigt. Abbildung 1. Web Intelligence-Dokument zeigt eine einfache verschiebende durchschnittliche gewichtete bewegliche Durchschnitt Eine gewichtete gleitende durchschnittliche Formel mit einer Periode von 3 ist, wie bei unserer ersten einfachen gleitenden durchschnittlichen Formel oben ist dies nur für eine kleine Anzahl von Perioden praktisch. Ich habe noch nicht in der Lage, eine einfache Formel zu finden, die für größere gleitende durchschnittliche Perioden verwendet werden kann. Mathematisch ist es möglich, aber Einschränkungen mit Web Intelligence bedeutet, dass diese Formeln don8217t konvertieren. Wenn jemand in der Lage ist, dies zu tun, würde ich gerne hören Die folgende Abbildung ist ein WMA von Periode 6 in Web Intelligence implementiert. Abbildung 2. Web Intelligence-Dokument eines gewichteten beweglichen durchschnittlichen exponentiellen Moving Average Ein exponentieller gleitender Durchschnitt ist ganz einfach, um in Web Intelligence zu implementieren und ist daher eine geeignete Alternative zu einem gewichteten Moving Average. Die Grundformel ist hier hart codiert 0,3 als unser Wert für Alpha. Wir wenden diese Formel nur für Perioden an, die größer sind als unsere zweite Periode, so dass wir eine if-Anweisung verwenden können, um diese zu filtern. Für unsere erste und zweite Periode können wir den vorherigen Wert verwenden und so ist unsere endgültige Formel für EMA, unten ist ein Beispiel für eine EMA, die auf unsere Bestandsdaten angewendet wird. Abbildung 3. Web Intelligence-Dokument zeigt eine exponentielle Moving Average Input Controls Als unsere EMA Formel doesn8217t auf die Größe der gleitenden durchschnittlichen Zeitraum verlassen und unsere einzige Variable ist Alpha können wir Input Controls verwenden, damit der Benutzer den Wert von Alpha anpassen. Um dies zu tun, erstellen Sie eine neue Variable namens 8216alpha8217 und definieren it8217s Formel als, aktualisieren Sie unsere EMA Formel zu, Erstellen Sie eine neue Eingabesteuerung Auswahl unserer Alpha-Variable als Eingabe-Control-Report-Objekt Verwenden Sie einen einfachen Schieberegler und legen Sie die folgenden Eigenschaften, Sobald Sie getan haben should be able to move the slider and immediately see the changes to the trend line in the chart Conclusion We looked at how to implement three types of moving average in Web Intelligence and although all were possible the Exponential Moving Average is probably the easiest and most flexible . I hope you found this article interesting and as always any feedback is very welcome. Post navigation Leave a Reply Cancel reply You must be logged in to post a comment. The trick to Weighted Moving Average (WMA) is you have to create a variable which represents the numerators of WMA (see Wikipedia for reference.) This should look like the following: Previous(Self) (n Close) 8211 ( Previous(RunningSum(Close)) 8211 Previous(RunningSum(Close)n1) where n is the number of periods. Then the actual WMA8217s formula would be like this: Numerator (n (n 1) 2) where Numerator is the variable you created earlier.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.