Словарный запас чата и база всех сообщений - Пит-стоп - Форум - Клавогонки

Маньяк

18

Я на днях немного упоролся и скачал логи чата с 1 января 2013 года по 15 марта 2015. Это оказалось довольно просто и быстро и поэтому я решил, что на этом останавливаться не стоит и надо бы занести всё в базу. И я занес уже 862 384 сообщений за весь 2013 год.

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

скачать с dropbox урезанную версию без ников пользователей (полную спрашивайте приватно)
скачать с copy то же и самое

UPDATE: 1 734 961 сообщений за 2013-01-01 - 2015-03-14
скачать с dropbox урезанную версию без ников пользователей (полную даю лично)

Это sqlite база, лично я с ней работаю через утилиту sqlite3. Изнутри база из себя представляет 3 таблицы:

messages (id, username, message, time, timestamp)
words (id, word, count)
usages (word_id, message_id)

В таблице words содержатся слова, найденные по регулярному выражению:

([а-яА-Я]+-[а-яА-Я]+)|([а-яА-Я]+)

и количество использований в сообщениях, не больше одного использования в одном сообщении - здесь, наверное, я лохонулся и надо было колонку count вынести в таблицу usages, но уже пофиг. Иными словами: если слово в сообщении используется несколько раз ("если бы я был богат, я бы дозаправил клавогонки"), то в words.count это было посчитано за один раз.

Таблица usages связывает слово с сообщением, в котором оно было использовано один или более раз.
То есть запросом

select username, message from messages join usages on messages.id=usages.message_id where usages.word_id=1;

можно вывести все сообщения, в которых используется слово с id=1 (слово "так").

Пожалуй отвечу на очевидный вопрос: а нафига это надо?
- Да просто так. Мне показалось забавным написать парсер и забить всё в базу и я на быструю руку это реализовал. Но а раз уж у меня что-то получилось, не удалять же это теперь.

Возможно, кому-то еще это покажется интересным и может быть кто-то придумает, как можно использовать то, что у меня получилось. Хоть логи и публичны и каждый пользователь понимает, что его сообщения могут читать все, кому не лень, я все-таки спрятал ники пользователей в базе, которую я предоставляю публично. Но я с радостью предоставлю полную версию базы любому, кто у меня ее спросит, приватно.

Вот какие у меня возникали идеи, как можно было бы это использовать:

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

un4given, например, написал генератор сообщений-бреда пользователя на основе его слов в чате своими силами, теперь вот мог бы использовать для этого базу. Если захочет, поделится ссылкой сам.

Если у кого-то есть только идеи и/или алгоритмы для реализации, но не хватает знаний для воплощения, то буду рад их рассмотреть и если будет желание/время/умения, то с радостью реализую.

Последний раз отредактировано 26 марта 2015 в 18:44 пользователем Passer

Я на днях немного упоролся и скачал логи чата с 1 января 2013 года по 15 марта 2015. Это оказалось довольно просто и быстро и поэтому я решил, что на этом останавливаться не стоит и надо бы занести всё в базу. И я занес уже [b]862 384 сообщений[/b] за весь [b]2013 год[/b].

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

[url="https://www.dropbox.com/s/kzlyzqt8ctt13ma/chatlogs.zip?dl=0"]скачать с dropbox[/url] урезанную версию без ников пользователей (полную спрашивайте приватно)
[url="https://copy.com/tcfOJAkfqP1eoakW"]скачать с copy[/url] то же и самое

[color="#ff0000"]UPDATE:[/color] [b]1 734 961[/b] сообщений за [b]2013-01-01 - 2015-03-14[/b]
[url="https://www.dropbox.com/s/jr5si6las55jiu3/chatlogs_20130101-20150314.zip?dl=0"]скачать с dropbox[/url] урезанную версию без ников пользователей (полную даю лично)

Это sqlite база, лично я с ней работаю через утилиту [url="https://sqlite.org/download.html"]sqlite3[/url].  Изнутри база из себя представляет 3 таблицы:

[b]messages[/b] (id, username, message, time, timestamp)
[b]words[/b] (id, word, count)
[b]usages[/b] (word_id, message_id)

