Sortieren nach bester Übereinstimmung

[Alexander (HH)]

Moin Moin!

Mal wieder ein Wald-und-Bäume-Problem:

Ich suche in einer Tabelle voller Benutzer-Daten über mehrere Spalten nach mehreren Wortfragmenten, und möchte die Datensätze zuerst zurückbekommen, für die die meisten Übereinstimmungen vorhanden sind. Datenbank ist im Moment PostgreSQL 8.2, soll später aber auch mit anderen Datenbanken funktionieren. PostgreSQLs ILIKE-Operator ist ein case-insensitives LIKE, für andere SQL-Dialekte wird ggf. ein anderer Operator eingesetzt. Der letzte Ausweg wäre, die Datensätze in der Anwendung nochmal zu vergleichen und zu sortieren, aber das erscheint mir wenig elegant.

  
SELECT  
  * -- in Wirklichkeit eine lange Spaltenliste --  
FROM  
  users  
WHERE  
  account ILIKE '%foo%' OR account ILIKE '%bar%' OR account ILIKE '%baz%'  
  OR  
  firstname ILIKE '%foo%' OR firstname ILIKE '%bar%' OR firstname ILIKE '%baz%'  
  OR  
  lastname ILIKE '%foo%' OR lastname ILIKE '%bar%' OR lastname ILIKE '%baz%'  
  OR  
  email ILIKE '%foo%' OR email ILIKE '%bar%' OR email ILIKE '%baz%'  
ORDER BY  
  NUMBER_OF_MATCHES_IN_WHERE() -- HILFE! Die Funktion gibt's nicht. ;-) --  

Alexander

[Tom]

Hello,

wie wäre es denn mit Funktionen, wie Levenshtein() oder Soundex()?

http://de.wikipedia.org/wiki/Levenshtein-Distanz
http://de.wikipedia.org/wiki/Soundex

http://search.postgresql.org/search?u=%2Fdocs%2F8.3%2Finteractive%2F&q=soundex

Liebe Grüße

Tom vom Berg

[Alexander (HH)]

Moin Moin!

wie wäre es denn mit Funktionen, wie Levenshtein() oder Soundex()?

Soundex ist viel zu unscharf und spezifisch für Englisch. Meine Suche wird zwar irgendwann auch mal in einer englischen Umgebung laufen, aber eben auch in Umgebungen, die mit englisch absolut nichts zu tun haben, nicht einmal den gleichen Zeichensatz. Da liefert Soundex in etwa das gleiche Ergebnis wie random().

Levenshtein ist ebenfalls zu unscharf, ich suche eben keine Wechselstabenverbuchsler.

Ich habe (in einer Web-Umgebung auf einem irgendwann aus dem Internet erreichbaren Server) ein einzelnes Textfeld für eine "einfache Suche", in die man mehr oder beliebige Teile von Namen, E-Mail-Adresse, Ort usw. eingeben kann. Sucht jemand dort z.B. nach "alex", soll er alle Leute aufgelistet bekommen, deren Vor- oder Nachname, Wohnort, E-Mail-Adresse, Account-Name die exakte Zeichenfolge "alex" enthält. Platz eins soll Alexander Alexandropolis aus Alexandria mit der E-Mail-Adresse [e-mail] sein, Alexandra Meier aus Flensburg mit [e-mail] wäre dann auf den letzten Plätzen.

Bei einer Suche nach "alexandra meier flensburg" wäre die Flensburgerin dann allerdings sehr wahrscheinlich das Top-Ergebnis.

Alexander

[steckl]

Hi,

Ich kenne (seit ein paar Tagen) die mySql-Funktion MATCH AGAINST. Hierbei werden die Ergebnisse nach Relevanz bewertet.

Ob es sowas auch in anderen DBMSs - wie Postgre - gibt weiß ich jedoch nicht.

mfG,
steckl

[Tom]

Hello,

NUMBER_OF_MATCHES_IN_WHERE() -- HILFE! Die Funktion gibt's nicht. ;-) --

die kannst Du Dir aber vielelicht mit mit Benutzervariablen und einem geschachtelten if() oder iif() basteln.

oder

• Benutzerdefinierte Funktionen?
• Stored Procedure?

Liebe Grüße

Tom vom Berg

[Vinzenz Mai]

Hallo Alexander,

Ich habe (in einer Web-Umgebung auf einem irgendwann aus dem Internet erreichbaren Server) ein einzelnes Textfeld für eine "einfache Suche", in die man mehr oder beliebige Teile von Namen, E-Mail-Adresse, Ort usw. eingeben kann. Sucht jemand dort z.B. nach "alex", soll er alle Leute aufgelistet bekommen, deren Vor- oder Nachname, Wohnort, E-Mail-Adresse, Account-Name die exakte Zeichenfolge "alex" enthält. Platz eins soll Alexander Alexandropolis aus Alexandria mit der E-Mail-Adresse [e-mail] sein, Alexandra Meier aus Flensburg mit [e-mail] wäre dann auf den letzten Plätzen.

Bei einer Suche nach "alexandra meier flensburg" wäre die Flensburgerin dann allerdings sehr wahrscheinlich das Top-Ergebnis.

wie steckl bereits schrieb, ist dies ein hübscher Fall für die Volltextsuche.
Die schlechte Nachricht zuerst: PostgreSQL 8.2 fehlt die Volltextsuche.
Die gute Nachricht: PostgreSQL 8.3 bietet eine Volltextsuche.

Aus diesem Grund würde ich an Deiner Stelle die Voraussetzungen für Dein Projekt von PostgreSQL 8.2 auf PostgreSQL 8.3 anheben.

Freundliche Grüße

Vinzenz

[Alexander (HH)]

Moin Moin!

wie steckl bereits schrieb, ist dies ein hübscher Fall für die Volltextsuche.
Die schlechte Nachricht zuerst: PostgreSQL 8.2 fehlt die Volltextsuche.
Die gute Nachricht: PostgreSQL 8.3 bietet eine Volltextsuche.

Aus diesem Grund würde ich an Deiner Stelle die Voraussetzungen für Dein Projekt von PostgreSQL 8.2 auf PostgreSQL 8.3 anheben.

Ich lese mir gerade die Doku durch, sieht auf den ersten Blick recht passend aus. Dann werde ich wohl demnächst mal 8.3.irgendwas installieren. Zum Glück ist das Projekt noch im Larvenstadium.

Danke,
Alexander

[Ilja]

yo,

du könntest mit group by arbeiten, wo die gleichen "treffer" zusammengefasst werden und du über das COUNT(*) sortierst. das hängt aber auch ein wenig von der ausgabe (projektion) ab.

oder du machst deine abfrage als unterabfrage in die from klausel, von dort kannst du dann eine korrelierte unterabfrage machen, die du in die sortierung einbaust. bin mir nicht ganz sicher, ob die korrelation auch mit unterabfragen in der from klausel geht, einfach ausprobieren.

wenn alles nicht geht, arbeite mit einer temp tabelle.

Ilja