Sql-und-Xml - Home

Sql-Tutorial

Mit Aggregat-Funktionen und GROUP BY Daten auswerten und aggregieren

Vorbemerkung

  • Mit SELECT, FROM und WHERE erhalten Sie einzelne Zellen der gewählten Tabellen zurück. Es wird jedoch keine Aggregation des Ergebnisses durchgeführt, die Daten werden nicht zusammengefaßt. Liefert Ihre JOIN-Anweisung 50.000 Zeilen und entfernt die WHERE-Klausel hiervon 30.000, so enthält Ihre Ergebnistabelle 20.000 Zeilen. Alle Techniken mit Aggregatfunktionen fassen dagegen mehrere Zeilen zu einer Ergebniszeile zusammen und geben nur noch diese aus.
  • Eine solche Aggregation / Zusammenfassung / Ermittlung von Kennziffern / Konsolidierung kann sich über die gesamte Tabelle erstrecken. In diesem Fall werden nur Aggregatfunktionen, jedoch keine GROUP BY - Klausel benötigt. Oder die Kennziffern sollen aufgesplittet nach Artikeln, Verkäufern, Datum, Regionen oder anderen Kriterien bestimmt werden. In diesem Fall wird die Tabelle zunächst gemäß dieser Kriterien mit der GROUP BY - Klausel in Teiltabellen zerlegt und für jede Teiltabelle die Kennziffern berechnet. Damit liegt pro Teiltabelle eine Ergebniszeile vor, eventuell sind die konkreten Werte, die über die Zugehörigkeit zu dieser Teiltabelle entscheiden, ebenfalls mit ausgegeben. Aus all diesen Einzelzeilen wird das Gesamtergebnis zusammengefügt.
  • Bildlich gesprochen: Die bisherigen Techniken liefern Ihnen einen Stapel von Zeilen zurück. Verwenden Sie eine Aggregat-Funktion, so wird der Zeilenstapel auf eine Zelle, einen einzigen Wert gestaucht. Verwenden Sie mehrere Aggregat-Funktionen, so wird der Zeilenstapel auf mehrere Zellen komprimiert, diese werden als eine Zeile ausgegeben. Gruppieren Sie das Ergebnis zusätzlich mit GROUP BY, so wird der Gesamtstapel zunächst gemäß der GROUP BY - Kriterien in Teilstapel zerlegt, über jeden dieser Teilstapel wird aggregiert, dieser also in einer Zeile zusammengefaßt, so daß am Ende alle Teilstapel-Ergebniszeilen zum Gesamtergebnis vereinigt werden.
  • Sollen Ergebnisse, die mittels der Aggregatfunktionen ermittelt wurden, als zusätzliches Filterkriterium herangezogen werden, so können solche Ausdrücke in der HAVING-Klausel angegeben werden.

Syntax

  • Rückgabe einer einzelnen Zelle - einfache Aggregation
    SELECT <Aggregatfunktion(Spaltenname) As [Spaltenname]>
    FROM ...
    [WHERE ...]
  • Rückgabe einer Zeile mit mehreren Zellen - mehrere Kennziffern gleichzeitig ermitteln
    SELECT <Aggregatfunktion-1(Spaltenname-A1) As Ausgabename-1,>
    	<Aggregatfunktion-2(Spaltenname-A2) As Ausgabename-2,>
    	...
    	<Aggregatfunktion-n(Spaltenname-An) As Ausgabename-n>
    FROM ...
    [WHERE ...]
    [HAVING ...]
  • Gruppierung in Teilgruppen, Aggregation über die Teilgruppen und Zusammenfassen der Einzelergebnisse
    SELECT <Aggregatfunktion-1(Spaltenname-A1) As Ausgabename-1,>
    	<Aggregatfunktion-2(Spaltenname-A2) As Ausgabename-2,>
    	...
    	<Aggregatfunktion-n(Spaltenname-An) As Ausgabename-n>,
    	Spaltenname-1,
    	Spaltenname-2,
    	...
    	Spaltenname-m
    
    FROM ...
    [WHERE ...]
    GROUP BY Spaltenname-1,
    	Spaltenname-2,
    	...
    	Spaltenname-m
    [HAVING ...]
    

