Kapitel 27

D3kjd3Di38lk323nnm
531
27 Transact-SQL-Erweiterungen
in Bezug auf Analysis Services
Im zweiten Teil dieses Buches haben wir die Eigenschaften der Transact-SQLSprache in Bezug auf die Bearbeitung von operativen Daten gezeigt. Auf der anderen Seite war die Sprache in den früheren SQL Server-Versionen nicht für die
komplexe Datenanalyse geeignet. Die einzige Funktionalität der Sprache waren
die Aggregatfunktionen (wie SUM und COUNT) sowie die Möglichkeit, die
Daten auf eine einfache Art zu gruppieren.
Die erste Version, welche die erweiterte Funktionalität für die komplexe
Datenanalyse bot, war SQL Server 2000. Die aktuelle Version des Datenbanksystems hat diese Funktionalität signifikant durch die Einführung von Aggregatund Rangfunktionen erweitert.
27.1 OLAP-Erweiterungen in Transact-SQL
Der SQL:1999-Standard war der erste Standard, der Funktionen für die komplexe Datenanalyse enthalten hat. Der Teil des Standards, der diese Funktionalität enthält, ist unter dem Namen SQL/OLAP bekannt. SQL/OLAP führt viele
neue Funktionen und Operatoren ein.
SQL Server 2005 enthält viele Erweiterungen in Bezug auf SQL/OLAP. Manche dieser Erweiterungen sind kompatibel zum Standard und manche nicht. Alle
Erweiterungen können in folgende vier Gruppen unterteilt werden:
■
■
■
■
CUBE- und ROLLUP-Operatoren
Aggregat- und Rangfunktionen
TOP n-Klausel
Relationale Operatoren PIVOT- und UNPIVOT
Die folgenden Abschnitte beschreiben diese Erweiterungen.
27.1.1
CUBE-Operator
Die GROUP BY-Klausel definiert eine oder mehrere Spalten einer Tabelle als
Gruppenkennzeichen, wonach die Reihen gruppiert werden. Falls in einer solchen
Dusan Petkovic, SQL Server 2005, dpunkt.verlag, ISBN 978-3-89864-367-2
532
27 Transact-SQL-Erweiterungen in Bezug auf Analysis Services
GROUP BY-Klausel mit einer Aggregatfunktion die WITH CUBE-Angabe spezifiziert wird, werden zu der Ausgabe eines Ergebniswertes auch zusätzliche Ergebnisreihen ausgegeben, die sich auf alle in der GROUP BY-Klausel angegebenen
Spalten beziehen. (Diese werden Super-Aggregate genannt.) Für jede mögliche
Kombination der in der GROUP BY-Klausel erscheinenden Spalten werden
Super-Aggregate gebildet.
Beispiel 27.1 erstellt die Tabelle project_dept, die im weiteren Text als Basis
für alle Abfragen benutzt wird.
Beispiel 27.1
USE beispiel
create table project_dept
( dept_name char( 20 ) not null,
emp_cnt int not null,
budget float,
date_month datetime );
insert into project_dept values
( 'Research', 5, 50000, '01.01.2002' );
insert into project_dept values
( 'Research', 10, 70000, '01.02.2002' );
insert into project_dept values
( 'Research', 5, 65000, '01.07.2002' );
insert into project_dept values
( 'Accounting', 5, 10000, '01.07.2002' );
insert into project_dept values
( 'Accounting', 10, 40000, '01.02.2002' );
insert into project_dept values
( 'Accounting', 6, 30000, '01.01.2002' );
insert into project_dept values
( 'Accounting', 6, 40000, '01.02.2003' );
insert into project_dept values
( 'Marketing', 6, 10000, '01.01.2003' );
insert into project_dept values
( 'Marketing', 10, 40000, '01.02.2003' );
insert into project_dept values
( 'Marketing', 3, 30000, '01.07.2003' );
insert into project_dept values
( 'Marketing', 5, 40000, '01.01.2003' );
Die neue Tabelle – project_dept – enthält vier Spalten: die Spalte dept_name
(Abteilungsname), die Spalte emp_cnt (Mitarbeiteranzahl) und die Spalten budget
(Mittel jeder Abteilung) und date_month. Mit den INSERT-Anweisungen in Beispiel 27.1 werden insgesamt elf Reihen in die Tabelle geladen. Der Inhalt der
Tabelle project_dept wird in der Tabelle 27–1 gezeigt.
27.1 OLAP-Erweiterungen in Transact-SQL
dept_name
Tab. 27–1
emp_cnt
533
budget
date_month
50000
01.01.2002
Research
5
Research
10
70000
01.02.2002
Research
5
65000
01.07.2002
Accounting
5
10000
01.07.2002
Accounting
10
40000
01.02.2002
Accounting
6
30000
01.01.2002
Accounting
6
40000
01.02.2003
Marketing
6
10000
01.01.2003
Marketing
10
40000
01.02.2003
Marketing
3
30000
01.07.2003
Marketing
5
40000
01.01.2003
Tabelle 27-1: Der Inhalt der Tabelle project_dept
Die folgenden Beispiele werden den Unterschied zwischen der Gruppierung der
Spalten der Tabelle project_dept ohne und zusätzlich mit dem CUBE- bzw.
ROLLUP-Operator zeigen. Der Hauptunterschied ist, dass die GROUP BY-Klausel nur Werte von einzelnen Gruppierungen nach den entsprechenden Spalten
ausgibt, während die ROLLUP- und CUBE-Operator zusätzliche Werte (so
genannte Super-Aggregate) ermitteln und ausgeben. Das folgende Beispiel zeigt
zuerst die Verwendung der GROUP BY-Klausel.
Beispiel 27.2
USE beispiel
SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets
FROM project_dept
GROUP BY dept_name, emp_cnt
Das Ergebnis ist:
dept_name
emp_cnt
sum_of_budgets
-------------------------------------------Marketing
3
30000.0
Accounting
5
10000.0
Marketing
5
40000.0
Research
5
115000.0
Accounting
6
70000.0
Marketing
6
10000.0
Accounting
10
40000.0
Marketing
10
40000.0
Research
10
70000.0
Dusan Petkovic, SQL Server 2005, dpunkt.verlag, ISBN 978-3-89864-367-2
534
27 Transact-SQL-Erweiterungen in Bezug auf Analysis Services
Beispiel 27.2 verwendet die GROUP BY-Klausel, um die Reihen der Tabelle
project_dept nach den Spalten dept_name und emp_cnt zu gruppieren.
Die Verwendung des CUBE-Operators wird in Beispiel 27.3 gezeigt.
Beispiel 27.3
USE beispiel
SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets
FROM project_dept
GROUP BY dept_name, emp_cnt
WITH CUBE
Das Ergebnis ist:
dept_name
emp_cnt
sum_of_budgets
-------------------------------------------Accounting
5
10000.0
Accounting
6
70000.0
Accounting
5
10000.0
Accounting
6
70000.0
Accounting
10
40000.0
Accounting
NULL
12000.0
Marketing
3
30000.0
Marketing
5
40000.0
Marketing
6
10000.0
Marketing
10
40000.0
Marketing
NULL
120000.0
Research
5
115000.0
Research
10
70000.0
Research
NULL
185000.0
NULL
NULL
425000.0
NULL
3
30000.0
NULL
5
165000.0
NULL
6
80000.0
NULL
10
150000.0
Neben den Reihen, die auch in der Ausgabe des Beispiels 27.2 existieren, beinhaltet die Ausgabe des Beispiels 27.3 alle möglichen Super-Aggregate. Ein SuperAggregat wird als NULL im Ergebnis ausgegeben, spezifiziert aber alle Werte. Die
folgende Reihe:
NULL
NULL
425000
zeigt z.B. die Summe aller Mittel aller Projekte der Tabelle project_dept, während
die Reihe
NULL
3
30000
die Summe aller Mittel der Projekte mit genau drei Beschäftigten ausgibt.
27.1 OLAP-Erweiterungen in Transact-SQL
535
Weil der CUBE-Operator alle möglichen Kombinationen der Super-Aggregate ausgibt, ist
die Anzahl der ausgegebenen Reihen gleich, unabhängig von der Reihenfolge der in der
GROUP BY-Klausel angegebenen Spalten.
27.1.2
Operator ROLLUP
Der Operator ROLLUP schränkt die Anzahl der erstellten Super-Aggregate im
Vergleich zum CUBE-Operator ein. Damit wird mit der WITH ROLLUP-Angabe
in der GROUP BY-Klausel ein Auszug aller Aggregate erstellt.
Beispiel 27.4 zeigt die Verwendung des ROLLUP-Operators.
Beispiel 27.4
USE beispiel
SELECT dept_name,emp_cnt,SUM(budget) sum_of_budgets
FROM project_dept
GROUP BY dept_name, emp_cnt
WITH ROLLUP
Das Ergebnis ist:
dept_name
emp_cnt
sum_of_budgets
-------------------------------------------Accounting
5
10000.0
Accounting
6
70000.0
Accounting
10
40000.0
Accounting
NULL
120000.0
Marketing
3
30000.0
Marketing
5
40000.0
Marketing
6
10000.0
Marketing
10
40000.0
Marketing
NULL
120000.0
Research
5
115000.0
Research
10
70000.0
Research
NULL
185000.0
NULL
NULL
425000.0
Bei der Anwendung des CUBE-Operators werden, wie aus dem Ergebnis des Beispiels 27.3 ersichtlich, alle möglichen Super-Aggregate berechnet. Das Ergebnis
des Beispiels 27.4 ist eine Untermenge des Ergebnisses des Beispiels 27.3. In diesem Fall werden die Gesamtsumme sowie die Summen für jede einzelne Abteilung
zusätzlich berechnet.
Das folgende Beispiel zeigt, dass die Änderung der Reihenfolge der Spalten in
der GROUP BY-Klausel die Ausgabe einer Abfrage mit dem ROLLUP-Operator
beeinflusst.
Dusan Petkovic, SQL Server 2005, dpunkt.verlag, ISBN 978-3-89864-367-2
536
27 Transact-SQL-Erweiterungen in Bezug auf Analysis Services
Beispiel 27.5
USE beispiel
SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets
FROM project_dept
GROUP BY emp_cnt, dept_name
WITH ROLLUP
Das Ergebnis ist:
dept_name
emp_cnt
sum_of_budgets
-------------------------------------------Marketing
3
30000.0
NULL
3
30000.0
Accounting
5
10000.0
Marketing
5
40000.0
Research
5
115000.0
NULL
5
165000.0
Accounting
6
70000.0
Marketing
6
10000.0
NULL
6
80000.0
Accounting
10
40000.0
Marketing
10
40000.0
Research
10
70000.0
NULL
10
150000.0
NULL
NULL
425000.0
Beispiel 27.5 unterscheidet sich von Beispiel 27.4 in der Reihenfolge der zum
Gruppieren benutzten Spalten. Aus diesem Grund ist das Ergebnis des Beispiels
27.5 anders als das Ergebnis des Beispiel 27.4, weil beim ROLLUP-Operator nur
diejenigen Super-Aggregate ausgegeben werden, die in Bezug zu der ersten Spalte
in der GROUP BY-Klausel stehen.
27.1.3
Aggregat- und Rangfunktionen
SQL Server 2005 unterstützt einige neue OLAP-Funktionen, die als Rangfunktionen bezeichnet werden. Eine Rangfunktion gibt für jede Reihe einer Gruppe
einen Rang als Nummer aus. Folgende Rangfunktionen werden unterstützt:
■
■
■
■
RANK
DENSE_RANK
ROW_COUNT
NTILE
Die folgenden Beispiele zeigen die Verwendung aller dieser Funktionen.
27.1 OLAP-Erweiterungen in Transact-SQL
537
Beispiel 27.6
USE beispiel
SELECT RANK() OVER(ORDER BY budget DESC) AS rank_budget, dept_name, emp_cnt,
budget
FROM project_dept
WHERE budget <= 50000;
Das Ergebnis ist:
rank_budget
dept_name
emp_cnt
budget
----------------------------------------------------1
Research
5
50000
2
Accounting
10
40000
2
Accounting
6
40000
2
Marketing
10
40000
2
Marketing
5
40000
6
Marketing
3
30000
6
Accounting
6
30000
8
Accounting
5
10000
8
Marketing
6
10000
Beispiel 27.6 verwendet die RANK-Funktion, um eine Ganzzahl (in der ersten
Ergebnisspalte) zu liefern, die den Rang der Reihe innerhalb der Ergebnismenge
ermittelt. Das Beispiel verwendet die OVER-Klausel
OVER(ORDER BY budget DESC) AS rank_budget
mit der die Ergebnismenge nach der budget-Spalte absteigend sortiert wird.
Die RANK-Funktion verwendet die so genannte logische Aggregierung, d.h., falls zwei
oder mehrere Reihen der Ergebnismenge denselben Wert in der Sortierspalte haben, wird
ihnen derselbe Rang gegeben. Die Reihe mit dem nächsthöheren Wert in der Sortierspalte wird den Rangwert haben, der alle vorherigen Reihen berücksichtigt (siehe die
6. bzw. 8. Reihe der Ausgabe).
Folgendes Beispiel zeigt die Verwendung zweier weiterer Rangfunktionen:
DENSE_RANK und ROW_NUMBER.
Beispiel 27.7
USE beispiel
SELECT DENSE_RANK() OVER( ORDER BY budget DESC ) AS rank_budget,
ROW_NUMBER() OVER( ORDER BY budget DESC ) AS row_number,
dept_name, emp_cnt, budget
FROM project_dept
WHERE budget <= 50000;
Dusan Petkovic, SQL Server 2005, dpunkt.verlag, ISBN 978-3-89864-367-2
538
27 Transact-SQL-Erweiterungen in Bezug auf Analysis Services
Das Ergebnis ist:
rank_budget
row_number
dept_name
emp_cnt
budget
-----------------------------------------------------------1
1
Research
5
50000
2
2
Accounting
10
40000
2
3
Accounting
6
40000
2
4
Marketing
10
40000
2
5
Marketing
5
40000
3
6
Marketing
3
30000
3
7
Accounting
6
30000
4
8
Accounting
5
10000
4
9
Marketing
6
10000
Die ersten beiden Spalten der Ergebnismenge zeigen die Werte für die
DENSE_RANK- und ROW_NUMBER-Funktionen, in dieser Reihenfolge. Das
Ergebnis der DENSE_RANK-Funktion ist ähnlich dem Ergebnis der RANKFunktion, bis auf einen Unterschied: Die DENSE_RANK-Funktion vergibt
sequenziell die Rangwerte, egal wie viele Reihen denselben Rangwert haben.
Die Ausgabe der ROW_NUMBER-Funktion ist offensichtlich: Jede Reihe
bekommt einen eindeutigen Wert, wobei die erste Reihe den Wert 1 zugewiesen
bekommt, die zweite den Wert 2 usw.
Die in den Beispielen 27.6 und 27.7 verwendete OVER-Klausel spezifiziert
die Sortierung der Ergebnismenge. Die Hauptfunktion dieser Klausel liegt aber
woanders: Sie wird verwendet, um die Ergebnismenge in Gruppen (so genannte
Partitionen) zu unterteilen und danach eine Funktion auf einzelne Partitionen
anzuwenden.
Funktionen, die eine Ergebnismenge in Partitionen unterteilen, werden Fensterfunktionen
(Window Functions) genannt. Eine Fensterfunktion kann entweder in der SELECT-Liste
oder in der ORDER BY-Klausel erscheinen. Die Transact-SQL-Sprache unterstützt zwei
Formen von Fensterfunktionen: Rang- und Aggregatfunktionen.
Das folgende Beispiel zeigt, wie die RANK-Funktion auf einzelne Partitionen
angewendet werden kann.
Beispiel 27.8
USE beispiel
SELECT dept_name, emp_cnt, CAST( budget AS INT ) AS budget, date_month,
RANK() OVER( PARTITION BY date_month ORDER BY emp_cnt desc ) AS rank
FROM project_dept;
27.1 OLAP-Erweiterungen in Transact-SQL
539
Das Ergebnis ist:
dept_name
emp_cnt
budget
date_month
rank
-----------------------------------------------------------Accounting
6
30000
1.1.2002
1
Research
5
50000
1.1.2002
2
Research
10
70000
1.2.2002
1
Accounting
10
40000
1.2.2002
1
Research
5
65000
1.7.2002
1
Accounting
5
10000
1.7.2002
1
Marketing
6
10000
1.1.2003
1
Marketing
5
40000
1.1.2003
2
Marketing
10
40000
1.2.2003
1
Accounting
6
40000
1.2.2003
2
Marketing
3
30000
1.7.2003
1
Das Ergebnis des Beispiels 27.8 ist gruppiert in acht Partitionen aufgrund der
Werte in der Spalte date_month:
PARTITION BY date_month
Danach wird die RANK-Funktion auf jede einzelne Partition angewendet.
Der grundsätzliche Unterschied in der Verwendung der GROUP BY-Klausel und der
Gruppierung mit Hilfe der OVER-Klausel ist, dass die OVER-Klausel jede Reihe einer
Gruppe (Partition) separat ausgibt, während die GROUP BY-Klausel je eine Reihe für
jede einzelne Gruppe ausgibt.
Wie wir schon erwähnt haben, kann eine Fensterfunktion entweder eine Rangoder eine Aggregatfunktion sein. Nachdem wir in dem vorherigen Beispiel die
Verwendung der RANK-Funktion als Fensterfunktion gezeigt haben, werden wir
im nächsten Beispiel die Verwendung einer Aggregatfunktion erklären.
Beispiel 27.9
USE beispiel
SELECT dept_name,
budget,
SUM( emp_cnt ) OVER( PARTITION BY dept_name ) AS emp_cnt_sum,
AVG( budget ) OVER( PARTITION BY dept_name ) AS budget_avg,
COUNT( dept_name ) OVER( PARTITION BY dept_name ) AS dept_cnt
FROM project_dept;
Dusan Petkovic, SQL Server 2005, dpunkt.verlag, ISBN 978-3-89864-367-2
540
27 Transact-SQL-Erweiterungen in Bezug auf Analysis Services
Das Ergebnis ist:
dept_name
budget
emp_cnt_sum
budget_avg
dept_cnt
------------------------------------------------------------------Accounting
10000
27
30000
4
Accounting
40000
27
30000
4
Accounting
30000
27
30000
4
Accounting
40000
27
30000
4
Marketing
10000
24
30000
4
Marketing
40000
24
30000
4
Marketing
30000
24
30000
4
Marketing
40000
24
30000
4
Research
50000
20
61666,6666666667
3
Research
70000
20
61666,6666666667
3
Research
65000
20
61666,6666666667
3
Die Ergebnismenge des Beispiels 27.9 ist partitioniert in drei Gruppen aufgrund
der Werte in der Spalte dept_name. Danach wird die Aggregatfunktion SUM auf
diese verwendet, um die Gesamtanzahl der Mitarbeiter jeder Partition zu ermitteln. Auf dieselbe Art und Weise werden mit Hilfe der Aggregatfunktionen AVG
und COUNT der Durchschnittswert der Mittel als auch die Anzahl der Mitarbeiter jeder Abteilung berechnet.
Die vorherigen Beispiele haben gezeigt, wie einfache Abfragen mit Hilfe der
OVER-Klausel formuliert werden können. Die tatsächliche Stärke dieser Klausel
zeigt sich erst, wenn man sie für die Implementierung komplexer Abfragen verwendet. Die folgenden beiden Beispiele zeigen dies.
Beispiel 27.10
Finden Sie die Abteilungen mit dem höchsten Etat, partitioniert nach der Mitarbeiteranzahl.
SELECT dept_name, emp_cnt, budget
FROM (SELECT dept_name, emp_cnt,
MAX(budget) OVER (PARTITION BY emp_cnt) AS max_empcnt, budget
FROM project_dept) AS a
WHERE budget = max_empcnt;
Das Ergebnis ist:
dept_name
emp_cnt
budget
-------------------------------------Marketing
3
30000
Research
5
65000
Accounting
6
40000
Research
10
70000
Die Unterabfrage in der FROM Klausel des Beispiels 27.10 berechnet den maximalen Wert der Abteilungsmittel. Dieser Wert ist für jede Partition, die auf der
Basis der Mitarbeiteranzahl ermittelt wurde, berechnet. Danach wird der maxi-
27.1 OLAP-Erweiterungen in Transact-SQL
541
male Wert in der WHERE-Klausel der äußeren Abfrage mit jedem existierenden
Wert der Abteilungsmittel verglichen, um den maximalen Wert jeder Partition zu
ermitteln.
Wir haben also einen aggregierten Wert verwendet, um den Vergleich mit
jedem einzelnen entsprechenden Basiswert innerhalb einer einzigen Abfrage
durchzuführen. Damit haben wir eine elegante Lösung gefunden für den Fall,
dass detaillierte Daten innerhalb einer SELECT-Anweisung mit summierten
Daten verglichen werden können.
Das folgende Beispiel zeigt die Verwendung einer Rangfunktion.
Beispiel 27.11
Finden Sie je zwei Abteilungen mit dem höchsten Etat, gruppiert nach den Abteilungsnamen.
SELECT dept_name, budget,
FROM (SELECT dept_name,
RANK() OVER (PARTITION
budget
FROM project_dept) AS
WHERE dept_rank <=2
dept_rank
emp_cnt,
BY dept_name ORDER BY budget DESC) AS dept_rank,
a
Das Ergebnis ist:
dept_name
budget
emp_cnt
-------------------------------------Accounting
40000
1
Accounting
40000
1
Marketing
40000
1
Marketing
40000
1
Research
70000
1
Research
65000
2
Beispiel 27.11 ähnelt dem Beispiel 27.10. Der Unterschied ist, dass wir die Rangfunktion RANK() verwenden, damit aus jeder Partition die beiden Abteilungen
mit dem höchsten Etat ausgewählt werden. (In diesem Beispiel wird auch eine
andere Spalte – dept_name – für die Partitionierung verwendet.)
27.1.4
TOP n-Klausel
Die TOP n-Klausel wird für die Ausgabe der ersten n Reihen des Ergebnisses, die
aufgrund eines Kriteriums ausgewählt wurden, verwendet.
Beispiel 27.12
USE beispiel
SELECT TOP 8 dept_name, budget
FROM project_dept
ORDER BY budget DESC
Dusan Petkovic, SQL Server 2005, dpunkt.verlag, ISBN 978-3-89864-367-2
542
27 Transact-SQL-Erweiterungen in Bezug auf Analysis Services
Das Ergebnis ist:
dept_name
budget
-----------------------Research
70000.0
Research
65000.0
Research
50000.0
Accounting
40000.0
Marketing
40000.0
Accounting
40000.0
Marketing
40000.0
Accounting
30000.0
Wie aus der Syntax der SELECT-Anweisung in Beispiel 27.12 ersichtlich, muss
die TOP n-Klausel in der SELECT-Liste vor allen Spaltennamen geschrieben
werden.
Die TOP n-Klausel kann mit der zusätzlichen Option PERCENT verwendet
werden. In diesem Fall wird der angegebene Prozentsatz der Reihen des Ergebnisses, die aufgrund eines Kriteriums ausgewählt wurden, ausgegeben. Eine weitere
Option – WITH TIES – spezifiziert, dass zusätzliche Reihen im Ergebnis ausgegeben werden, falls der Wert der letzten auszugebenden Ergebnisreihe mehrmals
vorkommt.
Beispiel 27.13
Finden Sie sechs Abteilungen mit der kleinsten Angestelltenanzahl.
USE beispiel
SELECT TOP 6 WITH TIES *
FROM project_dept
ORDER BY emp_cnt
Das Ergebnis ist:
dept_name
emp_cnt
budget
----------------------------------Marketing
3
30000.0
Marketing
5
40000.0
Research
5
50000.0
Research
5
65000.0
Accounting
5
10000.0
Accounting
6
30000.0
Accounting
6
40000.0
Marketing
6
10000.0
Das Ergebnis des Beispiels 27.13 enthält acht Reihen, weil drei Abteilungen existieren, die sechs Angestellte beschäftigen.
SQL Server 2005 erlaubt die Verwendung der TOP n-Klausel auch in
INSERT-, UPDATE- und DELETE-Anweisungen. Folgendes Beispiel zeigt die
Verwendung der TOP n-Klausel in einer UPDATE-Anweisung.
27.1 OLAP-Erweiterungen in Transact-SQL
543
Beispiel 27.14
USE beispiel
update top (3) project_dept
SET budget = budget * 0.9
where budget in (select TOP 3 budget
from project_dept
order by budget desc) ;
Die UPDATE-Anweisung in Beispiel 27.14 vermindert die Mittel der drei Abteilungen mit dem höchsten Budget jeweils um 10 %.
Das folgende Beispiel zeigt die Verwendung der TOP n-Klausel in einer
DELETE-Anweisung.
Beispiel 27.15
USE beispiel
DELETE TOP (4)
FROM project_dept
WHERE budget IN
(select TOP 4 budget from project_dept
order by budget asc);
In Beispiel 27.15 wird die TOP n-Klausel für das Löschen der drei Projekte mit
dem kleinsten Budget verwendet.
27.1.5
PIVOT- und UNPIVOT-Operatoren
PIVOT und UNPIVOT sind neue relationale Operatoren, welche die Transformation von tabellarischen Ausdrücken in relationale Tabellen ermöglichen. Der
PIVOT-Operator transformiert tabellarische Ausdrücke, indem die eindeutigen
Werte einer Spalte des Ausdrucks in mehrere Ausgabespalten umgewandelt werden. Zusätzlich dazu können Aggregationsoperationen auf einer oder mehreren
restlichen Eingabespalten durchgeführt werden.
Folgendes Beispiel zeigt die Verwendung des PIVOT-Operators.
Beispiel 27.16
USE beispiel
select *, month(date_month) as month, year(date_month) as year
into project_dept_pivot
from project_dept
go
SELECT year, [1] as January, [2] as February, [7] July FROM
( select budget, year, month from project_dept_pivot) p2
PIVOT
(SUM(budget)
FOR month
IN ([1],[2],[7]))
AS P
Dusan Petkovic, SQL Server 2005, dpunkt.verlag, ISBN 978-3-89864-367-2
544
27 Transact-SQL-Erweiterungen in Bezug auf Analysis Services
Das Ergebnis ist:
year
January
February
July
-----------------------------------2002
80000
110000
75000
2003
50000
80000
30000
Die erste SELECT-Anweisung in Beispiel 27.16 erstellt eine neue Tabelle namens
project_dept_pivot, die wir verwenden werden, um zu zeigen, wie der PIVOTOperator angewendet werden kann. Die neue Tabelle ist identisch mit der Tabelle
project_dept, bis auf zwei zusätzliche Spalten: month und year. Die beiden neuen
Spalten enthalten die Monats- und Jahreswerte der Spalte date_month in dieser
Reihenfolge.
Die zweite SELECT-Anweisung enthält eine innere Abfrage, die der Teil der
FROM-Klausel ist. Die PIVOT-Klausel ist der Teil der inneren Abfrage. Diese
Klausel enthält die Aggregatfunktion SUM(budget).
Der zweite Teil der PIVOT-Klausel spezifiziert die PIVOT-Spalte (month) und
die Spaltenwerte, die als Spaltenüberschriften in der Ausgabe verwendet werden.
(In Beispiel 27.16 sind es der erste, der zweite und der siebte Monat des Jahres.)
Die Werte jeder einzelnen Spalte werden mit Hilfe der entsprechenden Aggregatfunktion berechnet. (Wie aus der Ausgabe des Beispiels 27.16 ersichtlich, müssen
nicht alle möglichen Werte der PIVOT-Spalte für die Ausgabe verwendet werden.)
Der UNPIVOT-Operator führt die umgekehrte Operation durch.
27.2 Zusammenfassung
Dieses Kapitel erläutert die ganze Funktionalität von SQL/OLAP, die SQL Server
2005 unterstützt. Insgesamt existieren vier Gruppen von SQL/OLAP-Funktionen:
■
■
■
■
CUBE- und ROLLUP-Operatoren
Aggregat- und Rangfunktionen
TOP n-Klausel
Relationale Operatoren PIVOT- und UNPIVOT
CUBE- und ROLLUP-Operatoren sind Teil des SQL/OLAP-Standards, ihre standardisierte Syntax sieht aber anders aus als die Transact-SQL-Syntax. Die Syntax
der Rangfunktionen RANK, DENSE_RANK und ROW_NUMBER bei Microsoft entspricht der standardisierten Syntax gleichnamiger Funktionen.
Die TOP n-Klausel sowie die PIVOT- und UNPIVOT-Operatoren sind spezifische Microsoft-Erweiterungen zu SQL/OLAP.