Transcript 05-Verdichten

5
Verdichten von Daten
mit Gruppenfunktionen
Ziele
• Kennenlernen der vorhandenen
Gruppenfunktionen
• Anwendung von Gruppenfunktionen
• Gruppieren von Daten mittels GROUP
BY Klausel
• Selektieren von Gruppen unter
Anwendung der HAVING Klausel
5-2
Was sind Gruppenfunktionen?
Gruppenfunktionen operieren auf Mengen von
Zeilen und ergeben ein Resultat pro Gruppe.
EMP
DEPTNO
SAL
--------- --------10
2450
10
5000
10
1300
20
800
20
1100
20
3000
20
3000
20
2975
30
1600
30
2850
30
1250
30
950
30
1500
30
1250
5-3
“Maximales
Gehalt in
EMP Tabelle”
MAX(SAL)
--------5000
Typen von Gruppenfunktionen
• AVG
• COUNT
• MAX
• MIN
• SUM
• STDDEV
• VARIANCE
5-4
Einsatz von Gruppenfunktionen
SELECT
FROM
[WHERE
[GROUP BY
[ORDER BY
5-5
[column,] group_function(column)
table
condition]
column]
column];
Verwendung von AVG und SUM
AVG and SUM anwendbar für numerische Daten.
SQL> SELECT
2
3 FROM
4 WHERE
AVG(sal), MAX(sal),
MIN(sal), SUM(sal)
emp
job LIKE 'SALES%';
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)
-------- --------- --------- --------1400
1600
1250
5600
5-6
Anwendung von MIN und MAX
MIN and MAX für beliebigen Datentyp geeignet.
SQL> SELECT
2 FROM
MIN(hiredate), MAX(hiredate)
emp;
MIN(HIRED MAX(HIRED
--------- --------17-DEC-80 12-JAN-83
5-7
COUNT-Funktion
COUNT(*) liefert die Anzahl von Zeilen in
einer Tabelle.
SQL> SELECT
2 FROM
3 WHERE
COUNT(*)
--------6
5-8
COUNT(*)
emp
deptno = 30;
Anwendung der COUNT- Funktion
COUNT(expr) liefert die Anzahl Zeilen, die
nicht null sind.
SQL> SELECT
2 FROM
3 WHERE
COUNT(COMM)
----------4
5-9
COUNT(comm)
emp
deptno = 30;
Gruppenfunktion und Nullwerte
Gruppenfunktionen ignorieren Nullwerte
in der Spalte.
SQL> SELECT AVG(comm)
2 FROM
emp;
AVG(COMM)
--------550
5-10
Anwendung der NVL Funktion
auf Gruppenfunktionen
Die NVL-Funktion zwingt Gruppenfunktionen zur Einbeziehung von Nullwerten.
SQL> SELECT AVG(NVL(comm,0))
2 FROM
emp;
AVG(NVL(COMM,0))
---------------157.14286
5-11
Gruppierung von Daten
EMP
DEPTNO
SAL
--------- --------10
2450
10
5000
10
1300
20
800
20
1100
20
3000
20
3000
20
2975
30
1600
30
2850
30
1250
30
950
30
1500
30
1250
5-12
2916.6667
“durchschnittl. DEPTNO AVG(SAL)
Gehalt
------- --------in EMP
2175
10 2916.6667
Tabelle
20
2175
für jede
Abteilung”
30 1566.6667
1566.6667
Gruppierung von Daten:
GROUP BY Klausel
SELECT
FROM
[WHERE
[GROUP BY
[ORDER BY
column, group_function(column)
table
condition]
group_by_expression]
column];
Aufteilung von Zeilen in einer Tabelle in
kleinere Gruppen durch Anwendung der
GROUP BY Klausel.
5-13
Anwendung der GROUP BY Klausel
Alle Spalten in der SELECT Liste, die nicht
in Funktionen verwendet werden, müssen
in der GROUP BY Klausel stehen.
SQL> SELECT
deptno, AVG(sal)
2 FROM
emp
3 GROUP BY deptno;
DEPTNO AVG(SAL)
--------- --------10 2916.6667
20
2175
30 1566.6667
5-14
Anwendung von GROUP BY
Die GROUP BY Spalte braucht nicht in der
SELECT Liste enthalten zu sein.
SQL> SELECT
AVG(sal)
2 FROM
emp
3 GROUP BY deptno;
AVG(SAL)
--------2916.6667
2175
1566.6667
5-15
EMP
DEPTNO
--------10
10
10
20
20
20
20
20
30
30
30
30
30
30
5-16
Gruppierung nach mehreren
Spalten
JOB
SAL
--------- --------MANAGER
2450
DEPTNO JOB
SUM(SAL)
PRESIDENT
5000
-------- --------- --------CLERK
1300
10 CLERK
1300
CLERK
800
“summiere Gehälter in
10 MANAGER
2450
CLERK
1100 der EMP Tabelle
10 PRESIDENT
5000
ANALYST
3000
für jeden Job,,
20 ANALYST
6000
gruppiert nach
ANALYST
3000
20 CLERK
1900
Abteilung”
MANAGER
2975
20 MANAGER
2975
SALESMAN
1600
30 CLERK
950
MANAGER
2850
30 MANAGER
2850
SALESMAN
1250
30 SALESMAN
5600
CLERK
950
SALESMAN
1500
SALESMAN
1250
Anwendung von GROUP BY
bei mehreren Spalten
SQL> SELECT
deptno, job, sum(sal)
2 FROM
emp
3 GROUP BY deptno, job;
DEPTNO JOB
SUM(SAL)
--------- --------- --------10 CLERK
1300
10 MANAGER
2450
10 PRESIDENT
5000
20 ANALYST
6000
20 CLERK
1900
...
9 rows selected.
5-17
Illegale Anfragen
mit Gruppenfunktionen
Jede Spalte oder Ausdruck in der SELECT
Liste, welche nicht Aggregatfunktion sind,
müssen in der GROUP BY Klausel enthalten sein.
SQL> SELECT
2 FROM
deptno, COUNT(ename)
emp;
SELECT deptno, COUNT(ename)
*
ERROR at line 1:
ORA-00937: not a single-group group function
5-18
Illegale Anfragen
mit Gruppenfunktionen
• WHERE-Klausel zum Filtern von Gruppen
verboten.
• Dafür gibt‘s die HAVING Klausel.
SQL>
2
3
4
SELECT
FROM
WHERE
GROUP BY
deptno, AVG(sal)
emp
AVG(sal) > 2000
deptno;
WHERE AVG(sal) > 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
5-19
Ausschließen von Gruppen
EMP
DEPTNO
SAL
--------- --------10
2450
10
5000
10
1300
20
800
20
1100
20
3000
20
3000
20
2975
30
1600
30
2850
30
1250
30
950
30
1500
30
1250
5-20
5000
3000
2850
“Maximales
Gehalt
pro Abteilung
größer als
$2900”
DEPTNO MAX(SAL)
--------- --------10
5000
20
3000
Ausschließen von Gruppen:
HAVING Klausel
HAVING Klausel zum Filtern von Gruppen
– Zeilen werden gruppiert.
– Gruppenfunktion angewendet.
– Gruppen, die die HAVING Klausel
erfüllen, werden angezeigt.
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
5-21
column, group_function
table
condition]
group_by_expression]
group_condition]
column];
Anwendung der HAVING Klausel
SQL>
2
3
4
SELECT
FROM
GROUP BY
HAVING
deptno, max(sal)
emp
deptno
max(sal)>2900;
DEPTNO MAX(SAL)
--------- --------10
5000
20
3000
5-22
Anwendung der HAVING Klausel
SQL>
2
3
4
5
6
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
job, SUM(sal) PAYROLL
emp
job NOT LIKE 'SALES%'
job
SUM(sal)>5000
SUM(sal);
JOB
PAYROLL
--------- --------ANALYST
6000
MANAGER
8275
5-23
Schachtelung von Funktionen
Wie groß ist das maximale Durchschnittsgehalt in einer Abteilung?
SQL> SELECT
max(avg(sal))
2 FROM
emp
3 GROUP BY deptno;
MAX(AVG(SAL))
------------2916.6667
5-24
Zusammenfassung
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
column, group_function(column)
table
condition]
group_by_expression]
group_condition]
column];
Reihenfolge der Auswertung der Klauseln:
• WHERE Klausel
• GROUP BY Klausel
• HAVING Klausel
5-25
Übungen
• Anwendung von Gruppenfunktionen in
verschiedenen Anfragen
• Bildung von Gruppen zur Darstellung
von mehreren Ergebnissen
• Ausschließen von Gruppen mittels
HAVING Klausel
5-26