Хранение, учет, вывод статистики просмотров для статьи MySQL 


Хранение, учет, вывод статистики просмотров для статьи MySQL

Опубликовано

Привет! В этой статье хочу поделиться своим опытом создания механизма для учета количества просмотров статей. С его помощью в блоке под названием “Популярные статьи” я размещаю девять наиболее просматриваемых в течении недели.

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

Если вас предварительно заинтересовал данный способ хранения и учета статистики, то далее я подробно опишу его реализацию

Структура таблицы

Для хранения статистики создайте таблицу`bestweekpost` со следующими столбцами:

  • id – уникальный идентификатор;
  • idpost – ID статьи, для которой ведется подсчет посещений;
  • count – счетчик;
  • dayofweek – день недели (1-7, что соответствует понедельник-воскресенье)
  • date – дата;

SQL запрос для создания таблицы

CREATE TABLE IF NOT EXISTS `bestweekpost` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`idpost` int(11) NOT NULL,
`count` int(11) NOT NULL,
`dayofweek` enum("1","2","3","4","5","6","7") COLLATE cp1251_general_cs NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_cs AUTO_INCREMENT=1;

Универсальный запрос на Добавление/Обновление статистики

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

  1. случай - статистики для статьи еще не существует. Необходимо добавить новую запись ведения учета просмотров в таблицу;
  2. случай – статистика для статьи уже ведется. Необходимо увеличить счетчик просмотров на 1;
  3. случай - статистика для статьи уже существует в базе, однако она устарела (данные за прошлую неделю). Необходимо обнулить счетчик (установить значение равное 1) и обновить устаревшую дату записи на новую (текущую);

Получается, чтобы вести недельный отчет для одной статьи понадобиться всего 7 записей в таблице.

Для решения поставленной задачи удобно воспользоваться оператором INSERT, так как помимо основного его предназначения, добавления новой записи в таблицу, в него вложена дополнительная операция, работающая следующим образом: если при добавлении новой записи оператором INSERT, в таблице уже существует запись с идентичным уникальным идентификатором (`id`), иначе говоря дубликат, то управление передается ON DUPLICATE KEY UPDATE составной части оператора INSERT, где можно обновить значения любого поля записи.

Листинг 1. Упрощенный запрос INSERT для добавления нового просмотра

INSERT INTO `bestweekpost` 
(`id`,`idpost`,`count`,`dayofweek`,`date`)
VALUES
(
(вложенный запрос 1),
"{$idpost}",
"1", 
(IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1))), 
NOW()
)
ON DUPLICATE KEY UPDATE 
 `bestweekpost`.`count` =(вложенный запрос 2),
 `bestweekpost`.`date` = NOW()

В листинге представлен упрощенный вид запроса INSERT, необходимый для добавления нового посещения в таблицу `bestweekpost`. Как видно из листинга в двух местах оператора INSERT расположены два вложенных запроса заключенные в круглые скобки, это сложные конструкции, подробное рассмотрение которых, приведено в следующих главах. В операторе INSERT также располагается запись (IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1))) для получения дня недели, подробнее о ней рассмотрено в следующей главе.

Вложенный запрос 1

Вложенный запрос 1 располагается в поле, где необходимо указать уникальный идентификатор добавляемой в таблицу записи (`id`) оператора INSERT (смотрите листинг 1). Соответственно задачей вложенного запроса, возвращение `id` найденной записи учета посещений для статьи, либо null если найти ничего не удалось.

Конструкция запроса состоит из выборки SELECT заключенной в условный оператор IFNULL

Листинг 2. Вложенный запрос 1

IFNULL((SELECT `bwp`.`id`
FROM `bestweekpost` as `bwp`
WHERE 
`idpost`="{$idpost}" and 
`dayofweek`=(IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1)))
LIMIT 1),null)

SELECT запрашивает из таблицы `bestweekpost` статью с `idpost`="{$idpost}" и днем недели `dayofweek`=(IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1)))

IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1)) – с помощью этой записи будет получен день недели в цифровом выражении от 1-7 (понедельник - воскресенье). Конечно, можно использовать команду DAYOFWEEK(CURDATE()) для получения дня недели по стандарту MYSQL (цифры те же 1-7, только 1 соответствует воскресенью, 2-7, понедельник-суббота), однако для простоты дальнейших вычислений я предпочел сконвертировать день недели. Т.е. текущая запись IF(…) означает, что если сегодня воскресенье то день недели равен 7, иначе от дня недели отнимается 1.

Наконец условный оператор IFNULL возвращает значение в зависимости от результата выборки SELECT: либо id записи, найденной в результате запроса, либо null, если найти ничего не удалось.

Обратите внимание, если вы используйте вложенный запрос SELECT внутри INSERT и запрашиваете данный из той же таблицы, куда добавляет записи INSERT, то необходимо имя таблицы указать через псевдоним, иначе обработчик выдаст ошибку.

Листинг 3.

//Запрос выполнится корректно
SELECT `bwp`.`id`
FROM `bestweekpost` as `bwp`

//Запрос выполнится не корректно
SELECT `id`
FROM `bestweekpost`

Резюмируя первый вложенный запрос можно выделить главный момент, если в результате его работы было возвращено значение null то в таблицу добавляется новая запись (случай 1). Иначе управление передается оператору ON DUPLICATE KEY UPDATE в котором размещен второй вложенный запрос.

Вложенный запрос 2

На данном этапе в конструкции INSERT управление передано оператору DUPLICATE KEY UPDATE, задача которого, обновить данные содержащиеся в записи, найденной в результате выборки SELECT из первого вложенного запроса (случай 2 и 3).

Вложенный запрос 2 располагается в месте, где определяется новое значения для поля `count` (смотрите листинг 1).

В выборке SELECT вложенного запроса 2, условие WHERE дополняется новой проверкой `date`=CURDATE(). Выборка помещается в условный оператор IFNULL, который возвращает 0, если в результате запроса SELECT ничего не было найдено (что соответствует устареванию записи со статистикой, смотрите случай 3), либо возвращает текущее количество просмотров статьи (смотрите случай 1). В завершении к любому из результатов добавляется 1 – добавление нового просмотра.

Листинг 4. Вложенный запрос 2

((IFNULL((SELECT `bwp`.`count`
FROM `bestweekpost` as `bwp`
WHERE 
`idpost`="14" and 
`dayofweek`=(IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1))) and
`date`=CURDATE()
LIMIT 1),0)+1)

Результирующий запрос

Подставив вложенные запросы в конструкцию оператора INSERT из листинга 1, получим следующее:

Листинг 5. Результирующий запрос

INSERT INTO `bestweekpost` 
(`id`,`idpost`,`count`,`dayofweek`,`date`)
VALUES
(
(IFNULL((SELECT `bwp`.`id`
FROM `bestweekpost` as `bwp`
WHERE 
`idpost`="{$idpost}" and 
`dayofweek`=(IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1)))
LIMIT 1),null)),
"{$idpost}",
"1",
(IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1)))
,NOW()
)

ON DUPLICATE KEY UPDATE
`bestweekpost`.`count` = ((IFNULL((SELECT `bwp`.`count`
FROM `bestweekpost` as `bwp`
WHERE 
`idpost`="{$idpost}" and 
`dayofweek`=(IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1))) and
`date`=CURDATE()
LIMIT 1),0)+1)),
`bestweekpost`.`date` = NOW();
Таким образом, с помощью всего одного универсального запроса можно вести учет статистики посещений.

Вывод данных статистики по посещениям

Запросить 10 самых посещаемых статей можно запросом из листинга 6.

Листинг 6. Выборка 10 самых просматриваемых статей

SELECT 
`bestweekpost`.`idpost`,
sum(`bestweekpost`.`count`) as count,
FROM `bestweekpost`
WHERE 
`bestweekpost`.`dayofweek`<=(IF(DAYOFWEEK(CURDATE())=1,7,(DAYOFWEEK(CURDATE())-1))) 
GROUP BY `bestweekpost`.`idpost`
ORDER BY count desc
LIMIT 10