Есть таблица со статистикой посещений со следующими полями:

id | sess_id | metrika_visitor | ip | referrer …. и тп

sess_id - сессия
metrika_visitor - ID пользователя, который для новых посетителей пустой, а потом им выставляется куки. То есть любой второй хит пользователя со включенными куки уже заполняет поле metrika_visitor уникальным ID.

1. посчитать общее количество посетителей.

у меня запрос такой:

новые посетители:
SELECT count(sess_id) as newvis FROM table WHERE metrika_visitor = '' GROUP BY sess_idж

остальные посетители:
SELECT count(metrika_visitor) as othervis FROM table WHERE metrika_visitor <> '' GROUP BY metrika_visitor;

сложив newvis и othervis должны вроде как получить посетителей. Получаем ерунду.
Делал через count() GROUP BY потому что distinct глючный в некоторых версиях mysql.

2. Каким образом получить ядро посетителей? То есть у кого количество возвратов в заданный интервал (неделя, месяц, год) больше 2х. ВОзможно есть другие варианты подсчета ядра ?!

3. Как считать пути по сайту ? Я полагаю нужно получить отдельные выборки, сгруппированные по сессиям. Потом их уже в PHP объединять ?! Как мне кажется достаточно долгий по времени процесс.

4. Как определить среднее время, проведенное за сессию с помощью MYSQL запроса? То есть как получить выборку разностей первого и последнего хита для каждой сессии ???? Это, пожалуй, самое сложное для меня.

Если кто может помочь с запросами - большое тем спасибо.

начнем с того, что никто не считает посетителей количеством сессий.
уникальные посещения принято считать за день.

А продолжем тем, что поинтересуемся у автора, каким образом он заставил 100% посетителей своего сайта возвращать куки.

Сложно как-то все у тебя.
Напиши какие у тебя цели, простым и понятным текстом.
Нужно сразу перед началом представлять что тебе нужно.

Приведи полный формат и логику таблицы.
Одна запись в табице = один хит, так?
sess_id и metrika_visitor друг друга исключают?

Ну и главный вопрос: почему бы не использовать готовое решение?

<sergio.ga> писал(а):

Приведи полный формат и логику таблицы. Одна запись в табице = один хит, так? sess_id и metrika_visitor друг друга исключают?

sess_id - это сессия пользователя в ее привычном понимании (PHPSESSID). Пользователь может каждый день заходить на сайт и у него будут разные сессии. При этом данного посетителя никак кроме как по IP (что чрезвычайно неточно) не определить: куда он ходил и что купил.

metrika_visitor - это уникальный ID пользователя, сохраняющийся в куки пользователя. Заходя повторно на сайт, пользователь идентифицируется как "тот самый, который уже был" и можно проследить его поведение за всю историю работы статистики. В частности: от захода до покупки или какой-то другой целевой страницы.
Предваряя много выпадок в адрес того, что куки ставятся не у всех, 97-98% от всех посетителей - более чем достаточно.

<sergio.ga> писал(а):

Ну и главный вопрос: почему бы не использовать готовое решение?

Странный вопрос. Я думал тут форум программистов.

Чебурген писал(а):

А продолжем тем, что поинтересуемся у автора, каким образом он заставил 100% посетителей своего сайта возвращать куки.

А где я написал про 100% ?

Jamakaser писал(а):

Сложно как-то все у тебя. Напиши какие у тебя цели, простым и понятным текстом. Нужно сразу перед началом представлять что тебе нужно.

Простым и понятным языком - это обычно сложно и трудно
Хорошо. Видно сообщения с большим объемом текста плохо усваиваются сразу

Задача номер 1: отбразить пути пользователей по сайту. Пример можно посмотреть на SpyLog'е или HotLog'е.

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

Меня инетресует вопрос как это моджно сделать? Да, я могу взять всю выборку из базы данных и строить дерево путей по сайту на чистом PHP. Но при 500 000 записях в БД это мне видится не лучшим вариантом. Видимо есть вариант на SQL получать отдельные выборки по путям. Мерджить их и выбирать после этого наиболее популярные.

Так вот, вопрос. Кто-нибудь писал свою статистику с отображением путей по сайту ?

Спасибо.

CTR писал(а):

<sergio.ga> писал(а): Ну и главный вопрос: почему бы не использовать готовое решение? ;) Странный вопрос. Я думал тут форум программистов.

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

Ну да ладно.
Решение задачи поиска маршрутов ИМО в любом случае требует анализа всей статистики. Ну а как именно это делать - за один раз или отдельными выборками - это вам решать.
Альтернативно, можно строить маршруты отдельно от статистики в момент посещения пользователем страниц сайта.

<sergio.ga> писал(а):

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

