Analytische Auswertungen in SQL

Analytische Auswertungen in SQL
LV Datenorientierte Systemanalyse, WS 2014/15
Dr. Walter Ebner, <[email protected]>
Institut für Informationswirtschaft
Wirtschaftsuniversität Wien
• Funktionen und Operatoren
– Logische Operatoren
– Vergleichsoperatoren
– Mathematische Operatoren und Funktionen
– Zeichenketten Funktionen
– Pattern Matching
– Formatierungsfunktionen
• Konditionale Ausdrücke
• Aggregatsfunktionen
• Window Functions
Datenorientierte Systemanalyse – Analytische Auswertungen
Überblick
5
AND
OR
NOT
Logische Konjunktion
Logische Disjunktion
Logische Negation
Die Präzedenz (Reihenfolge der Abarbeitung) der
Operatoren ist:
1. NOT
2. AND
3. OR
Datenorientierte Systemanalyse – Analytische Auswertungen
Logische Operatoren
6
<
kleiner
>
größer
<=
kleiner oder gleich
>=
größer oder gleich
=
ist gleich
<> oder !=
•
•
•
ist ungleich
a BETWEEN x AND y oder a NOT BETWEEN x AND y
expression IS NULL
expression IS NOT NULL
Achtung: expression=NULL funktioniert nicht, da es immer FALSE ist.
Datenorientierte Systemanalyse – Analytische Auswertungen
Vergleichsoperatoren
7
•
+
Addition
-
Subtraktion
*
Multiplikation
/
Division (Ganzzahlendivision schneidet den Rest ab)
%
Modulo (Rest einer Ganzzahlendivision)
^
Potenzierung (2^3=8)
!
Fakultät (5!=5*4*3*2*1=120)
Daneben gibt es noch eine Reihe von bitweisen Operatoren
Datenorientierte Systemanalyse – Analytische Auswertungen
Mathematische Operatoren
8
abs(x)
Absolutbetrag (negatives Vorzeichnen wird entfernt)
ln(x)
Natürlicher Logarithmus
log(x)
Logarithmus mit Basis 10
pi()
Die Kreiszahl Pi (3.14)
random()
Zufallszahl zwischen 0 und 1
round(v)
Rundet v auf eine Ganzzahl
round(v, s)
sqrt(x)
Rundet v auf s Dezimalstellen
Quadratwurzel
power(a, b) Potenzierung a hoch b (äquivalent mit a^b)
sin(x),
Trigonometrische Funktionen
cos(x),
tan(x),
asin(x), etc.
…
Und viele mehr (siehe Doku von DBMS)
Datenorientierte Systemanalyse – Analytische Auswertungen
Mathematische Funktionen
9
string || string
Zeichenketten Verknüpfung
string || non-string Verknüpfung mit einem nicht String (‘value: ‘||3)
lower(s)
Konvertierung zu Kleinbuchstaben
upper(s)
Konvertierung zu Großbuchstaben
initcap(s)
Wandelt den ersten Buchstaben jedes Wortes
in einen Großbuchstaben um
length(s)
Gibt die Anzahl der Zeichen des Strings aus
trim(s)
Entfernt Whitespace vom Beginn und Ende
des Strings
position(substring Lokation des spezifizierten Substrings
in string)
substring(string
[from x] [for y])
Substring von Position x für die nächsten y
Zeichen
substring(string
from pattern)
Substring mit Regex-Pattern
Datenorientierte Systemanalyse – Analytische Auswertungen
Zeichenketten Operatoren und Funktionen
10
•
string LIKE pattern bzw. string NOT LIKE pattern
Bei der Verwendung von LIKE gibt es die folgenden Wildcards:
• % kein oder beliebig viele Zeichen
• _ genau ein Zeichen
•
string [NOT] SIMILAR TO regex-pattern (seit SQL:1999)
Das Regex Pattern ist nicht Posix konform. Hier wird wie beim
LIKE das % für beliebige Zeichenketten und das _ für genau
ein Zeichen verwendet.
•
Daneben bieten die meisten DBMS auch die Möglichkeit Posixkonforme Regular Expressions zu verwenden.
z.B. in PostgreSQL mit dem ~ Operator
Datenorientierte Systemanalyse – Analytische Auswertungen
Pattern Matching
11
current_date
gibt das aktuelle Datum zurück
current_time
gibt die aktuelle Zeit zurück
current_timestamp gibt das aktuelle Datum inkl. Zeit zurück
(äquivalent mit der Funktion now())
current_user
gibt den Usernamen zurück, mit den man sich
zur DB verbunden hat.
Datenorientierte Systemanalyse – Analytische Auswertungen
Build-in Scalar Functions
12
•
Um verschiedene Datentypen zu formatieren bzw. in bestimmte
Datentypen umzuwandeln gibt es die „to_“ Funktionen.
to_char(timestamp, text) Konvertiert Timestamp zu String
to_char(interval, text)
Konvertiert Interval zu String
to_char(numeric, text)
Konvertiert Zahl (Double, real, numeric) zu
String
to_date(text, text)
to_number(text, text)
Konvertiert String zu Datentyp Date
Konvertiert String zu Datentyp Numeric
to_timestamp(text, text) Konvertiert String zu Datentyp Timestamp
Der Aufruf der Funktionen ist immer gleich:
•
•
Der erste Parameter ist immer der Wert der formatiert werden soll.
Der zweite gibt das Muster des Inputs bzw. Outputs an.
Normales Datentyp-Casting geht mit: CAST(expression AS type)
Datenorientierte Systemanalyse – Analytische Auswertungen
Formatierungs- bzw. Umwandlungsfunktionen
13
•
Mit LIMIT und OFFSET kann man sich nur einer Teil des
Result-Sets ausgeben lassen.
SELECT select_list
FROM table_expression
LIMIT number [OFFSET offset_num]
Wird LIMIT number spezifiziert, werden maximal number Zeilen
retourniert (nur weniger, wenn das Ergebnis weniger Resultate
liefert).
OFFSET gibt an, dass offset_num Zeilen übersprungen werden
sollen, bevor mit der Ausgabe von Zeilen begonnen wird.
OFFSET 0 ist gleichbedeutend mit dem Weglassen dieser
Klausel. Wird LIMIT und OFFSET verwendet, werden zuerst
offset_num Zeilen übersprungen, bevor mit der Rückgabe von
number Zeilen begonnen wird.
•
Andere DBMS haben ähnliche Konstrukte um die Ergebnisausgabe zu limitieren.
Datenorientierte Systemanalyse – Analytische Auswertungen
Ergebnisausgabe limitieren in PostgreSQL
14
• CASE:
vergleichbar mit if/else Blöcken in anderen
Programmiersprachen:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
Datenorientierte Systemanalyse – Analytische Auswertungen
Konditionale Ausdrücke
15
SELECT * FROM test;
a
--1
2
3
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+------1 | one
2 | two
3 | other
Datenorientierte Systemanalyse – Analytische Auswertungen
Konditionale Ausdrücke – CASE Beispiel
16
• COALESCE:
COALESCE(value [, ...])
Die COALESCE Funktion gibt den ersten Wert zurück,
der nicht null ist.
SELECT COALESCE(description,
short_description, '(none)'), …
Ähnlich wie die Funktionen NVL und IFNULL bei
anderen Datenbankmanagementsystemen.
Datenorientierte Systemanalyse – Analytische Auswertungen
Konditionale Ausdrücke
17
• NULLIF:
NULLIF(value1, value2)
Gibt NULL zurück wenn value1 gleich value2 ist.
Ansonsten wird value1 zurückgegeben.
Tipp: mit NULLIF(value, '') kann man erreichen, dass
aus einer leeren Zeichenkette ein NULL Value wird.
Zum Beispiel im Zusammenhang mit COALESCE da
ansonsten, die leere Zeichenkette ausgegeben
werden würde, da eine leere Zeichenkette ungleich
NULL ist.
Achtung: in Oracle ist alles anders. Dort gibt es keine
leeren Zeichenketten  werden automatisch NULL.
Datenorientierte Systemanalyse – Analytische Auswertungen
Konditionale Ausdrücke
18
count(*)
count(expression)
Anzahl der Input Rows
Anzahl der Input Werte, die ungleich
NULL sind
avg(expression)
Durchschnitt (arithmetisches Mittel) über
die Input Werte
min(expression)
Gibt den kleinsten Wert zurück
max(expression)
Gibt den größten Wert zurück
sum(expression)
Gibt die Summe der Input Werte zurück
string_agg(expression, Input Werte werden zu einem String
delimiter)
zusammengehängt (Werte werden durch
den delimiter getrennt)
Datenorientierte Systemanalyse – Analytische Auswertungen
Aggregatsfunktionen
19
• Tipp: bei string_agg kann man auch ein ORDER BY
verwenden um die Werte zu sortieren.
SELECT wohnort, count(*) as anzahl,
string_agg(name, '; ' ORDER BY name) as namen
FROM angestellte GROUP BY wohnort;
Datenorientierte Systemanalyse – Analytische Auswertungen
Beispiel für string_agg()
20
avg(X)
corr(Y, X)
var_pop(expression)
var_samp(expression)
Durchschnitt (arithmetisches Mittel)
Korrelationskoeffizient zwischen X und Y
Varianz aller Werte (Grundgesamtheit)
Varianz aller Stichprobenwerte
stddev_pop(expression) Standardabweichung aller Werte
(Grundgesamtheit)
stddev_samp(expression)
Standardabweichung aller Werte der
Stichprobe
regr_slope(Y, X)
Steigung der Regressionsgeraden
(lineare Regression)
regr_intercept(Y, X)
Intercept der Regressionsgeraden
Datenorientierte Systemanalyse – Analytische Auswertungen
Statistische Aggregatsfunktionen in PostgreSQL
21
• Mit SQL:2003 wurde die window clause eingeführt.
• Durch diese window clause werden aus Aggregate
Functions Window Functions.
• Von Oracle werden diese Funktionen als Analytic
Functions bezeichnet.
• Derzeit werden sie unter anderem von den folgenden
Datenbankmanagementsystemen unterstützt:
Keine Unterstützung in MySQL!
Datenorientierte Systemanalyse – Analytische Auswertungen
Window Functions - Allgemeines
22
• Window Funktionen führen eine Berechnung über
eine Menge von Zeilen durch, die auf irgendeine
Weise verwandt mit der aktuellen Zeile sind.
• Dies ist ähnlich wie bei Aggregatsfunktionen aber mit
dem Unterschied, dass bei den Window Functions die
Ergebnisse nicht in eine Zeile zusammengezogen
werden – Die Zeilen bleiben erhalten.
Datenorientierte Systemanalyse – Analytische Auswertungen
Window Functions - Funktionsweise
23
• Window Functions werden mit Ausnahme der ORDER BY
clause zu allerletzt evaluiert. Wegen dieser späten Evaluierung
können sie deshalb nicht in der WHERE, GROUP BY oder
HAVING Klausel vorkommen.
• Zu beachten ist, dass die (Gruppe von) Zeilen, die für die
Kalkulation herangezoggen werden, nicht durch eine GROUP
BY clause bestimmt werden, sondern durch Partitioning
innerhalb der Window clause.
• Außerdem ist zu beachten, dass die Reihenfolge innerhalb der
Gruppen durch eine ORDER BY clause bestimmt wird. Aber
diese betrifft nur die Berechnung innerhalb der Window Function
und hat keine Auswirkung auf die Reihenfolge, wie die Zeilen
letztendlich ausgegeben werden.
Tipp: Man kann der Spalte einen Alias Namen geben und dann
diesen Aliasnamen zum sortieren des Ergebnisses verwenden.
Datenorientierte Systemanalyse – Analytische Auswertungen
Window Functions - Hinweise
FUNCTION_NAME(expr) OVER {window_name|(window_specification)}
window_specification ::= [window_name][partitioning][ordering][framing]
partitioning ::= PARTITION BY value [, value...] [COLLATE collation_name]
ordering ::= ORDER [SIBLINGS] BY rule [, rule...]
rule ::= {value|position|alias} [ASC|DESC] [NULLS {FIRST|LAST}]
framing ::= {ROWS|RANGE} {start|between} [exclusion]
start ::= {UNBOUNDED PRECEDING|unsigned-integer PRECEDING|
CURRENT ROW}
between ::= BETWEEN bound AND bound
bound ::= {start|UNBOUNDED FOLLOWING|unsigned-integer FOLLOWING}
exclusion ::= {EXCLUDE CURRENT ROW|EXCLUDE GROUP|EXCLUDE
TIES|EXCLUDE NO OTHERS}
Datenorientierte Systemanalyse – Analytische Auswertungen
SQL:2003 - Window Clause
FUNCTION_NAME(expr) OVER {window_name|(window_specification)}
window_specification ::= [partitioning][ordering][framing]
partitioning ::= PARTITION BY value [, value...]
ordering ::= ORDER BY rule [, rule...]
rule ::= {value|position|alias} [ASC|DESC] [NULLS {FIRST|LAST}]
framing ::= {ROWS|RANGE} {start|between}
start ::= {UNBOUNDED PRECEDING|unsigned-integer PRECEDING|
CURRENT ROW}
between ::= BETWEEN bound AND bound
bound ::= {start|UNBOUNDED FOLLOWING|
unsigned-integer FOLLOWING}
Die einfachste Form ist somit ein: function_name(expr) OVER ()
z.B. avg(salary) over()
Datenorientierte Systemanalyse – Analytische Auswertungen
PostgreSQL - Window Clause
Die folgenden Funktionen hängen von der Reihenfolge der Werte ab.
Deshalb ist hier zwingend ein ORDER BY im OVER() notwendig:
row_number()
rank()
dense_rank()
percent_rank()
cume_dist()
ntile(num_buckets)
 Nächste Folie
