数据准备:
create table login_logout(userid int,logintime string,logouttime string);
insert into login_logout values(1,'2020-10-01 11:11:11','2020-10-01 13:11:12'),(2,'2020-10-01 12:11:11','2020-10-01 15:11:11'),(3,'2020-10-01 13:11:11','2020-10-01 16:11:11'),(4,'2020-10-01 14:11:11','2020-10-01 15:11:11'),(5,'2020-10-01 14:11:11','2020-10-01 16:11:11'),(6,'2020-10-01 14:11:11','2020-10-01 17:11:11');
select max(zaixian) from
(
select
sum(count) over(order by time1) as zaixian
from
(
select
userid,logintime as time1,1 as count
from login_logout
union all
select
userid,logouttime as time1,-1 as count
from login_logout
) x
) m
;
结果是5
sum over可以求出到当前行的一个累计值。