Вы имеете ввиду Webalizer? А где там пути по сайту?

<sergio.ga> писал(а):

Решение задачи поиска маршрутов ИМО в любом случае требует анализа всей статистики. Ну а как именно это делать - за один раз или отдельными выборками - это вам решать. Альтернативно, можно строить маршруты отдельно от статистики в момент посещения пользователем страниц сайта.

Видимо, разумно это делать в оффлайне.

CTR писал(а):

Чебурген писал(а): А продолжем тем, что поинтересуемся у автора, каким образом он заставил 100% посетителей своего сайта возвращать куки. А где я написал про 100% ?

Вот здесь.

Цитата:

сложив newvis и othervis должны вроде как получить посетителей.

И я не люблю, когда игнорируют то, что я написал

Цитата:

Так вот, вопрос. Кто-нибудь писал свою статистику с отображением путей по сайту ?

Так вот вопрос. Кое-кто не пытался ли формализовать термин "перемещения по сайту" на чуть более детальном уровне, чем "как в спайлоге"?
Рекомендую.
А там, глядишь - и запрос сам составится...

Чебурген писал(а):

CTR писал(а): А где я написал про 100% ? Вот здесь. Цитата: сложив newvis и othervis должны вроде как получить посетителей.

Я повторю вопрос: где я написал про 100% ?

Чебурген писал(а):

И я не люблю, когда игнорируют то, что я написал

Люблю/не люблю - это в детском саду. Я ответил на все сообщения, которые мне писали. И про куки тоже.

Чебурген писал(а):

Цитата: Так вот, вопрос. Кто-нибудь писал свою статистику с отображением путей по сайту ? Так вот вопрос. Кое-кто не пытался ли формализовать термин "перемещения по сайту" на чуть более детальном уровне, чем "как в спайлоге"? Рекомендую. А там, глядишь - и запрос сам составится...

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

Итак, вот уже в третий раз повторю вопрос:

Есть mysql таблица со следующими полями:

… | referrer | query | sess_id | metrika_visitor | …

как лучше имплементировать процесс формирования путей посетителей по сайту?

вариант а) прочитать всю таблицу в ассоциативный массив и начать построение дерева путей с расстановкой частоты появления пути в выборке. (здесь весь процесс обработки данных ведется в PHP скрипте) -- решение в лоб. Мне не особенно импонирует.
b) Ваш вариант ?

Цитата:

Здесь вопрос не формализации, а имплементации

отлично.
какая конкретно проблема возникла при имплементации "давно формализованной без разночтений задачи" в конкретный SQL-запрос?

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

CTR писал(а):

<sergio.ga> писал(а): Мне просто не совсем понятно желание убить своё время на реализацию функциональности предоставляемой готовыми решениями, которые идут в комплекте практически к любому хостингу. Вы имеете ввиду Webalizer? А где там пути по сайту?

В Webalizer есть точки входа/выхода, что при умелом использовании тоже немало, а в Urchin, к примеру, есть и пути. Да и кроме этих двух достаточно и анализаторов логов и готовых статистических скриптов.

<sergio.ga> писал(а):

В Webalizer есть точки входа/выхода, что при умелом использовании тоже немало, а в Urchin, к примеру, есть и пути. Да и кроме этих двух достаточно и анализаторов логов и готовых статистических скриптов.

Разве Urchin бесплатный ?

Сдаюсь! Закрываем тему.

А почему нет поля время и дата? Как вы узнаете, когда пользователь, пришел на какую страницу?
Я не думаю, что кто либо на этом форуме в состоянии вам разложить от а, до я, весь алгоритм написания такой вот фигни.
Как узнать путь? Я бы считал по ip или уникальному id определенного пользователя в текущей сессии и дате – времени + от куда и куда пришел, и делал путь исходя из этих данных.
Я так понял эта цель, которую вы преследуете, а не подсчитать уникальные визиты?
И еще думаю, что один SQL запросом вы ничего не добьетесь.
Советую посмотреть, как сделали другие, а не говорить, что думал это форум для программистов.

NOT NULL писал(а):

Как узнать путь? Я бы считал по ip или уникальному id определенного пользователя в текущей сессии и дате – времени + от куда и куда пришел, и делал путь исходя из этих данных.

Если у вас есть для каждой записи (хита) номер сессии, то пути по сайту извлекаются элементарно:

делается select sess_id, page...order by time
для всего временного диапазона, и составляется ассоциативный массив

paths[sess_id][]=page для каждой записи.

page - адрес страницы, открытой пользователем
sess_id - сессия

после прохода по всем записям у вас получается paths, в котором для каждой сессии записан путь. Уже хорошо.

Далее не сложно додумать, как можно отсортировать пути по популярности.