同时在线最多的人数
题目需求
根据用户登录明细表(user_login_detail),求出平台同时在线最多的人数。
结果如下:
Cn(人数) |
---|
7 |
思路:按照login_ts和logout_ts的时间,将用户进行标记(登录标记为1,登出标记为-1)。将标记后的数据进行union,计算截至到当前时刻的在线人数和,最后取最大值即可。
第一步:把原始数据中的登录时间(login_time)和登出时间(logout_ts)取出来,然后进行标记(flag),每个用户登录时标记为1,登出时标记为-1。
第二步:将登录和登出标记后的数据进行union。
第三步:利用开窗函数求和,每次遇到登录时间就加1,遇到登出时间就减1,这样我们就得到表中所有登录的当前用户量。
第四步:最后求出平台同时在线最多的人数。
select max(online_user_cnt)
from
(
select sum(flag) over (order by l_time rows between unbounded preceding and current row ) online_user_cnt
from
(
select login_ts l_time,
1 flag
from user_login_detail
union all
select logout_ts,
-1
from user_login_detail
)t1
)t2;
-- 求出平台每天不同时点最大的在线人数
select date(l_time) as login_date,
hour(l_time) as login_hour,
max(online_user_cnt) as online_user_cnt_max
from
(
select user_id,
l_time,
sum(flag) over(order by l_time rows between unbounded preceding and current row ) online_user_cnt
from
(
select user_id,
login_ts l_time,
1 flag
from user_login_detail
union all
select user_id,
logout_ts,
-1 flag
from user_login_detail
)t1
)t2
group by date(l_time) , hour(l_time);
-- https://blog.csdn.net/caiyefly/article/details/127631130
注意:关于union和union all的选择
union和union all的区别是,union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复。
union因为要进行重复值扫描,所以效率低。如果合并没有刻意要删除重复行,那么就使用union all。
代码实现
-- 登录标记1 下线标记-1
select
login_ts l_time,
1 flag
from
user_login_detail
union
select
logout_ts l_time,
-1 flag
from
user_login_detail
-- 按照时间求和
select
sum(flag) over (order by t1.l_time rows between unbounded preceding and current row) sum_l_time
from
(
select
login_ts l_time,
1 flag
from
user_login_detail
union
select
logout_ts l_time,
-1 flag
from
user_login_detail
)t1
-- 拿到最大值 就是同时在线最多人数
select
max(sum_l_time)
from
(
select
sum(flag) over (order by t1.l_time rows between unbounded preceding and current row) sum_l_time
from
(
select
login_ts l_time,
1 flag
from
user_login_detail
union
select
logout_ts l_time,
-1 flag
from
user_login_detail
)t1
)t2
https://blog.csdn.net/caiyefly/article/details/127631130
注意:关于union和union all的选择
union和union all的区别是,union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复。
union因为要进行重复值扫描,所以效率低。如果合并没有刻意要删除重复行,那么就使用union all。