PostgreSQL 전체 텍스트 검색 위치별 순위

최근 전체 텍스트 검색에 문제가 발생했습니다. 저는 검색 입력에 이 기능을 사용하고 있습니다. 여기서 백엔드는 사용자가 입력할 때 가능한 일치 항목에 대한 힌트를 보냅니다. 백엔드 데이터베이스는 PostgreSQL입니다. 본문 내 검색어의 위치에 따라 순위를 매길 수 있는 힌트가 필요했습니다.

따라서 "스타워즈" 제목을 검색하면 "스타워즈 7-9가 어떻게 스타워즈의 세계를 바꾸었나(스타워즈에 대해 재미있게 만든 다큐멘터리)" 대신 "스타워즈" 게시물이 먼저 표시됩니다. 용어가 3번 나올수록 순위가 높습니다.

PostgreSQL의 전체 텍스트 검색

PostgreSQL에서는 전체 텍스트 검색을 매우 쉽게 수행할 수 있습니다. 사용할 수 있는 두 가지 주요 도구는 다음과 같습니다.

• tsVector - 검색 가능한 문서를 나타냅니다.
• tsquery - 문서에 대해 수행할 검색어를 나타냅니다.

블로그 게시물의 제목을 검색하고 싶다고 가정해 보겠습니다. 검색 가능하게 하려면 다음 쿼리를 사용하면 됩니다.

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

이 경우 검색할 때마다 게시물 제목을 ts벡터로 동적으로 변환합니다. 그러나 이 변환에는 시간이 좀 걸립니다. 더 나은 접근 방식은 이러한 변환을 데이터베이스에서 미리 수행하고 더 빠른 검색을 위해 제목에 대한 인덱스로 저장하는 것입니다.

제목 벡터의 새 열을 만들고 이 새 열의 색인을 생성해 보겠습니다.

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

이제 "JavaScript"를 검색해 보세요

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

다음과 같이 제목 열에서 직접 ts 벡터의 색인을 만들 수도 있습니다.

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

다음과 같이 검색을 사용하세요.

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

이제 전체 텍스트 검색이 엄청나게 빨라져 밀리초 안에 완료됩니다.

결과 순위 지정

PostgreSQL은 검색 결과의 점수를 매기고 순위에 따라 정렬할 수 있는 ts_rank 기능을 제공합니다. PostgreSQL은 다음 순위 옵션을 지원합니다.

• 0(기본값)은 문서 길이를 무시합니다
• 1은 순위를 문서 길이의 로그 1로 나눕니다
• 2 순위를 문서 길이로 나눕니다
• 4는 순위를 익스텐트 간 평균 조화 거리로 나눕니다(이는 ts_rank_cd로만 구현됨)
• 8은 문서 내 고유 단어 수로 순위를 나눕니다
• 16은 순위를 문서의 고유 단어 수에 대한 로그 1로 나눕니다
• 32는 순위를 1로 나눕니다

ts_rank를 다음과 같이 사용할 수 있습니다.

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

단, 문자열(예: 제목 열) 내 검색어 위치를 기준으로 하는 순위 옵션이 내장되어 있지 않습니다.

구조를 위한 POSITION

다행히 PostgreSQL에는 POSITION 함수가 있습니다. PostgreSQL POSITION 함수는 주어진 문자열 내에서 하위 문자열의 위치를 ​​찾는 데 사용됩니다. 우리의 경우에는 이렇게 사용할 수 있습니다

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

ts_rank는 2가 순위를 문서 길이로 나누기 때문에 정규화 정수 2를 사용합니다.
Magick 번호 0.0001은 POSTION 함수가 0이 아닌 1부터 계산하고 문자열이 없으면 0을 반환하기 때문에 0으로 나누는 것을 방지하기 위한 것입니다.

최종 코드는 다음과 같습니다.

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

더 많은 용어 검색 중

한 번에 더 많은 용어를 검색하는 경우 한 가지 주의 사항을 언급해야 합니다(예: JavaScript 및 TypeScript).

to_tsquery 함수의 인수는 논리 연산자 등을 포함하여 매우 유연하게 사용될 수 있습니다. 반면에 POSITION 함수는 문자열의 하위 문자열 "단지"입니다.

실제 사례

다음은 postgres(sql) npm 라이브러리를 사용하는 SvelteKit 웹 애플리케이션의 실제 엔드포인트 예입니다.

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

다음은 해당 문제의 문서에 대한 링크입니다:

• 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

위 내용은 PostgreSQL 전체 텍스트 검색 위치별 순위의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!