Anwendung und Korrektheit von Top N-Abfragen

Bachelor Konzeptionelle Modellierung
1

Top N funktioniert nicht bei “identischen” Datensätzen?

Disclaimer: Dieser Thread wurde aus dem alten Forum importiert. Daher werden eventuell nicht alle Formatierungen richtig angezeigt. Der ursprüngliche Thread beginnt im zweiten Post dieses Threads.

2

Anwendung und Korrektheit von Top N-Abfragen
Ich habe zwei (unabhängige) Fragen zur Anwendung von Top N-Abfragen.

Bei der SQL-Rechnerübung lautete Aufgabe 11:

[quote]Jetzt interessieren uns die Preisklassen von Produkten. Diese erhalten wir durch die Abfrage SELECT DISTINCT preis FROM produkt
Zeigen Sie den durchschnittlichen Preis an, sowie jeweils die 5 nächsten teureren und
die 5 nächsten billigeren. Außerdem soll der Abstand der Preisklasse zum Durchschnitt
ausgegeben werden. Beispielausgabe: [/quote]

Meine Lösung:

[code=sql]CREATE VIEW Abstand AS
SELECT DISTINCT preis,
(preis - (SELECT AVG(preis) FROM produkt)) AS abstand
FROM Produkt;

(SELECT
a1.preis,
COUNT() AS Rang
FROM Abstand a1, Abstand a2
WHERE
(a1.abstand >= a2.abstand AND a1.abstand > 0 AND a2.abstand > 0) OR
(a1.abstand <= a2.abstand AND a1.abstand < 0 AND a2.abstand < 0)
GROUP BY a1.preis, a1.abstand
HAVING COUNT() <= 5)

UNION

(SELECT AVG(preis), 0 AS Rang FROM Produkt)

ORDER BY preis;

DROP VIEW abstand;
[/code]

Ist das die intuitivste/beste Lösung? Leider gibt es keine Lösungshinweise auf StudOn.
Dieses UNION gefällt mir nicht besonders, doch ich fand keinen Weg, dieses in die TOP N-Abfrage direkt “reinzuwurschteln”.

[size=16]Allgemein zu TOP-N-Anfragen:[/size]
Kann es sein, dass diese nicht funktionieren, wenn man z. B. eine TOP 5-Abfrage machen möchte und 7 Datensätze hat, die im zu vergleichenden Attribut denselben Wert haben?

Beispielsweise ergibt das SELECT-Statement im folgenden Code eine leere Relation! Das ist klar, weil COUNT(*) sozusagen >= 7 für alle Datensätze ist.

=> [url=http://sqlfiddle.com/#!9/b3bc14/2/0]http://sqlfiddle.com/#!9/b3bc14/2/0[/url] (live, ändere zB den Code zu COUNT(*) <= 10, was funktionieren wird!)

[code=sql]CREATE TABLE test_rank (
a INTEGER PRIMARY KEY,
b INTEGER
);
INSERT INTO test_rank VALUES
(1, 1), (2, 1), (3, 1), (4, 1), (5, 1),
(6, 1), (7, 1), (8, 2), (9, 3), (10, 4),
(11, 5);

SELECT
COUNT() AS rang,
t1.b
FROM test_rank t1, test_rank t2
WHERE t1.b >= t2.b
GROUP BY t1.a, t1.b
HAVING COUNT() <= 5
ORDER BY rang ASC;

DROP TABLE test_rank;[/code]

Was macht man in solchen Fällen?
Sind dann nicht alle Top-N-Abfragen falsch, wenn man nicht garantieren kann, dass es nicht mehr als N Datensätze gibt, die im Vergleichsattribut gleich sind?

3

Das ist genau die Situation, in der diese Art von Top-N-Anfrage versagt: Wenn mehrere Tupel im Vergleichsattribut den gleichen Wert haben.

In der Praxis wird man eine Top-N-Anfrage deswegen anders (und einfacher) formulieren. In den KonzMod-Übungen wird’s halt mit einem Self-Join gemacht, weil es doch ganz lehrreich ist. Das ist mal eine Anfrage, in die man einen Riesenberg SQL-Features gleichzeitig reinpacken kann: Projektion, Join, Gruppierung + Aggregierung, Sortierung und Restriktion. :slight_smile:

Einen portablen Weg für Top-N-Anfragen gibt es übrigens erst seit dem SQL:2008-Sprachstandard (mit sinnvollen Erweiterungen seit SQL:2011).

1 Like