Select mit Annäherungswerten und mehreren Feldern

[Marco Gutenhof]

Hallo,

ich habe ein kleines Problem mit einer SQL Abfrage, ich habe folgende Tabelle:
id | abstand | sichtfeld | vergrößerung | länge

Es wird jetzt davon ausgegangen, dass ein Wert gesucht werden soll, der nicht in der Datenbank erxistiert, stattdessen soll dann der nächst höhere Wert und der nächst kleinere Wert ausgegeben werden.

Bisher habe ich das so gelöst:
SELECT TOP 1 * FROM db WHERE abstand >= #SUCHWERT# ORDER BY abstand
UND
SELECT TOP 1 * FROM db WHERE abstand <= #SUCHWERT# ORDER BY abstand DESC

Soweit klappt alles noch ganz gut, doch jetzt möchte ich genau das Gleiche mit einen weiteren Feld kombinieren und weiß nicht genau wie ich das anstellen soll. Eigentlich möchte ich das am liebsten mit allen 4 Feldern abstand | sichtfeld | vergrößerung | länge kombinieren können.

Also etwa so (Bsp. Für 2 Felder):
SELECT TOP 1 * FROM db WHERE abstand >= #SUCHWERT# AND sichtfeld >= #SUCHWERT2# OR sichtfeld <= #SUCHWERT2# ORDER BY abstand, sichtfeld
UND
SELECT TOP 1 * FROM db WHERE abstand <= #SUCHWERT# AND sichtfeld >= #SUCHWERT2# OR sichtfeld <= #SUCHWERT2# ORDER BY abstand, sichtfeld

 

[vop]

Vielleicht verstehe ich den Ansatz nicht ganz richtig
Wenn du beispielsweise diese Werte in der Tabelle hast

ZEILE id abstand sichtfeld vergrößerung Länge
1 1 4 3 12 123
2 3 6 7 23 777
3 5 8 9 34 444
4 7 2 12 45 333
5 9 10 55 65 255

wonach soll gesucht werden und welches sind die Datensätze vorher und hinterher?
Das ist doch bei jeder Spalte eine andere Zeile?
Bsp Suche ich in Spalte id nach dem Wert 6, so würde ich die Zeilen 3 (davor) und 4 (danch) erhalten und für die Spalte sichtfeld bei einer Suche nach dem Wert 6 die Zeilen 1 (davor) und 2 (danach)

Kannst Du vielleicht den konkreten Fall wofür du das einsetzen möchtest mitteilen, dann verstehe ich vielleicht eher worum es geht
vop

 

[Marco Gutenhof]

id | abstand | sichtfeld | vergrößerung | länge
1 10 100 1000 10000
2 20 200 2000 20000
3 30 300 3000 30000
4 40 400 4000 40000
5 50 500 5000 50000

Angenohmen ich suche jetzt nach Abstand = 15 (oder 11..19), dann soll die Zeile mit ID 1 und ID 2 ausgegeben werden.

Und genau dies soll dann mit anderen Feldern noch kombiniert werden können:
Abstand = 15 und sichtfeld = 250

Ausgabe:
ID 1, 2 und 3

ID 1 und 2, da Abstand gleich 15 ist und so am nächsten zu 10 und 20.
ID 2 und 3, da Sichtfeld gleich 250 ist und so am nächsten zu 200 und 300.


Extra: Da fällt mir gerade noch eine zusätzliche Aufgabe zu diesem Beispiel ein:
Es wird nur genau die Schnittstelle ausgegeben, d.h. ID 2 wird zweimal gefunden, also ist dies das einzige Ergebnis.
WIchtiger ist aber erst einmal das Obere.

 

[vop]

Okay, jetzt weiß ich zumindest was du damit meinst , in mehreren Spalten zu schauen.

War das übrigens auch nur eine Beispieltabelle von Dir oder sind die Werte in den Spalten tatsächliche Werte?

Anders gefragt:
Sind die Werte stets so angeordnet, dass mit aufsteigender ID auch alle Spaltenwerte
aufsteigen?
Also wenn in Eintrag mit id = 1 in Spalte xy ein Wert steht, dann ist der Eintrag in der nächsten Zeile (bsp. id=2) in Spalte xy stets größer oder gleich dem Wert aus der vorherigen Zeile?

Wenn dem so ist, kann man sich dieses Wissen evtl. zu Nutze machen.

Andere Frage: Haben die Werte in den Zeilen (wie im Beispiel) stets die gleiche Differenz?
Auch das könnte man sich zu Nutze machen. Wenn also stets Hunderterschritte im Abstand gelten könntest du suchen nach
ABSTAND BETWEEN Wert-100 AND Wert+100

Kommen wir einer Lösung näher?

 

[ManicMarble]