В таблице words содержатся слова, найденные по регулярному выражению:
[code]([а-яА-Я]+-[а-яА-Я]+)|([а-яА-Я]+)[/code]
и количество использований в сообщениях, [u]не больше одного использования в одном сообщении[/u] - здесь, наверное, я лохонулся и надо было колонку count вынести в таблицу usages, но уже пофиг. Иными словами: если слово в сообщении используется несколько раз ("если [u]бы[/u] [u]я[/u] был богат, [u]я[/u] [u]бы[/u] дозаправил клавогонки"), то в words.count это было посчитано за один раз.

Таблица usages связывает слово с сообщением, в котором оно было использовано один или более раз. 
То есть запросом
[code]select username, message from messages join usages on messages.id=usages.message_id where usages.word_id=1;[/code]
можно вывести все сообщения, в которых используется слово с id=1 (слово "так").

Пожалуй отвечу на очевидный вопрос: а нафига это надо?
- Да просто так. Мне показалось забавным написать парсер и забить всё в базу и я на быструю руку это реализовал. Но а раз уж у меня что-то получилось, не удалять же это теперь.

Возможно, кому-то еще это покажется интересным и может быть кто-то придумает, как можно использовать то, что у меня получилось. Хоть логи и публичны и каждый пользователь понимает, что его сообщения могут читать все, кому не лень, я все-таки спрятал ники пользователей в базе, которую я предоставляю публично. Но я с радостью предоставлю полную версию базы любому, кто у меня ее спросит, приватно.

Вот какие у меня возникали идеи, как можно было бы это использовать:
[list]
[*]топ слов - по сути уже реализован, но ему не повредил бы фильтр что ли,
[*]график количества сообщений по часам/дням/месяцам,
[*]слова опять же если их отфильтровать глазом или еще как-то можно использовать для создания пользовательских словарей.
[/list]

un4given, например, написал генератор сообщений-бреда пользователя на основе его слов в чате своими силами, теперь вот мог бы использовать для этого базу. Если захочет, поделится ссылкой сам.

Если у кого-то есть только идеи и/или алгоритмы для реализации, но не хватает знаний для воплощения, то буду рад их рассмотреть и если будет желание/время/умения, то с радостью реализую.

Маньяк

18

Топ 10 слов:

select word, count from words order by count desc limit 10;

не|111588
я|99571
в|91856
и|84502
а|67376
на|61301
да|50198
что|49742
это|47108
с|37335

Топ 10 слов с количеством букв больше 3:

select word, count from words where length(word) > 3 order by count desc limit 10;

select sum(count) from words;

3 981 499

Количество уникальных слов. На самом деле очень много среди этих слов - слова с опечатками или всякая ересь.

select count(*) from words;

226 402

Количество замеченных в чате пользователей. Доступно в полной версии.

select count(*) from (select count(*) from messages group by username);

3665
Отсюда среднее количество сообщений, оставленных одним пользователем: 862384/3665=235,3

Последний раз отредактировано 23 марта 2015 в 02:47 пользователем Passer

Маньяк

18

Количество сообщений, оставленных пользователем в дни, когда он появлялся:

select username, count(*) as cnt, strftime('%Y-%m-%d', timestamp / 1000, 'unixepoch') as day from messages group by username, day;

Количество дней, когда пользователь появлялся в чате:

select count(*) as days_count, username from (select username, count(*) as cnt, strftime('%Y-%m-%d', timestamp / 1000, 'unixepoch') as day from messages group by username, day)

И количество пользователей, которые появлялись в чате больше чем 1 день:

select count(*) from (select count(*) as days_count, username from (select username, count(*) as cnt, strftime('%Y-%m-%d', timestamp / 1000, 'unixepoch') as day from messages group by username, day) group by username having days_count > 1);

1267

То есть в чате за год засветилось 3665 разных пользователей и только 1267 (34.5%) из них хотя бы один раз вернулись в чат в другой день.

86 пользователей появлялись в чате больше 50 дней.

