Die Unterstützung von SQL/OLAP im SQL

Fachthema
Unterstützung von SQL/OLAP
DUŠAN PETKOVIĆ
Die Unterstützung von SQL/OLAP im SQLStandard und in relationalen Datenbanksystemen
Die Datenbanksprache SQL war von Anfang an so konzipiert,
dass sie sich besonders gut für die Verarbeitung von operationalen Unternehmensdaten eignete. In den letzten Jahren ist
ein weiteres Anwendungsgebiet für die SQL-Sprache dazugekommen: Die komplexe Analyse von Daten, die nicht in Clientanwendungen umständlich durchgeführt werden soll, sondern direkt vom Datenbankserver unterstützt wird. Für diese
Art von Datenverarbeitung war die SQL-Sprache ursprünglich
nicht besonders gut geeignet. Aus diesem Grund wurde im
ISO-Gremium ein Vorschlag für die Erweiterung von SQL um
analytische Operationen und Funktionen unter dem Namen
SQL/OLAP verabschiedet. Die heutigen relationalen Datenbanksysteme unterstützen die vorgeschlagenen Konzepte von
SQL/OLAP. Zusätzlich dazu hat jeder Datenbankhersteller seine eigenen SQL-Erweiterungen für die komplexe Analyse von
Daten.
Der erste Teil des Beitrags gibt einen Überblick über die im
SQL-Standard vorgeschlagenen Operationen und Funktionen
für die komplexe Analyse. Der zweite Teil des Beitrags, der in
der November-Ausgabe des Datenbank-Spektrums (Heft 23)
erscheinen wird, zeigt, inwieweit die vorgeschlagenen Konzepte in den wichtigsten kommerziellen relationalen Datenbanksystemen realisiert sind. Zusätzlich werden die jeweiligen
nicht standardisierten SQL/OLAP-Erweiterungen in Oracle,
IBM DB2 und Microsoft SQL Server betrachtet.
1 Einleitung
SQL/OLAP entstand im Jahre 1999. Zu Beginn dieses Jahres haben Oracle und IBM gemeinsam dem SQL-Normierungsgremium einen Vorschlag unterbreitet, wie die Erweiterungen in
Bezug auf analytische Funktionen aussehen sollten. IBM hat
Teile dieser Spezifikation schon Mitte desselben Jahres in
DB2 UDB V6.2 implementiert. Oracle Version 8i und DB2 UDB
Version 7.1 haben beide signifikante Teile des Vorschlags enthalten.
Auch weitere Hersteller waren in diesen Prozess involviert: Besonders die Hersteller von Clientanwendungen, wie MicroStrategy, Brio und Cognos, fanden die Idee, die Datenanalyse aus
(ihren) Clientanwendungen in den Datenbankserver zu verlagern,
vielversprechend. (Der generelle Nachteil der Datenverarbeitung
im Client liegt darin, dass unter Umständen eine sehr große Menge von Daten aus dem Server in den Client bewegt werden muss,
damit sie bearbeitet werden kann.)
Die wichtigsten Personen, die sowohl an der Entstehung der Idee
als auch an der Spezifikation mitgewirkt haben, waren Dr. Hamid
Pirahesh von IBM und Andy Witkowski von Oracle. Dr. Pirahesh
hat im Jahre 1998 mit seinem Team angefangen, die Erweiterun44
Datenbank-Spektrum 22/2007
gen in SQL in Bezug auf analytische Funktionen zu untersuchen.
Zum selben Zeitpunkt hat Oracle ein ähnliches Projekt gestartet.
Die beiden Datenbankhersteller haben von den Arbeiten der jeweils anderen Firma erfahren und danach entschieden, zusammen
vorzugehen. Auf der Basis der bereits erfolgten Studien haben die
beiden Firmen in sehr kurzer Zeit eine gemeinsame Spezifikation
erstellt. Dieser Vorschlag wurde mit den führenden Herstellern
von Clientapplikationen diskutiert und anschließend dem ANSIKomitee vorgelegt. Im Laufe desselben Jahres wurde unter der
Leitung von Jim Melton das Projekt intensiv weitergeführt und
abschließend im Jahre 2000 als Änderungsantrag (amendment)
zu SQL:1999-Standard verabschiedet [ISO/IEC 13294 2001].
(Durch das Hinzufügen von SQL/OLAP als Änderungsantrag war
es den Mitgliedern des Standardisierungsgremiums möglich,
SQL/OLAP als Teil des Standards im Jahre 2000 zu veröffentlichen, ohne die Verabschiedung des Standards ein Jahr davor zu
behindern.) Im aktuellen SQL-Standard (SQL:2003) befindet sich
die Spezifikation einzelner OLAP-Operationen und -Funktionen
im Part 2 des Standards [ISO/IEC 9075 2003].
Dieser Beitrag dient als »State of the Art« im Feld der analytischen Operationen und Funktionen. Wir beschreiben sowohl die
gesamte im SQL-Standard vorgeschlagene OLAP-Funktionalität
als auch das, was davon in den wichtigsten existierenden relationalen Datenbanksystemen implementiert wurde. Genauer gesagt,
wir vergleichen die im SQL:2003-Standard spezifizierten analytischen Operationen und Funktionen mit den entsprechenden Operationen und Funktionen, die die Hersteller IBM, Microsoft und
Oracle in ihren Datenbanksystemen implementiert haben. Dabei
wird u.a. gezeigt, welche Ähnlichkeiten und Unterschiede bei der
Implementierung von analytischen Operationen und Funktionen
zwischen den Datenbanksystemen DB2, Oracle und SQL Server
existieren.
Dieser Beitrag hat zwei Teile. Jeder Teil umfasst zwei Kapitel.
Nach der allgemeinen Einführung im ersten Kapitel, werden im
zweiten Kapitel alle im SQL-Standard vorgeschlagenen Erweiterungen in Bezug auf SQL/OLAP dargestellt. Zunächst wird das
Window-Konstrukt zusammen mit allen seinen Teilen erörtert.
Danach behandelt dieses Kapitel die Erweiterungen der GROUP
BY-Klausel, und zum Schluss wird die Menge von im Zusammenhang mit der Datenanalyse spezifizierten Funktionen diskutiert.
Die Implementierung von SQL/OLAP-Operationen und -Funktionen in Oracle, DB2 und SQL Server ist das Thema des vierten
Kapitels. In diesem Kapitel wird gezeigt, welche Funktionalität
von SQL/OLAP, die im SQL-Standard spezifiziert wurde, von
den Datenbankherstellern implementiert wurde. Kapitel 5 erläutert sowohl die proprietären OLAP-Erweiterungen der drei Datenbankhersteller als auch die Funktionalität, die vom SQL-Stan-
Unterstützung von SQL/OLAP
Fachthema
dard abweicht. Der Beitrag wird mit einer Zusammenfassung
(siehe Kapitel 3) und einer tabellarischen Darstellung aller standardisierten SQL/OLAP-Operationen und -Funktionen abgeschlossen (Kapitel 4 und 5 werden im Heft 23 erscheinen).
order clause«) aus einem existierenden Window-Konstrukt zu
übernehmen, sodass diese Teile nicht mehrfach geschrieben werden müssen. (Diese Vorgehensweise erleichtert es dem Optimierer, die Gleichheit von ORDER BY-Klauseln zu erkennen.)
2 SQL/OLAP im SQL-Standard
Weil die obige Spezifikation eines Window-Konstruktes manchmal kompliziert sein kann, unterstützt der SQL-Standard zwei
verschiedene Formen für die Definition von analytischen Funktionen:
Die OLAP-Funktionalität im SQL-Standard kann in drei Teile unterteilt werden:
• das Window-Konstrukt
• die Erweiterung der GROUP BY-Klausel
• Anfragefunktionen
Die folgenden Abschnitte beschreiben diese Teile.
2.1 Das Window-Konstrukt
Die wohl wichtigste Erweiterung, die SQL/OLAP enthält, ist das
Window-Konstrukt. Ein Fenster (Window) spezifiziert eine vom
Benutzer definierte Menge von Reihen, auf die eine Funktion angewendet wird. Die Größe eines Fensters kann dabei unterschiedlich spezifiziert werden: Meistens wird, ausgehend von der aktuellen Reihe, ein Reihenbereich definiert.
Die Syntax des Window-Konstruktes innerhalb der Tabellenspezifikation einer SELECT-Anweisung sieht folgendermaßen aus:
<table expression> ::=
<from clause>
[ <where clause> ]
[ <group by clause> ]
[ <having clause> ]
[ <window clause> ]
<window clause> ::=
WINDOW <window definition list>
<window definition list> ::=
<window definition> [ { <comma>
<window definition> } ... ]
<window definition> ::=
<new window name> AS <window specification>
<new window name> ::= <window name>
<window specification> ::=
<left paren> <window specification details>
<right paren>
<window specification details> ::=
[ <existing window name> ]
[ <window partition clause> ]
[ <window order clause> ]
[ <window frame clause> ]
<existing window name> ::= <window name>
Aus der obigen Syntax ist ersichtlich, dass die explizite Spezifikation eines Window-Konstruktes (»window specification«) immer am Ende aller in den SELECT-Anweisungen definierten
Klauseln angegeben werden muss. Genauso spezifiziert der SQLStandard, dass ein Window-Konstrukt neben »existing window
name« drei optionale Teile enthält:
• Partitionierung (»window partition clause«)
• Sortierung (»window order clause«)
• »window frame clause«
»existing window name« erlaubt es, die PARTITION BY-Klausel
(»window partition clause«) und ORDER BY-Klausel (»window
• die explizite Form
• die »in-line«-Form (»in-line window specification«)
Die folgende Syntax für analytische Funktionen (»window function«), die in Abschnitt 2.6 beschrieben werden, zeigt, wie die
»in-line«-Form verwendet werden kann:
<window function> ::=
<window function type> OVER
<window name or specification>
<window function type> ::=
<rank function type> <left paren>
<right paren>
| ROW_NUMBER <left paren> <right paren>
| <aggregate function>
<rank function type> ::=
RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST
<window name or specification> ::=
<window name>
| <in-line window specification>
<in-line window specification> ::=
<window specification>
Die explizite Form des Window-Konstruktes wird, wie schon erwähnt, am Ende aller Klauseln der SELECT-Anweisung spezifiziert (siehe Beispiel 2). Die »in-line«-Form des Window-Konstruktes wird in der Projektion der entsprechenden Anfrage
formuliert (siehe Beispiel 3).
Bevor wir anfangen, das Window-Konstrukt und SQL/OLAPFunktionen zu zeigen, werden wir die Tabelle employee einführen, die in fast allen Beispielen verwendet wird.
Beispiel 1
Dieses Beispiel zeigt die Erstellung der Tabelle employee gemeinsam mit den INSERT-Anweisungen, die einige Reihen in diese
Tabelle einfügen.
CREATE TABLE employee
(emp_id INT NOT NULL,
dept_id INT NOT NULL,
first_name VARCHAR(25),
last_name VARCHAR(25),
salary DECIMAL(8,2),
job VARCHAR(20),
year INT);
INSERT INTO employee VALUES (1111, 10,
'Martha', 'White', 4400, 'IT_PROG',2001);
INSERT INTO employee VALUES (1112, 10,
'John', 'Black', 8800, 'IT_PROG', 2003);
INSERT INTO employee VALUES (1113, 20,
'Bill', 'Austin', 7600, 'MK_REP', 2001);
INSERT INTO employee VALUES (1114, 20,
'Diana', 'Kimes', 4300, 'MK_MAN', 2003);
Datenbank-Spektrum 22/2007
45
Fachthema
Unterstützung von SQL/OLAP
INSERT INTO employee VALUES (1115, 20,
'David', 'Peters', 7600, 'IT_PROG', 2004);
INSERT INTO employee VALUES (1116, 30,
'Sibille', 'Peterson',12000,'AX_ASST', 2001);
INSERT INTO employee VALUES (1117, 30,
'Jack', 'Klein', 9900, 'MK_REP', 2003);
INSERT INTO employee VALUES (1118, 30,
'Alex', 'Armstrong', 8500, 'MK_REP', 2004);
INSERT INTO employee VALUES (1119, 30,
'Jennifer', 'May', 6700, 'AX_ASST', 2005);
INSERT INTO employee VALUES (1120, 40,
'Roy', 'Hunt', 9900, 'IT_PROG', 2005);
INSERT INTO employee VALUES (1121, 40,
'Wendy', 'Blunt', 8800, 'AX_ASST', 2004);
INSERT INTO employee VALUES (1122, 50,
'Valli', 'Begg', 7900, 'MK_MAN', 2001);
INSERT INTO employee VALUES (1123, 50,
'Pat', 'Donaldson', 4900, 'MK_MAN', 2001);
Abbildung 1 zeigt den Inhalt der Tabelle employee.
2.2 Die Bildung von Partitionen
Die Window-Partitionierung teilt die durch die Anfrage ausgewählten Reihen in eine oder mehrere Partitionen. Sie hat sehr viele Ähnlichkeiten mit der Gruppierung von Reihen mittels der
GROUP BY-Klausel, weil die Ergebnismenge, genauso wie bei
GROUP BY, nach einem oder mehreren Merkmalen gruppiert
wird. Im Unterschied zu GROUP BY, wo jede Gruppe als eine
einzelne Reihe in der Ergebnismenge vorkommt, gehört bei der
Window-Partitionierung jede einzelne Reihe der Partition zu der
Ergebnismenge. Die beiden Anfragen im folgenden Beispiel zeigen den Unterschied zwischen der Window-Partitionierung und
der GROUP BY-Klausel.
Beispiel 2
SELECT dept_id, SUM(salary) AS summe
FROM employee
GROUP BY dept_id;
SELECT dept_id, last_name as name,
SUM(salary) OVER (PARTITION BY dept_id)
AS summe
FROM employee;
Die Ausgaben der beiden Anweisungen sehen folgendermaßen
aus:
dept_id
--------10
20
30
40
50
summe
-----------------------------13200.00
19500.00
37100.00
18700.00
12800.00
dept_id
--------10
10
20
20
20
30
30
30
30
40
40
50
50
name
----------------White
Black
Austin
Kimes
Peters
Peterson
Klein
Armstrong
May
Hunt
Blunt
Begg
Donaldson
summe
-----------13200.00
13200.00
19500.00
19500.00
19500.00
37100.00
37100.00
37100.00
37100.00
18700.00
18700.00
12800.00
12800.00
Die erste SELECT-Anweisung liefert insgesamt 5 Reihen (für
jeden dept_id-Wert je eine), während die zweite alle Reihen der
Tabelle employee ausgibt.
Ein weiterer Unterschied zwischen dem Window-Konstrukt und
der GROUP BY-Klausel ist ebenfalls im vorherigen Beispiel zu
erkennen: Im Falle des Window-Konstruktes darf die Projektion
beliebige Spalten der Tabelle enthalten. (Das ist die Konsequenz
daraus, dass die Ergebnismenge einzelne Reihen in jeder Gruppierung enthält.)
Beispiel 3
Dieses Beispiel zeigt die einfache Form einer Partitionierung mit
der expliziten Form des Window-Konstruktes.
SELECT dept_id, last_name AS name,
SUM(salary) OVER w AS sum_sal_dept
FROM employee
WINDOW w AS (PARTITION BY dept_id);
emp_id
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
46
dept_id
10
10
20
20
20
30
30
30
30
40
40
50
50
first_name
Martha
John
Bill
Diana
David
Sibille
Jack
Alex
Jennifer
Roy
Wendy
Valli
Pat
last_name
salary
White
4400.00
Black
8800.00
Austin
7600.00
Kimes
4300.00
Peters
7600.00
Peterson
12000.00
Klein
9900.00
Armstrong
8500.00
May
6700.00
Hunt
9900.00
Blunt
8800.00
Begg
7900.00
Donaldson 4900.00
Datenbank-Spektrum 22/2007
job
IT_PROG
IT_PROG
MK_REP
MK_MAN
IT_PROG
AX_ASST
MK_REP
MK_REP
AX_ASST
IT_PROG
AX_ASST
MK_MAN
MK_MAN
year
2001
2003
2001
2003
2004
2001
2003
2004
2005
2005
2004
2001
2001
Abb. 1: Inhalt der Tabelle employee
Unterstützung von SQL/OLAP
Beispiel 4
Dieses Beispiel entspricht dem vorherigen Beispiel, das WindowKonstrukt ist diesmal »in-line« in der Projektion der SELECTAnweisung definiert.
SELECT dept_id, last_name AS name,
SUM(salary) OVER (PARTITION BY
dept_id) AS sum_sal_dept
FROM employee;
Die PARTITION BY-Klausel in den Beispielen 2 und 3 spezifiziert, dass alle Reihen der Tabelle employee nach den Werten der
Spalte dept_id gruppiert (partitioniert) werden und danach die
Summe von Gehältern für jede Partition gebildet wird. Die
OVER-Klausel fasst die Reihen zusammen, auf die eine Funktion, in diesem Fall die Aggregatfunktion SUM, angewendet
wird. (Die Funktionen, die auf ein Window-Konstrukt angewendet werden können, werden analytische Funktionen genannt.)
Die standardisierte Syntax des Window-Konstruktes ermöglicht
auch die Definition eines »leeren« Fensters, weil alle drei Teile
der Klausel optional sind.
Beispiel 5
Dieses Beispiel zeigt die einfachste Form eines Fensters. Die Anfrage ermittelt alle Reihen der Tabelle employee mit der Aufsummierung aller Gehälter in der letzten Spalte der Ergebnismenge.
SELECT dept_id, last_name AS name,
SUM(salary) OVER () AS summe
FROM employee;
2.3 Sortierung innerhalb von Partitionen
Die zweite optionale Komponente des Window-Konstruktes ist
die Sortierung. Die Sortierung wird mithilfe der ORDER BYKlausel für jede einzelne Partition durchgeführt. Die Syntax für
diesen Teil des Window-Konstruktes sieht folgendermaßen aus:
<window order clause> ::=
ORDER BY <sort specification list>
<sort specification list> ::=
<sort specification> [
{ <comma> <sort specification> } ... ]
<sort specification> ::=
<sort key>
[ <ordering specification> ] [
<null ordering> ]
<sort key> ::= <value expression>
<ordering specification> ::= ASC | DESC
<null ordering> ::= NULLS FIRST | NULLS LAST
Wie aus der Syntax ersichtlich, entspricht die ORDER BY-Klausel der gleichnamigen Klausel in der SELECT-Anweisung. Der
einzige Unterschied ist die Existenz der NULLS FIRST- bzw.
NULLS LAST-Angabe. Diese Angaben spezifizieren, wie die
NULL-Werte in jeder Partition geordnet werden sollen. Im Falle
von NULLS FIRST werden alle NULL-Werte in der entsprechenden Sortierung vor bzw. bei NULLS LAST nach allen anderen
Werten innerhalb der jeweiligen Partition sortiert. (Es können
mehrere verschiedene Sortierungen innerhalb eines WindowKonstruktes existieren.)
Die Sortierung der Reihen für die ORDER BY-Klausel der
SELECT-Anweisung ist nach der Spezifikation des Standards
Fachthema
»implementation defined«, das bedeutet, dass Datenbankhersteller selbst entscheiden können, ob sie NULL-Werte vor oder hinter
allen Nicht-NULL-Werten platzieren möchten.
Beispiel 6
In diesem Beispiel wird innerhalb eines »in-line«-Fensters die
Sortierung nach dem Familiennamen des Mitarbeiters durchgeführt. Weil keine PARTITION BY-Klausel angegeben ist, wird
die ganze Ergebnismenge als eine Partition betrachtet.
SELECT dept_id, last_name AS name,
SUM(salary) OVER(ORDER BY last_name NULLS
FIRST ROWS UNBOUNDED PRECEDING) AS sum_sal
FROM employee;
2.4 »Window frame clause«
Die letzte Komponente – »window frame clause« – spezifiziert,
auf welche Reihen aus der Umgebung der aktuellen Reihe eine
Aggregatfunktion angewendet wird. Die Syntax sieht folgendermaßen aus:
<window frame clause> ::=
<window frame units>
<window frame extent>
[ <window frame exclusion> ]
<window frame units> ::= ROWS | RANGE
<window frame extent> ::=
<window frame start>
| <window frame between>
<window frame start> ::= UNBOUNDED PRECEDING
| <unsigned value specification> PRECEDING
| CURRENT ROW
<window frame between> ::= BETWEEN
<window frame bound 1> AND
<window frame bound 2>
<window frame bound 1> ::= <window frame bound>
<window frame bound 2> ::= <window frame bound>
<window frame bound> ::=
<window frame start>
| UNBOUNDED FOLLOWING
| <unsigned value specification> FOLLOWING
<window frame exclusion> ::=
EXCLUDE CURRENT ROW
| EXCLUDE GROUP
| EXCLUDE TIES
| EXCLUDE NO OTHERS
Wie aus der Syntax ersichtlich, existieren für »window frame
clause« drei Angaben. Die erste Angabe – »window frame units«
– bezieht sich auf die möglichen Arten der Reihengruppierung:
ROWS bzw. RANGE. Die zweite Angabe – »window frame extent« – spezifiziert die Endpunkte des Fensters, das ausgegeben
werden soll. Schließlich gibt es auch die Möglichkeit, die Endpunkte des Fensters durch die Negation, d.h. durch das Ausschließen von explizit genannten Reihen, festzulegen (»window frame
exclusion«).
Um die Begriffe ROWS und RANGE zu erläutern, werden wir
zuerst die Nachbarschaft der aktuellen Reihe (CURRENT ROW)
definieren. Die Nachbarschaft einer aktuellen Reihe ist eine Teilmenge der Partition, zu der diese gehört, und zwar ein Reihenintervall bezüglich der angegebenen Sortierung.
Datenbank-Spektrum 22/2007
47
Fachthema
Unterstützung von SQL/OLAP
Bei der Angabe ROWS werden die Untergrenze und Obergrenze
dieses Intervalls durch die relative Position zur aktuellen Reihe
angegeben, also die Anzahl von Reihen vor oder nach der aktuellen Reihe, an der die jeweilige Grenze liegt.
Bei der Angabe RANGE werden die Untergrenze und Obergrenze dieses Intervalls durch den Vergleich mit den Sortierkriterien
bestimmt; das Intervall enthält also mit jeder Reihe auch alle Duplikate (bei ROWS ist das nicht der Fall). Wenn in der ORDER
BY-Klausel genau ein Sortierkriterium steht, kann man auch den
Abstand der Grenze zur aktuellen Reihe angeben, nämlich durch
die Differenz des Sortierkriteriums zwischen der aktuellen Reihe
und der jeweiligen Grenze. Dabei muss in der ORDER BY-Klausel genau ein Sortierkriterium stehen, und sein Datentyp muss die
Bildung von Differenzen zulassen: Es muss also einen numerischen Typ, einen DATETIME-Typ oder einen INTERVAL-Typ
enthalten.
In beiden Fällen (ROWS und RANGE) kann die Nachbarschaft
einer aktuellen Reihe leer sein: Bei ROWS ist z.B. eine Nachbarschaft, die aus den zehn direkten Vorgängern besteht (ROWS
BETWEEN 10 PRECEDING AND 1 PRECEDING), immer
dann leer, wenn die CURRENT ROW die erste Reihe ihrer Partition ist; und wenn wir mit RANGE als Nachbarschaft die Reihen
aus den zwei vorherigen Jahren suchen (RANGE BETWEEN 2
PRECEDING AND 1 PRECEDING), so ist es durchaus möglich,
dass es keine Reihen für die gesuchten Jahre gibt.
Beispiel 7
Das Beispiel zeigt die Verwendung der RANGE-Angabe innerhalb einer »window frame clause«.
SELECT year, last_name AS name,
COUNT(salary) OVER(ORDER BY dept_id
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS cnt
FROM employee
WHERE year = 2001;
Beispiel 8
Das Beispiel zeigt, wie man mithilfe der ROWS-Angabe innerhalb einer »window frame clause« sogenannte »gleitende Aggregate« (sliding aggregations) implementieren kann.
SELECT year, last_name AS name,
COUNT(salary) OVER(ORDER BY dept_id
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS cnt
FROM employee
WHERE year = 2001;
Die Ergebnisse der beiden vorherigen Beispiele zeigen den Unterschied für die beiden Angaben RANGE und ROWS:
YEAR
NAME
2001
2001
2001
2001
2001
-----------
White
Austin
Peterson
Begg
Donaldson
-----------
48
Datenbank-Spektrum 22/2007
CNT
1
1
1
2
2
-----
2001
2001
2001
2001
2001
White
Austin
Peterson
Begg
Donaldson
2
3
3
3
2
Mit der BETWEEN-Angabe werden die Untergrenze und Obergrenze von »window frame« in Bezug auf die aktuelle Reihe festgelegt, z.B.:
BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING
Ohne diese Angabe ist die Obergrenze immer CURRENT ROW
und nur die Untergrenze wird explizit festgelegt, z.B.:
ROWS 3 PRECEDING
Aus o.g. Grund sind folgende Angaben identisch:
...ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
...ROWS 3 PRECEDING
Der SQL-Standard erlaubt nur bei der BETWEEN-Angabe die
obere Grenze des Fensters explizit zu spezifizieren. Des Weiteren
ist bei der BETWEEN-Angabe die Reihenfolge der Grenzen festgelegt und kann nicht umgekehrt spezifiziert werden.
Mit EXCLUDE kann man aus einem »window frame« diejenigen
Reihen entfernen, die in allen Sortierkriterien mit der aktuellen
Reihe übereinstimmen, also:
• die aktuelle Reihe selber (EXCLUDE CURRENT ROW)
• nur die Reihen mit denselben Werten für die Sortierkriterien
wie die aktuelle Reihe (EXCLUDE TIES)
• sowohl die aktuelle Reihe als auch TIES (EXCLUDE
GROUP)
2.5 Erweiterung der GROUP BY-Klausel
Der SQL-Standard erweitert die GROUP BY-Klausel um drei Angaben:
• ROLLUP
• CUBE
• GROUPING SETS
Beispiel 9
In diesem Beispiel wird gezeigt, welcher Unterschied zwischen
der Gruppierung mit der GROUP BY-Klausel und der Erweiterung von GROUP BY durch die ROLLUP-Angabe existiert.
SELECT dept_id, year, SUM(salary) AS sum1
FROM employee
GROUP BY(dept_id, year);
SELECT dept_id, year, SUM(salary) AS sum1
FROM employee
GROUP BY ROLLUP(dept_id, year);
Der Hauptunterschied zwischen der ersten und der zweiten
SELECT-Anweisung ist, dass die GROUP BY-Klausel in der ersten nur Werte von einzelnen Gruppierungen nach den entsprechenden Spalten ausgibt, während die zweite durch die ROLLUPAngabe zusätzliche Werte, die sogenannten Super-Aggregate, er-
Fachthema
Unterstützung von SQL/OLAP
mittelt und ausgibt. (Super-Aggregate sind Summen, die durch
Aufaddierung nach einem Gruppierungsmerkmal entstehen.
Summe aller Gehälter für dept_id=10 in der Tabelle employee
wäre ein Beispiel für ein Super-Aggregat.)
alternativ ersetzt werden. Damit kann eine ROLLUP-Gruppierung durch eine Folge von Gruppenmengen und mithilfe der
GROUPING SETS-Klausel folgendermaßen implementiert werden:
Die folgenden Reihen sind der Teil der Ergebnismenge der zweiten SELECT-Anweisung, die in der ersten nicht vorkommen:
GROUP BY ROLLUP (dept_id, year) ist äquivalent zu:
DEPT_ID
10
20
30
40
50
null
YEAR
null
null
null
null
null
null
SUM1
13200
19500
37100
18700
12800
101300
Wie aus dem Teilergebnis zu sehen ist, wird die Aufaddierung
nach einer Gruppierungsspalte durch »null« angezeigt. Die Aufaddierung der Werte beider Gruppierungsspalten wird dementsprechend durch zwei »null«-Angaben spezifiziert.
Die ROLLUP-Angabe erstellt die Super-Aggregate nur über die
letzte angegebene Gruppierungsspalte, über die beiden letzten
und schließlich über alle angegebenen Gruppierungsspalten (das
sogenannte »grand total«). Aus diesem Grund ist die Ausgabe für
die folgenden beiden Angaben unterschiedlich:
GROUP BY ROLLUP(dept_id, year);
GROUP BY ROLLUP(year, dept_id);
Falls die Ausgabe aller möglichen Kombinationen der Super-Aggregate für beide Spalten erwünscht ist, wird die CUBE-Angabe
verwendet.
Beispiel 10
Dieses Beispiel gibt mithilfe der CUBE-Klausel alle möglichen
Kombinationen für beide Spalten dept_id und year aus.
SELECT dept_id, year, SUM(salary)
FROM employee
GROUP BY CUBE(dept_id, year);
Während die CUBE- und ROLLUP-Klauseln eine vordefinierte
Menge von Super-Aggregaten für die Ergebnismenge einer Anfrage ermitteln, berechnet die GROUPING SETS-Klausel Gruppen in Bezug auf verschiedene Spaltengruppen in derselben Anfrage. Die Verwendung dieser Klausel ist komplexer als die
Verwendung der anderen beiden, erlaubt aber eine feinere Auswahl von Gruppen.
Beispiel 11
Dieses Beispiel zeigt die Gruppierung nach zwei verschiedenen
Gruppen: dept_id und der Kombination der beiden Spalten
dept_id und year.
SELECT dept_id, year, SUM(salary) AS summe
FROM employee
GROUP BY GROUPING SETS ((dept_id),
(dept_id, year));
Die Erstellung von Super-Aggregaten mit den CUBE- und
ROLLUP-Klauseln kann durch eine Reihe von Gruppenmengen
GROUP BY GROUPING SETS ((dept_id, year),
(dept_id), ())
Genauso kann eine Gruppierung mithilfe der CUBE-Klausel
durch alle Permutationen der Elemente der Gruppenmengen ersetzt werden:
GROUP BY CUBE (dept_id, year) ist äquivalent zu:
GROUP BY GROUPING SETS ((dept_id, year),
(dept_id), (year), ())
Eine zusätzliche Erweiterung der GROUP BY-Klausel ist die
»feature« T434 des SQL-Standards, die die Angaben ALL (der
Standardwert) und DISTINCT zu dieser Klausel erlaubt. Mit der
Angabe DISTINCT werden Duplikate aus der Spezifikation für
GROUPING SETS eliminiert. (Kein einziges Datenbanksystem
unterstützt diese Erweiterung.)
2.6 SQL/OLAP-Anfragefunktionen
Die SQL/OLAP-Anfragefunktionen können in folgende Gruppen
unterteilt werden:
•
•
•
•
•
•
•
Aggregatfunktionen
Rangfunktionen
die GROUPING-Funktion
statistische Funktionen
inverse Verteilungsfunktionen
hypothetische Aggregatfunktionen
die WIDTH_BUCKET-Funktion
Die Aggregatfunktionen SUM, AVG, COUNT, MAX und MIN
werden in diesem Beitrag nicht erörtert, weil sie schon länger im
SQL-Standard spezifiziert und von etlichen Datenbanksystemen
implementiert sind.
Die oben genannten Funktionen lassen sich in zwei Gruppen unterteilen: erstens in diejenigen, die als »normale« Funktionen verwendet werden können, und zweitens in diejenigen, die zusätzlich
als analytische Funktionen (»window functions«) benutzt werden
können.
Von allen oben genannten Funktionen können hypothetische Aggregatfunktionen und die Funktion WIDTH_BUCKET nicht als
analytische Funktionen verwendet werden.
2.6.1 Rangfunktionen
Rangfunktionen geben für jede Reihe einer Partition einen Rang
als Nummer aus. Folgende Rangfunktionen werden vom SQLStandard unterstützt:
• RANK
• DENSE_RANK
Folgende Funktionen werden als Verteilungsfunktionen
(»distributed functions«) im SQL-Standard genannt:
• PERCENT_RANK
• CUME_DIST
Datenbank-Spektrum 22/2007
49
Fachthema
Unterstützung von SQL/OLAP
Um die Terminologie zu vereinfachen, werden wir im weiteren
Text alle vier o.g. Funktionen als Rangfunktionen bezeichnen.
Die ersten beiden Funktionen geben eine natürliche Zahl als Ergebnis zurück, während die letzten beiden einen normalisierten
Wert (einen Wert zwischen 0 und 1) liefern. (Eine weitere Funktion, die auch hier beschrieben wird, obwohl sie nicht zu den
Rangfunktionen gehört, ist die ROW_NUMBER-Funktion.)
Beispiel 12
In diesem Beispiel werden die drei Funktionen RANK,
DENSE_RANK und ROW_NUMBER gezeigt.
SELECT RANK() OVER (ORDER BY year) AS rnk,
DENSE_RANK() OVER (ORDER BY year)
AS dense_rnk,
ROW_NUMBER() OVER (ORDER BY year)
AS row_no, year
FROM employee;
Mithilfe der ROW_NUMBER-Funktion wird jeder Reihe innerhalb einer Partition ein eindeutiger Wert zugewiesen, wobei die
erste Reihe der Partition in der Sortierreihenfolge des Fensters
den Wert 1 liefert, für die zweite den Wert 2 usw. (Weil in unserem
Beispiel keine Partitionen spezifiziert sind, wird die ganze Ergebnismenge als eine Partition betrachtet.)
Die RANK-Funktion liefert für die aktuelle Reihe den Wert 1 plus
die Anzahl der Reihen in derselben Partition mit echt kleineren
Sortierkriterien als die betrachtete. Alle Reihen in einer Partition
mit gleichwertigen Sortierkriterien haben also denselben Rang,
und die Werte von RANK in einer Partition können Lücken aufweisen.
Die DENSE_RANK-Funktion liefert für die aktuelle Reihe den
Wert 1 plus die Anzahl der echt kleineren Sortierkriterien als die
betrachtete Reihe in der Partition. Alle Reihen in einer Partition
mit gleichwertigen Sortierkriterien haben also denselben
DENSE_RANK; die Werte von DENSE_RANK enthalten aber
keine Lücken. (RANK und DENSE_RANK liefern dieselben
Werte, falls es bezüglich der Sortierkriterien keine Duplikate
gibt.)
Mithilfe der ersten 6 Reihen der Ergebnismenge wird der Unterschied zwischen den Funktionen RANK und DENSE_RANK erörtert:
RNK
ROW_NO
YEAR
1
DENSE_RNK
1
1
2001
1
1
2
2001
1
1
3
2001
1
1
4
2001
1
1
5
2001
6
2
6
2003
Die PERCENT_RANK-Funktion gibt den Bruchteil der Reihen
mit echt kleineren Sortierkriterien als die aktuelle Reihe an, den
diese an allen Reihen der Partition außer der aktuellen Reihe haben. Dementsprechend wird die PERCENT_RANK-Funktion
durch die Formel: (RANK -1)/(n-1) berechnet, ist also ein normalisierter Wert (n stellt die Anzahl der Reihen in der Partition
50
Datenbank-Spektrum 22/2007
dar). Für n = 1 liefert PERCENT_RANK() den Wert 0e0. Der
Rückgabewert ist vom Datentyp »approximate numeric«, d.h.
FLOAT, DOUBLE PRECISION oder REAL.
CUME_DIST ist die Verteilungsfunktion von RANK: Sie ermittelt den relativen Rang einer Reihe innerhalb einer Partition, indem die relative Anzahl von Reihen bestimmt wird, deren Rang
kleiner gleich dem Rang der aktuellen Reihe ist. CUME_DIST
wird durch folgende Formel berechnet:
CUM_DIST(x) = (1/n)*Anzahl der Reihen <=x,
wobei n die Anzahl der Reihen in der Partition ist. Der Rückgabewert ist vom Datentyp »approximate numeric«.
2.6.2 Die GROUPING-Funktion
Bei der Erstellung eines Super-Aggregates wird der Wert der
Spalte, für die aufsummiert wird, durch »null« (den NULL-Wert)
gekennzeichnet. Damit der tatsächliche NULL-Wert von dem Super-Aggregat unterschieden werden kann, hat der SQL-Standard
die GROUPING-Funktion eingeführt.
Diese Funktion hat einen oder mehrere Spaltennamen als Parameter, die alle Gruppierungsspalten sein müssen. Ihr Wert ist ganzzahlig und positiv. In einer Reihe gibt er an, ob die Reihe zu einer
normalen Gruppe gehört (Wert = 0) oder zu einer Aggregierung
von Gruppen (Super-Aggregat) nach einer oder mehreren der angegebenen Gruppierungsspalten (Wert > 0).
Aus dem Wert der GROUPING-Funktion in Binärdarstellung
kann man die für die Super-Aggregate verwendeten Gruppierungsspalten erkennen: So liefert GROUPING(A, B, C) den Wert
1, wenn das Super-Aggregat nach der Spalte C erfolgt (aber nicht
nach A oder B), und den Wert 5, wenn das Super-Aggregat nach
den Spalten A und C erfolgt (aber nicht nach B).
Beispiele für die GROUPING-Funktion werden im gleichnamigen Abschnitt in Kapitel 4 (im Heft 23) gezeigt.
2.6.3 Statistische Funktionen
Zu den statistischen Funktionen gehören:
•
•
•
•
•
STDDEV_POP und STDDEV_SAMP
VAR_POP und VAR_SAMP
COVAR_POP und COVAR_SAMP
CORR
diverse Regressionsfunktionen
Die ersten zwei Funktionspaare enthalten einen Parameter, während der Rest der aufgelisteten Funktionen zwei Parameter hat.
Die Funktion STDDEV_POP(ausdruck) berechnet die Populations-Standardabweichung aller ausdruck-Werte für jede
Reihe der Partition. Genauso berechnet die Funktion
STDDEV_SAMP(ausdruck) die Stichproben-Standardabweichung aller ausdruck-Werte für jede Reihe der Partition.
Die Funktion VAR_POP(ausdruck) ermittelt die Varianz der
Population aller ausdruck-Werte für jede Reihe der Partition und
die Funktion VAR_SAMP(ausdruck) die Varianz der entsprechenden Stichprobe. Die Funktionen COVAR_POP und
COVAR_SAMP berechnen die Populations- bzw. StichprobenKovarianz für jede Reihe der Partition.
Unterstützung von SQL/OLAP
Die Funktion CORR(dependent_var, independent_var) berechnet
den Korrelationskoeffizienten zwischen einer Menge von abhängigen (dependent_var) und unabhängigen (independent_var) Variablen.
Im SQL-Standard wurden insgesamt 9 Regressionsfunktionen
spezifiziert. Ihre Namen und die kurze Beschreibung sind in Abbildung 2 [Kline et al. 2005] angegeben.
2.6.4 Inverse Verteilungsfunktionen
Die inversen Verteilungsfunktionen enthalten einen numerischen
Parameter, der dem Intervall [0;1] angehört. Der Parameterwert
stellt den prozentuellen Rang jeder Reihe innerhalb von Reihen,
die zu der entsprechenden Partition gehören, dar. Die Syntax der
inversen Verteilungsfunktionen sieht folgendermaßen aus:
<inverse distribution function> ::=
<inverse distribution function type>
<left paren>
<inverse distribution function argument>
<right paren>
<within group specification>
<inverse distribution function argument> ::=
<numeric value expression>
<inverse distribution function type> ::=
PERCENTILE_CONT | PERCENTILE_DISC
Fachthema
2.6.5 Hypothetische Aggregatfunktionen
Hypothetische Aggregatfunktionen stehen im Zusammenhang mit den schon erwähnten Rangfunktionen RANK,
DENSE_RANK, PERCENT_RANK und CUME_DIST. Die
Aufgabe von hypothetischen Aggregatfunktionen besteht darin,
den Rangwert zurückzugeben, der dem Parameterwert in der betreffenden Partition entsprechen würde, falls der angegebene
Wert für die Tabellenspalte tatsächlich existieren würde. Die Syntax sieht folgendermaßen aus:
<hypothetical set function> ::=
<rank function name> <left paren>
<hypothetical set function value
expression list> <right paren>
<within group specification>
<within group specification> ::=
WITHIN GROUP <left paren> ORDER BY
<sort specification list> <right paren>
<hypothetical set function value expression list>
::= <value expression [
{ <comma> <value expression> } ...]
Die Syntax für die hypothetischen Aggregatfunktionen unterscheidet sich von der Syntax der »reinen« Rangfunktionen. Die
Angabe WITHIN GROUP muss zusammen mit einer Rangfunktion spezifiziert werden, damit diese als hypothetische Funktion
erkannt wird. Beispiele für hypothetische Aggregatfunktionen
werden wir in Kapitel 4 geben.
Zu den inversen Verteilungsfunktionen gehören damit:
• PERCENTILE_DISC (perzentil)
• PERCENTILE_CONT (perzentil)
Die Funktion PERCENTILE_CONT generiert einen interpolierten Wert, der, wenn man ihn zu der Partition hinzufügen würde, dem angegebenen Parameter entspräche. Die Funktion
PERCENTILE_DISC liefert den Wert mit dem kleinsten kumulativen Verteilungswert, der größer/gleich dem angegebenen Parameter ist.
Beispiele für die beiden Verteilungsfunktionen werden in Kapitel 4 gegeben.
Hypothetische Aggregatfunktionen können nicht als analytische
Funktionen benutzt werden.
2.6.6 Die WIDTH_BUCKET-Funktion
Die WIDTH_BUCKET-Funktion teilt gegebene Werte auf Gruppen (»buckets«) gleicher Intervallgröße auf. Die Syntax dieser
Funktion sieht folgendermaßen aus:
WIDTH_BUCKET(ausdruck, min, max, buckets)
ausdruck ist ein Wert, der einer Gruppe zugewiesen wird. Er basiert auf einer oder mehreren von einer Abfrage zurückgegebenen
Spalten. buckets gibt an, wie viele Gruppen über den Wertebereich von min bis max definiert werden. Wenn sich ausdruck
Abb. 2: Vom SQL-Standard unterstützte Regressionsfunktionen
REGR_AVGX(dependent_var, independent_var)
REGR_AVGY(dependent_var, independent_var)
REGR_COUNT(dependent_var, independent_var)
REGR_INTERCEPT(dependent_var, independent_var)
REGR_R2(dependent_var, independent_var)
REGR_SLOPE(dependent_var, independent_var)
REGR_SXX(dependent_var, independent_var)
REGR_SXY(dependent_var, independent_var)
REGR_SYY(dependent_var, independent_var)
Berechnet den Durchschnittswert der unabhängigen
Variablen (independent_var).
Berechnet den Durchschnittswert der abhängigen Variablen
(dependent_var ).
Berechnet die Anzahl von Reihen, die in einer Partition übrig
geblieben sind.
Berechnet den y-Abschnitt der linearen Gleichung, die durch
das Paar (dependent_var, independent_var) festgelegt ist,
mithilfe der Methode des kleinsten Quadrats.
Berechnet das Quadrat des Korrelationskoeffizienten.
Berechnet die Steigung der linearen Gleichung, die durch das
Paar (dependent_var, independent_var) festgelegt ist, mithilfe
der Methode des kleinsten Quadrats.
Addiert die Quadrate der unabhängigen Variablen.
Addiert die Produkte aller Variablenpaare.
Addiert die Quadrate der abhängigen Variablen.
Datenbank-Spektrum 22/2007
51
Fachthema
Unterstützung von SQL/OLAP
nicht in dem Wertebereich der spezifizierten Gruppen befindet,
gibt die Funktion entweder 0 oder max+1 zurück, abhängig davon, ob ausdruck kleiner als min oder größer/gleich max ist.
Das Beispiel mit dieser Funktion wird in Kapitel 3 gezeigt. (Die
Funktion WIDTH_BUCKET kann nicht als analytische Funktion
verwendet werden.)
3 Kurze Zusammenfassung des ersten Teils
Der erste Teil des Beitrags gibt den Überblick über die im SQLStandard vorgeschlagenen Operationen und Funktionen für die
komplexe Analyse. Welche von diesen Operationen und Funktionen in großen Datenbanksystemen implementiert sind, wird im
zweiten Teil des Beitrags gezeigt, der im Heft 23 erscheinen wird.
Danksagung
An dieser Stelle möchte ich mich bei meinen Kollegen aus dem
SQL-Normierungsgremium, den Herren Michael Deckers und
Jost Enderle, für die konstruktive Kritik des Beitrags bedanken.
Literatur
[Celko 2006] Celko, J.: Analytics and OLAP in SQL. Morgan Kaufmann
Publishing, 2006.
[Gennick 2005] Gennick, J.: Find Answers Faster. Oracle Magazine, March/
April 2005.
[IBM DB2 a] IBM: DB2 Version 9 for Linux, UNIX and Windows, SQL
Reference Volume 1, 2006, ftp://ftp.software.ibm.com/ps/products/db2/
infro/vr9/pdf/letter/en_US/db2s1e90.pdf.
[IBM DB2 b] IBM: DB2 Version 9 for Linux, UNIX and Windows, SQL
Reference Vol. 2, 2006, ftp://ftp.software.ibm.com/ps/products/db2/infro/
vr9/pdf/letter/en_US/db2s2e90.pdf.
[ISO/IEC 13294 2001] ISO/IEC 13249-5-2001 Information technology –
Database languages – SQL – Part 5: Host Language Bindings –
AMENDMENT 1: On-Line Analytical Processing (SQL/OLAP).
[ISO/IEC 9075 2003] ISO/IEC 9075-2:2003 Information technology – Database languages – SQL – Part 2: Foundation (SQL/Foundation).
[Kline et al. 2005] Kline, K.; Kline, D.; Hunt, B.: XML in a Nutshell.
O’Reilly, 2005.
[Lehner 2003] Lehner, W.: Datenbanktechnologie für Data-WarehouseSysteme. dpunkt.verlag, Heidelberg, 2003.
[Melton 2003] Melton, J.: Advanced SQL:1999. Morgan Kaufmann
Publishing, 2003.
[Melton & Simon 2002] Melton, J.; Simon, A.: SQL:1999: Understanding
Relational Language Components. Morgan Kaufmann Publishing, 2002.
[Oracle 2007] Oracle-Dokumentation, 2007, www.oracle.com/technology/
docmentation/index.html.
[Petkovi… 2006a] Petkovi…, D.: Open Windows of Opportunity. SQL Server
Magazine, June 2006.
[Petkovi… 2006b] Petkovi…, D.: SQL Server 2005. dpunkt.verlag,
Heidelberg, 2006.
[Türker & Gertz 2000] Türker, C.; Gertz, M.: Semantic Integrity Support in
SQL-99 and Commercial (Object-)Relational Database Management
Systems. VLDB Journal, Vol. 10, No. 4, 2000.
[Winter 2000] Winter, R.: SQL-99’s New OLAP Functions. Intelligent Enterprise, Vol. 3, January 2000.
[Zemke et al. 1999] Zemke, F.; Kulkarni, K.; Witkowsky, A.; Lyle, B.:
Introduction to OLAP Functions. ISO/IEC JTC1/ SC32 WG3:YGJ-068
ANSI NCITS H2-99-154r2, May 1999.
52
Datenbank-Spektrum 22/2007
Dušan Petković
ist Professor für Datenbanksysteme an der FH
Rosenheim. Seine Arbeitsschwerpunkte liegen in
den Bereichen Performance Tuning und Datenbankmodellierung. Er ist Mitglied des deutschen
Normierungsgremiums für die SQL-Sprache.
Prof. Dr. Dušan Petković
Fachhochschule Rosenheim
Fakultät für Informatik
Hochschulstr. 1
83024 Rosenheim
[email protected]
www.fh-rosenheim.de