Алгоритмы обработки журналов посещений
Предположим, у меня есть таблица MySQL, которая выглядит следующим образом, где я отслеживаю, когда (дата) пользователь (User.id ) прочитайте статью на моем веб-сайте (Article.id ):
------------------------------------------
Article_Impressions
------------------------------------------
date | user_id | article_id
--------------------+---------+-----------
2013-04-02 15:33:23 | 815 | 2342
2013-04-02 15:38:21 | 815 | 108
2013-04-02 15:39:33 | 161 | 4815
...
Я пытаюсь определить, сколько сеансов у меня было, а также среднюю продолжительность сеанса на одного пользователя в данный день. Сеанс заканчивается, если статья не была прочитана в течение 30 минут после другой статьи.
Вопрос
Как я могу эффективно определить, сколько сеансов у меня было в определенный день? Я есть использование PHP и MySQL.
Моя первая идея - запросить все эти данные за определенный день, отсортировав их по пользователям. Затем я перебираю каждого пользователя, проверяю, было ли показ в течение 30 минут после последнего показа, и подсчитываю общее количество сеансов, проведенных каждым пользователем в тот день.
Поскольку у нас на сайте около 2 миллионов показов в день, я пытаюсь оптимизировать этот генератор отчетов.
2 answers
Попробуйте выполнить этот запрос
Запрос 1:
select
@sessionId:=if(@prevUser=user_id AND diff <= 1800 , @sessionId, @sessionId+1) as sessionId,
@prevUser:=user_id AS user_id,
article_id,
date,
diff
from
(select @sessionId:=0, @prevUser:=0) b
join
(select
TIME_TO_SEC(if(@prevU=user_id, TIMEDIFF(date, @prevD), '00:00')) as diff,
@prevU:=user_id as user_id,
@prevD:=date as date,
article_id
from
tbl
join
(select @prev:=0, @prevU=0)a
order by
user_id,
date) a
[ Результаты]:
| SESSIONID | USER_ID | ARTICLE_ID | DATE | DIFF |
-----------------------------------------------------------------
| 1 | 161 | 4815 | 2013-04-02 15:39:33 | 0 |
| 2 | 815 | 2342 | 2013-04-02 15:33:23 | 0 |
| 2 | 815 | 108 | 2013-04-02 15:38:21 | 298 |
| 3 | 815 | 108 | 2013-04-02 16:38:21 | 3600 |
Этот запрос вернет уникальный сеанс для каждого нового пользователя, а также для того же пользователя, если следующая прочитанная статья будет прочитана через 30 минут в соответствии с вашим требованием, указанным в вашем вопросе. Столбец diff возвращает разницу в секундах между 2 статьями одного и того же пользователя, что помогает нам подсчитать идентификатор сеанса. Теперь, используя этот результат, вам будет легко подсчитать среднее время на одного пользователя и также общее время за сеанс.
Надеюсь, это поможет вам...
Если концепция пользовательского "сеанса" важна для вашей аналитики, то я бы начал регистрировать данные в вашей таблице, чтобы сделать запрос данных, связанных с сеансом, не таким болезненным процессом. Простым подходом было бы зарегистрировать свой идентификатор сеанса PHP. Если ваш идентификатор сеанса PHP установлен на тот же 30-минутный срок действия, и вы регистрируете идентификатор сеанса PHP в этой таблице, то у вас в основном будет именно то, что вы ищете.
Конечно, это не поможет вам с вашими существующими записями. Я вероятно, вы бы пошли дальше и создали поле сеанса, а затем снова заполнили его случайно сгенерированными идентификаторами "сеанса". Я бы не стал искать для этого полностью SQL-решение, так как оно может не соответствовать вашим требованиям с точки зрения обработки крайних случаев (сеансы, охватывающие несколько дней и т. Д.). Я бы написал сценарий для выполнения этой обратной засыпки, который содержал бы всю необходимую вам логику.
Мой общий подход состоял бы в том, чтобы ВЫБРАТЬ все записи следующим образом:
SELECT user_id, date /* plus any other fields like unique id that you would need for insert */
FROM Article_Impressions
WHERE session_id IS NULL
ORDER BY user_id ASC, date ASC
Примечание: убедитесь, что у вас есть индекс на как поля user_id, так и поля даты.
Затем я бы прошелся по результирующему набору, построив временный массив каждого идентификатора пользователя, и прошелся бы по этому массиву для всех значений даты, назначив случайно сгенерированный идентификатор сеанса, который менялся бы каждый раз, когда изменение даты превышало 30 минут. Как только значение пользователя увеличится, я сделаю вставки для этого предыдущего пользователя, чтобы обновить значения session_id, а затем сброшу значение временного массива на пустое и продолжу этот процесс со следующим пользователем.
Примечание что, вероятно, важно придерживаться подхода, заключающегося в сохранении относительно небольшого временного/рабочего массива, подобного этому, так как с учетом количества записей, о которых вы говорите, вы, скорее всего, не сможете прочитать весь результирующий набор в массив в памяти.
Как только ваши данные заполнены, запрос становится тривиальным:
Уникальные сеансы для каждого дня:
SELECT DATE(date) as `day`, COUNT(DISTINCT session_id) AS `unique_sessions`
FROM Article_Impressions
GROUP BY `day`
ORDER BY `day` DESC /* or ASC depending on how you want to view it */
Среднее количество сеансов в день:
SELECT AVG(sessions_per_day.`unique_sessions`) AS `average_sessions_per_day`
FROM
(
SELECT DATE(date) as `day`, COUNT(DISTINCT session_id) AS `unique_sessions`
FROM Article_Impressions
GROUP BY `day`
) AS sessions_per_day
GROUP BY sessions_per_day.`day`
Примечание: вам нужен индекс в новом поле session_id.