msSQL-Protokolldaten – starten und enden von mehreren timestempeln

Ich habe eine Tabelle, die eine Reihe von verschiedenen Zeilen enthält, die aus generierten Protokolldateien stammt. Jede Zeile hat einen timestempel (im Epoche-Format) unten ist ein Beispiel von data, derzeit gibt es etwa 1,5 Millionen Zeilen data!

EpochTime Date Dbm Source 1370732265.373915000 2013-06-17 11:36:39.477 -85 1 1370732265.376506000 2013-06-17 11:36:39.487 -76 2 1370732265.398012000 2013-06-17 11:37:39.503 -81 1 1370732265.463492000 2013-06-17 11:37:39.520 -94 3 1370732265.692144000 2013-06-17 11:37:39.533 -77 2 1370732265.845195000 2013-06-17 11:38:39.550 -84 4 1370732265.933283000 2013-06-17 11:38:39.580 -84 4 1370732265.935863000 2013-06-17 11:38:39.597 -84 5 1370732265.939143000 2013-06-17 11:39:39.597 -84 2 1370732265.939858000 2013-06-17 11:39:39.613 -84 4 1370732265.965481000 2013-06-17 11:40:39.627 -82 5 1370732266.049712000 2013-06-17 11:40:39.677 -82 3 1370732266.110457000 2013-06-17 11:41:39.690 -84 4 1370732266.110457000 2013-06-17 11:41:39.690 -84 6 1370732266.110457000 2013-06-17 11:42:39.690 -84 3 1370732266.110457000 2013-06-17 11:42:39.690 -84 4 1370732266.110457000 2013-06-17 11:42:39.690 -84 6 1370732266.110457000 2013-06-17 11:43:39.690 -84 2 1370732266.110457000 2013-06-17 11:44:39.690 -84 1 

Was ich tun muss, ist, die Start- und Endzeit für jede Quelle zu finden, aber es kann nur eine maximale Spanne von 5 Minuten geben, bevor die Quelle wieder gezählt wird. dh Quelle 1 würde zweimal protokolliert werden, alle anderen Quellen würden weiterhin fortgesetzt werden, bis die Quelle nicht für 5 Minuten gesehen wurde, in eine Tabelle, die wie unten aussieht.

 ID Duration Store Start End MacID Dbm 7 31 1 2013-06-08 07:46:10.000 2013-06-08 08:17:00.000 1 -84 4 2 1 2013-06-08 18:42:53.000 2013-06-08 18:44:06.000 2 -83 2 1 1 2013-06-08 14:31:20.000 2013-06-08 14:32:08.000 3 -89 11 213 1 2013-06-08 12:43:55.000 2013-06-08 16:16:11.000 4 -86 6 585 1 2013-06-08 14:03:58.000 2013-06-08 23:48:44.000 5 -75 28 287 1 2013-06-08 07:15:40.000 2013-06-08 12:02:10.000 6 -88 28 287 1 2013-06-08 07:15:40.000 2013-06-08 12:02:10.000 1 -81 

vorzugsweise im auf der search nach einer vollständig SQL-Lösung wegen der Menge an data, die existiert, aufgrund der performance der loop durch so viele data. ich habe gegangen, aber alles, was ich bisher getan habe, zählt nur einmal die Quelle einmal pro Periode (derzeit auf ein Tage Wert der data gesetzt)

Die database läuft auf SQL server 2003

EDIT: Eine Sache, die nicht erwähnt wurde, war, dass der höchste DBM-Wert für jeden 'Besuch' mit den verarbeiteten data protokolliert werden muss

Sie verwenden SQL server 2012. Yeah.

Hier ist die Idee. Finden Sie dort, wo jede Sequenz für eine Quelle beginnt. Dies ist entweder das erste Mal, wenn die Quelle gesehen wird oder wann immer die Quelle seit fünf Minuten nicht gesehen wurde. Sie können dies mit der lag() .

Als nächstes eine kumulative Summe der NewStart Flagge. Alles in einer Sequenz hat den gleichen Wert, so kann dies für die Gruppierung verwendet werden.

Die endgültigen Ergebnisse stammen aus der Aggregation. Alle Felder in deiner Ausgabe sind nicht klar, aber hier ist SQL, um den Großteil der Arbeit zu machen:

 select count(*) as numIds, max(date) - MIN(date) as duration, 1 as store, MIN(date) as start, MAX(date) as end, source as MacId from (select t.*, SUM(NewStart) over (partition by source order by date) as Grp from (select t.*, (case when date - lag(date) over (partition by source order by date) < 5/(60*24.0) then 0 else 1 end) as NewStart from t ) t ) t group by grp