Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770
Форумы портала PHP.SU :: Версия для печати :: Структура бд для статистики сайта
Форумы портала PHP.SU » PHP » SQL и Архитектура БД » Структура бд для статистики сайта

Страниц (1): [1]
 

1. kir55rus - 18 Октября, 2013 - 18:38:53 - перейти к сообщению
Здравствуйте

Сайтом пользуются N человек ( 10<N<500 )
Каждый из них может создать по 1-100 потоков для приема трафика
Нужно отслеживать статистику по этим потокам по след.пунктам:
-хиты
-просмотры
-уникальный клики по ссылке http://site[dot]ru/go/123
-все клики по этой ссылке
-источники трафика

С первыми 4-мя проблем не возникло. Создал 2 таблицы для этого:
CODE (SQL):
скопировать код в буфер обмена
  1. --
  2. -- Структура таблицы `ips`
  3. --
  4.  
  5. CREATE TABLE IF NOT EXISTS `ips` (
  6.   `id` int(11) NOT NULL AUTO_INCREMENT,
  7.   `id_flow` int(11) NOT NULL,
  8.   `type` int(1) NOT NULL,
  9.   `ip` int(11) UNSIGNED NOT NULL,
  10.   PRIMARY KEY (`id`)
  11. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
  12.  
  13. -- --------------------------------------------------------
  14.  
  15. --
  16. -- Структура таблицы `stats`
  17. --
  18.  
  19. CREATE TABLE IF NOT EXISTS `stats` (
  20.   `id` int(11) NOT NULL AUTO_INCREMENT,
  21.   `id_flow` int(11) NOT NULL,
  22.   `date` date NOT NULL,
  23.   `views` int(11) NOT NULL,
  24.   `hits` int(11) NOT NULL,
  25.   `a_clicks` int(11) NOT NULL,
  26.   `u_clicks` int(11) NOT NULL,
  27.   PRIMARY KEY (`id`)
  28. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


Исходник счетчика:
PHP:
скопировать код в буфер обмена
  1. //Статистика
  2.  
  3. $date = date("Y-m-d");
  4. $ip = sprintf('%u', ip2long($_SERVER["REMOTE_ADDR"]));
  5. $id_flow = $GLOBALS['id_flow'];
  6. $id_go = $GLOBALS['id_go'];
  7.  
  8. /*
  9. В БД поле `type` обозначает тип статистики:
  10. 0 - просмотры/хиты
  11. 1 - клики
  12. */
  13.  
  14. //Если это переход на целевую страницу
  15. if($id_go){
  16.        
  17.         //Запрашиваем статистику за сегодняшнее число для данного потока
  18.         $s_res = xsql("SELECT `id` FROM `stats` WHERE `date`='". $date ."' AND `id_flow`=". $id_flow);
  19.        
  20.         //Если данных нет, то это первый посетитель за сегодня. Чистим вчерашние Ip и создаем запись для сегодняшнего дня
  21.         if(mysql_num_rows($s_res) == 0){
  22.                 xsql("DELETE FROM `ips` WHERE `id_flow`=". $id_flow);
  23.                
  24.                 xsql("INSERT INTO `stats` (`id_flow`, `date`, `views`, `hits`, `a_clicks`, `u_clicks`) VALUES (". $id_flow .", '". $date ."', 1, 1, 1, 1)");
  25.                 xsql("INSERT INTO `ips` (`id_flow`, `type`, `ip`) VALUES (". $id_flow .", 1, ". $ip .")");
  26.                
  27.         }else{ //Если уже есть ячейка для сегодняшнего дня, обновляем ее
  28.                
  29.                 $s_row = mysql_fetch_assoc($s_res);
  30.                
  31.                 //Если это уникальный клик
  32.                 $sip_res = xsql("SELECT `id` FROM `ips` WHERE `ip`=". $ip ." AND `id_flow`=". $id_flow ." AND `type`=1");
  33.                 if(mysql_num_rows($sip_res) == 0){
  34.                        
  35.                         //Прибавляем +1 к уникальным и не уникальным кликам
  36.                         xsql("UPDATE `stats` SET `a_clicks`=`a_clicks`+1, `u_clicks`=`u_clicks`+1 WHERE `id`=". $s_row['id']);
  37.                        
  38.                         //Заносим ip в базу данных
  39.                         xsql("INSERT INTO `ips` (`id_flow`, `type`, `ip`) VALUES (". $id_flow .", 1, ". $ip .")");
  40.                 }else{//Если это не уникальный клик
  41.                        
  42.                         //Прибаляем +1 к не уникальным кликам
  43.                         xsql("UPDATE `stats` SET `a_clicks`=`a_clicks`+1 WHERE `id`=". $s_row['id']);
  44.                 }
  45.                
  46.         }
  47.        
  48.        
  49. }else{ //Если просмотр страницы приема трафика
  50.        
  51.         //Запрашиваем статистику за сегодняшнее число для данного потока
  52.         $s_res = xsql("SELECT `id` FROM `stats` WHERE `date`='". $date ."' AND `id_flow`=". $id_flow);
  53.        
  54.         //Если данных нет, то это первый посетитель за сегодня. Чистим вчерашние Ip и создаем запись для сегодняшнего дня
  55.         if(mysql_num_rows($s_res) == 0){
  56.                 xsql("DELETE FROM `ips` WHERE `id_flow`=". $id_flow);
  57.                
  58.                 xsql("INSERT INTO `stats` (`id_flow`, `date`, `views`, `hits`, `a_clicks`, `u_clicks`) VALUES (". $id_flow .", '". $date ."', 1, 1, 0, 0)");
  59.                 xsql("INSERT INTO `ips` (`id_flow`, `type`, `ip`) VALUES (". $id_flow .", 0, ". $ip .")");
  60.                
  61.         }else{ //Если уже есть ячейка для сегодняшнего дня, обновляем ее
  62.                
  63.                 $s_row = mysql_fetch_assoc($s_res);
  64.                
  65.                 //Если Это уникальный посетитель
  66.                 $sip_res = xsql("SELECT `id` FROM `ips` WHERE `ip`=". $ip ." AND `id_flow`=". $id_flow ." AND `type`=0");
  67.                 if(mysql_num_rows($sip_res) == 0){
  68.                        
  69.                         //Прибавляем +1 к просмотру и хиту
  70.                         xsql("UPDATE `stats` SET `views`=`views`+1, `hits`=`hits`+1 WHERE `id`=". $s_row['id']);
  71.                        
  72.                         //Заносим ip в базу данных
  73.                         xsql("INSERT INTO `ips` (`id_flow`, `type`, `ip`) VALUES (". $id_flow .", 0, ". $ip .")");
  74.                 }else{//Если это не уникальный посетитель
  75.                        
  76.                         //Прибаляем +1 к просмотрам
  77.                         xsql("UPDATE `stats` SET `views`=`views`+1 WHERE `id`=". $s_row['id']);
  78.                 }
  79.                
  80.         }
  81.        
  82.        
  83. }
  84.  


Этот код инклудится в скрипт отдачи страниц для приема трафика (Его содержимое не важно. За исключением 2-х моментов: $id_flow - поток трафика, который создает юзер, $id_go используется для того, чтобы отличить переход на целевую страницу от просмотра одностраничника)


Вот так собирается стата по просмотрам и кликам. Ее можно сортировать по дате (Например, просмотреть статистику с 1 по 20 числа)

Теперь вопрос. Как лучше прикрутить сюда сортировку по сайтам-источникам? Статистику по ним нужно будет сортировать по дням (Посмотреть, сколько с источника было уникальных посетителей, сколько кликов по ссылке за определенный срок). То есть, должны собираться первые 4 параметра!
Была идея создать таблицы: хиты, просмотры, уник.клики, все клики. А при просмотре статистики запрашивать количество записей. Но думаю, что это плохой вариант, и должен быть другой способ. Не будут ведь так хранить данные какие-нибудь гиганты статистик, как я.метрика, или партнерские программы, например, CPA-пп


В общем, как нужно сконструировать таблицы в бд для моих целей? Заранее спасибо
2. kir55rus - 19 Октября, 2013 - 05:59:52 - перейти к сообщению
Еще есть вариант, изменить таблицу stats, а именно добавить поле source
В этом поле будет храниться в формате json массив:
Array(
'Site1.ru' => array( 'views' => 20, 'hits' => 10, 'a_clicks' => 5, 'u_clicks' => 2),
'Site2.ru' => array( 'views' => 30, 'hits' => 20, 'a_clicks' => 6, 'u_clicks' => 4),
...
)
И каждый раз, при обновлении статистики, обновлять этот массив.
Алгоритм:
PHP:
скопировать код в буфер обмена
  1.  $sources = json_decode($row['source'], true);
  2. /* увеличение значений
  3. Например, $sources[$utm_content]['views']++;
  4. */
  5. $sources = json_encode($sources);
  6. //обновление записи в бд
  7.  


Но боюсь, что это даст большую нагрузку на сервер. Каждый раз кодировать и раскодировать из json. Есть ли еще вариант какой-нибудь?

 

Powered by ExBB FM 1.0 RC1