Beispiele

  1. SELECT COUNT(A.DATUM) As [Zahl-der-Umsätze]
    FROM UMSATZ As A
    Beachten Sie, daß Ihnen diese Abfrage 9 als Ergebnis zurückliefert, also die Zahl der nichtleeren Zellen. Fügen Sie einen weiteren Datensatz ein und lassen bei diesem das Feld DATUM leer, so erhalten Sie mit der obigen Abfrage weiterhin das Ergebnis 9. Es wird nicht 2 (die Zahl der verschiedenen Datumsangaben) ausgegeben. Sind alle Zellen einer Tabelle belegt, so ist es für diese Abfrage mit COUNT unerheblich, welche Zelle Sie verwenden.
  2. SELECT A.DATUM, COUNT(A.DATUM) As [Zahl-der-Umsätze-pro-Tag]
    FROM UMSATZ As A
    GROUP BY A.DATUM
    Sie erhalten als Ergebnis zwei Zeilen, für jede der unterschiedlichen Datumsangaben eine Zeile. Jede Zeile enthält die zusätzliche Zelle 'Zahl-der-Umsätze-pro-Tag' mit den Werten 6 für den 24.06 und 3 für den 25.06.99. Verzichten Sie auf die Ausgabespalte A.DATUM, so können Sie die beiden Zellen mit den Werten 6 und 3 nicht mehr interpretieren.
  3. SELECT MAX(A.A_PREIS) As [teuerster-Artikel]
    FROM ARTIKEL As A
    Dies liefert Ihnen den Preis des teuersten Artikels (360.00). Beachten Sie, daß Sie mit dieser Abfrage noch nicht den Namen oder die Details dieses Artikels erfahren. Dies gelingt nur, wenn Sie das obige Ergebnis selbst als Kriterium in einer Where-Abfrage verwenden. Näheres finden Sie im Abschnitt über Unterabfragen. Hier eine Möglichkeit:
    SELECT B.*
    FROM ARTIKEL As B
    WHERE B.A_PREIS = (SELECT MAX(A.A_PREIS) As [teuerster-Artikel]
    	FROM ARTIKEL As A)
    Ergebnis:
    A_NRA_NAMEA_PREIS
    22Mantel360.00
  4. SELECT SUM(A.A_PREIS * B.A_STUECK) As [Gesamtumsatz]
    FROM ARTIKEL As A INNER JOIN UMSATZ As B
    ON A.A_NR = B.A_NR
    Diese Abfrage kombiniert jede Umsatz-Zeile mit der zu ihr gehörenden Artikel-Zeile. Damit sind die Spalten A_PREIS und A_STUECK bekannt und können miteinander multipliziert werden. Ohne die Aggregatfunktion SUM würden neun Zeilen mit dem tatsächlichen Umsatz dieser einzelnen Verkaufshandlung ausgegeben. So wird über all diese Einzelergebnisse summiert und der Gesamtumsatz von 27.472,00 ermittelt.
  5. SELECT A.A_NR, SUM(A.A_PREIS * B.A_STUECK) As [Umsatz-dieses-Artikels]
    FROM ARTIKEL As A INNER JOIN UMSATZ As B
    ON A.A_NR = B.A_NR
    GROUP BY A.A_NR
    Ergebnis:
    A_NRUmsatz-dieses-Artikels
    114.862,00
    121.990,00
    134.420,00
    2216.200,00

    Diese Aggregation zerlegt den Gesamtstapel der neun Umsätze zunächst in Teilstapel anhand der Artikelnummern. Für jeden Teilstapel wird multipliziert und die Summe ermittelt. Das Ergebnis wird, ergänzt um die Information, zu welchem Artikel die Summation gehört, ausgegeben.
  6. SELECT B.DATUM,
    	MAX(A.A_PREIS * B.A_STUECK) As [Max-Tages-Einzelumsatz]
    FROM ARTIKEL As A INNER JOIN UMSATZ As B
    ON A.A_NR = B.A_NR
    GROUP BY B.DATUM
    Ergebnis:
    DATUMMax-Tages-Einzelumsatz
    24.06.19993.867,50
    25.06.199912.600,00

    Anstelle der obigen Summation wird das Maximum über alle Einzelumsätze für jeden Tag ermittelt. Beachten Sie, daß Sie auch mit dieser Abfrage nicht ermitteln können, welcher Artikel für diesen höchsten Umsatz verantwortlich ist. Denn sobald Sie zur Gruppierung die Artikel-Nummer hinzunehmen, erhalten Sie für jede Kombination aus Artikel und Tag eine Zeile, so daß das Maximum über solche einzeiligen Teilmengen gleich dem Wert in dieser Zelle ist. Möchten Sie zusätzlich wissen, welcher Artikel für diesen höchsten Einzelumsatz zuständig ist, so kombinieren Sie diese Abfrage als JOIN mit einer direkten Multiplikation:
    SELECT A.DATUM, A.A_NR, A.Einzelumsatz
    
    FROM
    	(SELECT C.DATUM, B.A_NR,
    		B.A_PREIS * C.A_STUECK As [Einzelumsatz]
    	FROM ARTIKEL As B INNER JOIN UMSATZ As C
    	ON B.A_NR = C.A_NR) AS A
    
    INNER JOIN (
    	SELECT Y.DATUM,
    		MAX(X.A_PREIS * Y.A_STUECK) As [Max-Tages-Einzelumsatz]
    	FROM ARTIKEL As X INNER JOIN UMSATZ As Y
    	ON X.A_NR = Y.A_NR
    	GROUP BY Y.DATUM) As D
    
    ON (A.DATUM = D.DATUM) AND
    	(A.Einzelumsatz = D.[Max-Tages-Einzelumsatz])
    Ergebnis:
    DATUMA_NREinzelumsatz
    24.06.1999133.867,50
    25.06.19992212.600,00

    Unterstützt das Datenbanksystem - im Gegensatz zu Access - eine direkte Multiplikation im JOIN-Ausdruck, so können Sie den ersten geklammerten JOIN-Ausdruck herausziehen und die Multiplikation direkt in der JOIN-Klausel durchführen.
  7. SELECT B.DATUM, B.A_NR,
    	SUM(A.A_PREIS * B.A_STUECK) As Tagesumsatz
    FROM ARTIKEL As A INNER JOIN UMSATZ As B
    ON A.A_NR = B.A_NR
    GROUP BY B.DATUM, B.A_NR
    Ergebnis:
    DATUMA_NRTagesumsatz
    24.06.1999113.094,00
    24.06.1999121.990,00
    24.06.1999134.420,00
    24.06.1999223.600,00
    25.06.1999111.768,00
    25.06.19992212.600,00

    Das ist nun die klassische ausdifferenzierte Version: Die tatsächlichen Einzelumsätze werden pro Tag und pro Artikel aggregiert, das Ergebnis wird mit den beiden gruppierenden Spalten ausgegeben.
  8. SELECT A.DATUM, A.A_NR, COUNT(*) As [Umsatz-Anzahl]
    FROM UMSATZ As A
    GROUP BY A.DATUM, A.A_NR
    HAVING COUNT(*) > 1
    Ergebnis:
    DATUMA_NRUmsatz-Anzahl
    24.06.1999122
    24.06.1999132
    25.06.1999112

    Ohne HAVING-Klausel würde diese Abfrage sechs Zeilen zurückliefern - vier für den 24.06, an dem vier Artikel verkauft wurden, 2 für den 25.06. Die HAVING-Klausel definiert eine zusätzliche Einschränkung und beschränkt die Ausgabe auf jene Artikel, die mindestens zwei Mal verkauft worden sind.
  9. SELECT A.A_NR, SUM(A.A_PREIS * B.A_STUECK) As [Umsatz-dieses-Artikels]
    FROM ARTIKEL As A INNER JOIN UMSATZ As B
    ON A.A_NR = B.A_NR
    GROUP BY A.A_NR
    HAVING SUM(A.A_PREIS * B.A_STUECK) > 10000
    
    Dies ermittelt jene Artikel, mit welchen ein Umsatz von über 10.000 gemacht wurde. Die SQL-Abfrage ist dieselbe wie unter Beispiel 5, es wurde nur die HAVING-Klausel hinzugefügt. Artikel 22 wird ausgegeben.

