Каждый раз, когда регистрируемый пользователь посещает веб-сайт, их данные помещаются в таблицу, содержащую userId и дату (либо одну, либо нулевую строку на пользователя в день):
444631 2011-11-07 444631 2011-11-06 444631 2011-11-05 444631 2011-11-04 444631 2011-11-02 444631 2011-11-01 Мне нужно иметь готовый доступ к числу последовательных посещений, когда я вывожу пользовательские данные из главной пользовательской таблицы. В случае для этого пользователя это будет 4.
В настоящее время я делаю это через денормализованный счетчик consecutivevisits в главной пользовательской таблице, однако по неизвестным причинам он иногда сбрасывается. Я хочу попробовать подход, который использует исключительно данные в приведенной выше таблице.
Какой лучший SQL-запрос для получения этого числа (4 в приведенном выше примере)? Есть пользователи, у которых сотни посещений, у нас миллионы зарегистрированных пользователей и хитов в день.
EDIT : в соответствии с комментариями ниже я отправляю код, который я использую в настоящее время для этого; однако у него есть проблема, что он иногда сбрасывается без причины, и он также перезагружает его для всех в выходные дни, скорее всего, из-за изменения DST.
// Called every page load for logged in users public static function OnVisit($user) { $lastVisit = $user->GetLastVisit(); /* Timestamp; db server is on the same timezone as www server */ if(!$lastVisit) $delta = 2; else { $today = date('Y/m/d'); if(date('Y/m/d', $lastVisit) == $today) $delta = 0; else if(date('Y/m/d', $lastVisit + (24 * 60 * 60)) == $today) $delta = 1; else $delta = 2; } if(!$delta) return; $visits = $user->GetConsecutiveVisits(); $userId = $user->GetId(); /* NOTE: t_dailyvisit is the table I pasted above. The table is unused; * I added it only to ensure that the counter sometimes really resets * even if the user visits the website, and I could confirm that. */ q_Query("INSERT IGNORE INTO `t_dailyvisit` (`user`, `date`) VALUES ($userId, CURDATE())", DB_DATABASE_COMMON); /* User skipped 1 or more days.. */ if($delta > 1) $visits = 1; else if($delta == 1) $visits += 1; q_Query("UPDATE `t_user` SET `consecutivevisits` = $visits, `lastvisit` = CURDATE(), `nvotesday` = 0 WHERE `id` = $userId", DB_DATABASE_COMMON); $user->ForceCacheExpire(); }
Я пропустил тег mysql и написал это решение. К сожалению, это не работает в MySQL, поскольку оно не поддерживает функции окна .
Я отправляю его в любом случае, поскольку я прилагаю к нему некоторые усилия. Протестировано с помощью PostgreSQL. Будет работать аналогично Oracle или SQL Server (или любой другой достойной RDBMS, поддерживающей функции окна).
CREATE TEMP TABLE v(id int, visit date); INSERT INTO v VALUES (444631, '2011-11-07') ,(444631, '2011-11-06') ,(444631, '2011-11-05') ,(444631, '2011-11-04') ,(444631, '2011-11-02') ,(444631, '2011-11-01') ,(444632, '2011-12-02') ,(444632, '2011-12-03') ,(444632, '2011-12-05');
-- add 1 to "difference" to get number of days of the longest period SELECT id, max(dur) + 1 as max_consecutive_days FROM ( -- calculate date difference of min and max in the group SELECT id, grp, max(visit) - min(visit) as dur FROM ( -- consecutive days end up in a group SELECT *, sum(step) OVER (ORDER BY id, rn) AS grp FROM ( -- step up at the start of a new group of days SELECT id ,row_number() OVER w AS rn ,visit ,CASE WHEN COALESCE(visit - lag(visit) OVER w, 1) = 1 THEN 0 ELSE 1 END AS step FROM v WINDOW w AS (PARTITION BY id ORDER BY visit) ORDER BY 1,2 ) x ) y GROUP BY 1,2 ) z GROUP BY 1 ORDER BY 1 LIMIT 1;
Вывод:
id | max_consecutive_days --------+---------------------- 444631 | 4
Позже я нашел еще лучший способ. числа grp не являются непрерывными (но непрерывно растут). Не имеет значения, так как это просто означает конец:
SELECT id, max(dur) + 1 AS max_consecutive_days FROM ( SELECT id, grp, max(visit) - min(visit) AS dur FROM ( -- subtract an integer representing the number of day from the row_number() -- creates a "group number" (grp) for consecutive days SELECT id ,EXTRACT(epoch from visit)::int / 86400 - row_number() OVER (PARTITION BY id ORDER BY visit) AS grp ,visit FROM v ORDER BY 1,2 ) x GROUP BY 1,2 ) y GROUP BY 1 ORDER BY 1 LIMIT 1;
SQL Fiddle для обоих.
Если нет необходимости вести журнал каждый день, когда пользователь был зарегистрирован на веб-сайте, и вы хотите узнать только о последовательных днях, когда он был зарегистрирован, я бы предпочел:
Выбирайте 3 столбца: LastVisit (Дата), ConsecutiveDays (int) и Пользователь.
При регистрации вы проверяете запись для пользователя, определяете, был ли последний визит «Сегодня – 1», затем добавьте 1 в столбцы «Последовательные дни» и сохраните «Сегодня» в столбце LastVisit. Если последний вид больше, чем «Сегодня – 1», сохраните 1 в последовательных днях.
НТН