Сложный поиск по БД

@insideone · Регистрация: 10.01.2010

Студворк — интернет-сервис помощи студентам

PHP + MySQL. Разрабатываю виртуального собеседника по мотивам темы Алгоритм работы чат-бота
На входе поиску по базе данных - массив слов (сокращенных) на выходе N результатов которые содержат больше всего эти слова. На первом месте соответственно полное совпадение, а допустим максимальный предел - совпали лишь половина слов. В базе тоже хранятся такие массивы слов в каждой строке.
Как реализовать наиболее оптимально?

Vovan-VE · 16.07.2010, 18:41

А эти наборы слов ("фразы") уже как-то хранятся и таблице(-ах), или в этом, от части, вопрос и заключается?

Думается, слова хранить в таблице слов (по одному слову в записи), фразы в своей таблице (id фразы), связь фразы с каждым словом в отдельной таблице (id_фразы <=> id_слова). Тогда, думается, можно будет построить необходимый запрос.

Структура

Слова

Code
1
2
3
4
5
id  word
--------
1   foo
2   bar
3   lol

Фразы

Code
1
2
3
4
id  ...
-------
1   ...
2   ...

Их связь

Code
1
2
3
4
5
6
phrase_id  word_id
------------------
    1         1
    1         2
    2         1
    2         3

Фраза 1: foo bar
Фраза 2: foo lol

@insideone · 16.07.2010, 18:54 **[ТС]**

Сообщение от Vovan-VE

А эти наборы слов ("фразы") уже как-то хранятся и таблице(-ах), или в этом, от части, вопрос и заключается?

Да-да, они хранятся и являются как бы ключами к тому что должен дать в ответ бот. Смысл найти ключ наиболее похожий к фразе юзера. Но фраза юзера дополнительно обрабатывается для простоты и приводятся к массиву урезаных слов упорядоченных по алфавиту. Хм.
Идея разделить все по нескольким таблицам кажется мне интересной. Сейчас обдумаю, спасибо

Vovan-VE · 16.07.2010, 19:01

Дамп

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE `phrases` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;
 
INSERT INTO `phrases` VALUES
(1),
(2);
 
CREATE TABLE `phrase_words` (
  `phrase_id` INT(10) NOT NULL,
  `word_id` INT(10) NOT NULL,
  UNIQUE KEY `phrase_id` (`phrase_id`,`word_id`)
) ENGINE=MyISAM;
 
INSERT INTO `phrase_words` VALUES
(1, 1),
(1, 2),
(2, 1),
(2, 3);
 
CREATE TABLE `words` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `word` VARCHAR(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `word` (`word`)
) ENGINE=MyISAM;
 
INSERT INTO `words` VALUES
(1, 'foo'),
(2, 'bar'),
(3, 'lol');

Вот такой запрос для начала

SQL
1
2
3
4
5
6
7
8
SELECT pw.`phrase_id`, COUNT(pw.`word_id`) AS `wcount`
FROM (
  SELECT `id` 
  FROM `words` 
  WHERE `word` IN ('foo', 'bar')
) AS `w` INNER JOIN `phrase_words` AS `pw` ON w.`id` = pw.`word_id`
GROUP BY pw.`phrase_id`
ORDER BY `wcount` DESC

Выдает ID фраз и сколько слов совпало:

Code
1
2
3
phrase_id  wcount
    1        2
    2        1

Добавлено через 3 минуты
Но тогда в моем случае для оптимизации получается, что одно и то же слово не может быть в одной фразе несколько раз.

@insideone · 16.07.2010, 19:10 **[ТС]**

Тээкс,
1. Юзер вводит фразу
2. Разбивается обрабатывается -> массив слов
3. Запрос найти все ID по этим словам -> массив ID слов (используем первую таблицу "Слова")
4. Найти все ключи которые содержат N слов, вернуть массив ['ключ'] => N (используем третью таблицу "Их связь")
5. Обработать PHP этот массив и запросить по выбраному ключу (используем вторую таблицу "Фразы")

