Форум: Форум PHPФорум ApacheФорум Регулярные ВыраженияФорум MySQLHTML+CSS+JavaScriptФорум FlashРазное
Новые темы: 0000000
Самоучитель PHP 5 / 6 (3 издание). Авторы: Кузнецов М.В., Симдянов И.В. Социальная инженерия и социальные хакеры. Авторы: Кузнецов М.В., Симдянов И.В. MySQL 5. В подлиннике. Авторы: Кузнецов М.В., Симдянов И.В. PHP на примерах (2 издание). Авторы: Кузнецов М.В., Симдянов И.В. MySQL на примерах. Авторы: Кузнецов М.В., Симдянов И.В.
ВСЕ НАШИ КНИГИ
Консультационный центр SoftTime

Форум MySQL

Выбрать другой форум

 

Здравствуйте, Посетитель!

вид форума:
Линейный форум Структурный форум

тема: Организация базы данных для ведения статистики
 
 автор: Phantom   (09.07.2009 в 04:35)   письмо автору
 
 

Если не вдаваться в тонкости проекта, то нужно следующее. Есть сайт с кучей поддоменов. На каждом поддомене есть по четыре страницы, назовём их 1.php , 2.php, 3.php и 4.php. Нужно как-то хранить в базе количество уникальных посещений на каждую страницу каждого поддомена и выводить статистику в админ панели в виде примерно такой таблицы:
____________________________________________
| Поддомен | 1.php | 2.php | 3.php | 4.php |
--------------------------------------------
| vasia    | 5     | 0     | 34    | 1     |
| petia    | 0     | 12    | 22    | 4     |
--------------------------------------------

Как определять уникальность посетителей, я придумаю. Мне нужно именно спроектировать базу данных и как-то осуществлять подсчёт статистики. С MySQL у меня тоговато. Не могу даже решить использовать одну таблицу или лучше четыре (под каждую страницу). И да, ещё, количество и названия поддоменов заранее не известно.

   
 
 автор: Valick   (09.07.2009 в 07:45)   письмо автору
 
   для: Phantom   (09.07.2009 в 04:35)
 

Одну таблицу.
id int(11);
p_dom varchar(255);
n_str varchar(255);
u_pos int(11);

примерно так

А лучше три таблицы
1 pdd
id_p int(11);
p_dom varchar(255);

2 stranic
id_s int(11);
n_str varchar(255);

3 stat
id int(11);
id_p int(11);
id_s int(11);
u_pos int(11);

примерно так

   
 
 автор: Phantom   (09.07.2009 в 07:53)   письмо автору
 
   для: Valick   (09.07.2009 в 07:45)
 

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

   
 
 автор: Valick   (09.07.2009 в 09:50)   письмо автору
 
   для: Phantom   (09.07.2009 в 07:53)
 

нужно сравнить, например, его идентификатор сессии с теми, что уже заходили на сайт
глупости, Вам нужно разобраться кто такие уникальные посетители
Но мне нужно именно так, как я описал в первом посте
Я Вам привёл примерный пример для первого поста не вдаваясь в подробности.
В базу будут записываться уникальные идентификаторы посетителей
это в самом простом случае ip, но ни как не идентификатор сессии и об этом Вы не говорили в первом посте. И даже в этом случае таблицы будут примерно такими же.

И оперделитесь наконец... нужно или не нужно вдаваться в подробности.

3 stat 
id int(11); - идентификатор записи (автоинкремент)
id_p int(11); - идентификатор поддомена
id_s int(11); - идентификатор страницы (! возможно нужна денормализация)
id_u int(11); - идентификатор юзера
u_pos int(11); - количество посещений определённым юзером определённой страницы определённого поддомена


4 users
id_u int(11); - уникальный идентификатор юзера
n_user varchar(255);

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

   
 
 автор: Phantom   (09.07.2009 в 10:06)   письмо автору
 
   для: Valick   (09.07.2009 в 09:50)
 

Какая разница как определять уникальность посетителя? Это не относится к данному вопросу никак. IP точно мне не подходит. Если дело в том, что в роли идентификатора нужно использовать число, так как поиск по числам происходит быстрее, то нет проблем, сделаю число.
В подробности вдаваться нужно. Не совсем понял структуру ваших таблиц. Что есть что? Я не знаю как посчитать и вывести статистику. Одним запросом или несколькими?

   
 
 автор: Valick   (09.07.2009 в 11:40)   письмо автору
 
   для: Phantom   (09.07.2009 в 10:06)
 

Не совсем понял структуру ваших таблиц.
что конкретно не понятно?
если совсем не понятно, то будем начинать с самого начала

   
 
 автор: Phantom   (09.07.2009 в 10:08)   письмо автору
 
   для: Valick   (09.07.2009 в 09:50)
 

Что такое денормализация?
(уже посмотрел в википедии, вспомнил)
id_p int(11); - идентификатор поддомена

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

   
 
 автор: Valick   (09.07.2009 в 11:33)   письмо автору
 
   для: Phantom   (09.07.2009 в 10:08)
 

А откуда я возьму
Так и хочется сказать "от верблюда"
как Вы собираетесь показывать статистику для поддоменов без самих поддоменов?)
Я полагаю идентификатор поддомена будет присваиваться автоинкрементом, при занесении имени поддомена в соответствующую таблицу, а уж как Вы будете его туда заносить и следить за актуальностью (добавлять/удалять) этой таблицы это совсем другая история.

   
 
 автор: Phantom   (09.07.2009 в 12:16)   письмо автору
 
   для: Valick   (09.07.2009 в 11:33)
 

