Aus einer Detailtabelle mit einer Aggregatfunktion einzelne Zeilen bestimmen

• » Home
• »SQL-Praxis
• » Aggregatfunktionen

• Folgendes Szenario: Eine Grundtabelle mit Objekten (etwa Artikel, Strom- oder Gaszähler). Eine Detailtabelle, die zu jedem Element in der Grundtabelle mehrere Einträge sammelt. Die Detailtabelle enthält eine Datumsspalte und eine Spalte mit zusätzlichem Inhalt - den Artikelpreis oder den aktuellen Zählerstand. Bei jeder Preisänderung für diesen Artikel wird eine neue Zeile mit der Artikel-Id, dem neuen Preis und dem Gültig-Ab-Datum ergänzt. Oder: Der Zählerstand wird jeden Monat abgelesen und in der Tabelle mit Zähler-Id und Datum eingetragen. Ihre Aufgabe besteht darin, zu jeder Zeile in der Grundtabelle sowie zu einem fest vorgegebenen Datum die zu diesem Zeitpunkt gültigen Zeilen in der Detailtabelle zu ermitteln. Es soll also der zum 01.05.2004 gültige Artikelpreis oder der letzte Zählerstand bestimmt werden.

   

  Für die folgenden Ausführungen wird angenommen, daß die Kombination aus fkey und Datum eindeutig ist, daß es also pro Artikel / Gaszähler höchstens einen Eintrag pro Tag gibt. Die Spalte fkey steht für foreign key, also für den Fremdschlüssel in der Detailtabelle, der auf den Primärschlüssel in der Grundtabelle verweist.
• Betrachten Sie die folgende Tabelle tbl_Details:

   

  id	fkey	A-Preis	Datum
  1	35	39.80	01.03.2004
  2	35	44.50	01.05.2004
  3	35	41.90	01.07.2004
  4	35	44.50	02.07.2004
  5	38	99.00	01.03.2004
  6	38	110.59	01.04.2004
  7	38	122.30	01.05.2004
  8	38	129.80	01.06.2004

   

  Bei dem Artikel mit der Nummer 35 kann man an einen üblichen Artikel mit einem Sonderangebot am 01.07.2004 denken. Der fkey = 38 kann auch die Daten für einen Gas- oder Stromzähler repräsentieren, dessen Daten monatlich abgefragt werden. A-Preis bedeutet in diesem Fall den Zählerstand.

Schrittweises Zusammensetzen der Sql-Lösung

• Zunächst wird der Fall behandelt, daß der letzte Preis bzw. Zählerstand interessiert. Da 'letzter Preis' = neuestes Datum, kann nach dem Maximum der Datumswerte gesucht werden, gruppiert nach den Elementen der Grundtabelle, hier also nach der Spalte fkey. Entscheidend ist, daß diese Abfrage, die später als Teiltabelle in einen JOIN eingebaut wird, nur zwei Spalten - fkey und Datum - enthält, die eigentlichen 'Nutzdaten' - der Preis bzw. der Zählerstand - jedoch noch fehlen.

  SELECT A.fkey, Max(A.Datum) As [Max-Datum]
  From tbl_Details As A
  Group By A.fkey

  Diese Abfrage liefert die folgende Ergebnistabelle:

   

  fkey	Max-Datum
  35	02.07.2004
  38	01.06.2004

   

  Die inhaltlich wesentliche Spalte A-Preis darf an dieser Stelle nicht zur Sql-Abfrage mit hinzugenommen werden. Denn ansonsten müßte nach dieser gruppiert werden, so daß bei drei verschiedenen Preisen bzw. vier verschiedenen Zählerständen auch drei bzw. vier Zeilen anstelle einer Zeile pro fkey ausgegeben werden würde. Ebenso darf nicht über die interessierende Spalte aggregiert werden, um diese 'irgendwie mit in die Ausgabe hineinzuquetschen'. Denn jede Aggregatfunktion würde alle Zeilen zu diesem fkey berücksichtigen. Höchstens im Spezialfall der Maximums-Funktion in Kombination mit monoton wachsenden Preisen bei einer Suche nach dem spätestmöglichen Wert würde eine solche Abfrage ein korrektes Ergebnis liefern.
