SQL query to calculate visit duration from log table
I have a MySQL LOGIN_LOG table with field IDs, PLAYER, TIMESTAMP and ACTION. ACTION can be either "login" or "logout". Only about 20% of logins have an accompanying exit line. For those who do this, I want to calculate the average duration.
I am thinking of something like
select avg(LL2.TIMESTAMP - LL1.TIMESTAMP)
from LOGIN_LOG LL1
inner join LOGIN_LOG LL2 on LL1.PLAYER = LL2.PLAYER and LL2.TIMESTAMP > LL1.TIMESTAMP
left join LOGIN_LOG LL3 on LL3.PLAYER = LL1.PLAYER
and LL3.TIMESTAMP between LL1.TIMESTAMP + 1 and LL2.TIMESTAMP - 1
and LL3.ACTION = 'login'
where LL1.ACTION = 'login' and LL2.ACTION = 'logout' and isnull(LL3.ID)
is this the best way to do it, or is there another efficient one?
source to share
If you have data, you probably can't do anything much faster, because you need to look at the LOGIN and LOGOUT record and make sure there is no other LOGIN (or LOGOUT?) Record for the same user in between.
Alternatively find a way to ensure the shutdown logs the logout so that the data is complete (instead of filling 20%). However, the request probably still needs to ensure that all criteria are met, so it won't be very useful for the request.
If you can get the data in a format that LOGIN and the corresponding LOGOUT times are in the same record, you can greatly simplify the query. I don't understand if the SessionManager does this for you.
source to share
Do you have an object of type SessionManager that can take timeouts? Since there may be a timeout logged there, you can get the last activity time from that and the timeout period.
Or you log all the activity on the website / service, and thus you can directly query the duration of the visit to the website or service and see what actions they took. For a website, Apache log analyzers can probably generate the required statistics.
source to share
I agree with JeeBee, but another advantage of the SessionManager object is that you can handle the sessionEnd event and write an exit string with the active time in it. So you're probably going from 20% accompanying exit lines to 100% of accompanying exit lines. The request for the time of activity will be trivial and consistent across all sessions.
source to share
If only 20% of your users are actually logged out, this search won't give you very accurate times for each session. The best way to estimate how long the average user session will take in the mean time between actions or avg. time per page. So this can be multiplied by the average number of pages / actions per visit to give a more accurate time.
Alternatively, you can define avg. time for each page and then get the session end time = session time to that point + battery life on the last page. This will give you a much finer (and accurate) amount of time spent per session.
As far as the given SQL is concerned, it seems more complicated than you really need. Such an aggregate operation can often be better handled / more maintainable in code external to the database, where you can have the full power of any language you choose, not just SQL's rather convoluted statistical capabilities.
source to share