Думаю, теперь соображу что как сделать. Просто изначально не думал из-за одной статистики воротить такую кучу таблиц. Если будут вопросы, отпишусь тут. Только скорее всего вечером, не раньше. Спасибо за помощь.

   
 
 автор: Trianon   (09.07.2009 в 14:19)   письмо автору
 
   для: Phantom   (09.07.2009 в 12:16)
 

Насчет кучи таблиц.
При аккуратной организации БД обычно требуется по одной таблице на каждую описываемую сущность, независимо от ттого, сколько экземпляров этой сущности существует в проекте
(И возможно еще по одной таблице на каждую многострочную (N:N) связку, если конечно саму эту связку отдельной сущностью не считать)

Вам предложили содать таблицы для сущностей
а) домена
б) страницы
в) посетителя
и связки
г) визит посетителя на страницу в домене

Как видите, будь у Вас хоть 20 страниц, хоть 300 - число таблиц от этого не вырастет.
Чего не сказать о Вашем втором варианте из "Не могу даже решить использовать одну таблицу или лучше четыре (под каждую страницу)."

   
 
 автор: Phantom   (10.07.2009 в 04:24)   письмо автору
 
   для: Trianon   (09.07.2009 в 14:19)
 

Одно теперь всё же непонятно. Теперь мне, чтобы получить количество посещений, нужно по одному запросу на каждую страницу в базу что ли посылать? Или можно как-то в одном запросе путём ввода пользовательских переменных в запросе и подсчёте средствами MySQL? Просто раньше я в таких случаях просто выполнял поиск по базе и при помощи функции PHP mysql_num_rows() получал количество найденных строк. Теперь же мне нужно получить число для каждой страницы. Если делать несколько запросов, то при каждом из них будет осуществляться поиск по одной и той же таблице, причём по всей целиком. Теоретически ведь можно как-то составить такой запрос, чтобы поиск по таблице осуществлялся один раз и подсчёт шёл в переменных, а потом они возвращались мне. Но я не настолько хорошо знаю MySQL, чтобы составить такой запрос.

   
 
 автор: Valick   (10.07.2009 в 08:30)   письмо автору
 
   для: Phantom   (10.07.2009 в 04:24)
 

и подсчёте средствами MySQL
количество посещений (г. визит посетителя на страницу в домене) у Вас уже должно быть подсчитано (средствами РНР вы определяете кто и куда зашёл и если такого юзера/домена/страницы нет, то добавляются новые строки, а если есть то обновляется уже существующая строка) его остаётся лишь извлечь.

   
 
 автор: Phantom   (10.07.2009 в 08:33)   письмо автору
 
   для: Valick   (10.07.2009 в 08:30)
 

Теперь я вообще ничего не понимаю.

   
 
 автор: Phantom   (10.07.2009 в 08:41)   письмо автору
 
   для: Phantom   (10.07.2009 в 08:33)
 

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

   
 
 автор: Valick   (10.07.2009 в 08:50)   письмо автору
 
   для: Phantom   (10.07.2009 в 08:41)
 

вы ничего не говорили про сегодня
если вам нужна ещё и статистика по дням, но нужно будет добавить в таблицу (г) ещё одно поле.
Не я предлагаю, а Вам придется при каждом посещении определять юзера и знать на каком он домене и на какой он странице и если такая запись в базе есть, то увеличивать счётчик на единицу
(либо каждый раз, либо в зависимости от промежутка времени минута, час, сутки, неделя, год, 100 или 1000 лет это зависит от того какая именно статистика Вам нужна).
а если он впервые куда-то зашёл, то добавлять соответствующие строки в соответствующих таблицах.

   
 
 автор: Phantom   (10.07.2009 в 08:56)   письмо автору
 
   для: Valick   (10.07.2009 в 08:50)
 

Разумеется статистика мне нужна по дням. Будет ещё временное поле. Я же максимально упростил вопрос тут в теме, чтобы не вдаваться в детали, которые мне и так понятны. Я не собирался держать в базе или где-либо ещё количество посетителей. По-моему, это как минимум неправильно! В базе я собираюсь держать записи по посетителям! А их количество будет просчитываться только тогда, когда это будет нужно! Я не представляю, как можно сделать иначе. Зачем при каждом посетителе лишний раз нагружать поиском базу, если можно просто записать новое посещение в неё и всё?

   
 
 автор: Valick   (10.07.2009 в 08:59)   письмо автору
 
   для: Phantom   (10.07.2009 в 08:41)
 

а можно использовать что-то типа буфера (ещё одна таблица куда сплошным потоком будут записываться ! домен ! страница ! юзер ! , а потом уже раз в час или сутки будут обрабатываться результаты и на их основе будут изменяться данные в основных таблицах, а буфер очищаться.

   
 
 автор: Phantom   (10.07.2009 в 10:28)   письмо автору
 
   для: Valick   (10.07.2009 в 08:59)
 

О! Вот этот способ мне нравится ;-) Так и сделаю. Это оптимальный вариант наверно. Ща всё переосмыслю.

   
 
 автор: Phantom   (12.07.2009 в 04:36)   письмо автору
 
   для: Valick   (10.07.2009 в 08:59)
 

Теперь не могу составить запрос на выборку данных.
Получилась у меня такая таблица:


`stat`
id    int(11); // автоинкремент
d_id  int(11); // id поддомена
p_id  int(11); // id страницы
date  date;    // Дата
count int(11); // Число посетителей в данный день по данному поддомену на данной странице


Мне нужно по указанному диапазону дат вернуть таблицу, которая указана в первом посте. Как это сделать в один запрос - не приложу ума. А если делать в несколько, то я даже боюсь представить сколько их будет. То есть нужно как-то в одном запросе сделать следующее:

