Analytische Funktionen – erfolgreich eingesetzt

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 Oracle9i 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 OracleFunktionalitä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
---------10
10
10
20
20
20
20
20
30
30
30
30
30
30
ENAME
SAL
SUM_SAL
---------- ---------- ---------CLARK
2450
8750
KING
5000
8750
MILLER
1300
8750
SMITH
800
10875
JONES
2975
10875
SCOTT
3000
10875
ADAMS
1100
10875
FORD
3000
10875
ALLEN
1600
9400
WARD
1250
9400
MARTIN
1250
9400
BLAKE
2850
9400
TURNER
1500
9400
JAMES
950
9400
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
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 Oracle10g 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:
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
Rows
------48
5911266
5920777
9463
5911266
5911266
9454
48
918843
1826
918843
918843
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
Row Source Operation
--------------------------------------------------SORT AGGREGATE (cr=298420 pr=0 pw=0 time=16352528 us)
TABLE ACCESS BY LOCAL INDEX ROWID SALES (cr=298420 pr=0 pw=0 time=82781550 us)
NESTED LOOPS (cr=21875 pr=0 pw=0 time=17885399 us)
TABLE ACCESS FULL TIMES (cr=2688 pr=0 pw=0 time=57388 us)
PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=19187 pr=0 pw=0 time=30093878 us)
BITMAP CONVERSION TO ROWIDS (cr=19187 pr=0 pw=0 time=12109898 us)
BITMAP INDEX SINGLE VALUE SALES_TIME_BIX PARTITION: KEY KEY (cr=19187 pr=0 …)
SORT GROUP BY (cr=1773 pr=0 pw=0 time=1800321 us)
HASH JOIN (cr=1773 pr=0 pw=0 time=12866998 us)
TABLE ACCESS FULL TIMES (cr=56 pr=0 pw=0 time=3736 us)
PARTITION RANGE ALL PARTITION: 1 28 (cr=1717 pr=0 pw=0 time=5513208 us)
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.
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 Oracle9i 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:
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
t.calendar_year
SUM(s.amount_sold)
100 * SUM(s.amount_sold) / LAG(SUM(s.amount_sold))
OVER (PARTITION BY p.prod_category
ORDER BY t.calendar_year) -100
, 100 * RATIO_TO_REPORT (SUM(s.amount_sold))
OVER (PARTITION BY t.calendar_year)
, RANK ()
OVER (PARTITION BY t.calendar_year
ORDER BY SUM(s.amount_sold) DESC)
, RANK ()
OVER (PARTITION BY p.prod_category
ORDER BY SUM(s.amount_sold) DESC)
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
category
year
total_amount
change_ly
pct_year
cat_rank
year_rank
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
•
•
•
•
Oracle9i SQL Reference, Release 2 (9.2), Part No. A96540-02, Chapter 6
Oracle9i 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
Weitere Informationen zu analytischen Funktionen werden in den Trivadis-Kursen 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