Последний раз отредактировано 23 марта 2015 в 20:22 пользователем Passer

Количество сообщений, оставленных пользователем в дни, когда он появлялся:
[code]select username, count(*) as cnt, strftime(&#39;%Y-%m-%d&#39;, timestamp / 1000, &#39;unixepoch&#39;) as day from messages group by username, day;[/code]

Количество дней, когда пользователь появлялся в чате:
[code]select count(*) as days_count, username from (select username, count(*) as cnt, strftime(&#39;%Y-%m-%d&#39;, timestamp / 1000, &#39;unixepoch&#39;) as day from messages group by username, day)[/code]

И количество пользователей, которые появлялись в чате больше чем 1 день:
[code]select count(*) from (select count(*) as days_count, username from (select username, count(*) as cnt, strftime(&#39;%Y-%m-%d&#39;, timestamp / 1000, &#39;unixepoch&#39;) as day from messages group by username, day) group by username having days_count > 1);[/code]

1267

То есть в чате за год засветилось 3665 разных пользователей и только 1267 (34.5%) из них хотя бы один раз вернулись в чат в другой день.

86 пользователей появлялись в чате больше 50 дней.

Маньяк

18

Количество сообщений в чате по месяцам:

select strftime('%m', timestamp / 1000, 'unixepoch') as month, count(*) as cnt from messages group by month;

01|82328
02|69162
03|77370
04|66468
05|52364
06|35540
07|35902
08|66520
09|80458
10|77773
11|93483
12|125016

Маньяк

18

Windows (точнее консоль) не очень дружит с русскими символами (utf8), что немного затрудняет использование sqlite3. Отобразить русские символы еще можно, если перед использованием sqlite3 вбить:

chcp 65001

А вот ввод чего-нибудь русского в запросах лично у меня приводил к вылетанию sqlite3 утилиты.

В свою очередь например на ubuntu все работает на ура:

sudo apt-get install sqlite3
sqlite3 /path/to/chatlogs.db
...

Маньяк

18

Топ приветственных слов, которое я вспомнил:

select word, id from words where word in ("привет", "здравствуйте", "здорово", "здарова", "саламалейкум", "салам", "здрасте", "здрасти", "ахой", "чау", "хелло", "хеллоу") order by count desc;

Последний раз отредактировано 24 марта 2015 в 01:16 пользователем Passer

Клавокодер

120

Последний раз отредактировано 2 декабря 2018 в 23:49 пользователем un4given

Маньяк

18

select word, count from words where word in ("привет", "здравствуйте", "здорово", "здарова", "саламалейкум", "салам", "здрасте", "здрасти", "ахой", "чау", "хелло", "хеллоу", "хай", "халло", "салют", "превед", "приветствую") order by count desc;

Последний раз отредактировано 24 марта 2015 в 01:19 пользователем Passer

Маньяк

18

Слово "ок" использовалось только в

select count from words where word="ок";

7891 сообщении из 862+к. По-моему, мало. Либо народ не любит соглашаться, либо делает это другими словами.

Маньяк

18

Словарный запас для каждого пользователя:

select distinct word_id, words.word, messages.username from usages join messages on message_id=messages.id, words on words.id=word_id;

Можно, кстати, вынести в отдельную таблицу.

Топ 10 пользователей, использующих больше разных слов в своих сообщениях:

select username, count(*) as cnt from
(select distinct word_id, messages.username from usages join messages on message_id=messages.id)
group by username order by cnt desc limit 10;

un4given 45404
udezik 36808
Slonic 27982
sudo 25029
buzzy 24626
IGovernFun 21178
Vicompte 20698
G-Light 19455
Роша 17740
batton 17033

Важно. Словом считается любая последовательность русских букв. То есть каждая падежная форма слова считается новым словом. Здесь можно было бы воспользоваться алгоритмом стемминга для выдачи более правдивого результата.

Последний раз отредактировано 24 марта 2015 в 19:34 пользователем Passer

Маньяк

18

Немного визуализации:

По рекомендации ун4а сервис: https://www.jasondavies.com/wordcloud/#
На скриншоте 500 слов из топа 1000.

Скачать список слов вместе с повторениями. Можно копипастить сразу в текстовое поле, менять настройки по вкусу и смотреть на результат.

Запрос:

select word, count from words where length(word) > 2 and not word like "%-%" order by count desc limit 1000;

Я попутно удалил 10 нецензурных слов, если что, так что по факту топ990.

P.S. Сервис учитывает повторения слов и повторяющиеся чаще рисует бОльшими бувами. Чтобы не повторять каждое слово по несколько тысяч раз согласно топу я взял минимальное значение count и все остальные count разделил на него (repeats = round(word.count / min)) и потом каждое из слов повторил в файле посчитанное количество раз. Делал я это в консоли хрома на javascript само собой, опять же на скорую руку.

Последний раз отредактировано 25 марта 2015 в 00:25 пользователем Passer

Немного визуализации:
[img]http://i.imgur.com/ZDUrGtb.png[/img]

По рекомендации ун4а сервис: [url="https://www.jasondavies.com/wordcloud/#"]https://www.jasondavies.com/wordcloud/#[/url]
На скриншоте 500 слов из топа 1000.

[url="https://www.dropbox.com/s/wcvro5ugppasn4x/top1000_with_repeats.txt?dl=0"]Скачать список слов вместе с повторениями[/url]. Можно копипастить сразу в текстовое поле, менять настройки по вкусу и смотреть на результат.

Запрос:
[code]select word, count from words where length(word) > 2 and not word like "%-%" order by count desc limit 1000;[/code]

Я попутно удалил 10 нецензурных слов, если что, так что по факту топ990.

P.S. Сервис учитывает повторения слов и повторяющиеся чаще рисует бОльшими бувами. Чтобы не повторять каждое слово по несколько тысяч раз согласно топу я взял минимальное значение count и все остальные count разделил на него (repeats = round(word.count / min)) и потом каждое из слов повторил в файле посчитанное количество раз. Делал я это в консоли хрома на javascript само собой, опять же на скорую руку.

Маньяк

18

Топ 100 слов с количеством букв >= 8

Топ 500 слов с количеством букв >= 8

Запрос:

select word, count from words where length(word) >= 8 and id not in (94649, 94628) and not word like "%-%" order by count desc limit 500;

Последний раз отредактировано 26 марта 2015 в 00:53 пользователем Passer

Маньяк

18

2013.01.01-2015.03.14 dropbox
Эту же ссылку можно найти в первом сообщении.

На днях возникли споры о том, что с файлами работать лучше, чем с базой. Да, работа напрямую с файлами будет намного быстрее, чем с базой, но для работы напрямую с файлами надо
* либо знать какой-то из языков программирования и для более сложных анализов даже на достаточно неплохом уровне (регулярные выражения, массивы, ассоциативные массивы, многомерные массивы, для быстроты структуры данных, например, деревья),
* либо достаточно хорошо знать возможности unix shell, я бы сказал, что очень хорошо и иметь под рукой либо виртуалку с unix-системой, либо эту же unix-систему в установленном виде, либо надеяться, что cygwin под виндой позволит все то же, что доступно в unix shell.

Если посчитать количество сообщений, используя утилиту, напрямую из файла/файлов занимает пару сотен миллисекунд, то через базу это может занять пару секунд. Разница десятикратная, да, но время ожидания в обоих случаях скажем так не ощутимо и не играет роли, если нам не требуется повторить этот процесс несколько сотен/тысяч раз.

В свою очередь познать sql намного проще, все мои запросы выше можно сохранить как views прямо в базе, а также понасоздавать дополнительные таблицы и проставить индексы. Всё это по-прежнему будет храниться в одном файле и для использования не надо ничего устанавливать - большинство утилит на базе sqlite портабельны.

Ну и данные из базы всегда можно вытащить обратно в файл, для этого в sqlite предусмотрены команды
.mode
.output
.separator

Например:

sqlite3 chatlogs_20130101-20150314.db
.mode tabs
.output chatlogs_20130101-20150314.log
select strftime("%Y-%m-%d", timestamp / 1000, "unixepoch"), time, username, message from messages;

Последний раз отредактировано 26 марта 2015 в 19:15 пользователем Passer

[url="https://www.dropbox.com/s/jr5si6las55jiu3/chatlogs_20130101-20150314.zip?dl=0"]2013.01.01-2015.03.14 dropbox[/url] 
Эту же ссылку можно найти в первом сообщении.

На днях возникли споры о том, что с файлами работать лучше, чем с базой. Да, работа напрямую с файлами будет намного быстрее, чем с базой, но для работы напрямую с файлами надо 
* либо знать какой-то из языков программирования и для более сложных анализов даже на достаточно неплохом уровне (регулярные выражения, массивы, ассоциативные массивы, многомерные массивы, для быстроты структуры данных, например, деревья), 
* либо достаточно хорошо знать возможности unix shell, я бы сказал, что очень хорошо и иметь под рукой либо виртуалку с unix-системой, либо эту же unix-систему в установленном виде, либо надеяться, что cygwin под виндой позволит все то же, что доступно в unix shell.

Если посчитать количество сообщений, используя утилиту, напрямую из файла/файлов занимает пару сотен миллисекунд, то через базу это может занять пару секунд. Разница десятикратная, да, но время ожидания в обоих случаях скажем так не ощутимо и не играет роли, если нам не требуется повторить этот процесс несколько сотен/тысяч раз.

В свою очередь познать sql намного проще, все мои запросы выше можно сохранить как views прямо в базе, а также понасоздавать дополнительные таблицы и проставить индексы. Всё это по-прежнему будет храниться в одном файле и для использования не надо ничего устанавливать - большинство [url="http://www.sqlite.org/cvstrac/wiki?p=ManagementTools"]утилит на базе sqlite[/url] портабельны.

Ну и данные из базы всегда можно вытащить обратно в файл, для этого в sqlite предусмотрены [url="https://www.sqlite.org/cli.html"]команды[/url] 
.mode
.output
.separator

Например:
[code]sqlite3 chatlogs_20130101-20150314.db
.mode tabs
.output chatlogs_20130101-20150314.log
select strftime("%Y-%m-%d", timestamp / 1000, "unixepoch"), time, username, message from messages;[/code]

Новичок

80

Внезапно красное словцо нарисовалось

Маньяк

18

График количества сообщений в каждый из дней в период 2013-01-1 - 2015-03-31, реализованный пользователем un4given с использованием Google Charts.

И так выглядит график с интервалом в 7 дней за 2013-01-01 - 2015-01-01. Интервал скорее всего не совпадает с днями недели понедельник-воскресенье:

Если кого интересуют реализации, можете смело обращаться приватно. Всеми своими скриптами, если не брезгуете читать говнокод, с радостью поделюсь.

Организатор событий

41

Ну всё, можно словарик делать: самые употребительные слова чата.

Организатор событий

115

ага или расширить словарик: бесячные слова.
Не поняла почему Светулька - животное, в ряд расположили. Светулька редко бывает в открытых заездах, но на драге играет неплохо.

Последний раз отредактировано 4 апреля 2015 в 20:40 пользователем ТОМА-АТОМНАЯ

Маньяк

18

За 2013 год я уже сделал: http://klavogonki.ru/vocs/104975/

Распорядок слов на картинке случайный. Я некоторые оскорбительные и нецензурные слова убрал, чтоб они ни с кем рядом не оказались. Если кто-то действительно оскорблен тем, как его ник расположился в картинке или не нравится, что он вообще фигурирует в моих "исследованиях", пишите - сделаю фильтр и исключу конкретные ники.

Последний раз отредактировано 4 апреля 2015 в 21:47 пользователем Passer

Организатор событий

115

скрытый текст…

За словарик отдельное спасибо.

Новичок

24

И за экскурс в SQL спасибо.
Знаю хорошо, но не часто пишу такие запросы, как в конце...

Форум «Пит-стоп» / Словарный запас чата и база всех сообщений

Чтобы писать в форуме, нужно зарегистрироваться.

Автор:

Обратный адрес:

Тема:

Сообщение: