SQL Model Clause Tutorial, Teil 2
Eine Übersetzung aus dem Englischen. Original von 
Rob van Wijk
Multi-cell Referenzen
Es ist möglich, mehrere Zellen mit einer Regel anzusprechen. Dies geschieht über eine sogenannte multi-cell Referenz. Um dies zu zeigen führe ich eine neue Tabelle ein mit einem zusammengesetzten Primärschlüssel um mit mehreren Dimensionen arbeiten zu können. Die Tabelle enthält monatliche Verkaufszahlen zweier Bücher für 2005 und 2006 und wird wie folgt angelegt:
SQL> create table sales 2 as 3 select 'The Da Vinci Code' book ... (siehe Skript) SQL> select * from sales; BOOK MONTH COUNTRY AMOUNT ----------------- -------- -------------- ---------- The Da Vinci Code 01.03.05 Netherlands 5 The Da Vinci Code 01.04.05 Netherlands 8 The Da Vinci Code 01.05.05 Netherlands 3 The Da Vinci Code 01.07.05 Netherlands 2 The Da Vinci Code 01.10.05 Netherlands 1 The Da Vinci Code 01.02.05 United Kingdom 15 The Da Vinci Code 01.03.05 United Kingdom 33 The Da Vinci Code 01.04.05 United Kingdom 47 The Da Vinci Code 01.05.05 United Kingdom 44 The Da Vinci Code 01.06.05 United Kingdom 11 The Da Vinci Code 01.08.05 United Kingdom 2 The Da Vinci Code 01.05.05 France 2 The Da Vinci Code 01.08.05 France 3 The Da Vinci Code 01.01.06 France 4 Bosatlas 01.01.05 Netherlands 102 Bosatlas 01.02.05 Netherlands 55 Bosatlas 01.03.05 Netherlands 68 Bosatlas 01.04.05 Netherlands 42 Bosatlas 01.05.05 Netherlands 87 Bosatlas 01.06.05 Netherlands 40 Bosatlas 01.07.05 Netherlands 31 Bosatlas 01.08.05 Netherlands 26 Bosatlas 01.09.05 Netherlands 22 Bosatlas 01.10.05 Netherlands 23 Bosatlas 01.11.05 Netherlands 88 Bosatlas 01.12.05 Netherlands 143 Bosatlas 01.01.06 Netherlands 31 Bosatlas 01.02.06 Netherlands 18 Bosatlas 01.03.06 Netherlands 15 Bosatlas 01.04.06 Netherlands 11 Bosatlas 01.05.06 Netherlands 17 Bosatlas 01.06.06 Netherlands 9 Bosatlas 01.07.06 Netherlands 12 Bosatlas 01.08.06 Netherlands 20 Bosatlas 01.09.06 Netherlands 4 Bosatlas 01.10.06 Netherlands 5 Bosatlas 01.11.06 Netherlands 1 Bosatlas 01.12.06 Netherlands 1 38 Zeilen ausgewählt.
Das Buch “Bosatlas” hat Einträge für jeden Monat, aber nur für die Niederlande. “The Da Vinci Code” wurde in drei Ländern verkauft, aber nicht jeden Monat. Die Spalten book, month und country bilden den Primärschlüssel für diese Tabelle. Um den Monat deutlicher zu machen, ändere ich das Format folgendermaßen:
SQL> alter session set nls_date_format = 'fmmonth yyyy' 2 / Session wurde geändert.
Das Beispiel unten zeigt, wie alle Verkäufe von “Bosatlas” nach Juni 2006 verdoppelt werden:
SQL> select book 2 , month 3 , country 4 , amount 5 from sales 6 model 7 return updated rows 8 partition by (country) 9 dimension by (book,month) 10 measures (amount) 11 rules 12 ( amount['Bosatlas',month > date '2006-06-01'] = 13 amount['Bosatlas',cv(month)] * 2 14 ) 15 / BOOK MONTH COUNTRY AMOUNT ----------------- -------------- -------------- ---------- Bosatlas juli 2006 Netherlands 24 Bosatlas august 2006 Netherlands 40 Bosatlas september 2006 Netherlands 8 Bosatlas oktober 2006 Netherlands 10 Bosatlas november 2006 Netherlands 2 Bosatlas dezember 2006 Netherlands 2 6 Zeilen ausgewählt.
Der Größenwert amount hat nun zwei Dimensionen. Also müssen wir eine Zelle im Model sowohl mit einem Buch, als auch einem Monat referenzieren. Mit einer Regel werden sechs Zellen geändert, durch anwenden des Ausdrucks month > date ‘2006-06-01’ auf der linken Seite der Regel. Auf der rechten Seite wird die Funktion cv verwendet um auf den zugehörigen Wert auf der linken Seite zu verweisen, cv steht für current value (aktueller Wert). Anstelle von cv(month) hätte ich auch cv() verwenden können, da nur eine multi-cell Referenze vorhanden ist. cv() kann auch verwendet werden wenn es mehrere multi-cell Referenzen gibt, vorausgesetzt es ist klar auf welche Dimension sie Bezug nimmt. Falls nicht erhältst du einen ORA-32611:
SQL> select book
2 , month
3 , country
4 , amount
5 from sales
6 model
7 return updated rows
8 partition by (country)
9 dimension by (book, month)
10 measures (amount)
11 rules
12 ( amount[any,any] = cv()
13 )
14 order by book, month
15 /
( amount[any,any] = cv()
*
FEHLER in Zeile 12:
ORA-32611: Falsche Benutzung des Operators MODEL CV
Wenn du dich auf alle Dimensionswerte beziehen willst, anstatt auf eine Untermenge, kannst du ANY verwenden:
SQL> select book 2 , month 3 , country 4 , amount 5 from sales 6 model 7 return updated rows 8 partition by (country) 9 dimension by (book, month) 10 measures (amount) 11 rules 12 ( amount[any,date '2005-08-01'] = 200 13 ) 14 order by book, month 15 / BOOK MONTH COUNTRY AMOUNT ----------------- -------------- -------------- ---------- Bosatlas august 2005 Netherlands 200 The Da Vinci Code august 2005 United Kingdom 200 The Da Vinci Code august 2005 France 200 3 Zeilen ausgewählt.
In dieser Regel wird ANY verwendet um sich auf alle Bücher zu beziehen, die Verkaufseinträge für August 2005 haben. Multi-cell Referenzen sind auch möglich mit dem Schlüsselwort FOR, zum Beispiel: amount[‘Bosatlas’,for month from date ‘2005-03-01’ to date ‘2005-08-01’ increment 1], und mit BETWEEN, zum Beispiel: aantal[‘Bosatlas’],month between date ‘2005-03-01’ and date ‘2005-08-01’], und mit allen anderen Vergleichsoperatoren.
Referenz Modelle
Referenzmodelle sind Submodelle innerhalb eines Modells. Das Hauptmodell kann alle Werte aus dem Referenzmodell verwenden, aber die Werte aus dem Referenzmodell sind für die äußere Selectliste nicht sichtbar. Wir haben es hier mit Hilfsdaten zu tun, die einen nur-lesen Status für das Hauptmodell haben. Um dies zu verdeutlichen erstelle ich eine weitere Tabelle, die Preise für die zwei Bücher enthält:
SQL> create table prices 2 as 3 select 'Bosatlas' book, 42.95 price from dual union all 4 select 'The Da Vinci Code', 19.95 from dual 5 / Tabelle wurde erstellt.
Die zwei Zeilen der Preistabelle werden dem Hauptmodell über ein Referenzmodell zur Verfügung gestellt:
SQL> select book 2 , month 3 , country 4 , amount 5 , to_char(turnover,'99G990D00') turnover 6 from sales 7 where month between date '2005-07-01' and date '2005-12-31' 8 model 9 reference prices on (select book, price from prices) 10 dimension by (book) 11 measures (price) 12 main resultaat 13 partition by (country) 14 dimension by (book, month) 15 measures (0 as turnover, amount) 16 rules 17 ( turnover[any,any] = amount[cv(),cv()] * price[cv(book)] 18 ) 19 order by book 20 , month 21 / BOOK MONTH COUNTRY AMOUNT TURNOVER ----------------- -------------- -------------- ---------- ---------- Bosatlas juli 2005 Netherlands 31 1.331,45 Bosatlas august 2005 Netherlands 26 1.116,70 Bosatlas september 2005 Netherlands 22 944,90 Bosatlas oktober 2005 Netherlands 23 987,85 Bosatlas november 2005 Netherlands 88 3.779,60 Bosatlas dezember 2005 Netherlands 143 6.141,85 The Da Vinci Code juli 2005 Netherlands 2 39,90 The Da Vinci Code august 2005 United Kingdom 2 39,90 The Da Vinci Code august 2005 France 3 59,85 The Da Vinci Code oktober 2005 Netherlands 1 19,95 10 Zeilen ausgewählt.So enthält das Referenzmodell sowohl Dimensionen als auch Größenwerte, aber es kann weder eine partition clause noch seine eigenen Regeln enthalten. Vielleicht ist dir auch aufgefallen, dass sowohl das Hauptmodell, als auch das Referenzmodell jetzt einen Namen haben: Das Referenzmodell heißt prices mit dem vorangestellten Schlüsselwort REFERENCE, und das Hauptmodell heißt result mit dem vorangestellten Schlüsselwort MAIN. Der Größenwert price aus dem Referenzmodell wird innerhalb der Regel des Hauptmodells verwendet über die Phrase price[cv(book)]. Neu ist hier auch die Bildung eines neuen Größenwerts turnover, initial gefüllt mit 0 für alle existierenden Zellen
Iteration
Iterationen eröffnen die Möglichkeit, dieselbe Regel mehrmals auf dasselbe Modell anzuwenden. Im Beispiel unten wird die Anzahl der Verkäufe für „The Da Vinci Code“ zur vierten Potenz erhoben ohne die POWER Funktion zu verwenden, nur um zu zeigen wie Iterationen funktionieren. Um dies zu tun müssen wir die Anzahl der Verkäufe drei Mal mit der ursprünglichen Anzahl multiplizieren und zwar so:
SQL> select book 2 , month 3 , country 4 , a1 amount 5 , a2 amount_to_the_fourth 6 from sales 7 where book = 'The Da Vinci Code' 8 and country = 'Netherlands' 9 model 10 partition by (country) 11 dimension by (book,month) 12 measures (amount a1, amount a2) 13 rules iterate (3) 14 ( a2[any,any] = a2[cv(),cv()] * a1[cv(),cv()] 15 ) 16 order by month 17 / BOOK MONTH COUNTRY AMOUNT AMOUNT_TO_THE_FOURTH ----------------- -------------- -------------- ---------- -------------------- The Da Vinci Code märz 2005 Netherlands 5 625 The Da Vinci Code april 2005 Netherlands 8 4096 The Da Vinci Code mai 2005 Netherlands 3 81 The Da Vinci Code juli 2005 Netherlands 2 16 The Da Vinci Code oktober 2005 Netherlands 1 1 5 Zeilen ausgewählt.
Direkt nach dem Schlüsselwort RULES plazieren wir das Schlüsselwort ITERAGE, gefolgt von der Anzahl der Iterationen. Größenwert a1 enthält alle originalen, unveränderten Verkaufszahlen. Größenwert a2 enthält initial die Werte 5, 8, 3, 2 und 1, genauso wie a1. Während jeder der drei Iterationen werden diese Werte mit den Werten des Größenwerts a1 multipliziert. Nach der ersten Iteration enthält a1 25, 64, 9, 4 und 1, nach der zweiten 125, 512, 27, 8 und 1. Und zuletzt können die Werte in obigem Ergebnis abgelesen werden.
Praktisch im Umgang mit Iterationen ist das Schlüsselwort UNTIL, gefolgt von einem booleschen Ausdruck um unnötige Iterationen auslassen zu können. Das Wort ITERATION_NUMBER kann in der Regel verwendet werden, die die Anzahl der Iterationen enthält. Achtung, es beginnt bei 0 zu zählen; in obigem Beispiel mit ITERATE(3) zählt es von 0 bis 2.
Um zu zeigen wie UNTIL und ITERATION_NUMBER arbeiten, habe ich ein Beispiel konstruiert, bei dem zukünftige Verkäufe prognostiziert werden unter der Annahme, dass sie pro Jahr um 75% fallen. Jedes Jahr werden wir also nur 25% der Bücher wie im Vergleichsmonat des Vorjahres verkaufen, wobei Bruchteile abgeschnitten werden. Das Statement muss Datensätze generieren, bis ein ganzes Jahr kein Buch mehr verkauft wurde. Das macht folgende Abfrage:
SQL> select book 2 , country 3 , to_date(to_char(y) || to_char(m),'yyyymm') month 4 , amount 5 from sales 6 where book = 'Bosatlas' 7 and extract (year from month) = 2006 8 model 9 partition by ( book, country) 10 dimension by ( extract(year from month) y, extract(month from month) m) 11 measures (amount, 0 max_monthly_amount) 12 rules upsert all 13 iterate (100) until (max_monthly_amount[2007+iteration_number,1] < 4) 14 ( amount[2007+iteration_number,any] 15 = trunc(amount[2006+iteration_number,cv()]/4) 16 , max_monthly_amount[2007+iteration_number,1] 17 = max(amount)[2007+iteration_number,m between 1 and 12] 18 ) 19 order by y, m 20 /
Das ist viel komplexer als das, was wir vorher gesehen haben. Wie du in der WHERE Klausel sehen kannst, beginnen wir mit den 12 Zeilen für das Buch „Bosatlas“ 2006. Die Monatsspalte wird in 2 Dimensionen aufgespaltet: Die Jahreszahl und den Monat. Dies ermöglicht es 12 Zeilen für ein ganzes Jahr in einem Durchgang zu erstellen. Um ein Jahr mit 0 Verkäufen für alle Monate zu erzeugen kreiere ich eine Hilfsgröße max_monthly_amount. Dieser Größenwert wird über eine analytische Funktion gefüllt mit der Syntax max(amount)[2007+iteration_number, month_number between 1 and 12] und liefert die größte Verkaufszahl für das laufende Jahr. Dieser Größenwert wird in der UNTIL Klausel verwendet. Die Iterationen sollen stoppen wenn diese höchste Anzahl kleiner als 4 ist; denn wenn die Verkaufszahl 4 oder größer ist, dann führt es zu einer Zahl größer 0 im nächsten Jahr. Mit den aktuellen Datensätzen werden von den angegebenen 100 Iterationen nur 2 durchgeführt und bringen folgendes Ergebnis:
BOOK COUNTRY MONTH AMOUNT ----------------- -------------- -------------- ---------- Bosatlas Netherlands januar 2006 31 Bosatlas Netherlands februar 2006 18 Bosatlas Netherlands mõrz 2006 15 Bosatlas Netherlands april 2006 11 Bosatlas Netherlands mai 2006 17 Bosatlas Netherlands juni 2006 9 Bosatlas Netherlands juli 2006 12 Bosatlas Netherlands august 2006 20 Bosatlas Netherlands september 2006 4 Bosatlas Netherlands oktober 2006 5 Bosatlas Netherlands november 2006 1 Bosatlas Netherlands dezember 2006 1 Bosatlas Netherlands januar 2007 7 Bosatlas Netherlands februar 2007 4 Bosatlas Netherlands märz 2007 3 Bosatlas Netherlands april 2007 2 Bosatlas Netherlands mai 2007 4 Bosatlas Netherlands juni 2007 2 Bosatlas Netherlands juli 2007 3 Bosatlas Netherlands august 2007 5 Bosatlas Netherlands september 2007 1 Bosatlas Netherlands oktober 2007 1 Bosatlas Netherlands november 2007 0 Bosatlas Netherlands dezember 2007 0 Bosatlas Netherlands januar 2008 1 Bosatlas Netherlands februar 2008 1 Bosatlas Netherlands märz 2008 0 Bosatlas Netherlands april 2008 0 Bosatlas Netherlands mai 2008 1 Bosatlas Netherlands juni 2008 0 Bosatlas Netherlands juli 2008 0 Bosatlas Netherlands august 2008 1 Bosatlas Netherlands september 2008 0 Bosatlas Netherlands oktober 2008 0 Bosatlas Netherlands november 2008 0 Bosatlas Netherlands dezember 2008 0 36 Zeilen ausgewählt.
In dieser Abfrage wird der UPSERT ALL Modus verwendet. Es ist einer von drei möglichen: UPDATE, UPSERT und UPSERT ALL, wobei UPSERT der Default ist und in allen vorangegangenen Beispielen verwendet wurde. Im UPDATE Modus können keine neuen Zeilen erzeugt werden, so wie mit UPSERT. Der Unterschied zwischen UPSERT und UPSERT ALL ist, dass letzterer neue Zellen erzeugt, wenn die linke Seite der Regel das Schlüsselwort ANY enthält, im UPSERT Modus würde es alle Übereinstimmungen zu existierenden Zellen finden. Im UPSERT ALL Modus erzeugt es jeweils neue Zellen für jede Übereinstimmung der nicht-positionalen Dimensionswerte.
Es sind noch mehr Funktionen und Features zur Model Klausel verfügbar, die es wert sind studiert zu werden, aber ich will sie hier nur kurz erwähnen. Zunächst die Funktionen PRESENTV und PRESENTNNV, das Predicate IS PRESENT und die Schlüsselwörter KEEP NAV und IGNORE NAV. Kurz gesagt kann man damit zwischen fehlenden Zellen und existierenden Zellen mit NULL Werten unterscheiden. Dann gibt es die PREVIOUS Funktion mit der man den vorhergehenden Wert in der UNTIL Klausel referenzieren kann. Und zuletzt erwähne ich die Klauseln AUTOMATIC ORDER und SEQUENTIAL ORDER, die es Oracle ermöglichen selbst zu entscheiden in welcher Reihenfolge die Regeln angewendet werden sollen. Alle Klauseln und Funktionen in diesem Absatz sind in der Dokumentation recht klar beschrieben.
Im letzten Teil werde ich versuchen zu erklären, welchen praktischen Nutzen die Model Klausel haben kann. Ich werde Lösungen für früher unlösbare Fragen zeigen und für Probleme, die effizienter als mit früheren Techniken gelöst werden können. Häufige Besucher der OTN-Foren werden zweifellos wissen auf welche Probleme ich abziele.
oracle@matzberger.de Für Kontakte aller Art, auch Fehlermeldungen zum Tutorial
Impressum