1) Отобрать записи по диапазону дат
2) Выявить количество различных поддоменов в отобранных строках
3) Выявить количество страниц в отобранных строках
4) Сложить значение count для каждой повторяющейся пары поддомен/страница
5) И каким-то образом всё это вернуть в удобном виде, чтобы потом id'ы поддоменов и страниц заменить названиями из других таблиц и вывести на экран в виде html таблицы

Можно конечно сделать это всё в несколько запросов и считать каждое число в каждой ячейке в итоговой таблице отдельным запросом. То есть это будет выглядеть так:

1) Получаем список id'ов всех поддоменов за указанный период времени
2) В цикле отправляем по запросу для получения id'ов страниц для каждого поддомена отдельно и за указанный период времени
3) Внутри предыдущего цикла заводим ещё один и отправляем по запросу на каждый найденный поддомен/страницу для того, чтобы найти все значения count за указанный промежуток времени и сложить их для данной пары поддомен/страница

То есть если мы имеем 10 поддоменов и 4 страницы, то количество запросов будет 41! Это как минимум, если я чего-то не упускаю при написании этого всего! И при каждом из этих запросов будет проверяться вся таблица!

Есть ещё более идиотический вариант:

Отсылаем один запрос для получения ВСЕХ строк таблицы, которые подходят под данный период времени, и уже средствами PHP разгребаем всё это.

-------------------------------------------

Что прикажете делать?

   
 
 автор: Valick   (12.07.2009 в 06:11)   письмо автору
 
   для: Phantom   (12.07.2009 в 04:36)
 

где у Вас юзеры в таблице?
Будь моя воля, я бы Вам приказал книги читать ;)
Боюсь на "пальцах" разговаривать далее не имеет смысла, попробую к вечеру прикинуть эту байду "в цвете".

   
 
 автор: Phantom   (12.07.2009 в 06:20)   письмо автору
 
   для: Valick   (12.07.2009 в 06:11)
 

Мне нужно общий подсчёт, а не подсчёт количества посещений для каждого юзера. Все посещения будут записываться в отдельную таблицу и раз в час или полчаса информация в вышеописанной мною таблице будет изменяться. И после полуночи таблица с записями посещений будет очищаться. Что не так? По-моему всё правильно.

   
 
 автор: Valick   (12.07.2009 в 07:08)   письмо автору
 
   для: Phantom   (12.07.2009 в 06:20)
 

Мне нужно по указанному диапазону дат вернуть таблицу, которая указана в первом посте.
и
Мне нужно общий подсчёт, а не подсчёт количества посещений для каждого юзера.
как-то не вяжется.
Все посещения будут записываться в отдельную таблицу и раз в час или полчаса информация в вышеописанной мною таблице будет изменяться. И после полуночи таблица с записями посещений будет очищаться. Что не так? По-моему всё правильно.
Очищать таблицу нужно всякий раз как только анализируете её содержимое и записываете данные в исходную таблицу. Это раз в час или полчаса или в полночь (а так же перед выводом статистики в админке) в противном случае получите неправильную статистику.

   
 
 автор: Phantom   (12.07.2009 в 07:16)   письмо автору
 
   для: Valick   (12.07.2009 в 07:08)
 

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

Статистика мне нужна как можно более реалтаймовой. Поэтому информацию о посещениях в течении текущего дня нужно поддерживать как можно более актуальной.

Как тогда предложите оптимально всё организовать и, что самое главное, как извлекать информацию?

   
 
 автор: Phantom   (13.07.2009 в 01:39)   письмо автору
 
   для: Phantom   (12.07.2009 в 07:16)
 

Извините, что достал тут вас, но что мне делать-то? Помогите составить запрос на выборку по моим условиям. Я тогда уже сам его подгоню под свои нужды.

   
 
 автор: Valick   (13.07.2009 в 02:13)   письмо автору
 
   для: Phantom   (13.07.2009 в 01:39)
 

Извините, что достал тут вас,
если будете продолжать в том же духе, то вряд-ли достанете ;)
но что мне делать-то?
Вспомнить Карлсона "Спокойствие, только спокойствие."
Ну и немножечко терпения)

   
 
 автор: Phantom   (13.07.2009 в 02:20)   письмо автору
 
   для: Valick   (13.07.2009 в 02:13)
 

если будете продолжать в том же духе, то вряд-ли достанете ;)
В смысле? Это вы о том, что я тут только вечером и утром бываю и не успеваю достать? :-D
Вспомнить Карлсона "Спокойствие, только спокойствие."
Не, не, я спокоен, просто блин мне проект надо сдавать. Я думал, что статистику сделаю за пару часов, а тут такая шляпа вышла =( Никогда не приходилось делать ничего подобного. Никто из тех, с кем вместе работаем, не шарит в SQL настолько хорошо, чтобы составлять заумные запросы. Я как бы считаю, что каждый своим делом должен заниматься. А MySQL мне бывает нужен не столь часто. Я вообще не знаю, зачем взялся писать скрипт, уже почти год как разработкой J2me занимаюсь. А уж раз взялся и большую часть работы сделал, как бы отказываться уже некрасиво.

   
 
 автор: Trianon   (13.07.2009 в 01:49)   письмо автору
 
   для: Phantom   (12.07.2009 в 04:36)
 

1) SELECT * FROM `stat` WHERE `date` BETWEEN '$from' AND '$to'

2) SELECT COUNT(DISTINCT d_id) FROM `stat` WHERE `date` BETWEEN '$from' AND '$to'

3) SELECT COUNT(DISTINCT p_id) FROM `stat` WHERE `date` BETWEEN '$from' AND '$to'