Aggregatfunktionen

  1. COUNT: Ermittlung der Zeilen bzw. Zellen. Diese Funktion kann in zwei verschiedenen Versionen genutzt werden: COUNT(*) gibt die Anzahl aller Zeilen gemäß dem GROUP-BY-Abschnitt zurück. COUNT(<Spaltenname>) liefert die Zahl der nichtleeren Zellen in der angegebenen Spalte.
  2. MIN(<Spaltenname>) / MAX(<Spaltenname>): Ermittelt das Minimum / Maximum der angegebenen Spalte.
  3. SUM(<Spaltenname>): Summation über alle Zellwerte
  4. AVG(<Spaltenname>): Mittelwert aller Zellwerte, NULL-Werte werden ignoriert.
Dies sind die Funktionen, die jedes Datenbanksystem mit Sql-Schnittstelle anbieten dürfte. Hinweise für weitere Funktionen Ihres Systems finden Sie in der zugehörigen Dokumentation. Die Aggregatfunktionen mit numerischem Bezug (SUM, AVG) können nur für Spalten verwendet werden, welche numerische Datentypen enthalten.

Bemerkungen

  • Wenn Sie eine Spalte ausgeben, über diese jedoch weder aggregieren noch sie in der GROUP-BY-Klausel erwähnen, so erhalten Sie eine typische Fehlermeldung: 'Sie wollten eine Abfrage ausführen, die den angegebenen Ausdruck 'A_NR' nicht als Teil der Aggregatfunktion einschließt' (Beispiel Access). Diese Fehlermeldung ist insofern irreführend, da es drei Möglichkeiten gibt: Sie entfernen die Spalte aus der Liste der Ausgabespalten, Sie fügen die Spalte zum GROUP-BY-Abschnitt hinzu oder Sie aggregieren über diese Spalte. Beim MS-SQLServer heißt die analoge Fehlermeldung: 'Die U.A_NR-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion enthalten und keine GROUP BY-Klausel vorhanden ist'. Wurde eine GROUP-BY-Klausel angegeben, so wird Ihnen mitgeteilt: 'und nicht in der GROUP BY-Klausel enthalten ist'. Beim MS-SqlServer ist die Fehlermeldung also weitaus spezifischer als bei Access.
  • Ist zusätzlich ein WHERE-Abschnitt angegeben, so wird dieser zuerst kostengünstig ausgewertet. Die Aggregation mit eventueller vorheriger Gruppierung wird erst anschließend ausgeführt. In den HAVING-Abschnitt gehören deshalb nur jene Ausdrücke, welche sich auf Aggregationen beziehen.
  • Access neigt bei der visuellen Codegenerierung dazu, bei vorhandenen Aggregationen alle Kriterien von der WHERE-Bedingung in die HAVING-Klausel zu verschieben. Dies ist jedoch unnötig teuer und sollte gegebenenfalls per Hand bzw. durch den Verzicht auf die visuelle Codierung vermieden werden.
  • Wenn Sie nur wissen möchten, welche verschiedenen Zeilen existieren, so liefern die beiden Alternativen DISTINCT sowie das Gruppieren nach allen Ausgabespalten dieselben Ergebnisse. DISTINCT ist jedoch immer ressourcenschonender als GROUP BY und deshalb in solchen Fällen erste Wahl.
  • In PHP-Foren findet sich wiederholt Code, mit welchem die Zahl der Zeilen in einer Tabelle ermittelt werden soll. Dieser Code ist wie folgt aufgebaut:
    $select = mysql_query("Select * From Artikel");
    $reihen = mysql_num_rows($select);
    Eine solche Verarbeitung widerspricht sämtlichen Prinzipien ressourcenschonender Programmierung. Akzeptabel ist eine Lösung der Form:
    $select = mysql_query("Select Count(*) From Artikel");
    $zahl_der_zeilen = mysql_fetch_row($select);
    echo $zahl_der_zeilen[0];
    
    Ein DBMS sollte nur jene Zeilen zurückgeben, die tatsächlich benötigt werden. Das Zählen von Datensätzen wird von jedem DBMS rascher erledigt als im Rahmen eines externen Aufrufs von der umgebenden Wirtssprache. Ferner wird nur eine einzige Zelle, also ein Gebilde aus einer Spalte und einer Zeile, zurückgegeben. Bei gespeicherten Prozeduren läßt sich der Code weiter optimieren:
    Create Procedure _up_count_Artikel
    	@num_rows int Output
    As
    
    Select @num_rows = Count(*)
    From Artikel
    In diesem Fall muß keine DataTable oder ein RecordSet mit Informationen über die zurückgegebenen Datentypen erstellt werden, da der Rückgabewert von vornherein bekannt ist. Der Aufruf kann nun (VB.NET) so erfolgen:
    Dim oCmd As New SqlCommand("_up_count_Artikel", oConn), _
    	i_num_rows As Integer
    
    oCmd.Parameters.Add("@num_rows", SqlDbType.Int).Direction = _
    	ParameterDirection.Output
    Try
    	oCmd.ExecuteNonQuery()
    	i_num_rows = CType(oCmd.Parameters("@num_rows").value, Integer)
    
    Catch _e As Exception
    	'Fehlerbehandlung
    End Try

© 2003-2015 Jürgen Auer, Berlin.