step1: 查找room_id，user_id，进入时间，并将进入时间记为tms，将进入行为tag标记为1；

step2: 查找room_id，user_id，离开时间，并将离开时间记为tms，将离开行为tag标记为-1；

step3: 将step1，step2中得到的两张表全连接；

step4: 利用窗口函数按照room_id分组，按照所有时间tms排序，求和；

step5: 按照room_id分组，查找每个room_id最大同时在线人数;

step6: 按照最大同时在线人数降序排序。

select room_id, max(num) as max_num

from(

select

room_id,

user_id,

sum(tag) over(partition by room_id order by tms) as num

from(

select

room_id,

user_id,

in_time as tms,

1 as tag

from user_view_tb

union all

select

room_id,

user_id,

out_time as tms,

-1 as tag

from user_view_tb

)a1

)a

group by room_id order by max_num desc;