п.3 думаю прост, а вот п4 надо бы подумать. п5. банален. но в PHP придется обрабатывать. т.к. не обязательно выдывать результат с наибольшей релятивностью и можно внести элемент случайности (хотя я думаю можно и в базе выбрать RAND'омом но на PHP была бы более интересная логика где шанс более релевантной фразы выше)

Добавлено через 2 минуты

Сообщение от Vovan-VE

Но тогда в моем случае для оптимизации получается, что одно и то же слово не может быть в одной фразе несколько раз

пожалуй это не страшно, думаю с ботом обычно общаются короткими фразами, а не ёмкими сочинениями где много повторяющихся слов

Vovan-VE · 16.07.2010, 19:14

Сообщение от insideone

а вот п4 надо бы подумать

Так, мы же уже нашли все фразы, где в принципе есть указанные слова. Что нам теперь с этим делать, любую фразу выбрать?

@insideone · 16.07.2010, 19:33 **[ТС]**

Сообщение от Vovan-VE

Так, мы же уже нашли все фразы, где в принципе есть указанные слова. Что нам теперь с этим делать, любую фразу выбрать?

В идеале вернуть наиболее релевантные, т.е. те в которых количество больше определенного процента, например 50%. Т.е. на входе массив из 10 слов, а выбираются только те ID фраз-ключей в которых слов 5 и более.
ps. Тяжело пока с пониманием таких сцепленных запросов, пожалуй если оно то реализует буду пытаться внедрить подобный запрос, что получиться сообщу

Добавлено через 9 минут
Вот что тут ещё пока не хватает. Мы выбираем фразу, но на каждую фразу-ключ должны быть фразы-ответы (то что ответит бот). Ну всмысле на "привет" бот может отвечать не одной фразой а несколькими ("привет", "здравствуй", "hello %username%").

Vovan-VE · 16.07.2010, 19:40

Тогда вот такой запрос забодяжил

SQL
1
2
3
4
5
6
7
8
9
10
11
12
SELECT pw.`phrase_id`, f.`wcount`, COUNT(pw.`word_id`) AS `words`
FROM (
  SELECT pw.`phrase_id`, COUNT(pw.`word_id`) AS `wcount`
  FROM (
    SELECT `id` 
    FROM `words` 
    WHERE `word` IN ('foo', 'bar')
  ) AS `w` INNER JOIN `phrase_words` AS `pw` ON w.`id` = pw.`word_id`
  GROUP BY pw.`phrase_id`
  ORDER BY NULL
) AS `f` INNER JOIN `phrase_words` AS `pw` ON f.`phrase_id` = pw.`phrase_id`
GROUP BY pw.`phrase_id`

Выдает: id_фразы - слов совпало - слов всего в фразе

Code
1
2
3
phrase_id  wcount  words
    1        2       2    -- совпали два из двух
    2        1       2    -- а здесь одно из двух

Осталось дописать в самом конце HAVING с соотошением между f.`wcount` (слов найдено) и `words` (слов в фразе) как-то так:

SQL
1
2
...
HAVING f.`wcount` / `words` >= 0.5

Добавлено через 3 минуты

Сообщение от insideone

Вот что тут ещё пока не хватает.

Так мы щас какую фразу выбираем? Что юзер написал или что бот должен ответить?

@insideone · 16.07.2010, 19:48 **[ТС]**

Сообщение от Vovan-VE

Так мы щас какую фразу выбираем? Что юзер написал или что бот должен ответить?

То что написал юзер является ключем к ответу. Сейчас выбирали ключ (вернее скорее ключи), а потом нужно уже будет выбрать ответ. На PHP выбирается ID фразы-ключа. А потом выбирается рандомная фраза-ответ по этому выбранному ID.
Наверное добавляется ещё таблица вроде:

Code
1
2
3
4
5
6
phrase_id       reply (TEXT)
1           'Привет'
1           'Здравствуй'
1           'Hello %username'
2           ...
...

Vovan-VE · 16.07.2010, 19:51

Т.е. всем тем, что было выше, мы потаемся понять, чтО нам написал юзер?

@insideone · 16.07.2010, 19:59 **[ТС]**

Сообщение от Vovan-VE

Т.е. всем тем, что было выше, мы потаемся понять, чтО нам написал юзер?

Пожалуй да. Смысл в составлении базы "вопрос-ответ(ы)".

SQL
1
WHERE `word` IN ('foo', 'bar')

То что ввел юзер - это вопрос боту ("Фу бар?"

). Он ищет по базе вопросов наиболее подходящий их тех который он знает. И он знает как на выбранный вопрос ответить. А далее случайно выбирается фраза ответ, несколько фраз-ответов в принципе чисто для реалистичности ответов (т.к. очень заметно если бот ответит на повторный вопрос слово в слово).

Vovan-VE · 16.07.2010, 20:05

Понял. Тогда дело осталось за малым: добавить таблицу ответов, как Вы и говорите.
Единственное, что не понятно: что если получилось несколько более-менее равнозначных догадок, о чем написал юзер? Какую выбрать и на какую отвечать? Рандомно любую при прочих равных условиях?

Добавлено через 2 минуты
В общем к тому страшному запросу надо после HAVING дописать

SQL
1
2
ORDER BY ...
LIMIT 1

И сделать его подзапросом для выбора рандомного ответа.

@insideone · 16.07.2010, 21:55 **[ТС]**

Сообщение от Vovan-VE

Единственное, что не понятно: что если получилось несколько более-менее равнозначных догадок, о чем написал юзер? Какую выбрать и на какую отвечать? Рандомно любую при прочих равных условиях?

Ну в теории можно было бы поизвращаться и назначить каждому ответу тематический идентификатор и во время беседы по ключевым фразам фиксировать на какую тему идет разговор. После уже выбирать приоритетно те фразы которые ближе к тематике. Думаю это было бы хорошее улучшение, но я вижу тут уже много сложностей, которые возможно не нужны. Разговор с идеальным ботом был бы несколько скучен. Конечно это был бы прорыв в AI (ха-ха) но в реальных ботах мне всегда нравились именно смешные попытки AI найти верный ответ и когда правильные ответы чередуются с маразмом от которого живот надрывается

Главное чтобы верные ответы тоже были иначе очень быстро наскучивает...

Добавлено через 1 час 47 минут
А почему phrase_id и ключ и поле? Меня запутывает

SQL
1
UNIQUE KEY `phrase_id` (`phrase_id`,`word_id`)

Vovan-VE · 16.07.2010, 22:01

Поля `phrase_id`,`word_id` - вместе уникальный ключ (с каким-то именем, пофиг каким). Не будет двух одинаковых пар.

@insideone · 16.07.2010, 22:34 **[ТС]**

Сообщение от Vovan-VE

с каким-то именем, пофиг каким

Вот это меня и настораживает как раз

Совсем уж нет смысла?...

Vovan-VE · 17.07.2010, 09:14

Сообщение от insideone

Совсем уж нет смысла?

Это я погорячился. Имена ключей/индексов используются в первую очередь для редактирования структуры таблицы (ALTER TABLE).
В SELECT'ах в FROM есть возможность использовать имена индексов и ключей чтобы "помочь" оптимизатору соптимизировать запрос.

@insideone · 18.07.2010, 17:58 **[ТС]**

Вставка данных тоже нужна. Вот пытаюсь вставить новый ключ так

PHP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
    $DB = __DB::Instance();
    $question = array('как', 'ты', 'думаешь');
 
    // Вставляем слова
    foreach ($question as $val)
    {
        $query = 
            'INSERT INTO aibot_words
            SET word=\''.$val.'\'
            ON DUPLICATE KEY UPDATE word=\''.$val.'\'';
        // запрос mysql_query()
    }
 
    // Запрос на ID вставленных слов
    $query =
        'SELECT id
        FROM `aibot_words`
        WHERE word IN (\''.implode('\', \'', $question).'\')';
    // запрос mysql_query()
    // получение результата в $result
    // тут ошибка в том что $result = array(0) - почему так не понимаю :( в базе слова добавились
 
    // нужен ID новой фразы
    $query =
        'SELECT MAX(id)
        FROM `aibot_questions`';
    // запрос mysql_query()
    // результат в $question_id
    $question_id++;
    
    foreach ($words_ids as $word_id)
    {
        $query = "INSERT INTO `questions` SET id = $question_id, word_id = $word_id";
        // запрос mysql_query()
    }

Опять же мне кажется повторные questions возможны, надо перед вставкой все это проверять. Почему id слов не выбираются.. кодировка? Сейчас utf8_general_ci

Vovan-VE · 18.07.2010, 18:06

Сообщение от insideone

тут ошибка в том что $result = array(0) - почему так не понимаю

А в коде выбора результата ошибок нет?

Сообщение от insideone

// нужен ID новой фразы
$question_id++;

Так там разве не AUTO_INCREMENT? Он бы сам сделал новый id.

@insideone · 18.07.2010, 23:07 **[ТС]**

Сообщение от Vovan-VE

А в коде выбора результата ошибок нет?

Нету, так же проверять?

PHP
1
2
    $result = mysql_fetch_array($this->_pResult, MYSQL_NUM);
    echo 'Ошибка выборки:' . mysql_error()); // "Ошибка выборки:"

Сообщение от Vovan-VE

Так там разве не AUTO_INCREMENT? Он бы сам сделал новый id.

Так вопрос же состоит не из одного слова

question_id word_id
1 0
1 1
1 2

в данном случае где 0 - как, 1 - ты, 2 - думаешь

Добавлено через 1 минуту
С mysql_fetch_array разобрался... глупая ошибка была

Vovan-VE · 19.07.2010, 15:33

Сообщение от insideone

Так вопрос же состоит не из одного слова

Так в таблице слов ID автоинкремент. Прото инсеришь слово без указания, а он сам делает новое значение для ID. После выполнения запроса через mysql_insert_id() его и узнаешь.
Фразу аналогично добавляешь в таблицу фраз - получаешь новое ID фразы. Затем уже в таблицу связи эти ID добавляешь.

Новые блоги и статьи Все статьи Все блоги /
Первый деплой lagorue 16.01.2026 Не спеша развернул своё 1ое приложение в kubernetes. А дальше мне интересно создать 1фронтэнд приложения и 2 бэкэнд приложения развернуть 2 деплоя в кубере получится 2 сервиса и что-бы они. . .	Расчёт переходных процессов в цепи постоянного тока igorrr37 16.01.2026 / * Дана цепь постоянного тока с R, L, C, k(ключ), U, E, J. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа, решает её и находит токи на L и напряжения на C в установ. режимах до и. . .	Восстановить юзерскрипты Greasemonkey из бэкапа браузера damix 15.01.2026 Если восстановить из бэкапа профиль Firefox после переустановки винды, то список юзерскриптов в Greasemonkey будет пустым. Но восстановить их можно так. Для этого понадобится консольная утилита. . .	Изучаю kubernetes lagorue 13.01.2026 А пригодятся-ли мне знания kubernetes в России?
Сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/	WordPad для Windows 11 Jel 10.01.2026 WordPad для Windows 11 — это приложение, которое восстанавливает классический текстовый редактор WordPad в операционной системе Windows 11. После того как Microsoft исключила WordPad из. . .	Classic Notepad for Windows 11 Jel 10.01.2026 Old Classic Notepad for Windows 11 Приложение для Windows 11, позволяющее пользователям вернуть классическую версию текстового редактора «Блокнот» из Windows 10. Программа предоставляет более. . .	Почему дизайн решает? Neotwalker 09.01.2026 В современном мире, где конкуренция за внимание потребителя достигла пика, дизайн становится мощным инструментом для успеха бренда. Это не просто красивый внешний вид продукта или сайта — это. . .

@insideone 3687 / 964 / 114 Регистрация: 10.01.2010 Сообщений: 2,550

	Сложный поиск по БД 16.07.2010, 17:43. Показов 6359. Ответов 47 Метки нет (Все метки) PHP + MySQL. Разрабатываю виртуального собеседника по мотивам темы Алгоритм работы чат-бота На входе поиску по базе данных - массив слов (сокращенных) на выходе N результатов которые содержат больше всего эти слова. На первом месте соответственно полное совпадение, а допустим максимальный предел - совпали лишь половина слов. В базе тоже хранятся такие массивы слов в каждой строке. Как реализовать наиболее оптимально? 0

Vovan-VE 13210 / 6599 / 1041 Регистрация: 10.01.2008 Сообщений: 15,069
	16.07.2010, 19:51
	Т.е. всем тем, что было выше, мы потаемся понять, чтО нам написал юзер? 0

Vovan-VE 13210 / 6599 / 1041 Регистрация: 10.01.2008 Сообщений: 15,069
	16.07.2010, 22:01
	Поля `phrase_id`,`word_id` - вместе уникальный ключ (с каким-то именем, пофиг каким). Не будет двух одинаковых пар. 0