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
Kontaktformular:
Schreiben Sie mir und wir bauen gemeinsam Ihre neue Web-Datenbank!
Mit dem Klick auf den Button stimmen Sie zu, daß Cookies in Ihrem Browser gespeichert werden. Informationen zu den gespeicherten Cookies finden Sie unter Datenschutz#Cookies.Bei Fragen zur Technik wenden Sie sich bitte an Server-Daten - Web-Datenbank-Lösungen