5 Verdichten von Daten mit Gruppenfunktionen. 5-2 Ziele Kennenlernen der vorhandenen Gruppenfunktionen Anwendung von Gruppenfunktionen Gruppieren von

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


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




5-19



• 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


to

restrict groups

to restrict groups



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

Documents

5 Verdichten von Daten mit Gruppenfunktionen. 5-2 Ziele Kennenlernen der vorhandenen Gruppenfunktionen Anwendung von Gruppenfunktionen Gruppieren von