Форум пользователей MySQL

Doxtor · 06.08.2009 14:13:09

Дoбрый.
Читаю и ищу уже второй день, к сожалению безрезультатно. На MySQL сделал не много и несложно, но есть желание разобраться в теме глубже.

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

Поделил задачу на следующие шаги:

1. Реализовать сбор статистики кликов по каждому артикулу в день.
Как я себе это представляю, логичнее создать отдельную таблицу с полями: id, data, 1, 2, 3, 4, 5, 6, 7…
Где цифры- id артиклей. Их будет не более 400, пока 300
Каждая новая строка -это новая дата.
Средствами PHP будут считаться клики и определяться есть ли в таблице сегоднешняя дата. До этого момента пока всё ясно.

Проблема 1: как реализовать добавление новой колонки в таблицу и обозвать это поле так же как и id арптикула.
т.е. это работает... тут всё понятно:

<?php
mysql_query("

 ALTER TABLE stat ADD id  VARCHAR( 255 ) NOT NULL

", $db) ;
?>

а нужно примерно так:

<?php
mysql_query("

 ALTER TABLE stat ADD $id VARCHAR( 255 ) NOT NULL

", $db) ;
?>
Возможно ли это?
Если это не возможно остаётся возможность добавлять эти поля вручную, что конечно не есть оптимально.
Допустим получили такую таблицу. Я продолжу.

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

Если есть другое решение всей задачи прошу тоже предлагать

paulus · 06.08.2009 14:41:48

Очень сложная структура таблицы, попробуйте хранить клики в виде «таблички-лога»
CREATE TABLE clickstat (id INT NOT NULL, ts TIMESTAMP, PRIMARY KEY (ts, id));

Как чуть более сложный вариант (но куда более экономный по ресурсам):
CREATE TABLE clickstat (id INT NOT NULL, ts DATE NOT NULL, cnt INT NOT NULL, PRIMARY KEY (ts, id));

В первом случае на каждый клик добавляется дополнительная строчка, во втором случае —
добавляется только в случае отсутствия такого же артикула за тот же день:
INSERT INTO clickstat VALUES ($article_id, CURRENT_DATE(), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

Для таких табличек окончательные группировки пишутся прямо «как думается»
SELECT id, SUM(cnt)
FROM clickstat
WHERE ts > DATE() - INTERVAL 3 MONTH
GROUP BY 1;

vasya · 06.08.2009 15:26:44

Только вместо DATE() следует писать DATE(now())

Doxtor написал:
Дoбрый.
Читаю и ищу уже второй день, к сожалению безрезультатно. На MySQL сделал не много и несложно, но есть желание разобраться в теме глубже.

В качестве разобраться в теме касательно первого вопроса. Синтаксис SQL не позволяет использовать переменные в указанном вами запросе. Имя столбца должно быть константой. Для преодоления этого ограничения используют подготовленные выражения. См. обсуждение http://sqlinfo.ru/forum/viewtopic.php?id=363

paulus · 06.08.2009 15:37:23

Дада, я имел в виду CURRENT_DATE(), а не DATE(), конечно же. Исправил inplace.
--
По поводу переменных: там переменная подставляется из PHP, так что с точки зрения
MySQL это будет константой.

Doxtor · 06.08.2009 18:42:04

Спасибо.
С созданием таблицы всё понятно, взял второй вариант.

А вот это никак не хочет идти:

INSERT INTO clickstat VALUES ($article_id, DATE(), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

Пишу для проверки в phpmyadmin:

INSERT INTO clickstat VALUES (1, DATE(), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

phpmyadmin ругается:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1' at line 1

Отредактированно Doxtor (06.08.2009 18:42:59)

Doxtor · 06.08.2009 18:49:10

Спасибо.
На дату теперь не ругается, зато ругается на "ON DUPLICATE KEY UPDATE cnt = cnt + 1' at line 1 "

vasya · 06.08.2009 18:57:29

А какая у вас версия MySQL?

Doxtor · 06.08.2009 19:30:13

MySQL-Client-Version: 5.0.60

Может там какой запятой не хватает?
Продолжаю кликать на гугл.

Отредактированно Doxtor (06.08.2009 21:21:16)

paulus · 06.08.2009 21:26:01

Клиент — это ладно, какой версии сервер?
У меня замечательно работает. Попробуйте скопировать-и-вставить код, может, где-то опечатываетесь?

Doxtor · 07.08.2009 14:42:54

Был 3-й MySQL, поменял на 4.1 и всё заработало.
Т.е. Сбор статистики работает замечательно.

Подитожу(раз уж раздел для начинающих):

Для сбора статистики кликов по артикулам, статьям, заметкам и пр.

1. Создать таблицу:

CREATE TABLE clickstat (id INT NOT NULL, ts DATE NOT NULL, cnt INT NOT NULL, PRIMARY KEY (ts, id));

 

2. На странице артикула, статьи, заметки и пр. вставить код

<?php

mysql_query("INSERT INTO clickstat VALUES ('$id', CURRENT_DATE(), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1", $db);

?>

3. На всякий случай проверить версию MySQL. Должен быть не ниже 4.1

4. Вывод данных выглядит следущим образом:

SELECT id, SUM(cnt)

FROM clickstat

WHERE ts > CURRENT_DATE() - INTERVAL 3 MONTH

GROUP BY 1;

P.S Форуму спасибо, в закладки, продолжаю жать на яндекс.

Отредактированно Doxtor (07.08.2009 15:08:27)

Doxtor · 07.08.2009 19:06:36

Я не сдался ещё, но уже близок к тому.

Что имеем:
1-я таблица с артикулами, у артикулов есть id
2-я таблица CREATE TABLE clickstat (id INT NOT NULL, ts DATE NOT NULL, cnt INT NOT NULL, PRIMARY KEY (ts, id));

Вынуть из таб.2 сумму кликов за N месяцев и отсортировать по id меня уже научили

А хочется вынуть артикулы из таб.1 и отсортировать по сумме кликов за N месяцев из таб2.

Вот такая задача. Заранее спасибо.

paulus · 07.08.2009 20:08:14

SELECT f.id, SUM(c.cnt)
FROM firsttable f LEFT JOIN clickstat c ON f.id = c.id
WHERE c.ts > CURRENT_DATE() - INTERVAL 3 MONTH
GROUP BY 1;

Doxtor · 07.08.2009 21:51:09

Спасибо, проверю уже в понедельник.
Хотел просто уточнить: если первая таблица- node, а вторая- stat, то выборка выглядит так:

SELECT node.id, SUM(stat.cnt)

FROM node LEFT JOIN stat ON node.id = stat.id

WHERE stat.ts > CURRENT_DATE() - INTERVAL 3 MONTH

GROUP BY 1;

??????

paulus · 09.08.2009 12:11:35

Да

Doxtor · 10.08.2009 11:49:19

Работает, но сортирует по GROUP BY 1, т.е. по id, а нужно по SUM( stat.cnt )
Возможно ли это?

paulus · 10.08.2009 13:45:58

ORDER BY 2 в конец выражения еще допишите Или ORDER BY 2 DESC, если
в обратном порядке.

Doxtor · 10.08.2009 17:57:09

Ступил, знал же, огромное спасибо. Всё работает. Ура!!!
Вот окончательный вариант с выводом и выборкой по категории, в этом случае из категории 14
Переменные $start, $num - постраничная навигация.
Остальное вроде должно быть понятно.

<?php 

$result = mysql_query("SELECT node.id, node.title, node.nummer, node.tieser, SUM(stat.cnt)

FROM node LEFT JOIN stat ON node.id = stat.id

WHERE stat.ts > CURRENT_DATE() - INTERVAL 3 MONTH

AND node.kat14='1'

GROUP BY 1

ORDER BY 5 DESC

LIMIT $start, $num", $db);

if(mysql_num_rows($result) > 0)

{

$myrow = mysql_fetch_array($result);}

do{

printf ("<a href='artikel/%s'><img class='midi' src='%s' title='%s'></a> 

<table>

  <tr>

     <td><span class='title_tieser'><a href='artikel/%s' title='%s'>%s</a></span>

     <p class='tieser'>%s</p>

     </td>

  </tr>

  <tr>

     <td><a href='angebote/artikel/%s' rel='nofollow'>Weiter...</a></p></td>

  </tr>

</table>",$myrow["id"],$myrow["midi"],$myrow["title"],$myrow["id"],$myrow["SUM(stat.cnt)"],$myrow["title"],$myrow["tieser"],$myrow["id"] );

}

   while ($myrow =  mysql_fetch_array($result));

?>

Отредактированно Doxtor (10.08.2009 17:59:00)

paulus · 10.08.2009 18:41:58

Можно еще немножко упростить логику, если писать

$result = mysql_query ("...");

while ($myrow = mysql_fetch_assoc($result)) {

  printf (...);

}

printf очень порадовал, обычно в скриптах используют print ("... $myrow['midi'] ... ");

Doxtor · 11.08.2009 15:05:11

Спасибо за совет.
Но тут я поступлю по принципу: работет- не трогай.

Т.к. стабильностью и скоростью я пока был доволен, а 100К уников в день в обозримом будующем не ожидаем.
Или насколько Ваш вариант лучше, быстрее и т.д.?

paulus · 11.08.2009 18:47:25

Он не сильно быстрее, просто меньше вызовов, меньше дублирования кода,
легче читается

Doxtor · 21.08.2009 18:14:47

У меня такой глупый вопрос:

а если бот заходит на страницу, то клики всё равно прибавляются?

Если ДА, то как это можно избежать?

Напомню, речь идёт вот об этой части кода:

INSERT INTO clickstat VALUES ($article_id, CURRENT_DATE(), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

Заранее спасибо.

paulus · 21.08.2009 18:26:05

Избежать можно путем не прибавления кликов в случае прихода бота

Боты не отличаются ничем от людей. Разве что обычно они приходят с заголовком HTTP
User-Agent специфичным для конкретного бота. Впрочем, ничто не мешает людям приходить
с такими же заголовками, или, наоборот, ботам приходить с людскими.

Отлавливание ботов — процесс увлекательный и интересный

Doxtor · 21.08.2009 19:15:05

Знаю что с PHP не сюда, но чтобы закончить тему и другим будет может полезно:

вот что-то типа этого?

<?

if ( strstr($_SERVER['HTTP_USER_AGENT'] != "") ) { $bot='1';}

else {mysql_query("INSERT INTO stat VALUES ('$id', CURRENT_DATE(), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1", $db);}

?>

paulus · 21.08.2009 19:51:01

Ну, только сравнивать надо не с пустой строкой. Например, мой браузер выписывает туда вот такое:
Mozilla/5.0 (X11; U; Linux i686; ru; rv:1.9.0.13) Gecko/2009080315 Ubuntu/9.04 (jaunty) Firefox/3.0.13

А я на бота не очень похож

P.S. И strstr тут как-то странно используется, попробуйте preg_match какой-нибудь.

Doxtor · 21.08.2009 20:17:37

М-да-а-а.

Походу кроме того что перечислить "все" боты выхода нет!?

Что получилось смотри ниже...список постоянно пополняется...
Или есть более элегантный вариант?
Про preg_match не понял.

<?

$agent = $_SERVER['HTTP_USER_AGENT'];

if ( strstr($agent, "Yandex")) { $bot='1';}

elseif ( strstr($agent, "Googlebot") ) {$bot='1';}

elseif ( strstr($agent, "Slurp") ) {$bot='1';}

elseif ( strstr($agent, "WebCrawler") ) {$bot='1';}

elseif ( strstr($agent, "ZyBorg") ) {$bot='1';}

elseif ( strstr($agent, "scooter") ) {$bot='1';}

elseif ( strstr($agent, "stack") ) {$bot='1';}

elseif ( strstr($agent, "aport") ) {$bot='1';}

elseif ( strstr($agent, "lycos") ) {$bot='1';}

elseif ( strstr($agent, "fast") ) {$bot='1';}

elseif ( strstr($agent, "rambler") ) {$bot='1';}

else {mysql_query("INSERT INTO stat VALUES ('$id', CURRENT_DATE(), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1", $db);}

?>

Отредактированно Doxtor (21.08.2009 20:49:03)

Форум пользователей MySQL

#1 06.08.2009 14:13:09

Сортировать артикулы по количеству кликов на них за последние 3 месяца

#2 06.08.2009 14:41:48

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#3 06.08.2009 15:26:44

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

Doxtor написал:

#4 06.08.2009 15:37:23

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#5 06.08.2009 18:42:04

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#6 06.08.2009 18:49:10

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#7 06.08.2009 18:57:29

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#8 06.08.2009 19:30:13

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#9 06.08.2009 21:26:01

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#10 07.08.2009 14:42:54

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#11 07.08.2009 19:06:36

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#12 07.08.2009 20:08:14

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#13 07.08.2009 21:51:09

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#14 09.08.2009 12:11:35

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#15 10.08.2009 11:49:19

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#16 10.08.2009 13:45:58

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#17 10.08.2009 17:57:09

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#18 10.08.2009 18:41:58

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#19 11.08.2009 15:05:11

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#20 11.08.2009 18:47:25

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#21 21.08.2009 18:14:47

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#22 21.08.2009 18:26:05

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#23 21.08.2009 19:15:05

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#24 21.08.2009 19:51:01

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

#25 21.08.2009 20:17:37

Re: Сортировать артикулы по количеству кликов на них за последние 3 месяца

Board footer