4) SELECT d_id, p_id, SUM(`count`) FROM `stat` GROUP BY d_id, p_id WHERE `date` BETWEEN '$from' AND '$to'

5) SELECT * FROM (SELECT ...) as t
LEFT JOIN p_table ON t.p_id = d_table.id
LEFT JOIN p_table ON t.p_id = p_table.id

в путь.
По Вашим условиям.
Только потом крошить батон на valick'а и меня не надо.

   
 
 автор: Phantom   (13.07.2009 в 02:11)   письмо автору
 
   для: Trianon   (13.07.2009 в 01:49)
 

Спасибо! Сейчс буду вникать. Ни на кого ничего крошить не буду. На своих ошибках учиться проще. Сделаю, посмотрю что получится.

   
 
 автор: Phantom   (13.07.2009 в 02:23)   письмо автору
 
   для: Trianon   (13.07.2009 в 01:49)
 

в путь.
По Вашим условиям.

А в один запрос это всё свернуть никак не получится?
А что если создать отдельную таблицу для каждой пары поддомен/страница? Может это упростит задачу?

   
 
 автор: .   (13.07.2009 в 02:52)
 
   для: Phantom   (13.07.2009 в 02:23)
 

lol

   
 
 автор: 5675675   (13.07.2009 в 02:53)
 
   для: .   (13.07.2009 в 02:52)
 

567567

   
 
 автор: Trianon   (13.07.2009 в 09:04)   письмо автору
 
   для: Phantom   (13.07.2009 в 02:23)
 

>А что если создать отдельную таблицу для каждой пары поддомен/страница? Может это упростит задачу?

Это её только усложнит. (см. 09.07.2009 в 14:19... внимательно см.)

   
 
 автор: Phantom   (13.07.2009 в 09:09)   письмо автору
 
   для: Trianon   (13.07.2009 в 09:04)
 

Посмотрел. Не понял, что чего усложнит?

   
 
 автор: Trianon   (13.07.2009 в 09:12)   письмо автору
 
   для: Phantom   (13.07.2009 в 09:09)
 

я ответил на Ваш вопрос.

   
 
 автор: Phantom   (13.07.2009 в 08:39)   письмо автору
 
   для: Trianon   (13.07.2009 в 01:49)
 

Вместо этого:
2) SELECT COUNT(DISTINCT d_id) FROM `stat` WHERE `date` BETWEEN '$from' AND '$to'
3) SELECT COUNT(DISTINCT p_id) FROM `stat` WHERE `date` BETWEEN '$from' AND '$to'

Можно написать в одном запросе:
SELECT COUNT(DISTINCT d_id), COUNT(DISTINCT p_id) FROM `stat` WHERE `date` BETWEEN '$from' AND '$to'

Уже хоть какая-то оптимизация =) Только я вот сейчас думаю, а нафига мне нужно количество? О_о Я нить вчерашней мысли потерял =(

   
 
 автор: oradev   (13.07.2009 в 09:20)   письмо автору
 
   для: Phantom   (13.07.2009 в 08:39)
 

Это не оптимизация!
Использование DISTINCT как раз-таки напротив делает запрос не производительным!

   
 
 автор: Phantom   (13.07.2009 в 09:36)   письмо автору
 
   для: oradev   (13.07.2009 в 09:20)
 

Ну так а какая альтернатива? Как ещё можно посчитать?

   
 
 автор: Phantom   (13.07.2009 в 10:01)   письмо автору
 
   для: Trianon   (13.07.2009 в 01:49)
 

WOW!
4) SELECT d_id, p_id, SUM(`count`) FROM `stat` GROUP BY d_id, p_id WHERE `date` BETWEEN '$from' AND '$to'

Вот за это отдельное огромное спасибо! Этот запрос ведь возвращает как раз всё, что надо, разве нет? Только GROUP BY и WHERE вроде нужно местами поменять. Пошёл тестить производительность =)))

   
 
 автор: Trianon   (13.07.2009 в 10:07)   письмо автору
 
   для: Phantom   (13.07.2009 в 10:01)
 

насчет местами поменять - да, конечно.
ну .. если его встроить внутрь (5) то получите еще и имена доменов и страниц.

   
 
 автор: Phantom   (13.07.2009 в 10:15)   письмо автору
 
   для: Trianon   (13.07.2009 в 10:07)
 

В который раз мне кажется, что я всё понял и теперь уже точно сделаю эту статистику, но что-то мне подсказывает, что опять возникнут какие-то проблемы :-D Поэтому ждите моего возвращения *LOL*

   
 
 автор: Phantom   (13.07.2009 в 10:26)   письмо автору
 
   для: Trianon   (13.07.2009 в 10:07)
 

Сделал тестовую таблицу размером в миллион строк. Дата в ней одинаковая во всех строках. Запрос выполняется за 1.6 секунды. Это нормально или слишком медленно? Реально ли как-то ускорить?

   
 
 автор: Trianon   (13.07.2009 в 11:02)   письмо автору
 
   для: Phantom   (13.07.2009 в 10:26)
 

составной индекс на группирующих полях создан?
индекс на поле даты создан?

   
 
 автор: Phantom   (13.07.2009 в 11:05)   письмо автору
 
   для: Trianon   (13.07.2009 в 11:02)
 

О_о Что-что? О_о

   
 
 автор: Trianon   (13.07.2009 в 11:07)   письмо автору
 
   для: Phantom   (13.07.2009 в 11:05)
 

Для миллиона строк в таблице без индексов скорость вполне приличная.

А что, у Вас будет миллион записей приходиться на одну дату?

   
 
 автор: Phantom   (13.07.2009 в 11:10)   письмо автору
 
   для: Trianon   (13.07.2009 в 11:07)
 

