Last Update: 2024 - 05 - 21 |
Access SQL - Select Top X Datensätze – Ohne Bindungenvon Philipp Stiefel, ursprünglich veröffentlicht 30. November 2015 In den letzten Wochen habe ich ein und dieselbe Frage in mehreren Foren gelesen. „Ich möchte mit einer Abfrage die ersten X Datensätze, sortiert nach Y ermitteln. Aber wenn sich Datensätze mit gleichen Werten darunter befinden, ergibt meine Abfrage mehr als die gewünschte Anzahl von Datensätzen. Ich möchte genau X Datensätze, nicht mehr.“ In diesem Artikel zeige ich, wie das in Microsoft Access mit relativ einfachen SQL Abfragen erreicht werden kann. VoraussetzungenIch die Abfragen in diesem Artikel basieren alle aus zwei einfachen Beispieltabellen mit Schauspielern (tblActors) und ihren Bewertungen (tblActorRatings), die von einer willkürlichen Instanz (mir ;-)) festgelegt wurden. Als nächste Voraussetzung brauchen wir eine einfache Abfrage, die die Schauspieler mit ihren Bewertungen zusammenführt. Hier ist sie:
SELECT a.ActorId, a.ActorName, ar.Rating, ar.DateOfRating
FROM tblActors a
INNER JOIN tblActorRatings ar
ON a.ActorId = ar.ActorId;
Ich speichere diese Abfrage unter dem Namen qryActors_Ratings. Einfache LösungDie grundlegende Anforderung die X am besten bewerteten Schauspieler abzufragen ist einfach zu lösen. Die Access Jet/Ace-Engine (die Datenbank Engine von Access) unterstützt das TOP Prädikat der SQL Abfragesprache. Also um nur die obersten X Datensätze abzufragen, schreiben wir eine Abfrage wie diese:
SELECT TOP 5 *
FROM qryActors_Ratings
ORDER BY Rating DESC
Diese Abfrage liefert die 5 am besten bewerteten Schauspieler aus unseren Tabellen. So weit war das einfach. Aber wenn wir uns das Ergebnis anschauen, wir ein Problem deutlich. In unserem Beispiel-Datenbestand sind mehrere Schauspieler mit einer Bewertung von 4. Alle sind in unserem Abfrageergebnis enthalten. Also, wie bestimmt bereits bemerkt hast, liefert unsere Abfrage nicht 5 Datensätze, sondern 6. Keine BindungenUm dieses Problem zu lösen, müssen wir über ein weiteres Merkmal sortieren damit wir eine eindeutige Reihenfolge ohne Bindungen erhalten. Die technische Anforderung an dieses Merkmal ist Ein-Eindeutigkeit. Diese Anforderung wird vom Primärschlüssel der Tabelle (tblActors) erfüllt. Einfach nur zusätzlich über den Primärschlüssel zu sortieren würde bereits die technische Anforderung erfüllen, aber es könnte Rückfragen der Benutzer auslösen. Wie „Warum ist Schauspieler A in dem Ergebnis enthalten, aber Schauspieler B nicht?“ Daher empfehle ich, eine Regel zu verwenden, die einfach zu erklären ist, wie „Neuere Bewertungen werden höher gewichtet.“ Um sicher zu gehen, dass es auch dann keine Bindungen gibt, wenn zwei Bewertungen das gleiche Datum haben, sollten wir zusätzlich noch den Primärschlüssel als drittes Sortierungsfeld einschließen.
SELECT TOP 5 *
FROM qryActors_Ratings
ORDER BY Rating DESC,
DateOfRating DESC,
ActorId DESC
Diese Abfrage liefert das gewünschte Ergebnis. Mehrere Bewertungen pro SchauspielerDas hätte eben das Ende des Artikels sein können. Wenn du aber genau auf mein Tabellendesign geachtet hast, könntest du bereits bemerkt haben, dass es mehr als eine Bewertung für einen Schauspieler geben kann. Dies kann dann dazu führen, dass ein Schauspieler mehrfach in den Ergebnissen enthalten ist. – Ich habe mal eine Bewertung hinzugefügt, die dieses Problem verdeutlicht. Wenn es deine Datenstruktur ebenfalls zulässt, dass diese Problemsituation auftritt, musst du dich für eine Methode entscheiden, um diese Duplikate aus den Ergebnissen zu entfernen. Ich sehe in diesem Kontext zwei mögliche Optionen dafür. Option 1 – Basierend auf dem Durchschnittswert der BewertungenDu änderst unsere Abfrage qryActors_Ratings von vorher, um die durchschnittliche Bewertung eines Schauspielers zu ermitteln. Das ist einfach mit der AVG Domänenaggregatfunktion zu lösen.
SELECT a.ActorId,
a.ActorName,
AVG(ar.Rating) AS Rating,
CDate(AVG(ar.DateOfRating)) AS DateOfRating
FROM tblActors AS a
INNER JOIN tblActorRatings AS ar
ON a.ActorId = ar.ActorId
GROUP BY a.ActorId,
a.ActorName;
Ich habe die AVG Funktion ebenfalls auf die Spalte DateOfRating angewendet. Der daraus resultierende Durchschnittswert ist allerdings eine Zahl und kein Datum, deshalb müssen wir diese mit der CDate-Funktion wieder in ein Datum umwandeln. (Das funktioniert, weil Datumswerte intern als Zahlen gespeichert werden.) Unsere Abfrage um das endgültige Ergebnis, die besten 5, zu ermitteln kann dann unverändert bleiben. – Nur die RatingId kann darin nicht mehr enthalten sein, weil jede einzelne Zeile der Ergebnisse auf mehreren Rating-Datensätzen basieren kann. Option 2 – Basierend auf der letzten BewertungDie andere plausible Methode, um die 5 Besten zu ermitteln, ist basierend auf der letzten (oder analog auch der ersten) Bewertung, die für jeden Schauspieler gespeichert ist. In dieser Abfrage verwenden wir die MAX Domänenaggregatfunktion, um die höchste RatingId aus der Tabelle tblActorRatings zu ermitteln. – Sie dir bewusst, dass dies nicht notwendigerweise die neuste Bewertung anhand des Datums DateOfRating ist, sondern der Wert, der zuletzt in unserer Tabelle eingegeben wurde. (Weil die RatingId ein AutoWert-Feld ist.)
SELECT TOP 5 qar.*
FROM qryActors_Ratings AS qar
WHERE RatingId =(SELECT MAX(ar.RatingId)
FROM tblActorRatings ar
WHERE ar.ActorId = qar.ActorId)
ORDER BY Rating DESC,
DateOfRating DESC,
ActorId DESC;
Es mag in manchen Fällen noch kompliziertere Anforderungen geben, aber ich denke diese beiden Optionen sind ein guter Startpunkt, von dem aus man in den meisten Szenarios zu einer Lösung kommen kann.
Ich werde Deine Email-Addresse niemals weitergeben. Du kannst den Newsletter jederzeit abbestellen. © 1999 - 2024 by Philipp Stiefel - Datenschutzerklärung |