sqlite - SQL Determining the length of a unique visit from access log -


i have table has access log records , want know average time of visit. i'm using sqlite jdbc populate table. table potentially few 100 million rows large. don't have sql experience.

the table similar 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 see duration of each visit. 1 visit when entry appears , continues appear (with same name) until time has passed (say 100 seconds). after that, session ends - if same name appears again new session.

ultimately want graph number of visits , durations.

i'm not sure kind of output easiest, here possible output useful:

name       time banana     3     (row5 - row3) apricot    6     (row6 - row1) apricot    0     (row7 - last visit >100s , no more subsequent visits) cake       0     (row8 - last visit >100s , no more subsequent visits) banana     10    (row10 - row9) 

(additionally, show id - id of start of visit or end)

how can done using sql?

this page: determining length of user's visit based on sql timestamps has similar question mine don't understand answer, nor work me (my db browser freezes).


Comments

Popular posts from this blog

java - UnknownEntityTypeException: Unable to locate persister (Hibernate 5.0) -

python - ValueError: empty vocabulary; perhaps the documents only contain stop words -

ubuntu - collect2: fatal error: ld terminated with signal 9 [Killed] -