ReSQL.ru
     
powered by simpleCommunicator - 2.0.37     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Есть две таблицы ... как подсчитать вхождения и сумму?
9 сообщений из 9, страница 1 из 1
Есть две таблицы ... как подсчитать вхождения и сумму?
    #39308328
Notorand
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Notorand
Гость
Приветствую уважаемых экспертов!
К моему глубокому сожалению, отдел мозга, отвечающий за составление SQL запросов у меня почему-то отключен, а жисть все равно принуждает к действию. Прошу помочь составить запрос для подсчета кол-ва вхождений слова из словаря во всех фразах и сумму метрик для фраз, где данное слово встречалось.

Есть две таблицы, одна с фразами:
Код: 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);


другая со словами:
Код: sql
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, но оказалось без базы никак.
...
Рейтинг: 0 / 0
13.09.2016, 12:25
| Ответить | Цитировать | Написать  
Есть две таблицы ... как подсчитать вхождения и сумму?
    #39308428
Notorand
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Notorand
Гость
Вроде получилось, тока медленно-медленно пашет:

Код: sql
1.
2.
3.
CREATE VIRTUAL TABLE textdata_fts USING fts5(phrase, metric);
INSERT INTO textdata_fts SELECT * FROM textdata;
SELECT vocabular.word,count(*),sum(textdata_fts.metric) FROM textdata_fts,vocabular WHERE textdata_fts MATCH vocabular.word GROUP BY vocabular.word;



Чтобы использовать MATCH перенес исходные данные в FTS5 таблицу. Все работает как надо, не знаю только с точки зрения эстетики правильно или нет, ну и скорость слабовата.
...
Рейтинг: 0 / 0
13.09.2016, 14:14
| Ответить | Цитировать | Написать  
Есть две таблицы ... как подсчитать вхождения и сумму?
    #39308610
pit_alex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pit_alex
Участник
Notorand,

Код: sql
1.
2.
3.
select v.word, count(word), sum(t.metric) from vocabular v, textdata t
where t.phrase like '%'||v.word||'%'
group by v.word
...
Рейтинг: 0 / 0
13.09.2016, 17:14
| Ответить | Цитировать | Написать  
Есть две таблицы ... как подсчитать вхождения и сумму?
    #39308698
Notorand
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Notorand
Гость
pit_alex, спасибо, забыл что можно алиасы длинным именам давать. К сожалению LIKE, GLOB не годятся, много ложняков, например слово "опер" выкатывает и "опера" и "операция" и "кооператив". Пришлось использовать match, там всё чётко по словам бьется.
...
Рейтинг: 0 / 0
13.09.2016, 21:44
| Ответить | Цитировать | Написать  
Есть две таблицы ... как подсчитать вхождения и сумму?
    #39309107
Фотография VSVLAD
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VSVLAD
Участник
Notorand"опер" выкатывает и "опера" и "операция" и "кооператив"

А так?
Код: sql
1.
t.phrase like '% '||v.word||' %'
...
Рейтинг: 0 / 0
14.09.2016, 15:21
| Ответить | Цитировать | Написать  
Есть две таблицы ... как подсчитать вхождения и сумму?
    #39309154
pit_alex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pit_alex
Участник
VSVLAD,

а если слово в начале или конце строки? то непрокатит
...
Рейтинг: 0 / 0
14.09.2016, 16:11
| Ответить | Цитировать | Написать  
Есть две таблицы ... как подсчитать вхождения и сумму?
    #39309401
pit_alex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pit_alex
Участник
VSVLAD,

можно конечно так

Код: sql
1.
2.
3.
select v.word, count(word), sum(t.metric) from vocabular v, textdata t
where t.phrase like '% '||v.word||' %' or t.phrase like '%'||v.word||' %' or t.phrase like '% '||v.word||'%'      
group by v.word



но скорости не будет
...
Рейтинг: 0 / 0
15.09.2016, 07:39
| Ответить | Цитировать | Написать  
Есть две таблицы ... как подсчитать вхождения и сумму?
    #39309403
pit_alex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pit_alex
Участник
pit_alex,

Код: plaintext
1.
2.
3.
4.
 [code=sql]
select v.word, count(word), sum(t.metric) from vocabular v, textdata t
where t.phrase like '% '||v.word||' %' or t.phrase like v.word||' %' or t.phrase like '% '||v.word      
group by v.word

...
Рейтинг: 0 / 0
15.09.2016, 07:41
| Ответить | Цитировать | Написать  
Есть две таблицы ... как подсчитать вхождения и сумму?
    #39310378
Notorand
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Notorand
Гость
pit_alex, ну не зря же все эти расширения для фулл текст сёрча изобретают, там индекс 2/3 от размера базы занимает. Импортнул базу, в csv занимала 4.3 Гига, в sqlite стала 4.6. Создал FTS5 индекс и база сразу выросла до 12.3 Гигов!
Оператор MATCH реально ускоряет создание выборки на таких объемах, в сравнении с LIKE. Пробовал регулярки в операторе REGEXP, так вообще тормозят страшно.
...
Рейтинг: 0 / 0
16.09.2016, 15:48
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / SQLite [игнор отключен] [закрыт для гостей] / Есть две таблицы ... как подсчитать вхождения и сумму?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=54&msg=39308428&tid=2008572]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 45ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 42ms
get tp. blocked users:
 1ms
others: 13ms
total:  145ms
созд. / загр.: 145ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]