12
MSSQL Prozentuale Übereinstimmung zweier Datensätze
Guten Morgen und frohes neues Jahr!
Ich habe eine MSSQL 2019 Datenbank und möchte eine Abfrage schreiben, in der ich die prozentuale Übereinstimmung von Artikelnummern zwischen zwei Angeboten bekommen.
Tabelle:
Angebotsnr (int), Artikelnr (varchar)
Beispiel
1, A500
1, A501
2, A60
2, A322
2, A333
3, A100
3, A501
Wenn ich jetzt das Angebot 1 nehme soll er gegen Angebote 2 und 3 prüfen und mit sagen, dass Angebot 2 keine (0% ) Übereinstimmungen hat und Angebot 3 hat 50% Übereinstimmung (fett markierte Artikel sind Übereinstimmungen).
Lässt sich dies über eine einzelne Abfrage machen und wenn ja, wie funktioniert das?
Viele Grüße,
Thomas
Ich habe eine MSSQL 2019 Datenbank und möchte eine Abfrage schreiben, in der ich die prozentuale Übereinstimmung von Artikelnummern zwischen zwei Angeboten bekommen.
Tabelle:
Angebotsnr (int), Artikelnr (varchar)
Beispiel
1, A500
1, A501
2, A60
2, A322
2, A333
3, A100
3, A501
Wenn ich jetzt das Angebot 1 nehme soll er gegen Angebote 2 und 3 prüfen und mit sagen, dass Angebot 2 keine (0% ) Übereinstimmungen hat und Angebot 3 hat 50% Übereinstimmung (fett markierte Artikel sind Übereinstimmungen).
Lässt sich dies über eine einzelne Abfrage machen und wenn ja, wie funktioniert das?
Viele Grüße,
Thomas
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 33624833716
Url: https://administrator.de/contentid/33624833716
Printed on: April 27, 2024 at 10:04 o'clock
12 Comments
Latest comment
Moin. Und auch gutes Neues .
Du kannst die Anzahl der Datensätze mit demselben. Wert ausgeben - COUNT
Das Ergebnis wäre in deinem Beispiel dann 1, bzw 2...
Der Sinn, das in % darzustellen erschließt sich mir gerade nicht, aber das kannst du ja mit dem Ergebnis von COUNT berechnen ...
Du kannst die Anzahl der Datensätze mit demselben. Wert ausgeben - COUNT
Das Ergebnis wäre in deinem Beispiel dann 1, bzw 2...
Der Sinn, das in % darzustellen erschließt sich mir gerade nicht, aber das kannst du ja mit dem Ergebnis von COUNT berechnen ...
WITH tabelle(Angebotsnr,Artikelnr) AS (
SELECT 1,'A500' UNION ALL
SELECT 1,'A501' UNION ALL
SELECT 2,'A60' UNION ALL
SELECT 2,'A322' UNION ALL
SELECT 2,'A333' UNION ALL
SELECT 3,'A100' UNION ALL
SELECT 3,'A501'
)
SELECT tabelle.Angebotsnr,
t0.Angebotsnr2,
convert(FLOAT,100) / count(*) * t0.uebereinstimmungen AS uebereinstimmungen_prozent
FROM tabelle
INNER JOIN (
SELECT t1.Angebotsnr AS Angebotsnr1,
t2.Angebotsnr AS Angebotsnr2,
sum(CASE WHEN t1.Artikelnr = t2.Artikelnr THEN 1 ELSE 0 END) AS uebereinstimmungen
FROM tabelle t1
CROSS JOIN tabelle t2
WHERE t1.Angebotsnr != t2.Angebotsnr
GROUP BY t1.Angebotsnr,t2.Angebotsnr
) t0
ON tabelle.Angebotsnr = t0.Angebotsnr1
WHERE tabelle.Angebotsnr = 1
GROUP BY tabelle.Angebotsnr,t0.Angebotsnr2,t0.uebereinstimmungenWenn du auf die Ausgabe von 0 % Übereinstimmung zu allen anderen Angeboten verzichtest, könnte man es mit INNER JOIN lösen:
WITH tabelle(Angebotsnr,Artikelnr) AS (
SELECT 1,'A500' UNION ALL
SELECT 1,'A501' UNION ALL
SELECT 2,'A60' UNION ALL
SELECT 2,'A322' UNION ALL
SELECT 2,'A333' UNION ALL
SELECT 3,'A100' UNION ALL
SELECT 3,'A501'
)
SELECT tabelle.Angebotsnr,
t0.Angebotsnr2,
convert(FLOAT,100) / count(*) * t0.uebereinstimmungen AS uebereinstimmungen_prozent
FROM tabelle
INNER JOIN (
SELECT t1.Angebotsnr AS Angebotsnr1,
t2.Angebotsnr AS Angebotsnr2,
count(*) AS uebereinstimmungen
FROM tabelle t1
INNER JOIN tabelle t2
ON t1.Artikelnr = t2.Artikelnr
AND t1.Angebotsnr != t2.Angebotsnr
GROUP BY t1.Angebotsnr,t2.Angebotsnr
) t0
ON tabelle.Angebotsnr = t0.Angebotsnr1
WHERE tabelle.Angebotsnr = 1
GROUP BY tabelle.Angebotsnr,t0.Angebotsnr2,t0.uebereinstimmungen
@ukulele-7 :
Davon ausgehend, das es deutlich mehr Angebots-Nummern gibt als im Beispiel genannt:
Meinst du da nicht auch, das die Nennung sämtlicher möglicher Nummern in der Abfrage zu aufwändig wird? 🤔
... mal abgesehen davon, das der Code mit jeder neuen Angebotsnummer angepasst werden müsste ...
Davon ausgehend, das es deutlich mehr Angebots-Nummern gibt als im Beispiel genannt:
Meinst du da nicht auch, das die Nennung sämtlicher möglicher Nummern in der Abfrage zu aufwändig wird? 🤔
... mal abgesehen davon, das der Code mit jeder neuen Angebotsnummer angepasst werden müsste ...
Zitat von @MirkoKR:
Davon ausgehend, das es deutlich mehr Angebots-Nummern gibt als im Beispiel genannt:
Meinst du da nicht auch, das die Nennung sämtlicher möglicher Nummern in der Abfrage zu aufwändig wird? 🤔
Das habe ich ja geschrieben, wird eventuell zum Problem. Die Anforderung war aber explizit das auch 0% Übereinstimmung zu Angebot 2 ausgegeben wird. Daher habe ich das im ersten Code so umgesetzt und SQL wird nicht so schnell schlapp machen wie er beim lesen von 0 % Einträgen Davon ausgehend, das es deutlich mehr Angebots-Nummern gibt als im Beispiel genannt:
Meinst du da nicht auch, das die Nennung sämtlicher möglicher Nummern in der Abfrage zu aufwändig wird? 🤔
Er kann die Ergebnismenge ja auch weiter Einschränken in dem er auf andere Eigenschaften einschränkt (z.B. selbes Jahr des Auftrags oder eine maximale datediff() zwischen den Aufträgen. Dazu muss nur die WHERE-Bedingung im CROSS JOIN erweitert werden.
... mal abgesehen davon, das der Code mit jeder neuen Angebotsnummer angepasst werden müsste ...
Nein da muss nichts angepasst werden. Die Ausgangs-Angebotsnr steht genau ein mal im Code, in der WHERE-Bedingung Zeile 26/27, die kann man auch komplett raus nehmen oder eben über Variablen füllen.
Naja, wenn COUNT = 1 zurück gibt, gibt es keine Wiederholung, also 0% 🤔
Hi,
die 0 Ergebnisse benötige ich in der Tat nicht und die zweite Variante funktioniert bestens!
Vielen Dank!
€: Habe nicht gesehen, dass ihr noch mehr geschrieben habt. Unsere Tabelle hat über 180000 Einträge und das Ergebnis kommt sofort. Läuft also sehr performant (nur Variante 2 getestet).
die 0 Ergebnisse benötige ich in der Tat nicht und die zweite Variante funktioniert bestens!
Vielen Dank!
€: Habe nicht gesehen, dass ihr noch mehr geschrieben habt. Unsere Tabelle hat über 180000 Einträge und das Ergebnis kommt sofort. Läuft also sehr performant (nur Variante 2 getestet).
Dann teste mal Variante 1 (vielleicht nach Feierabend? ), nur rein aus Interesse. Liefert dir natürlich auch 180.000 Treffer (einen pro Angebotsnr), egal ob 0 oder größer. Und der CROSS JOIN macht ein Kreuzprodukt aus 180.000 ^ 180.000 Datensätzen...
Was für eine Wiederholung? Das sind Joins und Gruppierungen bzw. Aggregate, nichts mit Rekursion oder Schleifen oder so.
), nur rein aus Interesse. Liefert dir natürlich auch 180.000 Treffer (einen pro Angebotsnr), egal ob 0 oder größer. Und der CROSS JOIN macht ein Kreuzprodukt aus 180.000 ^ 180.000 Datensätzen...Was für eine Wiederholung? Das sind Joins und Gruppierungen bzw. Aggregate, nichts mit Rekursion oder Schleifen oder so.
Was für eine Wiederholung? Das sind Joins und Gruppierungen bzw. Aggregate, nichts mit Rekursion oder Schleifen oder so.
Nun, TO will ja am Ende wissen, welche Auftragsnummer mehrfach - also wiederholt - vorkommt ...
Das Ergebnis aus der COUNT-Abfrage liefert ja tatsächlich erstmal soviele Treffer wie unterschiedliche Auftragsnummern ...
Wobei er auch schreibt, das 0-Treffer (keine Wiederholungen) nicht benötigt werden ...
Er kann also entweder
COUNT(...)-1 und auf > 0
oder
COUNT(...) > 1
FILTERN
... aber rein aus Interesse:
warum wurden Auftragsnummern überhaupt mehrfach vergeben? 🤔
Das kann man schon auf Datenbank-Ebene unterbinden ...
warum wurden Auftragsnummern überhaupt mehrfach vergeben? 🤔
Das kann man schon auf Datenbank-Ebene unterbinden ...
Nein der TO möchte prüfen, wie viele Artikelnr pro Angebotsnr identisch sind und das dann in Prozent umrechnen. In den Beispieldaten wählt er Angebot 1 und vergleicht es mit Angebot 2 und 3. Angebot 2 hat keine Artikelnr aus Angebot 1 (daher 0%), Angebot 3 hat eine Artikelnr aus Angebot 1, die zweite fehlt (daher 50%). Das Angebot 2 mit 0% nicht in der Ausgabe stehen soll war ja erst später klar.
Der zweite Code hat einen "inneren" Join, der erst alle identischen Artikelnr-Paare mit unterschiedlichen Angebotsnr findet. Im Äußeren Select wird dann der Anteil der Paare an der Gesamtanzahl errechnet.
Es gibt noch einen Haken der die Performance bremsen kann, daher würde ich die WHERE Condition noch in den inneren Select übernehmen, kann aber sein das der Optimizer das sowieso macht.
Der zweite Code hat einen "inneren" Join, der erst alle identischen Artikelnr-Paare mit unterschiedlichen Angebotsnr findet. Im Äußeren Select wird dann der Anteil der Paare an der Gesamtanzahl errechnet.
Es gibt noch einen Haken der die Performance bremsen kann, daher würde ich die WHERE Condition noch in den inneren Select übernehmen, kann aber sein das der Optimizer das sowieso macht.
WITH tabelle(Angebotsnr,Artikelnr) AS (
SELECT 1,'A500' UNION ALL
SELECT 1,'A501' UNION ALL
SELECT 2,'A60' UNION ALL
SELECT 2,'A322' UNION ALL
SELECT 2,'A333' UNION ALL
SELECT 3,'A100' UNION ALL
SELECT 3,'A501'
)
SELECT tabelle.Angebotsnr,
t0.Angebotsnr2,
convert(FLOAT,100) / count(*) * t0.uebereinstimmungen AS uebereinstimmungen_prozent
FROM tabelle
INNER JOIN (
SELECT t1.Angebotsnr AS Angebotsnr1,
t2.Angebotsnr AS Angebotsnr2,
count(*) AS uebereinstimmungen
FROM tabelle t1
INNER JOIN tabelle t2
ON t1.Artikelnr = t2.Artikelnr
AND t1.Angebotsnr != t2.Angebotsnr
WHERE t1.Angebotsnr = 1
GROUP BY t1.Angebotsnr,t2.Angebotsnr
) t0
ON tabelle.Angebotsnr = t0.Angebotsnr1
GROUP BY tabelle.Angebotsnr,t0.Angebotsnr2,t0.uebereinstimmungen
OK, da habe ich dann was falsch interpretiert ... 
Ich habe lange überlegt und will dann doch noch meine fünf Pfennige in den Ring werfen:
Es wurde bereits darauf hingewiesen, dass eine kreuzweise Verknüpfung einer Tabelle eine grundstätzlich quadratische Ergebniswirkung nach sich zieht. Einmal mehr zeigt sich dadurch, dass die geschickte Gestaltung von Abfragen etwas mit Mathematik und den Logik-Gesetzen für Vergleichsoperationen zu tun hat.
Im vorliegenden Fall mögen 180.000 Tabellenzeilen und das Quadrat von 180.000 zunächst als nicht besonders tragisch erscheinen. Jedoch möge man sich vor Augen führen, dass das (ungefilterte) quadratische Abfrage(zwischen)ergebnis des JOIN-Konstrukts bereits dann, wenn nur eine einzige Tabellenzeile hinzukommt, um sage und schreibe 2n + 1 = 360.001 Zeilen erhöht wird, was schon erheblich ist, weil diese erhöhte Zeilenzahl dann von der DB-Engine während des Abfragelaufs weiterzuverarbeiten ist. Jeder mag sich an den Graphen der Parabel der Quadratfunktion erinnern, wie sich das bei geringfügiger Erhöhung von x für y weiter fortsetzen wird.
Und wie sieht das praktisch in einem halben Jahr aus? Bleibt das bei den 180.000 Tabellenzeilen? Wohl eher nicht! Es müssen allso ganz andere Zeilenwerte unterstellt werden; jedenfalls ist es nicht ausschließbar.
Das in der bisherigen Abfragegestaltung die Verknüpfung einer Tabellenzeile mit sich selbst ausgeschlossen wird, ändert nichts am fortbestehenden Quadratproblem!
Dann kommt noch ein Logik-Aspekt hinzu. Was macht es für einen Sinn, zwei Angebote hinsichtlich ihres Deckungsgrades in beide Richtungen betrachten zu wollen? Eigentlich keinen. Zumeist werde ich doch "nur" wissen wollen, wieviel von dem alten Angebot noch im neuen Angebot enthalten ist. Eine Rückwärtsbetrachtung in die andere Richtung dürfte in der Regel keine Rolle spielen.
Vor diesem Hintergrund erscheint es mir geboten, die JOIN-Bedingung von
nach
zu verändern. Dadurch reduziert sich das mathematische Problem der Anzahl der Abfragezeilen der Kreuzverknüpfung, die weiterzuverarbeiten sind, von n^2 zu n(n+1)/2 ~ (n^2)/2. Also eine Halbierung der Ergebnismenge des JOIN-Konstrukts und somit voraussichtlich eine tendenzielle Verdopplung der Abfrageleistung. Bei steigenden Datenmengen dürfte dieser Einsparungseffekt deutlich spürbar und sogar essentiell werden.
Aber selbst dann, wenn es im vorliegenden Fall auf solche Betrachtungen rein praktisch nicht weiter ankommen sollte, so gehört es für mich dennoch bei der Erstellung von Abfragen jederzeit dazu, diese immer fokussiert und performant zu halten. Denn dann muss man sich später nicht mit einer zeitaufwendigen Optimierungssuche herumplagen. Gerade solche logischen Abfrage"fehler" sind nur sehr schwer zu finden im Vergleich zur erstmaligen Erstellung der Abfrage. Überdies schärfen solche ständig geübten Überlegungen das Problembewusstsein und werden letztlich zur Routine ...
Viele Grüße
HansDampf06
PS: Freilich ergibt sich aus der zweiten JOIN-Bedingung eine eigenständige Ergebnisbegrenzung, was aber das von mir diskutierte Problem dennoch unberührt lässt. Lediglich praktisch mag es durch die zweite JOIN-Bedingung etwas entschäft werden. Der Leistungsoptimierung dient meine vorgeschlagene Abwandlung gleichwohl und allemal.
Es wurde bereits darauf hingewiesen, dass eine kreuzweise Verknüpfung einer Tabelle eine grundstätzlich quadratische Ergebniswirkung nach sich zieht. Einmal mehr zeigt sich dadurch, dass die geschickte Gestaltung von Abfragen etwas mit Mathematik und den Logik-Gesetzen für Vergleichsoperationen zu tun hat.
Im vorliegenden Fall mögen 180.000 Tabellenzeilen und das Quadrat von 180.000 zunächst als nicht besonders tragisch erscheinen. Jedoch möge man sich vor Augen führen, dass das (ungefilterte) quadratische Abfrage(zwischen)ergebnis des JOIN-Konstrukts bereits dann, wenn nur eine einzige Tabellenzeile hinzukommt, um sage und schreibe 2n + 1 = 360.001 Zeilen erhöht wird, was schon erheblich ist, weil diese erhöhte Zeilenzahl dann von der DB-Engine während des Abfragelaufs weiterzuverarbeiten ist. Jeder mag sich an den Graphen der Parabel der Quadratfunktion erinnern, wie sich das bei geringfügiger Erhöhung von x für y weiter fortsetzen wird.
Und wie sieht das praktisch in einem halben Jahr aus? Bleibt das bei den 180.000 Tabellenzeilen? Wohl eher nicht! Es müssen allso ganz andere Zeilenwerte unterstellt werden; jedenfalls ist es nicht ausschließbar.
Das in der bisherigen Abfragegestaltung die Verknüpfung einer Tabellenzeile mit sich selbst ausgeschlossen wird, ändert nichts am fortbestehenden Quadratproblem!
Dann kommt noch ein Logik-Aspekt hinzu. Was macht es für einen Sinn, zwei Angebote hinsichtlich ihres Deckungsgrades in beide Richtungen betrachten zu wollen? Eigentlich keinen. Zumeist werde ich doch "nur" wissen wollen, wieviel von dem alten Angebot noch im neuen Angebot enthalten ist. Eine Rückwärtsbetrachtung in die andere Richtung dürfte in der Regel keine Rolle spielen.
Vor diesem Hintergrund erscheint es mir geboten, die JOIN-Bedingung von
AND t1.Angebotsnr != t2.AngebotsnrAND t1.Angebotsnr < t2.AngebotsnrAber selbst dann, wenn es im vorliegenden Fall auf solche Betrachtungen rein praktisch nicht weiter ankommen sollte, so gehört es für mich dennoch bei der Erstellung von Abfragen jederzeit dazu, diese immer fokussiert und performant zu halten. Denn dann muss man sich später nicht mit einer zeitaufwendigen Optimierungssuche herumplagen. Gerade solche logischen Abfrage"fehler" sind nur sehr schwer zu finden im Vergleich zur erstmaligen Erstellung der Abfrage. Überdies schärfen solche ständig geübten Überlegungen das Problembewusstsein und werden letztlich zur Routine ...
Viele Grüße
HansDampf06
PS: Freilich ergibt sich aus der zweiten JOIN-Bedingung eine eigenständige Ergebnisbegrenzung, was aber das von mir diskutierte Problem dennoch unberührt lässt. Lediglich praktisch mag es durch die zweite JOIN-Bedingung etwas entschäft werden. Der Leistungsoptimierung dient meine vorgeschlagene Abwandlung gleichwohl und allemal.