Einleitung Mit der Veröffentlichung von SQL Server 2016 Service Pack 1 ist die In-Memory ColumnStore-Technologie jetzt auch in den Standard-, Web - und sogar Express - und LocalDB-Editionen verfügbar. Neben dem Vorteil von nur 1 Codebasis zu pflegen, wird diese Änderung in der Politik auch eine klare Plattenspeicherplatz sparen aufgrund seiner hohen Daten-Deduplizierung und Kompression Ratios und nicht zuletzt seine auch eine ernsthafte Ad-hoc-Abfrage Leistung Booster Der Hauptunterschied zwischen den SQL-Varianten ist, wie viel CPU-Leistung und Speicher für Aufgaben wie (Re-) Aufbau des Clustered ColumnStore Index zugewiesen werden. Zum Beispiel: Mit der Standard Edition wird ein einziger Core (max. 100 Prozessorzeit des sqlservr Prozesses) verwendet und das Abfragen eines CCI geschieht mit maximal 2 CPUs (MAXDOP2), abgesehen von der Nutzung aller verfügbaren CPUs in Enterprise Edition. Erstellen eines Cluster-ColumnStore-Index (CCI) mit SQL Server 2016 Standard Edition: Erstellen eines CCI mit allen 4 verfügbaren Cores mit SQL Server 2016 Enterprise Edition: Die Basiszeitpunkte für das Laden von 7.2 GB 60 Million Zeilen aus einer einzelnen TPCH-lineItem-Datei zeigen nicht viel Ein Unterschied zwischen den Aromen, wenn Bulk Einfügen der Daten direkt in eine Heap-Tabelle oder eine Tabelle mit einem CCI wird der Unterschied klar, wenn wir die Zeit zum Bau eines CCI auf einer Heap-Tabelle oder den Wiederaufbau eines CCI zu vergleichen: Zusammenfassend, die absolute Schnellste Weg, um Daten in einer Tabelle mit einem Clustered ColumnStore Index zu haben, ist: Laden in Heap erstellen die CCI anschließend mit SQL 2016 Ent. Ed. Direktes Laden in CCI Für Tabellen, in denen bereits ein Clustered ColumnStore Index erstellt wurde, stellen Sie sicher, dass Sie direkt in komprimierte Zeilengruppen streamen, um den Durchsatz zu maximieren. Um dies zu tun, sollte die Batch-Größe des Batches gleich oder größer als 100K Zeilen (genau 102400) sein. Kleinere Batches werden zuerst in komprimierte Deltastabellen geschrieben, bevor Tupel in die endgültigen komprimierten Row Group Segmente verschoben wird, was bedeutet, dass SQL Server die Daten zweimal berühren muss: Es gibt verschiedene Optionen, um Daten zu laden und wir gehen über die am häufigsten verwendeten Wie der Befehl Bulk Insert, BCP und SSIS. Lets sehen, was benötigt wird, um beste Leistung zu bekommen und wie zu überwachen 1) T-SQL-Bulk Insert Lassen Sie uns mit dem Befehl BULK INSERT starten: Prüfen der Anzahl der Zeilen, die bereits in die CCI geladen wurden, auch wenn Wird die Tabelle Sperren-Option verwendet, fragen Sie eine neue dmv mit dem Namen sys. dmdbcolumnstorerowgroupphysicalstats: Diese DMV wird auch zeigen, die möglichen Resource Group Staaten detaillierter beim Laden. Beim Laden von Daten gibt es vier mögliche Gruppengruppen. Wenn Sie sehen, dass der Zustand INVISBILE wie in der Abbildung unten bedeutet, dass Daten in eine RowGroup komprimiert werden. 1: OPEN160160160160160160160 (RowGroup akzeptiert neue Datensätze) 2: CLOSED160160160 (RowGroup wird gefüllt, aber noch nicht durch den Tupel-Mover-Prozess komprimiert) 3: COMPRESSED160 (RowGroup ist im Prozess, aus Daten im Delta - RowGroup wird gefüllt und komprimiert). 4 TOMBSTONE160 (RowGroup ist bereit, Müll gesammelt und entfernt werden) Durch die Angabe der Batch-Größe mit einem Wert von 102400 oder höher erreichen Sie maximale Leistung und Daten werden gestreamt und direkt in seine endgültige RG komprimiert wird dieses Verhalten zeigt sich als COMPRESSED. Sie können auch ein DMV überprüfen, das mit SQL2014 eingeführt wurde, um den RowGroup-Status zu überprüfen, der die sys. columnstorerowgroups DMV ist: Testergebnis Masseneinfügung von Daten in eine Tabelle mit CCI über den Befehl Bulk Insert kann durch Hinzufügen der Batchsize102400 und TABLOCK-Optionen. Dies führt zu einer Verbesserung des Durchsatzes um 8. 2) BCP. exe Das BCP-Dienstprogramm wird noch ziemlich stark in vielen Produktionsumgebungen verwendet, damit es sich lohnt, es schnell zu überprüfen: standardmäßig sperrt das BCP 1000 Zeilen an der Zeit zu SQL Server. Die Zeit, die benötigt wird, um 7.2GB Daten über BCP zu laden: 530 Sekunden. Or160 113K rowssec Der RowGroup-Zustand zeigt NVISIBLE an, was bedeutet, dass bei den Standardeinstellungen der Delta Store verwendet wird. Um sicherzustellen, dass der BCP-Befehl die Daten direkt in die komprimierten RGs fließt, müssen Sie die Option batchsize b mit einem Wert von mindestens 102400 hinzufügen. Ich lief verschiedene Tests mit größeren Batchgrößen: bis zu 1048576, aber die 102400 gab mir am besten Ergebnis. BCP DB. dbo. LINEITEMCCI in F: TPCHlineitem. tbl S. - c - T - tquotquot - b 102400 h tablock Der RowGroup-Zustand zeigt nun COMPRESSED an, was bedeutet, dass wir den Delta Store und die Datenströme in die komprimierten RGs umgehen: Ergebnis: das BCP Abgeschlossen in 457 Sekunden oder 133K Zeilen pro Sekunde oder Beim Testen bemerkte ich, dass die Standardeinstellungen von SSIS 2016 Speicherpuffergrößen verwenden, die möglicherweise auch die Batchgröße auf weniger als 100K Zeilen beschränken können. Im Beispiel unten sehen Sie, dass die Daten in Deltaspeichern gelandet sind: Die RG-Zustände sind geschlossen und die deltastorehobtid-Felder werden gefüllt, was bedeutet, dass die Deltaspeicher genutzt werden. Dies war der Augenblick zu erreichen und zu überprüfen, mit meinen Kollegen, die glücklicherweise haben, um dies zu bemerken und eine Lösung ist bereits vorhanden (siehe: Data Flow Puffer Auto Sizing Fähigkeit Vorteile Datenbelastung in CCI). Um die CCI-Streaming-Fähigkeiten voll auszuschöpfen, müssen Sie die Einstellungen für den Standard-Speicher BufferSize amp MaxRows erhöhen: Ändern Sie diese in 10x größere Werte: 8211 DefaultMaxBufferRows von 10000 in 1024000 und das wichtigste: 8211 DefaultBufferSize von 10485760 in 104857600. Hinweis: Die neue AutoAdjustBufferSize-Einstellung sollte auf True gesetzt sein, wenn Sie sehr breite Datenzeilen laden. Ändern Sie auch die Werte für den Zieladapter: 8211 Zeilen pro Batch: 160 von keinem in 102400 8211 Maximale Einfügungs-Commitgröße: von 2147483647 in 102400 Die mit SQL Server 2016 SP1 eingeführte Feature-Parität eröffnet eine völlig neue Palette von Möglichkeiten, von Hoffnung zu profitieren Die oben beschriebenen Komplettlösungen helfen Ihnen, die Bulk Insert-, BCP - und SSIS-Leistung zu maximieren, wenn Sie Daten in einen Clustered-ColumnStore-Index laden. Dies ist der schnellste Weg, um Daten aus einer Flatfile in eine Tabelle in SQL Server 2016 zu laden Post auf diesem Thema vor vielen Jahren, die Einführung von In-Memory-optimierte Tabellen und aktualisierbar Columnstore Tabellenindizes. Auch die Liste der zu transportierenden Datentransportfahrzeuge wächst: Neben BCP, dem T-SQL-Bulk-Insert-Befehl, dem SSIS als ETL-Tool und PowerShell gibt es einige neue, wie PolyBase, External R Script oder ADF. In diesem Beitrag werde ich mit der Überprüfung beginnen, wie viel schneller die neue langlebige amp nicht dauerhaft In-Memory-Tabellen sind die Festlegung der Baseline Für diese Tests Im mit einem Azure DS4V2 Standard VM mit 8 cores28 GB RAM und 2 Festplatten mit Host-Caching RW aktiviert. (Beide Luns bieten 275 MBsec RW-Durchsatz, obwohl die GUI eine Grenze von 60MBsec angibt). Ich habe eine einzelne 60 Million row7.2 Gigabyte TPCH lineitem flache Datei als Daten zu laden. Als Baseline für den Vergleich verwenden wir die Zeit, die es braucht, um die Datei in eine Heap-Tabelle zu laden: Dieser reguläre Bulk Insert Befehl vervollständigt innerhalb von 7 Minuten mit einem Durchschnitt von 143K rowssec. Aktivieren der Testdatenbank für speicheroptimierte Tabellen Die in SQL20142016 enthaltenen In-Memory-Tabellen sind für sehr schnelle OLTP mit vielen kleinen Transaktionen und hohen Parallelität ausgelegt. Dies ist eine völlig andere Art von Arbeitsauslastung als Bulk-Insertion, aber eben Aus Kuriositäten gibt es einen Versuch Es gibt 2 Arten von In-Memory-Tabellen: langlebig und nicht haltbar Tabellen. Die dauerhafte werden persist Daten auf der Festplatte, die nicht haltbar diejenigen nicht. Um diese Option zu aktivieren, müssen wir eine Haushaltung durchführen und ein schnelles Datenträgervolumen für das Hosting dieser Dateien zuweisen. Ändern Sie zuerst die Datenbank, um die Option Enthält MEMORYOPTIMIZEDDATA zu aktivieren, gefolgt von dem Hinzufügen eines Dateipfads und einer Dateigruppe, die die speicheroptimierten Tabellen enthält: Die dritte Sache zu tun ist, einen separaten Speicherpool der SQL Server-Instanz hinzuzufügen, damit sie alle beibehalten kann Die Daten, die wir in In-Memory-Tabellen von seinem Default-Speicherpool laden: Binden einer Datenbank an einen Speicherpool Die Schritte zum Definieren eines separaten Speicher-Pools und zum Binden einer Datenbank an sie werden im Folgenden aufgeführt: Zusätzliche Speicher-Pools werden über den Server verwaltet SQL Resource Governor. Der vierte und letzte Schritt besteht darin, die Testdatenbank mit dem Befehl sys. spxtpbinddbresourcepool an den neuen Speicherpool zu binden.160 Damit die Bindung wirksam wird, müssen wir die Datenbank offline nehmen und wieder online holen. Sobald sie gebunden sind, können wir dynamisch die Speichermenge ändern, die ihrem Pool über den Befehl ALTER RESOURCE POOL PoolHk WITH (MAXMEMORYPERCENT 80) zugewiesen ist. Bulk Insert in Durable In-Memory-Tabelle Nun sind alle mit der Option In-Memory aktiviert, können wir eine in-Memory-Tabelle erstellen. Jede speicheroptimierte Tabelle muss mindestens einen Index (entweder einen Range - oder einen Hash-Index) aufweisen, die vollständig (wieder) im Speicher zusammengesetzt sind und niemals auf der Festplatte gespeichert sind. Eine dauerhafte Tabelle muss einen deklarierten Primärschlüssel haben, der dann vom erforderlichen Index unterstützt werden kann. Um einen Primärschlüssel zu unterstützen, fügte ich der Tabelle eine zusätzliche Rownumber-ROWID1-Spalte hinzu: Das Angeben einer Batchgröße von 1 (bis zu 5) Million Zeilen für das Bulk-Insert-Kommando hilft, Daten auf der Festplatte zu halten, solange das Bulk-Insert aktiv ist (anstatt zu speichern Alles am Ende) damit minimiert Speicherdruck auf den Speicher-Pool PookHK wir geschaffen. Die Datenlast in die dauerhafte In-Memory-Tabelle schließt in 5 Minuten 28 Sekunden oder 183K Rowssec ab. Das ist eine okay Zeit aber nicht so viel schneller als unsere Grundlinie. Betrachtet man die sys. dmoswaitstats zeigt, dass die no.1 waitstat IMPPROVIOWAIT ist, die auftritt, wenn SQL Server wartet auf eine Bulk-Last IO zu beenden. Betrachten des Leistungsindikators Bulk-Kopie Rowssec und Disk Write Bytessec zeigt das Spülen auf 275-MBsec-Plattenspitzen an, sobald eine Charge eingegangen ist (die grünen Spikes). Das ist das Maximum von dem, was die Platte liefern kann, aber nicht alles erklärt. Angesichts des geringen Gewinns werden wir diese für zukünftige Untersuchungen parken. Überwachen des Speicher-Pools Über die sys. dmresourcegovernorresourcepools dmv können wir überprüfen, ob unsere In-Memory-Tabelle den neu erstellten PoolHK-Speicher nutzt Pool: Der Ausgang zeigt an, dass die 7.2GB (einige extra für die Rowid) unkomprimiert in den Speicher geladen wurden PoolHk pool: Wenn Sie versuchen, mehr Daten zu laden, als Sie dem Pool zur Verfügung stehen, erhalten Sie eine korrekte Nachricht wie diese: Die Anweisung wurde beendet. Msg 701, Ebene 17, Status 103, Zeile 5 In dem Ressourcenpool 8216PookHK ist nicht genügend Arbeitsspeicher vorhanden, um diese Abfrage auszuführen. Um eine Ebene tiefer bei der Speicherplatzzuweisung auf einer Tabelle pro In-Speichertabelle auszusehen, können Sie die folgende Abfrage ausführen (aus dem SQL Server In-Memory OLTP-Interne für SQL Server 2016-Dokument): Die Daten, die wir gerade geladen haben, werden als Varheap-Struktur mit einem Hash-Index: Bisher so gut Jetzt können wir weitergehen und überprüfen, wie Staging in einer nicht dauerhaften Tabelle führt Bulk Insert in nicht-dauerhafte In-Memory-Tabelle Für IMND-Tabellen brauchen wir nicht einen Primärschlüssel, so dass wir nur Hinzufügen und Nicht-Cluster-Hash-Index und setzen DURABILITY SCHEMAONLY. Der Bulk-Einsatz Das Laden von Daten in die nicht dauerhafte Tabelle ist innerhalb von 3 Minuten mit einem Durchsatz von 335 K rowssec abgeschlossen (vs. 7 Minuten). Dies ist 2,3 mal schneller als das Einfügen in eine Heap-Tabelle. Herkömmlicherweise ist SSIS der schnellste Weg, um eine Datei schnell in SQL Server zu laden, da SSIS alle Daten verarbeiten wird, die vorverarbeitet werden, damit die SQL Server-Engine kann Verbringen ihre CPU-Ticks auf die Daten auf der Festplatte. Wird dies immer noch der Fall sein, wenn die Daten in eine nicht dauerhafte Tabelle eingefügt werden Im Folgenden eine Zusammenfassung der Tests, die ich mit SSIS für diesen Beitrag ausgeführt habe: die Option SSIS Fastparse und160 die DefaultBufferMaxRows und DefaultBufferSize-Einstellungen sind die wichtigsten Performance Booster. Auch der Native OLE DB (SQLOLEDB.1) Provider führt etwas besser als der SQL Native Client (SQLNCLI11.1). Wenn Sie SSIS und SQL Server nebeneinander ausführen, wird die Erhöhung der Netzwerkpaketgröße nicht benötigt.160160 Net Ergebnis: ein grundlegendes SSIS-Paket, das eine flache Dateiquelle liest und die Daten direkt über ein OLE DB-Ziel in die Non-Durable-Tabelle schreibt Ähnelt dem Befehl Bulk Insert in einer IMND-Tabelle: Die 60 Millionen Zeilen werden in 2 Minuten 59 Sekunden oder 335 KB rowssec geladen, identisch mit dem Befehl Bulk insert. SSIS mit Balanced Data Distributor Aber wait8230160 die In-Memory-Tabellen sind entworfen, um Lock-Amp-Latch frei, so dass dies bedeutet, dass wir Daten auch über mehrere Streams laden können Das ist leicht zu erreichen mit SSIS der Balanced Data Distributor bringt genau das (die BDD Wird im Common-Abschnitt der SSIS-Toolbox aufgelistet) Hinzufügen der BDD-Komponente und Einfügen der Daten in die gleiche Non-Durable-Tabelle mit 3 Streams bietet den besten Durchsatz: Wir sind jetzt bis zu 526000 Rowssec Betrachten dieser sehr flachen Linie mit nur 160 der CPU-Zeit, die von SQLServer verwendet wird, scheint es, wir schlagen einige Engpass: Ich schnell versucht, kreativ zu sein durch die Nutzung der Modulo-Funktion und fügte 2 weitere Datenströme im Paket (jeder Verarbeitung 13 der Daten) 160, aber es ist nicht besser Viel (1 min52sec) so ein großes Thema für eine Zukunft zu untersuchen post160160 Die In-Memory Nicht-Durable Tabelle Option bringt einige ernsthafte Leistungsverbesserungen für das Staging von Daten laden Daten 1,5x schneller mit einem regulären Masseneinsatz und bis zu 3,6x mal schneller Mit SSIS. Diese Option, die in erster Linie auf die Beschleunigung von OLTP ausgelegt ist, kann auch einen großen Unterschied machen, um Ihr Batch-Fenster schnell zu schrumpfen (Fortsetzung) 22 SQL für Analyse und Berichterstellung Oracle verfügt über erweiterte analytische SQL-Verarbeitungsfunktionen, indem eine neue Familie von analytischen SQL-Funktionen eingeführt wird. Mit diesen analytischen Funktionen können Sie berechnen: Ranglisten und Perzentile Verschieben von Fensterberechnungen Lineare Regressionsstatistik Die Rangierfunktionen umfassen kumulative Verteilungen, Prozentrang und N-Kacheln. Bewegliche Fensterberechnungen ermöglichen es Ihnen, verschiebende und kumulative Aggregationen wie Summen und Mittelwerte zu finden. Die Laglead-Analyse ermöglicht direkte Reihenreferenz-Referenzen, so dass Sie Periodenänderungen berechnen können. Mit der Firstlast-Analyse können Sie den ersten oder letzten Wert einer geordneten Gruppe finden. Weitere Erweiterungen für SQL sind der CASE-Ausdruck und der partitionierte äußere Join. CASE-Ausdrücke bieten, wenn-dann Logik nützlich in vielen Situationen. Partitionierte äußere Verknüpfung ist eine Erweiterung der ANSI-Outer-Join-Syntax, die es Benutzern ermöglicht, bestimmte Dimensionen selektiv zu verdichten und andere spärlich zu halten. Auf diese Weise können Reporting-Tools selektiv die Dimensionen verdichten, zum Beispiel diejenigen, die in ihren tabellarischen Berichten erscheinen, während andere spärlich beibehalten werden. Um die Performance zu erhöhen, können analytische Funktionen parallelisiert werden: Mehrere Prozesse können gleichzeitig alle diese Anweisungen ausführen. Diese Fähigkeiten machen Berechnungen einfacher und effizienter, wodurch die Leistung, Skalierbarkeit und Einfachheit der Datenbank verbessert wird. Analytische Funktionen werden wie in Tabelle 22-1 beschrieben klassifiziert. Tabelle 22-1 Analytische Funktionen und ihre Verwendungen 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 22-1 dargestellt. Abbildung 22-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 22-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 aggregierte Ergebnisse wie Summen und Mittelwerte zur Verfügung stehen. 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 z. B. 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 22-2). Abbildung 22-2 Schiebefenster Beispiel Ranking, Windowing und Reporting Funktionen In diesem Abschnitt werden die grundlegenden analytischen Funktionen für Ranking, Fenstering und Reporting dargestellt. Sample Linear Regression Calculation 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. Statistische Aggregate Oracle bietet eine Reihe statistischer SQL-Funktionen und ein Statistikpaket, DBMSSTATFUNCS. 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 Datensatz-Modus 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 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. Benutzerdefinierte Aggregate Oracle bietet eine Möglichkeit zur Erstellung eigener Funktionen, sogenannte benutzerdefinierte Aggregatfunktionen. Diese Funktionen sind in Programmiersprachen wie PLSQL, Java und C geschrieben und können als analytische Funktionen oder Aggregate in materialisierten Sichten verwendet werden. Weitere Informationen zu Syntax und Einschränkungen finden Sie im Oracle Database Data Cartridge Developers Guide. Die Vorteile dieser Funktionen sind: Sehr komplexe Funktionen können mit einer vollständig prozeduralen Sprache programmiert werden. 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. Pivoting-Operationen Das von Business Intelligence-Abfragen zurückgegebene d ata ist oft am nützlichsten, wenn es in einem Kreuztabellenformat dargestellt wird. Mit der Pivotklausel der SELECT-Anweisung können Sie Querstabulationsabfragen schreiben, die Zeilen in Spalten umwandeln und dabei Daten in den Prozess der Rotation aggregieren. Pivoting ist eine Schlüsseltechnologie in Data Warehouses. Dabei transformieren Sie mehrere Zeilen der Eingabe in weniger und allgemein breitere Zeilen im Data Warehouse. Beim Drehen wird für jedes Element in der Pivotspaltenwerteliste ein Aggregationsoperator angewendet. Die Pivot-Spalte darf keinen beliebigen Ausdruck enthalten. Wenn Sie auf einen Ausdruck schwenken müssen, sollten Sie den Ausdruck in einer Ansicht vor dem PIVOT-Vorgang platzieren. Die grundlegende Syntax lautet wie folgt: Um die Verwendung von Pivotings zu veranschaulichen, erstellen Sie die folgende Sicht als Grundlage für spätere Beispiele: Beispiel: Pivoting Die folgende Anweisung veranschaulicht ein typisches Pivot in der Kanalspalte: Beachten Sie, dass die Ausgabe vier neue Aliasspalten erstellt hat , DIREKTVERTRIEB. INTERNETSALES. KATALOGVERZEICHNIS. Und TELESALES. Eine für jeden der Schwenkwerte. Die Ausgabe ist eine Summe. Wenn kein Alias angegeben ist, sind die Spaltenüberschriften die Werte der IN-Liste. Schwenken auf mehrere Spalten Sie können auf mehr als einer Spalte schwenken. Die folgende Anweisung veranschaulicht einen typischen Mehrfachspalten-Pivot: Beachten Sie, dass dieses Beispiel eine mehrspaltige IN-Liste mit Spaltenüberschriften angibt, die für die Übereinstimmung mit den IN-Listenmitgliedern ausgelegt sind. Pivot: Mehrere Aggregate Sie können mit mehreren Aggregaten schwenken, wie im folgenden Beispiel gezeigt: Beachten Sie, dass die Abfrage Spaltenüberschriften erstellt, indem die Pivotwerte (oder der Alias) mit dem Alias der Aggregatfunktion plus einem Unterstrich verkettet werden. Unterscheidung von PIVOT-generierten Nulls aus Nullstellen in Quelldaten Sie können zwischen Nullwerten unterscheiden, die aus der Verwendung von PIVOT und denen, die in den Quelldaten vorhanden sind, erzeugt werden. Das folgende Beispiel veranschaulicht NULLs, die PIVOT generiert. Die folgende Abfrage gibt Zeilen mit 5 Spalten, Spalte prodid zurück. Und die sich ergebenden Säulen Q1. Q1COUNTTOTAL. Q2. Q2COUNTTOTAL. Für jeden eindeutigen Wert von prodid. Q1COUNTTOTAL gibt die Gesamtzahl der Zeilen zurück, deren qtr-Wert Q1 ist. Das heißt, und Q2COUNTTOTAL gibt die Gesamtzahl der Zeilen zurück, deren qtr-Wert Q2 ist. Angenommen, wir haben eine Tabelle sales2 der folgenden Struktur: Aus dem Ergebnis wissen wir, dass es für prodid 100 zwei Verkaufsreihen für Quartal Q1 gibt. Und 1 Verkaufsreihe für Quartal Q2 für Prodid 200 gibt es 1 Verkaufsreihe für Quartal Q1. Und keine Verkaufsreihe für Quartal Q2.So, in Q2COUNTTOTAL. Können Sie feststellen, dass NULLlt1gt von einer Zeile in der ursprünglichen Tabelle stammt, deren Maß einen Nullwert hat, während NULLlt2gt auf keine Zeilen zurückzuführen ist, die in der ursprünglichen Tabelle für prodid 200 im Quartal Q2 vorhanden sind. Unpivoting-Operationen Ein Unpivot kehrt einen PIVOT-Vorgang nicht um. Stattdessen dreht es Daten aus Spalten in Zeilen. Wenn Sie mit geschwenkten Daten arbeiten, kann eine UNPIVOT-Operation keine Aggregationen, die von PIVOT oder auf andere Weise erstellt wurden, umkehren. Um Unpivot zu veranschaulichen, erstellen Sie zunächst eine schwenkbare Tabelle, die vier Spalten enthält, für Quartale des Jahres: Die Tabelleninhalte ähneln den folgenden: Die folgende UNPIVOT-Operation dreht die Viertelspalten in Zeilen. Für jedes Produkt gibt es vier Zeilen, eine für jedes Quartal. Beachten Sie die Verwendung von INCLUDE NULLS in diesem Beispiel. Sie können auch EXKLUSIVE NULLS verwenden. Die die Standardeinstellung ist. Darüber hinaus können Sie auch mit zwei Spalten unpivot, wie im Folgenden: Wildcard und Unterabfrage Pivoting mit XML-Operationen Wenn Sie ein Platzhalter-Argument oder Unterabfrage in Ihre Pivot-Spalten verwenden möchten, können Sie dies mit PIVOT-XML-Syntax. Mit PIVOT XML ist die Ausgabe der Operation korrekt formatiertes XML. Das folgende Beispiel veranschaulicht die Verwendung des Wildcard-Schlüsselworts ANY. Es gibt XML aus, das alle Channel-Werte in salesview enthält: Beachten Sie, dass das Schlüsselwort ANY in PIVOT-Operationen nur als Teil einer XML-Operation verfügbar ist. Diese Ausgabe enthält Daten für Fälle, in denen der Kanal im Datensatz vorhanden ist. Beachten Sie außerdem, dass Aggregationsfunktionen eine GROUP BY-Klausel angeben müssen, um mehrere Werte zurückzugeben, doch enthält die Pivotklausel keine explizite GROUP BY-Klausel. Stattdessen führt die Pivotklausel eine implizite GROUP BY durch. Das folgende Beispiel veranschaulicht die Verwendung einer Unterabfrage. Es gibt XML aus, das alle Kanalwerte und die Verkaufsdaten enthält, die jedem Kanal entsprechen: Der Ausgang verdichtet die Daten, um alle möglichen Kanäle für jedes Produkt einzuschließen. Data Densification for Reporting Daten werden in der Regel 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 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 Join verwenden können, um die Lücken in jeder Dimension zu füllen, 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 vorherigen Abfrage nehmen und eine partitionierte äußere Verknüpfung mit einem dichten Satz ausführen Zeit-Daten. In der folgenden Abfrage verweisen wir auf 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 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. Beachten Sie, dass die GROUP BY-Klausel eine verkettete ROLLUP ist, die die Rollup-Hierarchie für die Zeit - und Produktdimensionen angibt. 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.
No comments:
Post a Comment