*PARDON* не знаю, что за индексы.

   
 
 автор: Phantom   (13.07.2009 в 11:32)   письмо автору
 
   для: Trianon   (13.07.2009 в 11:07)
 

Нет, нет. Я тут прикинул, миллион записей в этой таблице скопится примерно за 70 лет. Так что я думаю, что дальнейшая оптимизация тут не обязательна. Правда, я пока ещё с пятым запросом не объединил. Так, я ушёл делать и тестировать дальше всякие мелочи с запросами. Ещё вернусь :-D

   
 
 автор: Phantom   (14.07.2009 в 06:55)   письмо автору
 
   для: Trianon   (13.07.2009 в 11:07)
 

Не могу понять конструкцию пятого запроса:
5) SELECT * FROM (SELECT ...) as t 
LEFT JOIN p_table ON t.p_id = d_table.id
LEFT JOIN p_table ON t.p_id = p_table.id

Разве в секцию FROM можно загнать целый запрос SELECT?
------
Всё проверил, скрестил запросы, настроил под свой скрипт. Работает! Скорость практически не уменьшилась после скрещения запросов. Теперь нужно как-то эту таблицу обновлять... :-D Сейчас буду думать.

   
 
 автор: Valick   (14.07.2009 в 08:52)   письмо автору
 
   для: Phantom   (14.07.2009 в 06:55)
 

Разве в секцию FROM можно загнать целый запрос SELECT?
в секцию FROM записывают что? - таблицу..
а результатом работы SELECT является что? - правильно таблица)

   
 
 автор: Phantom   (14.07.2009 в 09:12)   письмо автору
 
   для: Valick   (14.07.2009 в 08:52)
 

Круто. У меня прямо просветление. Много нового узнал =) Я вот теперь думаю, как организовать статистику более реалтаймовой. Что если в отдельную таблицу записывать поддомен, страницу, и уникальный id юзера, а потом, например, какждые 15 или 30 минут проверять эту таблицу и делать пересчёт статиситки на основании новых посещений. Примерно схема такая:
Скрипт считает количество записей по каждому поддомену и странице, начиная с указанного id'а и вносит изменения в конечную таблицу статистики. Также скрипт записывает в файл на сервере автоинкрементный id последней строки таблицы. Спустя 15 (или 30) минут, скрипт снова делает то же самое и берёт id из файла. И так до бесконечности. А после полуночи все записи из таблицы будут удаляться. Тут никаких потерь посещений не будет. И лишних тоже. Как вам такая система?

   
 
 автор: Phantom   (14.07.2009 в 09:15)   письмо автору
 
   для: Phantom   (14.07.2009 в 09:12)
 

Небольшая поправка к схеме. Пересчёт статистики можно делать не каждые 15-30 минут, а только тогда, когда админ захочет посмотреть статистику. Зачем лишний раз базу гонять?

   
 
 автор: Valick   (14.07.2009 в 10:12)   письмо автору
 
   для: Phantom   (14.07.2009 в 09:15)
 

Пересчёт статистики можно делать не каждые 15-30 минут, а только тогда, когда админ захочет посмотреть статистику
не совсем так... но уже лучше.
как только админ захочет посмотреть статистику обрабатывать временную таблицу (буфер) нужно в обязательном порядке. Это обеспечит вашу так сказать реалтаймовость по русски сказать оперативность. Но вот нафиг вам оставлять "живой" временную таблицу (буфер!) после того как вы её обработали и в следующий раз заморачиваться с id я так и не могу понять. Периодическая обработка (и уничтожение) временной таблицы (буфера!!!) нужна лишь для снижения нагрузки на БД она может быть не только раз в 15 или 30 минут, но и по любому другому условию (например после 5000 посетителей и до следующих 5000 и тд.)

   
 
 автор: Phantom   (14.07.2009 в 10:23)   письмо автору
 
   для: Valick   (14.07.2009 в 10:12)
 

Но вот нафиг вам оставлять "живой" временную таблицу (буфер!) после того как вы её обработали и в следующий раз заморачиваться с id я так и не могу понять.
Так ведь по ней я буду определять является ли очередной зашедший посетитель уникальным или же он уже есть в таблице, что означает, что он не в первый раз зашёл на сайт.

   
 
 автор: Valick   (14.07.2009 в 10:41)   письмо автору
 
   для: Phantom   (14.07.2009 в 10:23)
 

Зачем возвращаться "к нашим баранам"? те. к тому от чего мы хотели уйти...
в буфер Вы пишете куда когда и кто зашёл сплошным потоком без определения уникальности... и без запроса выборки к базе, грубо говоря сплошные INSERT, а уж уникальность нужно определять в момент обработки временной таблицы (и настаиваю уничтожения!!! точнее обнуления))) )

   
 
 автор: Phantom   (14.07.2009 в 10:44)   письмо автору
 
   для: Valick   (14.07.2009 в 10:41)
 

Так под уникальных теперь что, ещё одну таблицу заводить?

   
 
 автор: Valick   (14.07.2009 в 12:31)   письмо автору
 
   для: Phantom   (14.07.2009 в 10:44)
 

нет :)
Основная таблица Вам для "мебели"?)
Принцып такой:
Лопатите временную таблицу, на предмет уникальных юзеров остальные отбрасываете (хотя лично я бы считал все посещения юзера для каждой страницы)
На основе полученного лопатите основную таблицу

   
 
 автор: Phantom   (14.07.2009 в 12:43)   письмо автору
 
   для: Valick   (14.07.2009 в 12:31)
 

