Abfrage: Kund hat Artikel X aber nicht Y gekauft

[Delphi.Narium]

Da es mir keine Ruhe lässt, eine Lösung ohne Subselect zu finden, hab' ich mal den ChatBot meiner Wahl befragt, diese Lösung wurde extrem schnell geliefert:

Zitat von Frage:

Ich möchte alle Kunden haben, die den Artikel X gekauft haben und nie den Artikel Y gekauft haben. (Hinweis die Artikel X und Y können in verschiedenen Rechnungen des selben Kunden auftreten). Die Tabellen sehen so aus:

Kunden
------
KdNr, Name, ...

Rechnungen
-----------
KdNr, ReNr, ....

Positionen
----------
ReNr, Artikel, ....

Zitat von Antwort:

Sie können die folgende SQL-Abfrage verwenden, um alle Kunden zu finden, die Artikel X gekauft haben und nie Artikel Y gekauft haben. Bitte ersetzen Sie 'X' und 'Y' durch Ihre tatsächlichen Artikelnamen.

SQL-Code:

SELECT k.*
FROM Kunden k
WHERE EXISTS (
    SELECT 1
    FROM Rechnungen r
    JOIN Positionen p ON r.ReNr = p.ReNr
    WHERE r.KdNr = k.KdNr AND p.Artikel = 'X'
)
AND NOT EXISTS (
    SELECT 1
    FROM Rechnungen r
    JOIN Positionen p ON r.ReNr = p.ReNr
    WHERE r.KdNr = k.KdNr AND p.Artikel = 'Y'
)

Zitat von Fortsetzung der Antwort:

In dieser Abfrage wird zuerst geprüft, ob ein Kunde einen Artikel X gekauft hat ('EXISTS'-Teil), und dann wird sichergestellt, dass der Kunde nie einen Artikel Y gekauft hat ('NOT EXISTS'-Teil).

Aber, wir möchten es doch ohne Subselect, daher eine Nachfrage:

Zitat von Frage:

Gibt es auch eine Lösungsmöglichkeit für diese Fragestellung, die ohne Subselect auskommt?

Zitat von Antwort:

Ja, es ist möglich, eine Lösung ohne Subselects zu erstellen, indem Sie 'JOIN' und 'GROUP BY' verwenden. Hier ist ein Beispiel:

SQL-Code:

SELECT k.KdNr, k.Name
FROM Kunden k
JOIN Rechnungen r ON k.KdNr = r.KdNr
JOIN Positionen p ON r.ReNr = p.ReNr
GROUP BY k.KdNr, k.Name
HAVING SUM(CASE WHEN p.Artikel = 4701 THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN p.Artikel = 5702 THEN 1 ELSE 0 END) = 0

Zitat von Fortsetzung der Antwort:

In dieser Abfrage wird die 'SUM'-Funktion mit einer 'CASE'-Anweisung verwendet, um zu zählen, wie oft jeder Kunde Artikel X und Y gekauft hat. Kunden, die Artikel X mindestens einmal gekauft haben und Artikel Y nie gekauft haben, werden in den Ergebnissen enthalten sein.

Bitte beachten Sie, dass diese Abfrage möglicherweise nicht so effizient ist wie die vorherige mit Subselects, insbesondere wenn Ihre Tabellen sehr groß sind. Es ist immer eine gute Idee, die Leistung zu überwachen und gegebenenfalls Indizes hinzuzufügen.

Es geht also doch

[jaenicke]

sum über case ist aber vermutlich die langsamste Variante, weil dies kaum optimiert werden kann. Meine letzte Variante ist auch kein echtes Subselect, weil die zweite Abfrage mit join kombiniert und nicht in jeder Zeile ausgeführt wird.

Holger hat mit seinem Einwand aber natürlich auch Recht:

Zitat von IBExpert:

ich finde sowas einfacher zu verstehen und modularer, weil auch mit anderen bedingungen einfach zu kombinieren und trotzdem
die unterbedingungen einfach lesbar sind.

[Uwe Raabe]

Zitat von Delphi.Narium:

Aber damit ist die Vermutung widerlegt:

Nicht zwingend.

SQL-Code:

SELECT K.KdNr, K.Name
FROM Kunden K
INNER JOIN Rechnungen R ON K.KdNr = R.KdNr
LEFT JOIN Positionen P1 ON R.ReNr = P1.ReNr AND P1.Artikel = 4701
LEFT JOIN Positionen P2 ON R.ReNr = P2.ReNr AND P2.Artikel = 5702
GROUP BY K.KdNr, K.Name
HAVING COUNT(P1.ReNr) > 0 and COUNT(P2.ReNr) = 0;

Allerdings lassen die nur rudimentär vorhandenen Testdaten eigentlich keine belastbare Aussage zu.

Unschön ist halt, dass man die Kunden-Felder zumindest im GROUP BY einzeln aufführen muss (Ich kenne zumindest kein System, das dort ein K.* akzeptiert). Allerdings ist man dann vielleicht eher gewillt, nur die wirklich benötigten Felder anzugeben. Wäre ja denkbar, dass lediglich die KdNr benötigt wird, dann wird es recht simpel.

[Delphi.Narium]

Zitat von Uwe Raabe:

Zitat von Delphi.Narium:

Aber damit ist die Vermutung widerlegt:

Nicht zwingend.

Deshalb schrieb ich ja später auch

Zitat von Delphi.Narium:

Es geht also doch

Zitat von Uwe Raabe:

SQL-Code:

SELECT K.KdNr, K.Name
FROM Kunden K
INNER JOIN Rechnungen R ON K.KdNr = R.KdNr
LEFT JOIN Positionen P1 ON R.ReNr = P1.ReNr AND P1.Artikel = 4701
LEFT JOIN Positionen P2 ON R.ReNr = P2.ReNr AND P2.Artikel = 5702
GROUP BY K.KdNr, K.Name
HAVING COUNT(P1.ReNr) > 0 and COUNT(P2.ReNr) = 0;

Das ist eine Abwandlung/Weiterentwicklung der von der KI weiter oben gelieferten Antwort oder hat zumindest große Ähnlichkeit mit ihr. Sie hat den Vorteil, dass sie ohne Case und Sum auskommt und sie ist dadurch deutlich eleganter und lesbarer (und vermutlich bei größeren Datenmengen auch schneller).

Zitat von Uwe Raabe:

Allerdings lassen die nur rudimentär vorhandenen Testdaten eigentlich keine belastbare Aussage zu.

Das war für mich auch das Hauptproblem erstmal Testdaten zu erstellen, die wirklich alle möglichen Fälle abdecken, hoffe aber, dass mir das in https://www.delphipraxis.net/1535576-post20.html gelungen ist.

Interessant ist: Wir haben jetzt acht unterschiedliche und funktionierende Lösungen zur Aufgabenstellung.

[Uwe Raabe]

Zitat von Delphi.Narium:

Das ist eine Abwandlung/Weiterentwicklung der von der KI weiter oben gelieferten Antwort oder hat zumindest große Ähnlichkeit mit ihr.

In der Tat. Ich muss den Part deiner Antwort irgendwie überlesen haben, sonst hätte ich mich darauf bezogen. Sorry!

[IBExpert]

so als technische basisinfo noch:

ein sum befehl wird immer die komplette ergebnismenge abklappern, spricht, wenn
du tausend kunden hast die alle je 1000 rechnungen mit dem verkauften produkt hatten,
kommt der server nicht drumherum, alle eine Million datensätze zu analysieren, um die komplett
unwichtige Information der summe zu ermitteln (war ja im threadtitel irrelevant).

Ein exists ist zumindest bei firebird so implementiert, das dieser sobald ein record
in der ergebnismenge gefunden wurde (bei der hoffentlich passende indizes nutzbar sind)
sofort die weiteren teilergebnisse ignoriert, also in dem o.a. fall mit passenden indizes
statt 1000000 operationen das ergebnis mit 1000 operationen ermitteln kann.

Bei single user datenbanken wird das kaum keiner merken, wenn aber dutzende oder hunderte user
solche abfragen in schlechter version parallel machen und so ermittelte unwichtige count
oder sum befehle auf einem Startscreen einer Applikation darstellt, dann wird der server
niemals performant sein, weil der immer nur schwachsinn mit mörderaufwand treibt.

wenn dann noch dazu kommt, das die 1000000 rechnungen und die 1000 kunden nicht über passende
indizes verfügen, reden wir auch intern nicht über 1000000 operationen sondern schnell mal über
1000000000 operationen.

firebird liefert sehr gute hilfsmittel um so was für die eigenen SQLs zu erkennen, aber das
Design der eigenen SQLs muss der programmierer verantworten. Wenn man eine summe brauchen würde
geht es nicht anders, aber aus der summe >= eigentlich nur die existenz auszuwerten ist
um es mal freundlich zu sagen unglücklich programmiert.