Приветствую уважаемых экспертов!
К моему глубокому сожалению, отдел мозга, отвечающий за составление SQL запросов у меня почему-то отключен, а жисть все равно принуждает к действию. Прошу помочь составить запрос для подсчета кол-ва вхождений слова из словаря во всех фразах и сумму метрик для фраз, где данное слово встречалось.
Есть две таблицы, одна с фразами:
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TABLE textdata (phrase TEXT NOT NULL COLLATE NOCASE, metric INTEGER, UNIQUE(phrase));
INSERT INTO textdata (phrase, metric) values('orange juice', 80);
INSERT INTO textdata (phrase, metric) values('red hat', 52);
INSERT INTO textdata (phrase, metric) values('red blood', 24);
INSERT INTO textdata (phrase, metric) values('black hole', 46);
INSERT INTO textdata (phrase, metric) values('man in black ', 30);
INSERT INTO textdata (phrase, metric) values('black swan', 22);
INSERT INTO textdata (phrase, metric) values('my green green eyes ', 18);
другая со словами:
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE vocabular (word TEXT NOT NULL COLLATE NOCASE, UNIQUE(word));
INSERT INTO vocabular (word) values('orange');
INSERT INTO vocabular (word) values('red');
INSERT INTO vocabular (word) values('black');
INSERT INTO vocabular (word) values('green');
INSERT INTO vocabular (word) values('blue');
ума не приложу как получить на выходе вот такую таблицу:
wordhitsumorange180red276black398green118blue00
где:
word - все слова из vocabular.word
hit - количество фраз из textdata.phrase в которых данное слово встречалось хотябы один раз
sum - сумма значений из textdata.metric для фраз из textdata.phrase, в которых данное слово встречалось хотябы один раз
Есть книжки М.Грабер SQL для простых смертных, Крис Фиайли SQL, но результата нет, а нужен то результат.
Читал сайт sqlite.org в целом и www.sqlite.org/fts5.html в частности, от туда научился использовать расширение FTS5 для полнотекстового поиска, а так же составлять словари.
Подскажите пож. как составить данный конкретный запрос и куда копать самостоятельно, что бы подружиться с SQLite для работы с текстовой статистикой?
Поначалу думал решать такие задачи с RegExp, но оказалось без базы никак.