Чувствую, что не понимаю =( Распишите поподробнее, чётко по пунктам русским языком =)

   
 
 автор: Phantom   (14.07.2009 в 15:04)   письмо автору
 
   для: Valick   (14.07.2009 в 12:31)
 

Сколько ни думал, не могу понять ваш вариант. Зачем лопатить основную таблицу? В ней только цифры нужно будет поменять для текущего дня. Основная таблица нужна, чтобы хранить количество хостов за всё время существования сайта. Чтобы за любой день можно было посмотреть. Или за временной диапазон.
Чем моя схема отличается от вашей?
1) В второстепенную таблицу записываем потоком каждое посещение любого юзера.
2) Как только админ зашёл смотреть статистику, считаем количество уникальных посетителей и обновляем основную таблицу.
3) Как только настала полночь, делаем всё то же, что в пункте два, а после очищаем второстепенную таблицу.

   
 
 автор: Valick   (14.07.2009 в 16:18)   письмо автору
 
   для: Phantom   (14.07.2009 в 15:04)
 

Очищать таблицу нужно чтобы единожды обработанные данные не обрабатывались повторно и не добавлялись в основную таблицу искажая статистику. Но если Вам хочется заморачиваться и следить сколько было и сколько добавилось юзеров между запросами админа или от запроса админа до полночи, то наздоровье. Это гигаГемор.
В начале нашего разговора Вы обмолвились, что определение уникальности юзера это "как два пальца пососать", да вот фигушки :) Помнится Вы весь этот скрипт хотели за пару часов написать...
Скачайте PowerCounter (или какой нибудь ещё счётчик) смотрите исходники анализируйте, скорее всего найдёте вектор нужного для Вас решения. А может и сам PowerCounter приспособите для своих нужд.

   
 
 автор: Trianon   (14.07.2009 в 16:27)   письмо автору
 
   для: Valick   (14.07.2009 в 16:18)
 

В PowerCounter 40 таблиц.

   
 
 автор: Phantom   (14.07.2009 в 16:32)   письмо автору
 
   для: Valick   (14.07.2009 в 16:18)
 

Да я итак уже много времени убил, некогда в чужих скриптах лазить. Тем более в 40 таблицах. На счёт уникальности: мне не нужна 100% правильная статистика. Достаточно считать посетителя уникальным за период его сессии. А уж коли нужно число, то можно, например, рассчитать crc32 от его иникатора сессии - вот и будет четырёхбайтовое число. Его и буду хранить в базе. Не вижу ни единого факта, который бы указывал на то, что мне так делать не стоит. Даже если вдруг у двух разных юзеров совпадёт контрольная сумма, то не велика потеря.

   
 
 автор: Valick   (14.07.2009 в 17:08)   письмо автору
 
   для: Phantom   (14.07.2009 в 16:32)
 

а то что у одного юзера может быть много сессий это "не колышит"?
Ну раз не видите фактов, то делайте как есть

   
 
 автор: Phantom   (14.07.2009 в 17:24)   письмо автору
 
   для: Valick   (14.07.2009 в 17:08)
 

а то что у одного юзера может быть много сессий это "не колышит"?
Совершенно не колышит =) В крайнем случае сделаю ещё куку на длительное время и усложню скрипт, но не думаю, что это потребуется. Сайт довльно специфичный, он не рассчитан на постоянных посетителей, которые будут снова и снова туда возвращаться.

   
 
 автор: Phantom   (16.07.2009 в 08:38)   письмо автору
 
   для: Valick   (14.07.2009 в 17:08)
 

Я вернулся. :-D Я вот что решил. Я не буду обновлять данные в основной таблице от запроса до запроса админа. Я просто буду их на лету считать и складывать с теми, что находятся в основной таблице. Придётся ещё усложнить запрос. Ну а после полуночи будет контрольный просчёт и запись в "архив", то есть в основную таблицу. После чего второстепенная таблица будет очищаться. На повестке дня такой вопрос: как мне после полуночи при помощи INSERT занести сразу все нужные мне значения? Конечно, я опять представляю быдлоспособ, при котором сначала селектом выбираются в многомерный массив количество хостов для кажого поддомена/страницы, а потом кучей инсертов в цикле это всё заносится в основную таблицу. Но ведь наверняка MySQL способна скушать какой-то хитрый запрос, который всё сам просчитает и занесёт в таблицу? Я как ни кумекал, не могу объединить SELECT и INSERT в один запрос. =(

   
 
 автор: Valick   (16.07.2009 в 10:34)   письмо автору
 
   для: Phantom   (16.07.2009 в 08:38)
 

INSERT INTO имя_таблицы [(имена_столбцов, ...)] SELECT имена_столбцов, ... FROM имя_таблицы, ... [WHERE ...];

осталось только селектом выбрать результирующую таблицу в которой поля совпадают с полями основной таблицы

   
 
 автор: Phantom   (16.07.2009 в 10:46)   письмо автору
 
   для: Valick   (16.07.2009 в 10:34)
 

Спасибо =) Попробую, отпишусь.

   
 
 автор: Phantom   (17.07.2009 в 07:33)   письмо автору
 
   для: Valick   (16.07.2009 в 10:34)
 

А как выбрать уникальную пару поддомен/страница из таблицы? И для каждой пары нужно посчитать количество уникальных значений из столбца с посетителями. Посмотрел в гугле, кто с подобной задачей сталкивался, рекомендуют делать либо промежуточную таблицу, либо конструкцию типа:
(SELECT stolbets1 FROM tablename) UNION (SELECT stolbets2 FROM tablename)
Но что-то мне ни то, ни другое не нравится. разве нельзя как-то проще? Тем более мне-то ещё нужно посчитать количество значений в третьем столбце.

   
 
 автор: Valick   (17.07.2009 в 15:15)   письмо автору
