Problema de SQL - calcular a sequência de dias máximos
Há uma tabela com dados de visitas:
uid (INT) | created_at (DATETIME)
Quero saber quantos dias seguidos um usuário visitou nosso aplicativo. Então, por exemplo:
SELECT DISTINCT DATE(created_at) AS d FROM visits WHERE uid = 123
retornará:
d
------------
2012-04-28
2012-04-29
2012-04-30
2012-05-03
2012-05-04
Existem 5 registros e dois intervalos - 3 dias (28 - 30 de abril) e 2 dias (3 a 4 de maio).
Minha pergunta é como encontrar o número máximo de dias que um usuário visitou o aplicativo em uma linha (3 dias no exemplo). Tentei encontrar uma função adequada nos documentos SQL, mas sem sucesso. Estou esquecendo de algo?
UPD: Obrigado pessoal por suas respostas! Na verdade, estou trabalhando com o banco de dados vertica analytics (http://vertica.com/), no entanto, essa é uma solução muito rara e poucas pessoas têm experiência com isso. Embora suporte padrão SQL-99.
Bem, a maioria das soluções funciona com pequenas modificações. Finalmente eu criei minha própria versão de consulta:
-- 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
Então agora só o que precisamos fazer é juntá-los de alguma forma, por exemplo, por índice de linha.
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
Amostra de saída:
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