我有两个表
id email
-------------------------------------------------------------
1 apple@apple.com
2 google@google.com
user_session_logs
id user_id start_timestamp end_timestamp
-------------------------------------------------------------
1 1 2016-01-01 12:00:00 2016-01-01 13:00:00
2 2 2016-01-01 14:00:00 2016-01-01 15:00:00
3 2 2016-02-03 06:00:00 2016-02-03 06:30:00
4 1 2016-03-03 05:00:00 2016-03-03 06:00:00
5 1 2016-04-03 07:00:00 2016-04-03 08:30:00
我想计算按登录实例(第一次登录,第二次登录等)分层的会话持续时间分布。根据上面的数据,我想得到
结果login_instance average_session_duration_minutes
-------------------------------------------------------------
1 60 (this is the average of all user's 1st session duration)
2 45 (this is the average of all user's 2nd session duration)
3 90 (this is the average of all user's 3rd session duration)
有什么想法这可以在mysql查询?
这可以通过对相同user_id的前一个条目(即较小的user_session_log id)计数登录实例的子选择来实现:
SELECT login_instance, AVG(session_duration_minutes) AS average_session_duration_minutes
FROM
(SELECT *,
(SELECT COUNT(*)
FROM user_session_logs usl2
WHERE usl2.user_id = usl.user_id AND usl2.id <= usl.id) AS login_instance,
TIMESTAMPDIFF(MINUTE, start_timestamp, end_timestamp) AS session_duration_minutes
FROM user_session_logs usl) subq
GROUP BY login_instance;
查看这里的演示:http://rextester.com/VVT36119
尝试以下查询
select
us.id,
usl.id,
usl.user_id,
DATEDIFF(HOUR,usl.start_timestamp ,usl.start_timestamp)
from user as us ,user_sessions_log as usl
where us.id=usl.id
Group By us.id;