• In der obigen Tabelle sind nur Ausgabezeilen enthalten, welchen genau eine Zeile der Detailtabelle entspricht. Dies gilt allgemein: Die Gruppierung nach fkey schließt es aus, daß ein fkey in mehreren Ergebniszeilen gleichzeitig vorkommt. Die Verwendung einer problemangepaßten Aggregatfunktion (MIN, MAX) gibt innerhalb dieser Teilmenge einen eindeutigen Wert zurück, so daß die Kombination aus fkey und Wert der Aggregatfunktion für diese Gruppe eindeutig ist. Also kann diese Tabelle als Unterabfrage für einen INNER JOIN verwendet werden, um die tatsächlich gewünschten inhaltlichen Werte zu erhalten - der zu diesem Datum gehörende Preis.

  SELECT A.*
  FROM tbl_Details As A INNER JOIN
  
  	(SELECT B.fkey, Max(B.Datum) As [Max-Datum]
  	FROM tbl_Details As B
  	GROUP BY B.fkey) As C
  
  ON A.fkey = C.fkey And
  	A.Datum = C.[Max-Datum]

  Diese Abfrage liefert nur noch die beiden folgenden Zeilen als Ergebnis:

   

  id	fkey	A-Preis	Datum
  4	35	44.50	02.07.2004
  8	38	129.80	01.06.2004

   

  Nun sind jedoch alle Spalten einschließlich der Primärschlüssel dieser Tabelle im Ausgabe-Resultset enthalten. Ferner ist die Spalte 'fkey' alleine und in Kombination mit 'Datum' eindeutig, da aus der ursprünglichen Detailtabelle alle Zeilen für diesen fkey mit Ausnahme dieser Zeile entfernt wurden.

   

  Eine solche Ausgabetabelle kann anschließend bsp. mit der eigentlichen Grundtabelle kombiniert werden, um etwa Artikelnamen oder die Hausnummern auszugeben, in welchen die Zähler installiert sind. Heißt diese Tabelle tbl_Artikel, so liefert die folgende Abfrage dieses Ergebnis:

  
  SELECT E.[Artikel-Nummer], E.[Artikel-Name],
  	A.Datum, A.[A-Preis]
  
  FROM (tbl_Artikel As E Inner Join tbl_Details As A
  
  ON E.[Artikel-Nummer] = A.fkey) INNER JOIN
  
  	(SELECT B.fkey, Max(B.Datum) As [Max-Datum]
  	FROM tbl_Details As B
  	GROUP BY B.fkey) As C
  
  ON A.fkey = C.fkey And
  	A.Datum = C.[Max-Datum]

  Beachten Sie, daß die zur Kenntlichmachung vergrößert dargestellten Klammern im Anschluß an FROM nur bei Datenbanksystemen wie Access notwendig sind, welche implizite Klammerungen nicht unterstützen. Beim Ms-Sql-Server kann und sollte auf eine solche Klammerung verzichtet werden. Unterabfragen müssen dagegen immer geklammert werden, unabhängig vom verwendeten System.

   

  Damit ist die ursprüngliche Aufgabenstellung zunächst vollständig für den Fall des letzten Eintrags gelöst.

Variationen bezüglich der Datumsangaben: Preis am Stichtag