4.5 Кб
 
   для: Phantom   (17.07.2009 в 07:33)
 

Вот черновой вариант того о чём я говорил.

   
 
 автор: Trianon   (17.07.2009 в 15:20)   письмо автору
 
   для: Phantom   (17.07.2009 в 07:33)
 

SELECT dom, page, COUNT(client) FROM tbl GROUP BY dom, page, client

   
 
 автор: Phantom   (17.07.2009 в 15:55)   письмо автору
 
   для: Trianon   (17.07.2009 в 15:20)
 

Trianon, Ой, что-то я забыл про GROUP BY *PARDON*
Valick, спасибо большое! Только я наверно завтра буду разбираться, устал сегодня и спать наверно пойду. Отпишусь завтра.

   
 
 автор: Valick   (17.07.2009 в 16:13)   письмо автору
 
   для: Phantom   (17.07.2009 в 15:55)
 

Создаёте базу, прописываете в config.php
создаёте таблицы - create.php
инклюжите count.php (половину своровал из ПоверКоунтера) к страницам которые нужно учитывать.
lopata.php - запускаете когда нужна обработка буфера
stat.php - смотрите статистику.

   
 
 автор: Phantom   (18.07.2009 в 13:09)   письмо автору
 
   для: Valick   (17.07.2009 в 16:13)
 

Что то в вашем скрипте куча ошибок. :-D Уже несколько запросов подправил и всё равно работает как-то не так. У вас поле даты в таблице названо `puttime`, а в некоторых запросах ипользуется `date`, которого в таблицах просто-напросто нет. Почему lopata.php инклудится в stat.php? Ведь лопата очищает буферную таблицу. А очищать её надо после полуночи, а не тогда, когда статистику смотрим. Пока вообще мало чего понимаю. Смотрю дальше.
-----
Ещё в лопате в одном запросе инсерт перепутаны местами дата и количество посетителей. Исправил, вроде заработало. А что за скрипт quest.php?
-----
Убрал строку
mysql_query("TRUNCATE TABLE buffer");

из лопаты. Теперь вроде всё работает именно как задумывалось. Эта строка нужна только при вызове лопаты после полуночи. Но опять же вы перестарались :-D Мне не нужно считать хиты, я же говорил. Мне нужны только хосты. И таблица в статистике такая, как я написал в первом посте. Именно так нужно. Обусловлено концепцией проекта.
-----
Ой, вру. У вас ведь юзеры хранятся в отдельной таблице. Что-то я запутался.

   
 
 автор: Valick   (18.07.2009 в 14:11)   письмо автору
 
   для: Phantom   (18.07.2009 в 13:09)
 

очищать её надо после полуночи, а не тогда, когда статистику смотрим
Это у вас так, а у меня очищается буфер сразу как только данные из него обработаны и никаких послеполуночи. И не важно сколько раз и когда вызывать лопату.
И инклудиться лопата в скрипт статистики, для того чтобы обеспечить реалтаймовость данных.
Мне не нужно считать хиты, я же говорил. Мне нужны только хосты.
а в чём проблема-то? просто не выводите их на экран))

   
 
 автор: Phantom   (18.07.2009 в 14:58)   письмо автору
 
   для: Valick   (18.07.2009 в 14:11)
 

Ещё не факт, что я все баги исправил :-D
А почему у вас не предусмотрено очищение таблицы с юзерами? Или в вашу идею входит считание уникальных хостов за любой период времени? Обычно вроде когда за период временной выводят статистику, то складывают хосты за каждые сутки. То есть если один и тот же человек зашёл в разные сутки, то в каждые из них он является уникальным.

   
 
 автор: Valick   (19.07.2009 в 10:35)   письмо автору
 
   для: Phantom   (18.07.2009 в 14:58)
 

А почему у вас не предусмотрено очищение таблицы с юзерами?
А потому, что этого делать ни в коем случае нельзя, так как эта таблица связана с основной таблицей. Очистить таблицу можно только тогда когда вы соберётесь очищать и основную таблицу статистики, предварительно обработав информацию из неё и запишите в единственном варианте, но обратной дороги не будет*.
То есть если один и тот же человек зашёл в разные сутки, то в каждые из них он является уникальным.
А у меня разве не так? Считаются количество посещений одного юзера за сутки (и записываются в поле соответствующей строки), следующие сутки - это новая строка и новая дата. Но очищать таблицу с юзерами, чтобы потом опять занести этого же юзера абсолютно не нужно.(а пока существует связь с другой таблицей, даже преступно)
Или в вашу идею входит считание уникальных хостов за любой период времени?
Совершенно верно... сегодня вы захотели одно, завтра другое - так пожалуйста, и не нужно ничего менять в структуре БД, извлекайте информацию в каком угодно виде.
___
единственное определение юзера упрощено, напримар дома у меня из под 64 Висты и из под 32 ХР скрипт думает что это один и тот же юзер.(хотя это физически так и есть, но лучше более детально распарсить строку юзер агента, хотя от корпоративных клиентов это не спасёт, они будут считаться как "одно целое".)

   
 
 автор: Phantom   (19.07.2009 в 11:10)   письмо автору
 
   для: Valick   (19.07.2009 в 10:35)
 

Почему бы не рассчитывать md5 от всей строки юзер агента? Зачем отдельно выделять браузер и ОС?
И ещё на счёт таблицы юзеров: если её никогда не очищать, она же забьётся быстро.

   
 
 автор: Valick   (19.07.2009 в 11:32)   письмо автору
 
   для: Phantom   (19.07.2009 в 11:10)
 