Hallo
Wenn ich das richtig verstehe, dann suchst Du den einen Datensatz, der in allen 4 Werten am nächsten an bestimmten "Wunschwerten" dran ist.
Mein erster Lösungsansatz gienge in die Richtung, eine Art Abweichung vom Ideal zu berechnen, danach zu sortieren und den ersten Satz auszugeben.

In MySQL würde ich das so versuchen:

SELECT
 *
FROM
 testtbl
ORDER BY
 (ABS(#wunsch_a# - abstand) + ABS(#wunsch_s# - sichtfeld) + ABS(#wunsch_v# - vergoesserung) + ABS(#wunsch_l# - laenge))
LIMIT 1

Geht das in die richtige Richtung?
Da in Deinem Beispiel die Größenordnungen der einzelnen Werte sehr unterschiedlich sind (einstellige Werte bei Abstand, Tausender-Werte bei Vergrößerung), müsste man in der Abweichungs-Berechnung noch einen Gewichtungs-Faktor einbauen. Mit diesem Faktor könnte man dann auch nett "spielen" um bestimmten Werten eine höhere Priorität zu geben.

Grüße,
Martin

 

[Marco Gutenhof]

Also die Werte in der Tabelle sind vollkommen unterschiedlich und unbekannt , ohne irgendwelche Regelmäßigkeiten. Ich hab die jetzt nur so gestellt, damit man leichter die Spalten unterscheiden kann...

Aber ich verstehe den Query nicht so genau, im Prinzip kommt da doch später raus:

SELECT
 *
FROM
 testtbl
ORDER BY
 (ABS(#wunsch_a# - abstand) + ABS(#wunsch_s# - sichtfeld) + ABS(#wunsch_v# - vergoesserung) + ABS(#wunsch_l# - laenge))
LIMIT 1

// ist gleich:
SELECT * FROM db ORDER BY #irgend_eine_zahl# LIMIT 1

Was soll das denn bewirken, du muss bei ORDER BY doch irgend ein Feld angeben?

 

[ManicMarble]

Natürlich muss in ORDER BY ein Feld stehen. Es kann aber auch eine Formel da stehen, die mit Werten aus einem oder mehreren Feldern Berechnungen anstellt.

In dem SELECT kommen sogar 4 Felder im ORDER BY vor: "abstand", "sichtfeld", "vergroesserung" und "laenge", genau wie in dem von Dir vorgegebenen Beispiel. Also nicht "ORDER BY #irgend_eine_zahl#".

Die Formel berechnet die Summe der Differenzen zwischen dem vorgegebenen #Wunschwert# und dem Datenbank-Feldinhalt. Je kleiner die Differenz, je genauer ist der Treffer.

Der Einfachheit halber erst mal ein Beispiel mit nur einem Wert:

Eine Tabelle enthält im Feld 'a' die Werte 10, 20, 30, 40, und 50.
Gesucht sei der Satz, der am nächsten an 28 dran ist.

SELECT * FROM db ORDER BY ABS(28 - a)

Formelergebnis für 10: 18
Formelergebnis für 20: 8
Formelergebnis für 30: 2
Formelergebnis für 40: 12
Formelergebnis für 50: 22

Bei Sortierung nach dem Formelergebnis landet der Satz mit dem Wert 30 also ganz oben.

Da Du jetzt 4 Wunschwerte für 4 unterschiedliche Felder hast, musst Du die Differenzen addieren.

Und um das Ganze noch komplizierter zu machen: Da die Werte in Deinem Beispiel in keinem vergleichbaren Verhältnis zueinander stehen (Abstand 1 bis 5, Sichtfeld 10 bis 50, Vergrößerung 100 bis 500, Länge 1000 bis 5000), solltest Du die Werte mit einem Faktor gewichten, sonst passt das mit der Summe nicht. Eine Differenz von beispielweise 100 wäre beim Abstand eine gigantische Abweichung, bei der Länge aber nur ein Zehntel eines Schrittes.
So ergäbe sich für Dein Beispiel folgendes Formel-Monster für ORDER BY:

... ORDER BY (ABS(#a# - a) * 1000) + (ABS(#s# - s) * 100) + (ABS(#v# - v) * 10) + ABS(#l# - l)

Ich haben die Feldnamen abgekürzt: a für Abstand, s für Sichtfeld, v für Vergrößerung und l für Länge. Die Vergleichswerte (Wunsch-Werte) heißen dementsprechend #a#, #s#, #v# und #l#.

Falls ich hier in eine völlig falsche Richtung denke, möge mich bitte jemand korrigieren.

Viele Grüße,
Martin

 

[Marco Gutenhof]

Das ist cool, das klappt ja viel besser als ich mir das vorgestellt habe! Die Technik mit den ORDER BY war mir völlig umbekannt, ich werde jetzt noch ein paar Testproben machen, aber ich denke mal soweit klappt das alles.
Dickes danke an euch, ihr habt mir super geholfen.