I have a table that has access log records and I want to know the average time of a visit. I'm using sqlite with JDBC to populate the table. The table could be potentially a few 100 million rows large. I don't have much SQL experience.
The table is similar to this:
ID Name Time(s)
1 Apricot 100
2 Apricot 101
3 Banana 102
4 Banana 102
5 Banana 105
6 Apricot 106
7 Apricot 300
8 Cake 300
9 Banana 310
10 Banana 320
I want to see the duration of each visit. One visit is when an entry appears and continues to appear (with the same name) until some time has passed (say 100 seconds). After that, the session ends - if the same name appears again then it is a new session.
Ultimately I want to graph the number of visits and their durations.
I'm not sure what kind of output would be the easiest, but here is a possible output that would be useful:
Name Time
Banana 3 (row5 - row3)
Apricot 6 (row6 - row1)
Apricot 0 (row7 - last visit is >100s and no more subsequent visits)
Cake 0 (row8 - last visit is >100s and no more subsequent visits)
Banana 10 (row10 - row9)
(Additionally, show the ID - this could be the ID of the start of a visit or the end)
How can this be done using SQL?
This page: Determining the length of a user's visit based on SQL timestamps has a similar question to mine but I don't really understand the answer, nor does it work for me (my DB browser freezes).