1) в данном случае можно и от всей строки.
2) не быстро, основная таблица будет гораздо больше

   
 
 автор: Phantom   (19.07.2009 в 11:40)   письмо автору
 
   для: Valick   (19.07.2009 в 11:32)
 

Так, ладно. Ща напишу класс на основе твоего примера.

   
 
 автор: Phantom   (20.07.2009 в 01:11)   письмо автору
 
   для: Valick   (19.07.2009 в 11:32)
 

Почему в лопате в основную таблицу заносится дата без времени, а тип поля стоит TIMESTAMP? Будет ведь всё равно записываться дата с нулевым временем. Наверно лучше сделать тип DATE. Если я ошибаюсь, то объясните в чём. Ещё не понимаю, зачем хранить время в каждой таблице. Время вроде нужно только в буферной и основной таблицах.

   
 
 автор: Valick   (20.07.2009 в 03:29)   письмо автору
 
   для: Phantom   (20.07.2009 в 01:11)
 

в каждой для прикола, а в основной действительно должно быть DATE, просто видимо забыл исправить

   
 
 автор: Phantom   (20.07.2009 в 04:33)   письмо автору
 
   для: Valick   (20.07.2009 в 03:29)
 

То есть из остальных таблиц можно убрать время?
-------------------------------
Класс в принципе готов. Вот только если его использовать универсально, то наверно нет смысла делать в нём метод для получения статистики. Ведь статистику может потребоваться получить в различном виде, не писать же под каждый запрос новый метод. Под свои нужды я уже сделал, осталось теперь переписать основной скрипт под этот класс и всё должно будет работать. Сделал метод addVisit($domain,$page,$uid) для добавления нового посещения. Я решил, что лучше не делать определение домена, страницы и уникального ида юзера внутри класса, а лучше передавать в качестве параметров метода. Теперь вместо домена например можно записывать в базу что-то другое, мало ли какую статистику придётся вести. А вместо браузера и IP можно определить уникальность каким-нибудь другим способом, например по сессии+куки. В общем, возможно скрипт ещё пригодится где-то в будущем =)
--------------------------------
Не могу найти как задать дефолтное значение DATE в виде текущей даты. То есть нужен аналог CURRENT_TIMESTAMP, но чтобы дату возвращал.

   
 
 автор: Valick   (20.07.2009 в 07:22)   письмо автору
 
   для: Phantom   (20.07.2009 в 04:33)
 

Не могу найти как задать дефолтное значение DATE в виде текущей даты. То есть нужен аналог CURRENT_TIMESTAMP, но чтобы дату возвращал.
это ещё зачем?
А вместо браузера и IP можно определить уникальность каким-нибудь другим способом
я бы рекомендовал вам оставить как есть ;)

   
 
 автор: Phantom   (20.07.2009 в 07:43)   письмо автору
 
   для: Valick   (20.07.2009 в 07:22)
 

это ещё зачем?
Ну у вас там в таблицах так указано, вот я и решил в главной таблице тип сменить на DATE и по аналогии сделать, чтобы как везде было.
я бы рекомендовал вам оставить как есть ;)
Если оставлять как есть, то все оперы мини определятся как один и тот же юзер.

   
 
 автор: Valick   (20.07.2009 в 08:03)   письмо автору
 
   для: Phantom   (20.07.2009 в 07:43)
 

Если оставлять как есть, то все оперы мини определятся как один и тот же юзер.
вряд ли у них будет одинаковый IP
Ну у вас там в таблицах так указано, вот я и решил в главной таблице тип сменить на DATE
во всех TIMESTAMP в основной таблице было и DATE (заносим руками в процессе обработки буфера) и TIMESTAMP - который я потом убрал, так как не особо ценная информация.

   
 
 автор: Phantom   (20.07.2009 в 08:39)   письмо автору
 
   для: Valick   (20.07.2009 в 08:03)
 

вряд ли у них будет одинаковый IP
Как раз будет. Так как у оперы мини определяется IP сервера, а серверов хоть и больше одного, но всё равно не много.
во всех TIMESTAMP в основной таблице было и DATE (заносим руками в процессе обработки буфера) и TIMESTAMP - который я потом убрал, так как не особо ценная информация.
Так я могу из остальных таблиц, кроме основной и буферной убрать поле с временем? Оно по-моему вообще там не нужно.

   
 
 автор: Valick   (20.07.2009 в 09:25)   письмо автору
 
   для: Phantom   (20.07.2009 в 08:39)
 

ну раз вам не нужно, то убирайте. вообще в этих таблицах может быть всё что угодно(соответственно касающееся юзера или домена), а время там для того чтобы например знать когда юзер впервые засветился в вашей статистике и например если за относительно короткий промежуток времени слишком много посещений, то это либо корпоративчик, либо бот накрутчик... ну примерно так.
ну а с мини операми и еже с ними это уж думайте как их различать
но ещё раз повторяю IP как составляющую, я бы оставил.
и ещё можно куку шифрованную сохранять (id + ещё что-нибудь) клиенту и чтобы это было приоритетное опознавание, если есть кука, то идентификация по ней, а если нет куки, а хеш уже присутствует в базе, то это либо тот же клиент, либо (что вероятнее) это уже другой юзер.
Ну а с зарегистрированными юзерами, вообще всё просто...

   
 
 автор: Phantom   (13.07.2009 в 10:54)   письмо автору
 
   для: Trianon   (13.07.2009 в 10:07)
 

Так, а можно как-нибудь заставить MySQL возвращать двумерный массив?

   
Rambler's Top100
вверх

Rambler's Top100 Яндекс.Метрика Яндекс цитирования