Datenbanken und Datawarehouses Übersicht Datenbank

†bersicht
Datenbanken und Datawarehouses
¥ EinfŸhrung
¥ GROUP BY
Niels Schršter
¥ Roll UpÔs
¥ Kreuztabellen
¥ Cubes
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Datenbank
Datenbanksysteme
¥ Ansammlung von Tabellen, die einen
ãAusschnitt der WeltÒ fŸr eine Benutzergruppe
beschreiben. Sie beschreiben die funktionalen
ZusammenhŠnge der Daten aus der Sicht der
Benutzer. Die Verwaltung der Tabellen
geschieht mit Hilfe eines Datenbanksystems [2]
¥ Software, ggf. auch Hardware und
administrative Ma§nahmen, die die Verwaltung
der Daten fŸr Benutzer Ÿbernehmen,
Datenschutz und Datensicherheit zu
gewŠhrleisten und den Benutzern Zugang zu
den Datenbanken zu ermšglichen
[2]
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Tabelle
¥ In der relationalen Darstellung werden Daten in
Tabellen gespeichert, die eine Menge von
Attribute besitzen. WŠhrend Ÿblicherweise die
Spalten einer Tabelle die einzelnen Attribute
reprŠsentieren, werden die eigentlichen Daten
durch Zeilen (Tupel) dargestellt
[3]
¥
¥
¥
¥
Attribute
¥ Vertikal angeordneter Teil einer Tabelle, mit
dem ein Name und ein Datentyp verknŸpft ist.
¥ Alle Attributwerte haben den mit dem Attribut
verknŸpften Datentyp
[2]
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Datenanalyse
Datenanalysetools
Formulierung einer Abfrage
Extrahierung der zusammengefassten Daten in
einer Tabelle oder Datei
Visualisierung der Ergebnisse
Analyse der Ergebnisse und Formulierung
einer neuen Abfrage
Datenbanken und Datawarehouses
¥
¥
¥
¥
Tendenzen
Anomalien
Cluster
Darstellung der extrahierten Daten im NDimensionalen Raum
Datenbanken und Datawarehouses
Extraktion
¥ Untermenge der ursprŸnglichen Daten, die fŸr
den Benutzer interessant sind
(Dimensionsreduktion)
SQL: SELECT
¥ Anweisung zum Auffinden von Daten, die in
einer Datenbank gespeichert sind. SELECT
stellt somit die ãzentraleÒ Anweisung dar.
¥ VielfŠltige Auswahlkriterien
[2]
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
SQL: SELECT
SQL
¥ Die Daten kšnnen aus mehreren Tabellen
zusammengebracht werden
¥ Die Auswahl der Zeilen kann gewissen
Bedingungen genŸgen
¥ Einige Spalten kšnnen von der Darstellung
ausgeschlossen werden
[2]
Datenbanken und Datawarehouses
¥ SQL Operator GROUP BY
¥ SQL Erweiterung: CUBE und ROLLUP
¥ Funktionen: COUNT(), SUM(), MIN(),
MAX(), AVG()
Datenbanken und Datawarehouses
Transaktion
Aggregation
¥ Eine Folge von Datenbankoperationen Ð dass
hei§t eine ZustandsŠnderung - , die bezŸglich
der DatenintegritŠt eine Einheit bilden
¥ Die Datenbank ist vor und nach der Transaktion
in konsistentem Zustand45
¥ Unter einer Aggregation versteht man das
Zusammenfassen von Daten mittels einer
Berechnungsvorschrift.
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Aggregationsoperator
Definitionen
¥ Ein Aggregationsoperator ist die
Berechnungsvorschrift, wie z.B. SUM(), AVG()
oder MAX(), mit der die betrachteten Daten bei
DurchfŸhrung einer Roll-Up -Operation
zusammengefasst werden kšnnen.
Datenbanken und Datawarehouses
¥ Histogramm: Aggregationen Ÿber berechnete
Kategorien
Datenbanken und Datawarehouses
Ê
Tabelle Sales
SQL Beispiel
ÊModel
Year
Chevy
Ê1990Ê
Red
5
Chevy
1990
White
87
Chevy
1990
Blue
62
Chevy
1991
Red
54
Chevy
1991
White
95
Chevy
1991
Blue
49
Chevy
1992
Red
31
Chevy
1992
White
54
ÊColorÊ
Sales
Chevy
1992
Blue
71
FORD
1990
Red
64
FORD
1990
White
62
FORD
1990
Blue
63
FORD
1991
Red
52
FORD
1991
White
9
FORD
1991
Blue
55
FORD
1992
Red
27
FORD
1992
White
62
FORD
1992
Blue
39
¥ Berechnung der Anzahl aller verkaufter Autos
SELECT SUM(Sales)
FROM
Sales;
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
SQL Bespiel
Probleme mit GROUP BY
¥ Anwendung des GROUP BY Operators:
SELECT
FROM
GROUP BY
Model, SUM(Sales)
Sales
Model;
Datenbanken und Datawarehouses
¥ Histogramme
¥ Roll UpÕs
¥ Kreuztabellen
Datenbanken und Datawarehouses
Roll Ups
Roll Up
¥ Roll-Up bezeichnet das Zusammenfassen von
Fakten aufgrund gleicher AusprŠgungen der
qualifizierenden Eigenschaften, wobei die
einzelnen quantifizierenden Eigenschaften unter
Verwendung von Aggregationsoperatoren
zusammengefŸhrt werden. [3]
ÊModelÊ
Year
ÊColorÊ
Sales by Model
by Year by Color
Chevy
Ê1994
Black
50
White
40
Black
85
White
115
Sales by Model
by Year
Sales by Model
90
1995
200
290
Anzahl der Spalten:
ÊModelÊ
Year
Color
2N
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Roll Up
Pivot Tabelle
ÊSalesÊ
Sales by Model
by Year
Year/Color
Sales by
Model
Chevy
Chevy
Ê1994Ê
1994
Black
White
50
40
90
90
290
290
Chevy
Chevy
1995
1995
Black
White
85
114
200
200
290
290
1994
White
Black
Chevy
50
40
90
85
Ford
50
10
60
Grand
Total
100
50
150
Anzahl der Spalten
Datenbanken und Datawarehouses
1994
Total
1995
Black
White
Model
1995
Total
Grand
Total
115
200
290
85
75
160
220
170
190
360
510
M ↔N
Datenbanken und Datawarehouses
EinfŸhrung des ALL Werts
UNION
¥ Vereinigung mehrerer Tabellen. Vorraussetzung
hierbei ist, dass die Spalten (Attribute) der zu
vereinigenden Tabellen vom gleichen Typ sind
SELECT ÔALLÕ, ÔALLÕ, ÔALLÕ, SUM(Sales)
Ê FROMÊ Sales
Ê WHEREÊ Model = ÔChevyÕ
UNION
SELECT Model, ÔALLÕ, ÔALLÕ, SUM(Sales)
Ê FROMÊ Sales
Ê WHEREÊ Model = ÔChevyÕÊÊ
GROUP BY Model
UNION
SELECT Model, Year, ÔALLÕ, SUM(Sales)
Ê FROMÊ Sales
Ê WHEREÊ Model = ÔChevyÕÊÊ
GROUP BY Model, Year
UNION
SELECT Model, Year, Color, SUM(Sales)
Ê FROMÊ Sales
Ê WHEREÊ Model = ÔChevyÕ
GROUP BY Model, Year, Color;
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Dreidimensionaler Roll Up
Asymmetrie von Rollups
ÊModelÊ
Year
Color
Sales
Model
Year
Color
Sales
Chevy
Ê1994Ê
Black
50
Chevy
ALL
Black
135
Chevy
1994
White
40
1994
ALL
90
Chevy
ALL
Chevy
White
155
Chevy
1995
Black
85
Chevy
1995
White
115
Chevy
1995
ALL
200
Chevy
ALL
ALL
290
Datenbanken und Datawarehouses
UNION
SELECT
FROM
WHERE
GROUP BY
Model, ÔALLÔ, Color, SUM(Sales)
Sales
Model = ÔChevyÔ
Model, Color;
Datenbanken und Datawarehouses
Kreuztabellen
Datacubes
¥ Die Tabellen werden Kreuztabellen genannt
¥ Kreuztabellen sind zweidimensionale Aggregationen
¥ †blicherweise werden Kreuztabellen kompakter
dargestellt:
Chevy
1994
1995
Total(ALL)
Black
50
85
135
White
40
115
155
Total(ALL)
90
200
200
¥ Wird z.B. eine andere Automarke hinzugefŸgt,
wird die Kreuztabelle Dreidimensional
¥ Der GROUP BY CUBE Operator generalisiert
GROUP BY, Roll Up und Kreuztabellen
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Erzeugen von Datacubes in SQL
Snowflakes
SELECT
Model, Year, Color,
SUM(sales) AS Sales
FROM
Sales
WHERE
Model in (ÔFordÕ, ÔChevyÕ)
AND Year BETWEEN 1990 AND 1992
GROUP BY CUBE Model, Year, Color;
Datenbanken und Datawarehouses
¥ Ein Snowflake ist ein Datenmodell fŸr den
logischen Entwurf einer Datenbank, das die
relationale ReprŠsentation multidimensionaler
Daten ermšglicht.
Datenbanken und Datawarehouses
Snowflake
ALL
Snowflake
ALL
Division
Group
Unit
ALL
ALL
black
Ford
Discount
Chevy
Product
Seller
Price
Office
white
Model
Year
District
Channel
Sales
1990
Region
Geography
ALL
Color
ALL
1991
1992
ALL
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Zugriff auf den Datacube
Zugriff auf Datacube
¥ Grenze zwischen Query und Visualisierung
unscharf
¥ Der Datenbankprogrammierer muss selber die
Abfragefunktionen aufsplitten in Query und
Visualisierung
¥ Dies bedingt Turing-VollstŠndige
Programmiersprache
¥ SQL3 ist Turing-VollstŠndig
Datenbanken und Datawarehouses
Berechnung einer Percent of Total Tabelle:
SELECT Model, Year, Color, SUM(Sales),
SUM(Sales)/
(SELECT SUM(Sales)
FROM
Sales
WHEREÊ
Model IN {ÔChevyÕ, ÕFordÕ}
AND
Year BETWEEN 1990 AND 1992
)
FROM SALES
WHEREÊ
Model IN {ÔChevyÕ, ÕFordÕ}
AND
Year BETWEEN 1990 AND 1992
GROUP BY CUBE Model, Year, Color;
Datenbanken und Datawarehouses
ALL
Vereinfachung
SELECT Model, Year, Color, SUM(Sales) AS
total,
ÊÊÊÊÊSUM(Sales) / total(ALL, ALL, ALL)
FROMÊÊ Sales
WHEREÊ Model IN {ÔChevyÕ, ÔFordÕ}
AND Year BETWEEN 1990 AND 1992
GROUP BY CUBE Model, Year, Color;
Indizierung eines 2D Cubes
SELECT
FROM
WHERE
AND
v
cube
row
= :i
column = :j
oder:
Cube.v(:i, :j)
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Berechnung von Aggregationen
Berechnen von Aggregationen
¥ Um Datenbewegungen und Kosten zu minimieren, sollten
Aggregationen auf mšglichst niedrigem System Level berechnet
werden
¥ Wenn mšglich Arrays oder Hashing benutzen um
Aggregationsspalten im Speicher zu organisieren, jeweils ein
Aggregationswert pro Array oder Hash Entry
¥ Sind die Aggregationswerte gro§e Strings, sollten diese mit
Hilfe von Hashes auf Integers gemappt werden
¥ Ist die Anzahl der Aggregate zu gro§, um in den
Hauptspeicher zu passen, sollte man die Daten
sortieren und dann mit einen sequentiellen Scan
aggregieren
¥ Wenn die Quelldaten Ÿber viele Festplatten
verteilt sind, sollten die einzelnen Teile parallel
aggregiert und anschlie§end verschmolzen
werden
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Distributive
Aggregationsfunktionen
Berechnung des Cube
¥ ALL erweitert jede Dimension um ein Element
¥ Ein N-Dimensionaler WŸrfel mit N Attributen,
die jeweils die KardinalitŠt Ci haben wird
Werte enthalten
⊆ (Ci + 1)
¥ Normalerweise
wird Ci sehr gro§ sein, was dann
nur noch wenig Auswirkungen auf die Summe
hat
¥ COUNT(), MIN(), MAX(), SUM()
F() ist distributiv, wenn es eine Funktion G() gibt und es gilt:
F ({ Xi , j}) = G ({F ({ Xi , j | i = 1,..., I }) | j = 1,...J })
Datenbanken und Datawarehouses
Datenbanken und Datawarehouses
Algebraische
Aggregationsfunktionen
Holistische Aggregationsfunktionen
¥ AVG(), standard deviation, MaxN(), MinN(),
center_of_mass()
F() ist algebraisch, wenn es eine M-Tupel wertige Funktion G()
gibt und eine Funktion H(), so da§ gilt:
F ({ Xi , j}) = H ({G ({ Xi , j | i = 1,..., I }) | j = 1,...J })
Datenbanken und Datawarehouses
¥
MostFrequent(), Rank()
F() ist holistisch, wenn die Anzahl der Attribute einer Aggregation nicht
konstant ist. Es gibt kein konstantes M, so dass ein M-Tupel die
Berechnung von
beschreiben
wŸrde.
F ({ Xi , j | i = 1,...,
I })
Datenbanken und Datawarehouses
Literaturverzeichnis:
[1] GRAY et al.
Data Cube: A Relational Aggregation Operator Gerneralizing Group-By,
Cross-Tab, and Sub-Totals
[2] Achilles
SQL: Standardisierte Datenbanksprache vom PC bis zu Mainframes
[3] Glossar Datawarehousing
http://www6.informatik.uni-erlangen.de:8080/ak-proj_offen/glossar.html
Datenbanken und Datawarehouses