Проблема SQL - вычислите максимальную последовательность дней
Есть таблица с данными о посещениях:
<code>uid (INT) | created_at (DATETIME) </code>
Я хочу узнать, сколько дней подряд пользователь посещал наше приложение. Так, например:
<code>SELECT DISTINCT DATE(created_at) AS d FROM visits WHERE uid = 123 </code>
вернусь:
<code> d ------------ 2012-04-28 2012-04-29 2012-04-30 2012-05-03 2012-05-04 </code>
Имеется 5 записей и два интервала - 3 дня (28–30 апреля) и 2 дня (3–4 мая).
У меня вопрос, как найти максимальное количество дней, которое пользователь посещал приложение подряд (в примере 3 дня). Пытался найти подходящую функцию в документах SQL, но безуспешно. Я что-то пропустил?
UPD: Спасибо, ребята, за ваши ответы! На самом деле, я работаю с аналитической базой данных vertica (http://vertica.com/), но это очень редкое решение, и только несколько человек имеют опыт работы с ним. Хотя он поддерживает стандарт SQL-99.
Ну, большинство решений работают с небольшими изменениями. Наконец я создал свою собственную версию запроса:
<code>-- returns starts of the vitit series SELECT t1.d as s FROM testing t1 LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', -1, t1.d)) WHERE t2.d is null GROUP BY t1.d s --------------------- 2012-04-28 01:00:00 2012-05-03 01:00:00 -- returns end of the vitit series SELECT t1.d as f FROM testing t1 LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', 1, t1.d)) WHERE t2.d is null GROUP BY t1.d f --------------------- 2012-04-30 01:00:00 2012-05-04 01:00:00 </code>
Так что теперь единственное, что нам нужно сделать, это как-то присоединиться к ним, например, по индексу строки.
<code>SELECT s, f, DATEDIFF(day, s, f) + 1 as seq FROM ( SELECT t1.d as s, ROW_NUMBER() OVER () as o1 FROM testing t1 LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', -1, t1.d)) WHERE t2.d is null GROUP BY t1.d ) tbl1 LEFT JOIN ( SELECT t1.d as f, ROW_NUMBER() OVER () as o2 FROM testing t1 LEFT JOIN testing t2 ON DATE(t2.d) = DATE(TIMESTAMPADD('day', 1, t1.d)) WHERE t2.d is null GROUP BY t1.d ) tbl2 ON o1 = o2 </code>
Пример вывода:
<code> s | f | seq ---------------------+---------------------+----- 2012-04-28 01:00:00 | 2012-04-30 01:00:00 | 3 2012-05-03 01:00:00 | 2012-05-04 01:00:00 | 2 </code>