Problema de SQL - calcula la secuencia de días máximos

Question

May 04, 2012, 01:33 PM

Problema de SQL - calcula la secuencia de días máximos

Hay una tabla con datos de visitas:

<code>uid (INT) | created_at (DATETIME)
</code>

Quiero saber cuántos días seguidos ha visitado nuestra aplicación un usuario. Así, por ejemplo:

<code>SELECT DISTINCT DATE(created_at) AS d FROM visits WHERE uid = 123
</code>

volverá:

<code>     d      
------------
 2012-04-28
 2012-04-29
 2012-04-30
 2012-05-03
 2012-05-04
</code>

Hay 5 registros y dos intervalos: 3 días (28 - 30 de abril) y 2 días (3 - 4 de mayo).

Mi pregunta es cómo encontrar el número máximo de días que un usuario ha visitado la aplicación en una fila (3 días en el ejemplo). Intenté encontrar una función adecuada en los documentos SQL, pero sin éxito. ¿Me estoy perdiendo de algo?

UPD: Gracias chicos por sus respuestas! En realidad, estoy trabajando con la base de datos analítica de Vertica (http://vertica.com/), sin embargo, esta es una solución muy rara y solo unas pocas personas tienen experiencia con ella. Aunque es compatible con el estándar SQL-99.

Bueno, la mayoría de las soluciones funcionan con ligeras modificaciones. Finalmente creé mi propia versión de consulta:

<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>

Así que ahora lo único que tenemos que hacer es unirlos de alguna manera, por ejemplo, por índice de fila.

<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>

Salida de muestra:

<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>