Существует таблица с данными о посещениях:
uid (INT) | created_at (DATETIME)
Я хочу найти, сколько дней подряд пользователь посетил наше приложение. Так, например,:
SELECT DISTINCT DATE(created_at) AS d FROM visits WHERE uid = 123
вернет:
d
------------
2012-04-28
2012-04-29
2012-04-30
2012-05-03
2012-05-04
Есть 5 записей и два интервала -3 дня (28 -30 апреля )и 2 дня (3 -4 мая ).
Мой вопрос заключается в том, как найти максимальное количество дней, в течение которых пользователь посещал приложение подряд (3 дня в примере ). Пытался найти подходящую функцию в документации по SQL, но безуспешно. Я что-то упускаю?
UPD: Ребята, спасибо за ответы! На самом деле, я работаю с базой данных vertica Analytics (http://vertica.com/), однако это очень редкое решение, и лишь немногие имеют опыт работы с ним. Хотя он поддерживает стандарт SQL -99.
Что ж, большинство решений работают с небольшими изменениями. Наконец, я создал свою собственную версию запроса :
-- 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
. Так что теперь нам осталось только соединить их каким-то образом, например, по индексу строки.
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
Пример вывода:
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