Kedudukan Carian Teks Penuh PostgreSQL mengikut Kedudukan

Baru-baru ini, saya menghadapi masalah dengan carian teks penuh. Saya menggunakan ciri ini dalam input carian saya, di mana bahagian belakang menghantar petunjuk kemungkinan padanan semasa anda menaip. Pangkalan data bahagian belakang ialah PostgreSQL. Saya memerlukan pembayang untuk disenaraikan mengikut kedudukan istilah yang dicari dalam teks.

Jadi, jika anda mencari tajuk "Star Wars", anda akan mendapat siaran "Star Wars" terlebih dahulu dan bukannya "Bagaimana Star Wars 7- 9 mengubah dunia Star Wars (dokumentari yang menyeronokkan tentang Star Wars)" yang mungkin mempunyai kedudukan yang lebih tinggi kerana istilah itu ada 3 kali ganda.

Carian Teks Penuh dalam PostgreSQL

Carian teks penuh dalam PostgreSQL boleh dicapai dengan mudah. Terdapat dua alatan utama untuk digunakan:

• tsvector - mewakili dokumen yang boleh dicari.
• tsquery - mewakili pertanyaan carian untuk dilakukan terhadap dokumen.

Katakan kita mahu mencari tajuk catatan blog kita. Untuk menjadikannya boleh dicari, kita boleh menggunakan pertanyaan berikut:

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

Dalam kes ini, kami menukar tajuk siaran kepada tsvector secara dinamik dengan setiap carian. Walau bagaimanapun, transformasi ini mengambil sedikit masa. Pendekatan yang lebih baik ialah melakukan transformasi ini terlebih dahulu dalam pangkalan data dan juga menyimpannya sebagai indeks untuk tajuk untuk carian yang lebih pantas.

Mari buat lajur baharu bagi vektor tajuk dan juga indeks lajur baharu ini:

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);

Sekarang cuba cari istilah "JavaScript"

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

Anda juga boleh membuat indeks daripada vektor ts terus pada lajur tajuk seperti ini:

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

dan gunakan carian seperti ini:

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

Kini, carian teks penuh akan menjadi sangat pantas, selesai dalam milisaat.

Kedudukan Keputusan

PostgreSQL menyediakan ciri ts_rank, yang membolehkan anda menjaringkan hasil carian dan menyusunnya berdasarkan kedudukan mereka. PostgreSQL menyokong pilihan kedudukan berikut:

• 0 (lalai) mengabaikan panjang dokumen
• 1 membahagikan pangkat dengan 1 logaritma panjang dokumen
• 2 membahagikan pangkat dengan panjang dokumen
• 4 membahagikan pangkat dengan jarak harmonik min antara takat (ini hanya dilaksanakan oleh ts_rank_cd)
• 8 membahagikan pangkat dengan bilangan perkataan unik dalam dokumen
• 16 membahagikan pangkat dengan 1 logaritma bilangan perkataan unik dalam dokumen
• 32 membahagikan pangkat dengan sendirinya 1

Anda boleh menggunakan ts_rank seperti ini:

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

Walau bagaimanapun, tiada pilihan kedudukan terbina dalam berdasarkan kedudukan istilah carian dalam rentetan (iaitu lajur tajuk).

KEDUDUKAN untuk menyelamatkan

Nasib baik ada fungsi POSITION dalam PostgreSQL. Fungsi PostgreSQL POSITION digunakan untuk mencari lokasi subrentetan dalam rentetan tertentu. Dalam kes kami, kami boleh menggunakannya seperti ini

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

ts_rank menggunakan integer normalisasi 2 kerana 2 membahagikan pangkat dengan panjang dokumen
Nombor sihir 0.0001 adalah untuk mengelakkan pembahagian dengan 0 kerana fungsi POSTION dikira daripada 1 bukan 0 dan mengembalikan 0 jika rentetan tidak ditemui.

Kod akhir mungkin kelihatan seperti ini:

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);

Mencari lebih banyak istilah

Satu kaveat perlu disebut jika anda mencari lebih banyak istilah sekali gus (seperti JavaScript dan TypeScript).

Argumen untuk fungsi to_tsquery boleh digunakan dengan kefleksibelan yang besar, termasuk pengendali logik dll. Fungsi POSITION sebaliknya ialah "hanya" subrentetan dalam rentetan.

Contoh Dunia Nyata

Berikut ialah contoh saya dari titik akhir dunia sebenar dalam aplikasi web SvelteKit yang menggunakan perpustakaan npm postgres (sql):

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

Berikut ialah pautan kepada dokumentasi dalam perkara:

• 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

Atas ialah kandungan terperinci Kedudukan Carian Teks Penuh PostgreSQL mengikut Kedudukan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!