• Die bisherige Lösung basiert darauf, eine Unterabfrage zu erstellen, welche pro fkey genau eine Zeile zurückliefert. Als zweites Kriterium wurde zunächst willkürlich die Zeile mit dem spätesten Datum gewählt, diese wurde von der MAX-Funktion zurückgegeben. Im Rahmen der allgemeinen Fragestellung ist ein solches Vorgehen zu unspezifisch: Gewünscht wird bsp. der Preis eines Artikels am 30.06 oder am 01.07.2004. Dies läßt sich jedoch relativ einfach durch eine Ergänzung der Unterabfrage erreichen. @cur_Date sei eine Variable mit dem gewünschten Datum:

  SELECT A.fkey, Max(A.Datum) As [Max-Datum]
  From tbl_Details As A
  Where A.Datum <= @cur_Date
  Group By A.fkey

  Eine zusätzliche Where-Klausel schränkt die zur Gruppierung herangezogenen Werte vor der Gruppierung ein. Damit werden zunächst alle ohnehin irrelevanten Zeilen ausgesondert und nur über die verbleibende Restmenge das Maximum ermittelt. Da innerhalb des gewünschten Zeitraums nach diesem Maximum nicht mehr ein neuer Preis festgesetzt worden ist, handelt es sich bei dieser Preisangabe zum maximalen Datum um den zu @cur_Date gültigen Preis. Beachten Sie, daß nicht nach Max(A.[A-Preis]), sondern nach Max(A.Datum) innerhalb der Gruppierung wird.
• In dieser Form kann die Abfrage jedoch versteckte Fehlergebnisse liefern. Denn wird @cur_Date bsp. mit dem Wert '01.07.2004' belegt, die Datumsangaben jedoch sekundengenau (etwa mit getDate() (Ms-Sql-Server) oder Now() (Access)) eingetragen, dann enthält die Variable @cur_Date den Wert '01.07.2004 00:00:00'. Wurden die Preise am Morgen des 01.07.2004 vor Geschäftsbeginn aktualisiert, ist in der Tabelle bsp. '01.07.2004 07:55:10' eingetragen, so daß die obige Abfrage den Preis vom Vortag ermittelt.

   

  Dies kann gelöst werden, indem zu @cur_Date mittels einer systemspezifischen Funktion ein Tag hinzuaddiert wird. Beim Ms-Sql-Server leistet dies die Anweisung

  WHERE A.Datum <= DateAdd(d, 1, @cur_Date)

  Beachten Sie, daß die folgende Anweisung zwar logisch äquivalent, unter Performance-Gesichtspunkten jedoch inadäquat wäre:

  WHERE DateAdd(d, -1, A.Datum) <= @cur_Date

  Bei der ersten Lösung wird einmalig zur Variablen der Wert hinzugefügt und anschließend mit diesem nun konstanten Wert verglichen. Die zweite Lösung berechnet für jede Zeile den neuen Wert und vergleicht erst diesen. Der Aufwands-Unterschied besteht zum einen in der vielfach notwendigen Rechenoperation. Zum anderen jedoch führt die Verwendung der Datumsspalte in einer Funktion in der Regel dazu, daß ein eventuell auf dieser Spalte gesetzter Index nicht mehr genutzt werden kann, sondern die ganze Spalte zunächst verarbeitet wird. Im ersten Fall wird dagegen - falls vorhanden - der Index unmittelbar zur Auswahl der Spalten verwendet.
• Eine auf den ersten Blick gleichrangige Lösung besteht in einem Umschreiben der Abfrage:

  SELECT A.fkey, Min(A.Datum) As [Max-Datum]
  From tbl_Details As A
  Where @cur_Date <= A.Datum
  Group By A.fkey

  Anstelle des Maximums aller Werte, die kleiner oder gleich dem Stichtag sind, wird das Minimum aller Werte verwendet, die gleich oder größer dem Stichtag sind. Diese Lösung stimmt jedoch nur dann, falls täglich eine Preisangabe oder ein Zählerstand in die Tabelle eingefügt wird. Ist dies nicht der Fall, so wird zum Datum '15.05.2004' das Datum, damit der Preis vom '01.06.2004' gewählt.
• Wesentlich ist, daß alle ergänzenden Einschränkungen für das Datum ausschließlich in der Where-Klausel der Unterabfrage durchzuführen sind. Damit wird die Menge der Zeilen, über die anschließend gruppiert wird, im notwendigen Maße verkleinert, so daß eventuell nur noch eine Zeile pro fkey übrigbleibt. Eine Aggregation über diese eine Zeile mit der MAX-Funktion liefert genau diese Zeile zurück, so daß die MAX-Funktion für verschiedene Where-Einschränkungen nutzbar ist.

Link zur hiesigen Seite als QR-Code