SQL-Problem - Berechnen Sie die maximale Tagesfolge
Es gibt eine Tabelle mit Besuchsdaten:
<code>uid (INT) | created_at (DATETIME) </code>
Ich möchte herausfinden, an wie vielen Tagen ein Nutzer unsere App besucht hat. Also zum Beispiel:
<code>SELECT DISTINCT DATE(created_at) AS d FROM visits WHERE uid = 123 </code>
wird zurückkehren:
<code> d ------------ 2012-04-28 2012-04-29 2012-04-30 2012-05-03 2012-05-04 </code>
Es gibt 5 Datensätze und zwei Intervalle - 3 Tage (28. - 30. April) und 2 Tage (3. - 4. Mai).
Meine Frage ist, wie man die maximale Anzahl von Tagen findet, die ein Benutzer die App hintereinander besucht hat (3 Tage im Beispiel). Versucht, eine geeignete Funktion in den SQL-Dokumenten zu finden, aber ohne Erfolg. Vermisse ich etwas?
UPD: Vielen Dank für Ihre Antworten! Eigentlich arbeite ich mit der Vertica Analytics-Datenbank (http://vertica.com/), dies ist jedoch eine sehr seltene Lösung und nur wenige Leute haben Erfahrung damit. Obwohl es den SQL-99-Standard unterstützt.
Nun, die meisten Lösungen funktionieren mit geringfügigen Änderungen. Schließlich habe ich meine eigene Version von query erstellt:
<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>
Jetzt müssen wir sie nur noch irgendwie verbinden, zum Beispiel über den Zeilenindex.
<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>
Beispielausgabe:
<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>