Всем привет! Итак, совсем недавно я писал коротенькую заметку с картинками касательно ru-доменов. Все бы хорошо, но мне никак не давало покоя то что эти данные лежат в csv который довольно просто импортнуть в БД и получить еще больше интересных данных.
Под катом мои наблюдения.
Приготовления
Для начала я решил выделить из названий доменов отдельные слова чтобы посчитать их вхождения. Алгоритм прост - отсекаем .ru, а в том что осталось выделяем отдельные слова выкидыванием лишних дефисов. Отдельно составил вхождения «слов» состоящих из последовательности цифр и отдельно слов без цифр. То есть получилось 3 файла - full,letter,digit. Где например домен site24.ru попал бы как site24,site и 24 в разные таблицы.
Теперь кроме одного csv под названием ru_domains у меня еще появились fullWord.csv, letterWord.csv и digitWord.csv.
Код сортировщика написан по всем канонам костыльного говнокодинга и приводить его здесь даже стыдно =)
Импорт в БД
Для опытов я создал отдельную БД в MySQL и создал там 4 таблицы - full,letter,digit и original. Первые 3 имеют в себе только по 2 поля - word и count. Это cлово и кол-во его вхождений соответственно. Таблица original содержит в себе 6 полей - домен, регистратор, дата регистрации, продлен до, дата окончания регистрации и какой-то еще один параметр (ну лень было разбираться).
И отдельно импортируем оригинальный csv с 5млн. записей. Тут заодно конвертируем дату в правильный формат. Данная конструкция отрабатывает на моем ноутбуке за 32 секунды. Так что это и правда быстро.
1 2 3 4 5 6 7 8 9
LOAD DATA INFILE "/var/www/srv/www/ru_domains" INTOTABLE original FIELDS TERMINATED BY'\t' ENCLOSED BY'"' LINES TERMINATED BY'\n' (name,reg,@reg_date,@ext_date,@off_date,hz) SET reg_date = STR_TO_DATE(@reg_date, '%d.%m.%Y') , exp_date = STR_TO_DATE(@exp_date, '%d.%m.%Y'), off_date = STR_TO_DATE(@off_date, '%d.%m.%Y');
Статистика
Ну а теперь я покажу вам свои мэдскиллз в MySQL =)
Начнем с самых популярных слов входящих в название домена:
Из всего этого списка мне больше всех понравился разве что самый «смешной» домен с многочисленным «AXAXAXAXA» =). По этому адресу нас встречает какая-то странная картинка и счетчик liveinternet.
Самые короткие рушки начинаются от 2 символов и они абсолютно все заняты:
1 2 3 4 5 6
SELECTCOUNT(*) FROM original WHERE LENGTH(name) =5; +----------+ |COUNT(*) | +----------+ |1296| +----------+
Но сегодня один домен освобождается и я попробую успеть его застолбить ).
Кстати, конкретно сегодня освобождается вот такое кол-во доменов:
1 2 3 4 5 6
SELECTCOUNT(*) FROM original WHERE off_date='2016-01-20'; +----------+ |COUNT(*) | +----------+ |3455| +----------+
Теперь посмотрим на 20 самых старых доменов в зоне ru:
SELECTCOUNT(*) FROM original WHERE name LIKE'%ulsk%'; +----------+ |COUNT(*) | +----------+ |186| +----------+
SELECTCOUNT(*) FROM original WHERE name LIKE'ul-%'; +----------+ |COUNT(*) | +----------+ |293| +----------+
SELECTCOUNT(*) FROM original WHERE name LIKE'%-ul.ru'; +----------+ |COUNT(*) | +----------+ |300| +----------+
SELECTCOUNT(*) FROM original WHERE name LIKE'%73.ru'; +----------+ |COUNT(*) | +----------+ |4222| +----------+
И с большим отрывом суффикс 73 обгоняет всевозможные вариации с ul. Казалось бы, это всего лишь цифры из списка субъектов РФ для автомобильных номеров. Но каждый ульяновец знает что по адресу rosstroymehcement73.ru нас будет ожидать ульяновский сайт =)
UPD: После важного комментария в Fb добавил к сравнению также вот такой вариант без дефиса:
1 2 3 4 5 6
SELECTCOUNT(*) FROM original WHERE name LIKE'ul%'; +----------+ |COUNT(*) | +----------+ |5852| +----------+
Тогда получается что такой вариант легко обходит даже сумму первых вариантов с префиксом с суффиксом 73. Не все конечно к Ульяновску относится, но все равно так точнее =)
P.S.
Для тех кто дочитал эту длинную телегу есть приятный бонус - SQL-база актуальная на сегодня. Там еще много всего интересного. А еще можно строить графики.
