View
108
Download
0
Category
Preview:
Citation preview
55
Verdichten von Daten mit GruppenfunktionenVerdichten von Daten mit Gruppenfunktionen
5-2
ZieleZiele
• Kennenlernen der vorhandenen Gruppenfunktionen
• Anwendung von Gruppenfunktionen
• Gruppieren von Daten mittels GROUP BY Klausel
• Selektieren von Gruppen unter Anwendung der HAVING Klausel
• Kennenlernen der vorhandenen Gruppenfunktionen
• Anwendung von Gruppenfunktionen
• Gruppieren von Daten mittels GROUP BY Klausel
• Selektieren von Gruppen unter Anwendung der HAVING Klausel
5-3
Was sind Gruppenfunktionen?Was sind Gruppenfunktionen?Gruppenfunktionen operieren auf Mengen von Gruppenfunktionen operieren auf Mengen von Zeilen und ergeben ein Resultat pro Gruppe.Zeilen und ergeben ein Resultat pro Gruppe.Gruppenfunktionen operieren auf Mengen von Gruppenfunktionen operieren auf Mengen von Zeilen und ergeben ein Resultat pro Gruppe.Zeilen und ergeben ein Resultat pro Gruppe.EMPEMP
““Maximales Maximales Gehalt in Gehalt in
EMP Tabelle”EMP Tabelle”
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
MAX(SAL)
---------
5000
5-4
Typen von GruppenfunktionenTypen von Gruppenfunktionen
• AVG
• COUNT
• MAX
• MIN
• SUM
• STDDEV
• VARIANCE
• AVG
• COUNT
• MAX
• MIN
• SUM
• STDDEV
• VARIANCE
5-5
Einsatz von GruppenfunktionenEinsatz von Gruppenfunktionen
SELECT [column,] group_function(column)FROM table[WHERE condition][GROUP BY column][ORDER BY column];
5-6
Verwendung von AVG und SUMVerwendung von AVG und SUM
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)-------- --------- --------- --------- 1400 1600 1250 5600
AVG and SUM anwendbar für numerische Daten.AVG and SUM anwendbar für numerische Daten.AVG and SUM anwendbar für numerische Daten.AVG and SUM anwendbar für numerische Daten.
SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE job LIKE 'SALES%';
5-7
Anwendung von MIN und MAXAnwendung von MIN und MAX
MIN and MAX für beliebigen Datentyp geeignet.MIN and MAX für beliebigen Datentyp geeignet.MIN and MAX für beliebigen Datentyp geeignet.MIN and MAX für beliebigen Datentyp geeignet.
SQL> SELECT MIN(hiredate), MAX(hiredate) 2 FROM emp;
MIN(HIRED MAX(HIRED--------- ---------17-DEC-80 12-JAN-83
5-8
COUNT-FunktionCOUNT-Funktion
COUNT(*)--------- 6
SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE deptno = 30;
COUNT(*) liefert die Anzahl von Zeilen in COUNT(*) liefert die Anzahl von Zeilen in einer Tabelle.einer Tabelle.COUNT(*) liefert die Anzahl von Zeilen in COUNT(*) liefert die Anzahl von Zeilen in einer Tabelle.einer Tabelle.
5-9
Anwendung der COUNT- FunktionAnwendung der COUNT- Funktion
COUNT(COUNT(exprexpr) liefert die Anzahl Zeilen, die ) liefert die Anzahl Zeilen, die nicht null sind. nicht null sind. COUNT(COUNT(exprexpr) liefert die Anzahl Zeilen, die ) liefert die Anzahl Zeilen, die nicht null sind. nicht null sind.
SQL> SELECT COUNT(comm) 2 FROM emp 3 WHERE deptno = 30;
COUNT(COMM)----------- 4
5-10
Gruppenfunktion und NullwerteGruppenfunktion und Nullwerte
Gruppenfunktionen ignorieren Nullwerte Gruppenfunktionen ignorieren Nullwerte in der Spalte.in der Spalte.Gruppenfunktionen ignorieren Nullwerte Gruppenfunktionen ignorieren Nullwerte in der Spalte.in der Spalte.
SQL> SELECT AVG(comm) 2 FROM emp;
AVG(COMM)--------- 550
5-11
Anwendung der NVL Funktion auf Gruppenfunktionen
Anwendung der NVL Funktion auf Gruppenfunktionen
Die NVL-Funktion zwingt Gruppenfunk-Die NVL-Funktion zwingt Gruppenfunk-tionen zur Einbeziehung von Nullwerten.tionen zur Einbeziehung von Nullwerten.Die NVL-Funktion zwingt Gruppenfunk-Die NVL-Funktion zwingt Gruppenfunk-tionen zur Einbeziehung von Nullwerten.tionen zur Einbeziehung von Nullwerten.
SQL> SELECT AVG(NVL(comm,0)) 2 FROM emp;
AVG(NVL(COMM,0))---------------- 157.14286
5-12
Gruppierung von Daten Gruppierung von Daten
EMPEMP
““durchschnittl.durchschnittl.Gehalt Gehalt in EMPin EMPTabelle Tabelle für jede für jede
Abteilung”Abteilung”
2916.66672916.6667
21752175
1566.66671566.6667
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
DEPTNO AVG(SAL)
------- ---------
10 2916.6667
20 2175
30 1566.6667
5-13
Gruppierung von Daten: GROUP BY Klausel
Gruppierung von Daten: GROUP BY Klausel
SELECT column, group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][ORDER BY column];
Aufteilung von Zeilen in einer Tabelle in Aufteilung von Zeilen in einer Tabelle in kleinere Gruppen durch Anwendung der kleinere Gruppen durch Anwendung der GROUP BY Klausel.GROUP BY Klausel.
Aufteilung von Zeilen in einer Tabelle in Aufteilung von Zeilen in einer Tabelle in kleinere Gruppen durch Anwendung der kleinere Gruppen durch Anwendung der GROUP BY Klausel.GROUP BY Klausel.
5-14
Anwendung der GROUP BY Klausel Anwendung der GROUP BY Klausel
Alle Spalten in der SELECT Liste, die nicht Alle Spalten in der SELECT Liste, die nicht in Funktionen verwendet werden, müssen in Funktionen verwendet werden, müssen in der GROUP BY Klausel stehen.in der GROUP BY Klausel stehen.
Alle Spalten in der SELECT Liste, die nicht Alle Spalten in der SELECT Liste, die nicht in Funktionen verwendet werden, müssen in Funktionen verwendet werden, müssen in der GROUP BY Klausel stehen.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-15
Anwendung von GROUP BYAnwendung von GROUP BY
Die GROUP BY Spalte braucht nicht in der Die GROUP BY Spalte braucht nicht in der SELECT Liste enthalten zu sein.SELECT Liste enthalten zu sein.Die GROUP BY Spalte braucht nicht in der Die GROUP BY Spalte braucht nicht in der SELECT Liste enthalten zu sein.SELECT Liste enthalten zu sein.
SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno;
AVG(SAL)--------- 2916.6667 21751566.6667
5-16
Gruppierung nach mehreren Spalten
Gruppierung nach mehreren Spalten
EMPEMP
““summiere Gehälter in summiere Gehälter in der EMP Tabelleder EMP Tabellefür jeden Job,, für jeden Job,, gruppiert nach gruppiert nach
Abteilung”Abteilung”
DEPTNO JOB SAL
--------- --------- ---------
10 MANAGER 2450
10 PRESIDENT 5000
10 CLERK 1300
20 CLERK 800
20 CLERK 1100
20 ANALYST 3000
20 ANALYST 3000
20 MANAGER 2975
30 SALESMAN 1600
30 MANAGER 2850
30 SALESMAN 1250
30 CLERK 950
30 SALESMAN 1500
30 SALESMAN 1250
JOB SUM(SAL)
--------- ---------
CLERK 1300
MANAGER 2450
PRESIDENT 5000
ANALYST 6000
CLERK 1900
MANAGER 2975
CLERK 950
MANAGER 2850
SALESMAN 5600
DEPTNO
--------
10
10
10
20
20
20
30
30
30
5-17
Anwendung von GROUP BY bei mehreren Spalten
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-18
Illegale Anfragen mit Gruppenfunktionen
Illegale Anfragen mit Gruppenfunktionen
Jede Spalte oder Ausdruck in der SELECT Jede Spalte oder Ausdruck in der SELECT Liste, welche nicht Aggregatfunktion sind, Liste, welche nicht Aggregatfunktion sind, müssen in der GROUP BY Klausel ent-müssen in der GROUP BY Klausel ent-halten sein.halten sein.
Jede Spalte oder Ausdruck in der SELECT Jede Spalte oder Ausdruck in der SELECT Liste, welche nicht Aggregatfunktion sind, Liste, welche nicht Aggregatfunktion sind, müssen in der GROUP BY Klausel ent-müssen in der GROUP BY Klausel ent-halten sein.halten sein.
SQL> SELECT deptno, COUNT(ename) 2 FROM emp;
SQL> SELECT deptno, COUNT(ename) 2 FROM emp;
SELECT deptno, COUNT(ename) *ERROR at line 1:ORA-00937: not a single-group group function
SELECT deptno, COUNT(ename) *ERROR at line 1:ORA-00937: not a single-group group function
Column missing in the GROUP BY clause
Column missing in the GROUP BY clause
Column missing in the GROUP BY clause
Column missing in the GROUP BY clause
5-19
Illegale Anfragen mit Gruppenfunktionen
Illegale Anfragen mit Gruppenfunktionen
• WHERE-Klausel zum Filtern von Gruppen verboten.
• Dafür gibt‘s die HAVING Klausel.
• WHERE-Klausel zum Filtern von Gruppen verboten.
• Dafür gibt‘s die HAVING Klausel.
SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 2000 4 GROUP BY deptno;
SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 2000 4 GROUP BY deptno;
WHERE AVG(sal) > 2000 *ERROR at line 3:ORA-00934: group function is not allowed here
WHERE AVG(sal) > 2000 *ERROR at line 3:ORA-00934: group function is not allowed here
Cannot use the WHERE clause
Cannot use the WHERE clause
to
restrict groups
to restrict groups
Cannot use the WHERE clause
Cannot use the WHERE clause
to
restrict groups
to restrict groups
5-20
Ausschließen von GruppenAusschließen von Gruppen
““MaximalesMaximalesGehaltGehalt
pro Abteilungpro Abteilunggrößer alsgrößer als
$2900”$2900”
EMPEMP
50005000
30003000
28502850
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
DEPTNO MAX(SAL)
--------- ---------
10 5000
20 3000
5-21
Ausschließen von Gruppen: HAVING Klausel
Ausschließen von Gruppen: HAVING Klausel
HAVING Klausel zum Filtern von GruppenHAVING Klausel zum Filtern von Gruppen
– Zeilen werden gruppiert.
– Gruppenfunktion angewendet.
– Gruppen, die die HAVING Klausel erfüllen, werden angezeigt.
HAVING Klausel zum Filtern von GruppenHAVING Klausel zum Filtern von Gruppen
– Zeilen werden gruppiert.
– Gruppenfunktion angewendet.
– Gruppen, die die HAVING Klausel erfüllen, werden angezeigt.
SELECT column, group_functionFROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];
5-22
Anwendung der HAVING KlauselAnwendung der HAVING Klausel
SQL> SELECT deptno, max(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING max(sal)>2900;
DEPTNO MAX(SAL)--------- --------- 10 5000 20 3000
5-23
Anwendung der HAVING KlauselAnwendung der HAVING Klausel
SQL> SELECT job, SUM(sal) PAYROLL 2 FROM emp 3 WHERE job NOT LIKE 'SALES%' 4 GROUP BY job 5 HAVING SUM(sal)>5000 6 ORDER BY SUM(sal);
JOB PAYROLL--------- ---------ANALYST 6000MANAGER 8275
5-24
Schachtelung von FunktionenSchachtelung von Funktionen
SQL> SELECT max(avg(sal)) 2 FROM emp 3 GROUP BY deptno;
MAX(AVG(SAL))------------- 2916.6667
Wie groß ist das maximale Durchschnitts-Wie groß ist das maximale Durchschnitts-gehalt in einer Abteilung? gehalt in einer Abteilung? Wie groß ist das maximale Durchschnitts-Wie groß ist das maximale Durchschnitts-gehalt in einer Abteilung? gehalt in einer Abteilung?
5-25
ZusammenfassungZusammenfassungSELECT column, group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];
Reihenfolge der Auswertung der Klauseln: Reihenfolge der Auswertung der Klauseln:
• WHERE Klausel
• GROUP BY Klausel
• HAVING Klausel
Reihenfolge der Auswertung der Klauseln: Reihenfolge der Auswertung der Klauseln:
• WHERE Klausel
• GROUP BY Klausel
• HAVING Klausel
5-26
ÜbungenÜbungen
• Anwendung von Gruppenfunktionen in verschiedenen Anfragen
• Bildung von Gruppen zur Darstellung von mehreren Ergebnissen
• Ausschließen von Gruppen mittels HAVING Klausel
• Anwendung von Gruppenfunktionen in verschiedenen Anfragen
• Bildung von Gruppen zur Darstellung von mehreren Ergebnissen
• Ausschließen von Gruppen mittels HAVING Klausel
Recommended