Вот решение поставленной задачи:
1) таблица зарегистрированных пользователей
[sql]CREATE TABLE users (
/* идентификатор пользователя */
user_id INT UNSIGNED NOT NULL,
/* день последнего посещения */
last_visit INT UNSIGNED NOT NULL,
PRIMARY KEY (user_id)
);[/sql]
2) таблица счетчиков уникальных посещений
[sql]CREATE TABLE counters (
/* номер дня, за который считается количество уникальных посетителей */
day_num INT UNSIGNED NOT NULL,
/* номер дня, когда посетители посетили ресурс в прошлый раз. last_visit <= day_num. */
last_visit INT UNSIGNED NOT NULL,
/* количество уникальных посетителей, пришедших в день day_num, которые до этого посещали ресурс в день last_visit */
cnt INT UNSIGNED NOT NULL,
PRIMARY KEY (day_num, last_visit)
);[/sql]
3) таблица для хранения дневного лога посещений
[sql]CREATE TABLE day_log (
/* идентификатор пользователя */
user_id INT UNSIGNED NOT NULL,
/* дополнительные данные */
);[/sql]
Последовательность SQL-запросов для обработки лога посещений [day_log] за день CURRENT_DAY:
1) создаем временную таблицу, содержащую список пользователей, пришедших за день CURRENT_DAY:
[sql]CREATE TEMPORARY TABLE tmp_table
SELECT DISTINCT a.user_id AS user_id,
IF(ISNULL(b.last_visit), CURRENT_DAY, b.last_visit) AS last_visit
FROM day_log AS a LEFT JOIN users AS b ON (a.user_id = b.user_id)[/sql]
2) обновляем день последнего посещения на CURRENT_DAY для пришедших в этот день пользователей:
[sql]UPDATE users SET last_visit = CURRENT_DAY WHERE user_id IN (SELECT user_id FROM tmp_table)[/sql]
3) добавляем новых пользователей в таблицу [users] для дня CURRENT_DAY:
[sql]INSERT INTO users (user_id, last_visit)
SELECT user_id, last_visit FROM tmp_table
WHERE last_visit = CURRENT_DAY[/sql]
4) добавляем новые строки в таблицу [counters], соответствующие дню CURRENT_DAY:
[sql]INSERT INTO counters (day_num, last_visit, cnt)
SELECT CURRENT_DAY, last_visit, COUNT(user_id) FROM tmp_table
GROUP BY last_visit[/sql]
5) удаляем временную таблицу
[sql]DROP TABLE tmp_table[/sql]
А вот и те запросы, ради которых мы так трудились:
1) запрос, возвращающий количество уникальных посетителей за определенный день M:
[sql]SELECT SUM(cnt) FROM counters
WHERE day_num = M[/sql]
2) запрос, возвращающий количество уникальных посетителей за количество дней N, начиная с дня M (если N == 1, то этот запрос превращается в предыдущий):
[sql]SELECT SUM(cnt) FROM counters
WHERE day_num >= M AND day_num < M + N AND (last_visit = day_num OR last_visit < M)[/sql]
[day_num] и [last_visit] - не обязательно номера дней. Это
могут быть последовательные номера любых интервалов времени, в зависимости от дискретности, с которой надо выводить отчеты.
В итоге скорость выполнения запроса по уникальным посетителям за количество подряд идущих интервалов времени N, начиная с интервала M, не зависит от посещаемости сайта, на котором установлен счетчик. Она
зависит лишь от параметров N и M.
)
