题目-求出平台同时在线最多的人数
题目需求
根据用户登录明细表(user_login_detail),求出平台同时在线最多的人数。
结果如下:
cn |
---|
7 |
需要用到的表:
用户登录明细表:user_login_detail
user_id(用户id) | ip_address(ip地址) | login_ts(登录时间) | logout_ts(登出时间) |
---|---|---|---|
101 | 180.149.130.161 | 2021-09-21 08:00:00 | 2021-09-27 08:30:00 |
102 | 120.245.11.2 | 2021-09-22 09:00:00 | 2021-09-27 09:30:00 |
103 | 27.184.97.3 | 2021-09-23 10:00:00 | 2021-09-27 10:30:00 |
SELECT
max(cn) as cn
FROM
(
SELECT
*,
sum(if (flag='login',1,-1)) over(ORDER BY actiontime) as cn
FROM
(
SELECT
user_id,login_ts as actiontime ,'login' as flag
FROM user_login_detail
UNION
SELECT
user_id,logout_ts as actiontime,'logout' as flag
FROM user_login_detail
)t1
)t2
如:
101,2021-06-10 12:12:12,login,1
102,2021-06-10 12:12:12,login,2
104,2021-06-10 12:12:12,login,3
103,2021-06-10 12:14:12,login,4
105,2021-06-10 13:12:12,login,5
104,2021-06-10 15:12:12,logout,4
101,2021-06-10 17:12:12,logout,3
102,2021-06-10 17:12:12,logout,2
103,2021-06-10 17:12:12,logout,1
105,2021-06-10 17:12:12,logout,0
101,2021-06-10 19:12:12,login,1
101,2021-06-10 21:12:12,logout,0
标签:同时在线,00,12,06,10,--,题解,2021,login
From: https://www.cnblogs.com/songxr/p/16944982.html