Analytische Funktionen – erfolgreich eingesetzt

Mittwoch, 10. November 2004
13h00, Mozartsaal
Analytische Funktionen –
erfolgreich eingesetzt
Dani Schnider
Trivadis AG, Glattbrugg, Schweiz
Schlüsselworte:
Analytische Funktionen, SQL, Performance Optimierung, Data Warehousing
Zusammenfassung
Analytische Funktionen wurden mit Oracle8i Release 2 eingeführt und in
Oracle 9i erweitert. Obwohl ihre zahlreichen und mächtigen Möglichkeiten
somit schon seit einigen Jahren zur Verfügung stehen, werden analytische
Funktionen heute leider immer noch selten eingesetzt. Hier soll gezeigt werden, wie verschiedene Fragestellungen mit Hilfe dieser Oracle-Funktionalität
elegant und effizient formuliert werden können und wo die Vorteile – aber
auch Grenzen – gegenüber herkömmlichen SQL-Abfragen liegen.
Einleitung
Ein einfaches Beispiel mit der guten alten EMP-Tabelle zu Beginn: Für jeden
Mitarbeiter soll der Name, sein Gehalt sowie die Gehaltsumme seiner Abteilung angezeigt werden:
DEPTNO ENAME
SAL
SUM_SAL
----------- ---------- ----------- ---------10 CLARK
2450
8750
10 KING
5000
8750
10 MILLER
1300
8750
20 SMITH
800
10875
20 JONES
2975
10875
20 SCOTT
3000
10875
20 ADAMS
1100
10875
20 FORD
3000
10875
30 ALLEN
1600
9400
30 WARD
1250
9400
30 MARTIN
1250
9400
30 BLAKE
2850
9400
30 TURNER
1500
9400
30 JAMES
950
9400
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Diese Abfrage lässt sich nicht mit einem einfachen SELECT-Befehl formulieren,
da Daten auf unterschiedlichen Verdichtungsstufen angezeigt werden. Während sich die Spalten ENAME und SAL auf einen einzelnen Mitarbeiter beziehen, ist die Spalte SUM_SAL eine Aggregation (= Verdichtung) pro Abteilung.
Eine mögliche Lösung ist die Verwendung einer Inline-View:
SELECT e1.deptno, e1.ename, e1.sal, e2.sum_sal
FROM emp e1, (SELECT deptno, SUM(sal) sum_sal
FROM emp
GROUP BY deptno) e2
WHERE e1.deptno = e2.deptno
ORDER BY e1.deptno
Solche Lösungen funktionieren bestens und werden auch oft angewendet. Der
Nachteil besteht hier darin, dass die Tabelle EMP zweimal gelesen werden muss.
Klar, bei 14 Datensätzen ist dies kein Problem, aber bei grösseren Datenmengen
können solche Abfragen zu Performanceverlusten führen.
Das gleiche Ergebnis lässt sich einfach und elegant mit einer analytischen
Funktion ermitteln. Dabei muss die Tabelle nur einmal gelesen werden, da die
Berechnung von analytischen Funktionen im gleichen Durchlauf – sozusagen
„nebenbei“ – erfolgt.
SELECT
,
,
,
FROM
deptno
ename
sal
SUM(sal) OVER (PARTITION BY deptno) sum_sal
emp
Grundprinzip der analytischen Funktionen
Jede analytische Funktion bezieht sich auf Teilmengen der Daten, auf sogenannte Partitionen (die nichts mit partitionierten Tabellen zu tun haben).
Anhand der Partitionen wird definiert, wie die Resultate der analytischen Funktion gruppiert werden. Die Aufteilung der Partitionen wird durch die Klausel
PARTITION BY angegeben. Wird sie weggelassen, bezieht sich die analytische
Funktion auf die gesamte Resultatmenge.
Durch die Klausel ORDER BY kann festgelegt werden, wie die Daten innerhalb
der Partition sortiert werden. Für viele der analytischen Funktionen ist die
Angabe der Reihenfolge zwingend, da sie für die Funktionsberechnung wesentlich ist. Die Reihenfolge der Ausgabe der Resultatmenge kann jedoch von der
Reihenfolge der Berechnung abweichen und wird – wie üblich in SQL – mit
einem ORDER BY am Ende des SELECT-Befehls definiert.
Falls für eine analytische Funktion ein ORDER BY spezifiziert wurde, kann die
Menge der für die Berechnung relevanten Datensätze durch die Definition
eines Windows zusätzlich eingeschränkt werden. Normalerweise umfasst das
Window die Datensätze vom Beginn der Partition bis zum aktuellen Datensatz,
es kann aber auch explizit durch ROWS (physische Grenze) oder RANGE
(logische Grenze) eingeschränkt werden.
Kompliziert? Anhand eines weiteren einfachen Beispiels soll das Grundprinzip
der analytischen Funktionen illustriert werden: Für jeden Mitarbeiter soll
berechnet werden, wie viele Mitarbeiter der gleichen Abteilung ein Gehalt im
Bereich von +/– 1000 Dollar des aktuellen Datensatzes haben. Die entsprechende SQL-Abfrage mit Verwendung der analytischen Funktion COUNT sieht
folgendermassen aus:
SELECT
,
,
,
,
deptno
ename
hiredate
sal
COUNT(*) OVER (PARTITION BY deptno
ORDER BY sal
RANGE BETWEEN 1000 PRECEDING
AND 1000 FOLLOWING) count_sal
FROM emp
Für jeden Datensatz der Resultatmenge wird nun die Spalte COUNT_SAL
berechnet, ausgehend von der aktuellen Partition und dem aktuellen Window.
Zu beachten ist dabei, dass die Partition und das Window „wandern“, d.h. für
den jeweils aktuellen Datensatz (Current Row) neu festgelegt werden.
Abb. 1: Partition, Window und Current Rows der analytischen Funktion
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Anwendungsbeispiel: Kumulierte Umsatzzahlen
Eine typische Anwendung von analytischen Funktionen sind kumulierte Summierungen („Year-to-date“ Abfragen), wie sie häufig in Data Warehouses vorkommen: Neben dem Umsatz pro Monat soll jeweils der gesamte Jahresumsatz
bis zum entsprechenden Monat angezeigt werden. Das nachfolgende Beispiel
zeigt, wie diese Problemstellung mittels einer analytischen Funktion gelöst
werden kann. Dazu gruppieren wir die Umsatzzahlen nach Kalendermonat.
Alle Monatsumsätze eines Jahres werden zu einer Partition zusammengefasst
(PARTITION BY calendar_year) und nochmals summiert. Die Datensätze werden dazu nach Monat aufsteigend sortiert (ORDER BY calendar_month_
desc). Dabei nützen wir die Tatsache aus, dass das für die Summierung verwendete Window jeweils alle Datensätze vom Anfang der Partition (Januar des
jeweiligen Jahres) bis zur Current Row (aktueller Monat) enthält.
SELECT
,
,
,
t.calendar_year year
t.calendar_month_desc month
SUM(s.amount_sold)/1000000 millions
SUM(SUM(s.amount_sold)/1000000)
OVER (PARTITION BY t.calendar_year
ORDER BY t.calendar_month_desc) millions_ytd
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_year, t.calendar_month_desc
Als Resultat wird jeweils der Monatsumsatz (Spalte MILLIONS) und der
aufsummierte (=kumulierte) Umsatz innerhalb des Jahres (MILLIONS_YTD)
ausgegeben:
YEAR
----------1998
1998
1998
1998
1998
1998
1998
1998
1998
1998
1998
1998
1999
1999
...
MONTH
MILLIONS MILLIONS_YTD
-------- -------- -----------1998-01
2.28
2.28
1998-02
2.37
4.65
1998-03
1.83
6.48
1998-04
1.98
8.46
1998-05
1.75
10.20
1998-06
1.87
12.07
1998-07
1.93
14.01
1998-08
1.97
15.98
1998-09
2.17
18.15
1998-10
2.24
20.38
1998-11
1.96
22.34
1998-12
1.74
24.08
1999-01
2.08
2.08
1999-02
2.36
4.44
Das Beispiel basiert auf dem „Sales History” Beispielschema von Oracle 10g mit
rund einer Million Einträgen in der SALES-Tabelle. Hier sind nun bereits deutliche Performanceunterschiede messbar. Der Execution Plan des ausgeführten
SELECT-Befehls zeigt, dass die Tabelle SALES nur einmal gelesen und mit TIMES
gejoined werden musste. Die Berechnung der analytischen Funktion wird am
Schluss (nach dem GROUP BY) ausgeführt und ist im Execution Plan als
WINDOW BUFFER dargestellt.
call
count
----- -------Parse
1
Execute
1
Fetch
5
----- -------total
7
Rows
---------48
48
918843
1826
918843
918843
cpu
elapsed disk
query
current
----- ----------- ----- --------- --------0.00
0.00
0
0
0
0.00
0.01
0
0
0
1.81
1.80
0
1773
0
----- ----------- ----- --------- --------1.81
1.82
0
1773
0
rows
---0
0
48
---48
Row Source Operation
--------------------------------------------------WINDOW BUFFER (cr=1773 pr=0 pw=0 time=1803746 us)
SORT GROUP BY (cr=1773 pr=0 pw=0 time=1803741 us)
HASH JOIN (cr=1773 pr=0 pw=0 time=11948131 us)
TABLE ACCESS FULL TIMES (cr=56 pr=0 pw=0 time=3896
us)
PARTITION RANGE ALL PARTITION: 1 28 (cr=1717 pr=0
pw=0 time=5513218 us)
TABLE ACCESS FULL SALES PARTITION: 1 28 (cr=1717
pr=0 pw=0 time=1838601 us)
Im Vergleich dazu dauert die entsprechende Abfrage ohne Verwendung von
analytischen Funktionen rund 10 mal länger und führt zu einem viel aufwändigeren Execution Plan, da mehrfach auf die Tabelle SALES zugegriffen
werden muss:
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
SELECT
,
,
,
t.calendar_year year
t.calendar_month_desc month
SUM(s.amount_sold)/1000000 millions
(SELECT SUM(s1.amount_sold)/1000000
FROM sales s1,
times t1
WHERE s1.time_id = t1.time_id
AND t1.calendar_year = t.calendar_year
AND t1.calendar_month_desc <= t.calendar_month_desc)
millions_ytd
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_year, t.calendar_month_desc
ORDER BY t.calendar_year, t.calendar_month_desc
call
count
----- -------Parse
1
Execute
1
Fetch
5
----- -------total
7
cpu
elapsed disk
query
current
----- ----------- ----- --------- --------0.01
0.00
0
0
0
0.00
0.00
0
0
0
18.14
18.15
0
300193
0
----- ----------- ----- --------- --------18.15
18.16
0
300193
0
rows
---0
0
48
---48
Rows
Row Source Operation
------- --------------------------------------------------48 SORT AGGREGATE (cr=298420 pr=0 pw=0 time=16352528 us)
5911266
TABLE ACCESS BY LOCAL INDEX ROWID SALES (cr=298420 pr=0
pw=0 time=82781550 us)
5920777
NESTED LOOPS (cr=21875 pr=0 pw=0 time=17885399 us)
9463
TABLE ACCESS FULL TIMES (cr=2688 pr=0 pw=0 time=57388 us)
5911266
PARTITION RANGE ITERATOR PARTITION: KEY KEY
(cr=19187 pr=0 pw=0 time=30093878 us)
5911266
BITMAP CONVERSION TO ROWIDS (cr=19187 pr=0 pw=0
time=12109898 us)
9454
BITMAP INDEX SINGLE VALUE SALES_TIME_BIX PARTITION: KEY KEY (cr=19187 pr=0 …)
48 SORT GROUP BY (cr=1773 pr=0 pw=0 time=1800321 us)
918843
HASH JOIN (cr=1773 pr=0 pw=0 time=12866998 us)
1826
TABLE ACCESS FULL TIMES (cr=56 pr=0 pw=0 time=3736 us)
918843
PARTITION RANGE ALL PARTITION: 1 28 (cr=1717 pr=0
pw=0 time=5513208 us)
918843
TABLE ACCESS FULL SALES PARTITION: 1 28 (cr=1717
pr=0 pw=0 time=1838592 us)
Ranking-Funktionen
Als analytische Funktionen können nicht nur die klassischen Gruppenfunktionen wie SUM, COUNT, MIN, MAX, AVG etc. verwendet werden, sondern es
gibt auch weitere Funktionen, die speziell für die Verwendungs als analytische
Funktionen konzipiert wurden. Dazu gehört die Familie der Ranking-Funktionen, mit denen beispielsweise Ranglisten oder Top-n-Abfragen formuliert werden können.
Die Funktionen RANK und DENSE_RANK berechnen die Ranglisten-Nummer
für jede Zeile der Ausgabe, basierend auf der Sortierung innerhalb der Partition.
Der Unterschied zwischen den beiden Funktionen besteht darin, wie nach
mehreren gleichplatzierten Datensätzen die Nummerierung fortgesetzt wird:
SELECT
,
,
FROM
WHERE
deptno, ename, sal
RANK() OVER (ORDER BY sal DESC) rank
DENSE_RANK() OVER (ORDER BY sal DESC) dense_rank
emp
deptno IN (10, 20)
DEPTNO
----------10
20
20
20
10
10
20
20
ENAME
SAL
RANK DENSE_RANK
---------- ----------- ----------- ----------KING
5000
1
1
SCOTT
3000
2
2
FORD
3000
2
2
JONES
2975
4
3
CLARK
2450
5
4
MILLER
1300
6
5
ADAMS
1100
7
6
SMITH
800
8
7
Solche Ranglisten lassen sich über die ganze Datenmenge oder pro Partition
berechnen, und das in einer einzigen Abfrage. Im nachfolgenden Beispiel wird
anhand der Verkaufszahlen pro Produkt eine Rangliste über alle Produkte gebildet, und gleichzeitig eine Rangliste innerhalb jeder Produktkategorie. Unabhängig davon, wie viele verschiedene analytische Funktionen berechnet werden, die Basistabelle – in diesem Fall die Tabelle PROD_SALES muss immer nur
einmal gelesen werden.
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
SELECT
,
,
,
,
prod_category
prod_name
amount
RANK() OVER (ORDER BY amount DESC) rank_total
RANK() OVER (PARTITION BY prod_category
ORDER BY amount DESC) rank_category
FROM prod_sales
ORDER BY prod_category
Bei der ersten RANK-Funktion wurde kein PARTITION BY angegeben. Somit
bezieht sich die Rangliste auf alle Daten. Im zweiten Fall wird durch Angabe
„PARTITION BY prod_category“ erreicht, dass für jede Produktkategorie eine
separate Rangliste erstellt wird.
Dieses Beispiel erweitern wir nun folgendermassen: Wir möchten für jede Produktkategorie das erfolgreichste Produkt mit seinem Umsatz anzeigen. Dazu
kann wiederum die Funktion RANK verwendet werden. Der naheliegenste
Ansatz wäre, die analytische Funktion in der WHERE-Klausel zu verwenden:
WHERE RANK() OVER (PARTITION BY … ORDER BY …) = 1
Hier stossen wir allerdings auf eine Einschränkung: Analytische Funktionen
können innerhalb eines SQL-Befehls im SELECT- und im ORDER BY-Teil verwendet werden, nicht jedoch in der WHERE- oder HAVING-Bedingung. Der
Versuch führt zu einer Fehlermeldung:
ORA-30483: window
functions are not allowed here
Die Einschränkung lässt sich aber einfach umgehen, indem die analytischen
Funktionen in eine Inline-View „verpackt“ werden, auf welche dann die Einschränkung erfolgt:
SELECT prod_category, prod_name, amount
FROM (SELECT prod_category
, prod_name
, amount
, RANK() OVER (PARTITION BY prod_category
ORDER BY amount DESC) ranking
FROM prod_sales)
WHERE ranking = 1
Sind wir nun wieder gleich weit wie am Anfang? Ein Vorteil von analytischen
Funktionen besteht ja darin, dass man dadurch Inline-Views vermeiden kann.
Und nun benötigen wir eine Inline-View, um die analytischen Funktionen
anzuwenden. Wo soll da der Vorteil sein?
Im Gegensatz zum Beispiel am Anfang dieses Beitrages muss hier die Basistabelle nur einmal gelesen werden, und auf das Resultat dieses SELECT-Befehls
erfolgt dann die Einschränkung. Zwar wäre es aus Performancegründen noch
besser, wenn die Einschränkung zu Beginn, also vor der Auswertung der gesamten Inline-View erfolgen könnte. Dies ist aber deshalb nicht möglich, weil
für die Berechnung der Produkte mit dem höchsten Umsatz die Verkaufszahlen
entsprechend aggregiert und sortiert werden müssen, bevor die Einschränkung
erfolgen kann.
Weitere Funktionen
Das gleiche Resultat lässt sich auch ohne Inline-View erreichen, und zwar mit
einer Erweiterung, die mit Oracle 9i eingeführt wurde: FIRST bzw. LAST können verwendet werden, wenn eine analytische Funktion nur auf den ersten
bzw. letzten Wert der Resultatmenge angewendet werden soll. In unserem Beispiel heisst das, dass der Name des ersten Produkts ermittelt werden soll, das
aus einer sortierten Liste nach absteigendem Umsatz zurückgegeben wird. Weil
wir die gesamte Abfrage nach Produktkategorie gruppieren, muss zusätzlich
eine Gruppenfunktion (z.B. MIN oder MAX) angegeben werden.
SELECT prod_category
, MIN(prod_name) KEEP (DENSE_RANK FIRST ORDER BY amount DESC)
, MAX(amount)
FROM prod_sales
GROUP BY prod_category
Weitere interessante analytische Funktionen sind LAG und LEAD. Damit ist es
möglich, ausgehend vom aktuellen Datensatz auf vorhergehende oder nachfolgende Datensätze zuzugreifen:
SELECT
,
,
FROM
ename, hiredate, sal
LAG(sal, 2) OVER (ORDER BY hiredate) lag_sal
LEAD(sal, 3) OVER (ORDER BY hiredate) lead_sal
emp
Das Resultat dieser Abfrage zeigt für jeden Mitarbeiter nicht nur sein Gehalt an,
sondern auch das Gehalt des vorletzten Mitarbeiters, der eingestellt wurde und
jenes des Mitarbeiters drei Anstellungsdaten später:
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Abb. 2: Analytische Funktionen LAG und LEAD
Anwendungsbeispiel: Vorjahresvergleich
Immer wieder besteht die Anforderung, dass zum Beispiel Verkaufszahlen mit
den entsprechenden Werten der Vorperiode verglichen werden sollen. Im
nachfolgenden Beispiel soll der Umsatz des aktuellen Monats dem Umsatz des
gleichen Monats im Vorjahr gegenübergestellt werden. Dazu wird jeweils die
Summe der Umsätze aus der 12-letzten Zeile übernommen. Das funktioniert
natürlich nur, wenn in jedem Monat Verkäufe gemacht wurden.
SELECT t.calendar_month_desc
, SUM(s.amount_sold)/1000000 millions
, LAG(SUM(s.amount_sold), 12)
OVER (ORDER BY t.calendar_month_desc)/1000000 millions_ly
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc
Neben den hier vorgestellten analytischen Funktionen gibt es zahlreiche weitere Möglichkeiten, insbesondere statistische Funktionen, auf die hier nicht im
Detail eingegangen wird. Einen Eindruck von der Mächtigkeit der analytischen
Funktionen soll das letzte Beispiel geben, in welchem verschiedene Funktionen
kombiniert in einer SQL-Abfrage verwendet werden. Pro Produktkategorie und
Jahr werden folgende Werte berechnet:
• TOTAL_AMOUNT: Gesamtumsatz der Produktkategorie im entsprechenden Jahr
• CHANGE_LY: Prozentuale Veränderung gegenüber Vorjahresumsatz der Kategorie
• PCT_YEAR: Prozentualer Anteil der Produktkategorie am gesamten Jahresumsatz
• CAT_RANK: Platzierung des Jahres innerhalb der Produktkategorie
• YEAR_RANK: Platzierung der Produktkategorie innerhalb des Jahres
SELECT
,
,
,
p.prod_category
category
t.calendar_year
year
SUM(s.amount_sold)
total_amount
100 * SUM(s.amount_sold) / LAG(SUM(s.amount_sold))
OVER (PARTITION BY p.prod_category
ORDER BY t.calendar_year) -100
change_ly
, 100 * RATIO_TO_REPORT (SUM(s.amount_sold))
OVER (PARTITION BY t.calendar_year)
pct_year
, RANK ()
OVER (PARTITION BY t.calendar_year
ORDER BY SUM(s.amount_sold) DESC)
cat_rank
, RANK ()
OVER (PARTITION BY p.prod_category
ORDER BY SUM(s.amount_sold) DESC)
year_rank
FROM sales s
JOIN times t USING (time_id)
JOIN products p USING (prod_id)
GROUP BY t.calendar_year, p.prod_category
ORDER BY p.prod_category, t.calendar_year
Sollten Sie nun der Meinung sein, dass dieser SQL-Befehl kompliziert ist, versuchen Sie die gleiche Abfrage ohne Verwendung von analytischen Funktionen
zu implementieren. Spätestens danach werden Sie die Einfachheit der analytischen Funktionen zu schätzen wissen.
Fazit
Mit den analytischen Funktionen hat Oracle eine SQL-Erweiterung eingeführt,
die sehr vielseitig und hilfreich ist, wenn es um die Formulierung von komplexen Auswertungen und Berechnungen geht. Aufgrund der guten Performance
eignen sie sich insbesondere auch für Abfragen auf grossen Datenmengen –
hauptsächlich, aber nicht nur im Umfeld von Data Warehouses. Hat man die
vielfältigen Möglichkeiten von analytischen Funktionen einmal entdeckt,
möchte man dieses Oracle-Feature nicht mehr missen.
Literatur
• Oracle 9i SQL Reference, Release 2 (9.2), Part No. A96540-02, Chapter 6
• Oracle 9i Data Warehousing Guide, Release 2 (9.2), Part No. A96520-01,
Chapter 19
• Oracle Database SQL Reference 10g Release 1 (10.1), Part No. B10759-01,
Chapter 7
• Oracle Database Data Warehousing Guide, 10g Release 1 (10.1), Part No.
B10736-01, Chapter 21
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Weitere Informationen zu analytischen Funktionen werden in den TrivadisKursen SQL-B (SQL für Fortgeschrittene) und O-DWH (Data Warehousing mit
Oracle) vermittelt.
Kontaktadresse:
Dani Schnider
Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg
Telefon:
Fax:
E-Mail:
Internet:
+41(0)1-808 70 20
+49(0)1-808 70 21
[email protected]
www.trivadis.com