Nummer der aktuellen Zeile innerhalb der Partition;
zählen beginnt bei 1
Rang der aktuellen Zeile (Es gibt Gaps wenn es
gleiche Ergebnisse gibt)
Rang der aktuellen Zeile ohne Gaps (peer groups)
Relativer Rang der aktuellen Zeile: (rank-1)/(total
rows-1)
Relativer Rang der aktuellen Zeile: (number of rows
preceding or peer with current row) / (total rows)
Ganzzahl von 1 bis num_buckets, die die Partition
so gleich wie möglich aufteilt.
Datenorientierte Systemanalyse – Analytische Auswertungen
Allgemeine Window Funktionen (1/2)
27
lag(value [, offset
[, default]])
Retourniert den Wert der Spalte value, der offset
Zeilen weiter hinten kommt (offset defaults to 1;
default ist standardmäßig NULL)
lead(value [, offset
[, default]])
Retourniert den Wert der Spalte value, der offset
Zeilen weiter vorne steht (offset defaults to 1)
first_value(value)
Gibt den ersten Wert innerhalb eines WindowFrames zurück.
last_value(value)
Gibt letzten Wert innerhalb eines Window-Frames
zurück.
nth_value(value, nth)
Gibt den nth-ten Wert innerhalb eines WindowFrames zurück.
Zusätzlich zu diesen Funktionen kann jede Aggregatsfunktion als Window
Function verwendet werden. Aggregatsfunktionen werden nur dann als
Window Function interpretiert, wenn nach ihrem Aufruf ein OVER() folgt.
Datenorientierte Systemanalyse – Analytische Auswertungen
Allgemeine Window Funktionen (2/2)
28
Window Functions - Beispiel
SELECT depname, empno, salary FROM empsalary;
Depname=Abteilung
Empno=Angestelltennr.
Salary=Gehalt
Datenorientierte Systemanalyse – Analytische Auswertungen
Die folgende Tabelle ist der Ausgangspunkt für unsere weiteren Abfragen:
29
Window Functions - Beispiel
SELECT depname, avg(salary)
FROM empsalary
GROUP BY depname;
• Soweit nichts neues: Hier wird AVG() als Aggregatsfunktion
verwendet und nicht als Window Function.
Datenorientierte Systemanalyse – Analytische Auswertungen
Geben Sie die Abteilungen mit dem jeweiligen Durchschnittsgehalt aus:
30
Nun sollten aber alle Personen mit ihren Gehalt und dem Durchschnittsgehalt der jeweiligen Abteilung ausgegeben werden:
SELECT depname, empno, salary, avg(salary) OVER
(PARTITION BY depname) FROM empsalary;
Datenorientierte Systemanalyse – Analytische Auswertungen
Window Functions - Beispiel
31
Lässt man das PARTITION BY weg, gibt es nur eine Partition (nämlich alle
Zeilen). Der Durchschnitt ist somit über alle Werte:
SELECT depname, empno, salary, avg(salary) OVER ()
FROM empsalary;
Datenorientierte Systemanalyse – Analytische Auswertungen
Window Functions - Beispiel
32
Mit rank() bekommt man den numerischen Rang innerhalb einer Partition,
der sich durch die Sortierung eines oder mehrerer Attribute ergibt.
SELECT depname, empno, salary, rank() OVER (PARTITION
BY depname ORDER BY salary DESC) FROM empsalary;
Datenorientierte Systemanalyse – Analytische Auswertungen
Window Functions - Beispiel
33
Window Functions - Beispiel
SELECT depname, empno, salary, row_number() OVER (PARTITION
BY depname ORDER BY salary DESC), rank() OVER (PARTITION BY
depname ORDER BY salary DESC), dense_rank() OVER (PARTITION
BY depname ORDER BY salary DESC) FROM empsalary;
Datenorientierte Systemanalyse – Analytische Auswertungen
Unterschied zwischen row_number(), rank() und dense_rank():
34
Damit man dasselbe Window nicht ständig wiederholen muss, kann man das
Window getrennt definieren und mit einen Namen versehen.
SELECT depname, empno, salary, row_number() OVER w AS sort_attr,
rank() OVER w, dense_rank() OVER w
FROM empsalary
WHERE 1=1 -- sinnlos, nur zum Zeigen, dass die Windowdef. danach kommt
WINDOW w AS(PARTITION BY depname ORDER BY salary DESC)
ORDER BY depname, sort_attr;
Datenorientierte Systemanalyse – Analytische Auswertungen
Window Functions - Beispiel
35
•
•
•
•
Es gibt noch ein anderes wichtiges Konzept bei den Window
Functions. Nämlich für jede Zeile gibt es innerhalb der Partition
eine Menge von Zeilen, die als Window Frame bezeichnet werden.
Viele (aber nicht alle) Window Functions beziehen in ihren
Berechnungen nur die Zeilen des Window Frames ein und nicht
alle Zeilen der Partition.
Standardmäßig, wenn ein ORDER BY spezifiziert ist, besteht der
Frame aus allen Zeilen vom Start der Partition bis zur aktuellen
Zeile plus alle folgenen Zeilen, die den selben Wert wie die
aktuelle Zeile haben.
Wird ORDER BY weggelassen, besteht der Frame standardmäßig
aus allen Zeilen der Partition.
Das Beispiel auf der nächsten Folie dient zur Veranschaulichung
dieses Standardverhaltens.
Datenorientierte Systemanalyse – Analytische Auswertungen
Window Functions – Window Frame
36
SELECT salary, sum(salary) OVER
() FROM empsalary;
SELECT salary, sum(salary) OVER
(ORDER BY salary)
FROM empsalary;
Beide 4800
Werte sind hier
schon dabei
Ohne ORDER BY besteht der
Frame aus allen Zeilen der
Partition.
Mit ORDER BY besteht der
Frame aus allen Zeilen bis zur
aktuellen (gemäß Sortierung).
Datenorientierte Systemanalyse – Analytische Auswertungen
Window Functions – Window Frame
37
• SQL in a Nutshell, 2nd edition
http://sqlnut.atw.hu/
• PostgreSQL - Window Functions
http://www.postgresql.org/docs/9.2/static/functions-window.html
• PostgreSQL - Window Functions Tutorial
http://www.postgresql.org/docs/9.2/static/tutorial-window.html
• PostgreSQL – Syntax Window Functions
http://www.postgresql.org/docs/9.2/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
• Oracle - Analytic Functions
http://www.oracle-base.com/articles/misc/analytic-functions.php
Datenorientierte Systemanalyse – Analytische Auswertungen
Weiterführende Literatur
38
Datenorientierte Systemanalyse – Analytische Auswertungen
ENDE
39