Как найти все схожие записи в таблице?

Question

[nurzhannogerbek]

Здравствуйте! Помогите пожалуйста разобраться.

В базе данных PostgreSQL есть таблица со списком вопросах и выглядит таблица следующим образом:

| QUESTION_ID | QUESTION_TEXT                                    |
|-------------|--------------------------------------------------|
| 1           | What is your favorite movie, cartoon and series? |
| 2           | What is your favorite movie cartoon and series   |
| 3           | what is your favorite Movie, Cartoon and Series  |
| 4           | Do you like apple?                               |
| 5           | do you like Apple                                |
| 6           | What is your favorite city?                      |

Как вы видите, в таблице есть схожие записи. К примеру, текст в записях с идентификатором 1, 2 и 3 похож для человеческого глаза, но эти записи разные для машины.

Если выполнить данный запрос возвращает лишь 2 записи c точным совпадением.

select
    *
from
    questions
where
    question_text in (
        'What is your favorite movie, cartoon and series?',
        'Do you like apple?'
    )

Мне нужно по этим двум вопросам, которые упомянуты в вышестоящем запросе найти все схожиме записи. Схожесь должна быть 90 процентов. Вроде как для таких целей есть модуль pg_trgm, в которой присутствует функция similarity. Она возвращает значение схожести от 0 до 1.

Что я сделал:
1) Я включил расширение pg_trgm:
CREATE EXTENSION pg_trgm;

2) Создал индекс на столбец question_text:

CREATE INDEX questions_trgm_idx ON questions
	USING gin (question_text gin_trgm_ops);

3) Данный запрос возвращает все записи по одному предложению. Как сделать схожий поиск по нескольким предложениям?

select
	question_text,
	similarity(
		question_text,
		'What is your favorite movie, cartoon and series?'
	)
from
	answers
where
	question_text % 'What is your favorite movie, cartoon and series?'
	and similarity(
		question_text,
		'What is your favorite movie, cartoon and series?'
	) >= 0.9;

Answer 1

[Валерий]

where .... like '%your_text%'

Comments

[nurzhannogerbek]

Как делать поиск не по одному предложению как вы предлагаете, а по нескольким? Вам пример не учитывает схожесть. Он учитывает вхождение, если не ошибаюсь. Это не совсем то, что я пытаюсь получить.

Answer 2

[zhaar]

А еще можно убрать все знаки препинания, т.к. они тоже будут создавать лишние проблемы

Comments

[nurzhannogerbek]

Здравствуйте! Можете свою мысль раскрыть более детально пожалуйста. Вообщем смысл в том, что у меня есть список эталонных вопросов. То есть то как они должны правильно грамматически писаться. В таблице есть такие же вопросы с разными ошибками. Пропустили знаки препинания где-то, разные регистры. Мне нужно найти вхожие предложения.

[zhaar]

Для PGSQL есть функция, которую используете. В первую очередь можно отсечь записи, которые идентичны. Далее, в зависимости от того, где хранятся оригиналы вопросов, то и нужно сравнивать.
Т.е. на примере ниже (все хранится в одной таблице), сверка и поиск "правильных" записей будет идти по всем полям.
Если таблица с правильными вопросами лежит отдельно (либо правильные вопросы имеют какой-то признак правильности), то в джойне нужно подключать ее.
Результатом будет как раз значение сходимости.
Если не устраивают результаты, то через replace\substring уберите все небуквенные символы, которые есть и уже сравнивайте их.


SET pg_trgm.similarity_threshold = 0.9;

SELECT similarity(n1.name, n2.name) AS sim, n1.name, n2.name
FROM names n1
JOIN names n2 ON n1.name <> n2.name
AND n1.name % n2.name
ORDER BY sim DESC;

[nurzhannogerbek]

zhaar, спасибо за ответ, но что если "правильные" записи у меня не хранятся в отдельной таблице? Есть лишь таблица со всеми записями (в моем случаи таблица questions, которую ранее упоминал в посте). Мне хотелось бы в самом запросе указать этот самый список "правильных" записей.

[zhaar]

NogerbekNurzhan, в самом запросе указывать правильный ответ - это очень печально.
Но в таком случае тебе придется в нескольких местах менять таблицу2 на явное значение, которое сравниваешь.
Но я рекомендую сделать хотя бы временные таблицы, куда пихать хотя бы 1 запись - так будет проще работать.