PostgreSQL-Volltextsuche, Rangfolge nach Position

Kürzlich bin ich auf ein Problem mit der Volltextsuche gestoßen. Ich verwende diese Funktion in meiner Sucheingabe, wobei das Backend während der Eingabe Hinweise auf mögliche Übereinstimmungen sendet. Die Backend-Datenbank ist PostgreSQL. Ich brauchte eine Rangfolge der Hinweise nach der Position des gesuchten Begriffs im Text.

Wenn Sie also nach dem Titel „Star Wars“ suchen, erhalten Sie zuerst den Beitrag „Star Wars“ statt „Wie Star Wars 7–9 die Welt von Star Wars veränderte (eine unterhaltsame Dokumentation über Star Wars)“, was möglicherweise der Fall war höherer Rang, da der Begriff dreimal vorkommt.

Volltextsuche in PostgreSQL

Eine Volltextsuche in PostgreSQL ist ganz einfach möglich. Es gibt zwei Hauptwerkzeuge, die verwendet werden können:

• tsvector – stellt ein durchsuchbares Dokument dar.
• tsquery – stellt die Suchabfrage dar, die für ein Dokument ausgeführt werden soll.

Angenommen, wir möchten nach den Titeln unserer Blogbeiträge suchen. Um sie durchsuchbar zu machen, können wir die folgende Abfrage verwenden:

SELECT 
id, 
title 
FROM blogposts
WHERE to_tsquery('JavaScript') @@ to_tsvector(posts.title);

In diesem Fall konvertieren wir die Beitragstitel bei jeder Suche dynamisch in einen TS-Vektor. Allerdings dauert diese Transformation einige Zeit. Ein besserer Ansatz besteht darin, diese Transformation vorab in der Datenbank durchzuführen und sie zur schnelleren Suche auch als Index für die Titel zu speichern.

Lasst uns eine neue Spalte mit Titelvektoren erstellen und auch diese neue Spalte indizieren:

ALTER TABLE blogposts ADD COLUMN search_vector tsvector;
UPDATE blogposts SET search_vector = (to_tsvector(posts.title));
CREATE INDEX titles_fts_idx ON blogposts USING gin(search_vector);

Versuchen Sie nun, nach dem Begriff „JavaScript“ zu suchen

SELECT 
id, 
title
FROM blogposts
WHERE to_tsquery('JavaScript') @@ search_vector;

Sie können auch Indizes aus ts-Vektoren direkt in der Titelspalte erstellen, wie folgt:

CREATE INDEX titles_fts_idx ON blogposts USING GIN (to_tsvector(posts.title));

und verwenden Sie die Suche wie folgt:

SELECT 
id, 
title
FROM blogposts
WHERE to_tsquery('JavaScript') @@ posts.title;

Jetzt wird die Volltextsuche rasend schnell sein und in Millisekunden abgeschlossen sein.

Rangfolge der Ergebnisse

PostgreSQL bietet die ts_rank-Funktion, mit der Sie Suchergebnisse bewerten und sie basierend auf ihrem Ranking ordnen können. PostgreSQL unterstützt die folgenden Ranking-Optionen:

• 0 (Standard) ignoriert die Dokumentlänge
• 1 teilt den Rang durch 1, den Logarithmus der Dokumentlänge
• 2 dividiert den Rang durch die Dokumentlänge
• 4 dividiert den Rang durch den mittleren harmonischen Abstand zwischen Extents (dies wird nur von ts_rank_cd implementiert)
• 8 dividiert den Rang durch die Anzahl der eindeutigen Wörter im Dokument
• 16 teilt den Rang durch 1, den Logarithmus der Anzahl eindeutiger Wörter im Dokument
• 32 teilt den Rang durch sich selbst 1

Sie können den ts_rank wie folgt verwenden:

SELECT
    ...
ts_rank(search_vector, to_tsquery('JavaScript'), 0) as rank_title
    ...
ORDER BY rank_title DESC NULLS LAST

Es gibt jedoch keine integrierte Ranking-Option basierend auf der Position des Suchbegriffs innerhalb der Zeichenfolge (d. h. Titelspalte).

POSITION zur Rettung

Glücklicherweise gibt es in PostgreSQL die POSITION-Funktion. Die PostgreSQL-Funktion POSITION wird verwendet, um die Position eines Teilstrings innerhalb eines bestimmten Strings zu finden. In unserem Fall können wir es so verwenden

SELECT 
id, 
title 
FROM blogposts
WHERE to_tsquery('JavaScript') @@ to_tsvector(posts.title);

ts_rank verwendet die Normalisierungszahl 2, da 2 den Rang durch die Dokumentlänge teilt
Die magische Zahl 0,0001 besteht darin, eine Division durch 0 zu vermeiden, da die POSTION-Funktion von 1 und nicht von 0 an zählt und 0 zurückgibt, wenn die Zeichenfolge nicht gefunden wird.

Der endgültige Code könnte so aussehen:

ALTER TABLE blogposts ADD COLUMN search_vector tsvector;
UPDATE blogposts SET search_vector = (to_tsvector(posts.title));
CREATE INDEX titles_fts_idx ON blogposts USING gin(search_vector);

Suche nach weiteren Begriffen

Eine Einschränkung muss erwähnt werden, wenn Sie nach mehreren Begriffen gleichzeitig suchen (wie JavaScript und TypeScript).

Die Argumente für die to_tsquery-Funktion können mit großer Flexibilität verwendet werden, einschließlich logischer Operatoren usw. Die POSITION-Funktion hingegen ist „nur“ ein Teilstring in string.

Beispiel aus der realen Welt

Hier ist mein Beispiel eines realen Endpunkts in der SvelteKit-Webanwendung, die die NPM-Bibliothek von Postgres (SQL) verwendet:

SELECT 
id, 
title
FROM blogposts
WHERE to_tsquery('JavaScript') @@ search_vector;

Hier sind die Links zur jeweiligen Dokumentation:

• https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-DOCUMENTS
• https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING
• https://www.postgresql.org/docs/9.1/functions-string.html

Das obige ist der detaillierte Inhalt vonPostgreSQL-Volltextsuche, Rangfolge nach Position. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!