step1: 按room_id相等的条件将user_view_tb左连接room_info_tb;

step2:查找相关信息，为了方便计算，注意当结束时间为"00:00:00"时，需设置为24:00:00；

step3:按照room_type，room_name分组，统计各个直播间的平均在线时长；

step4:按平均观看时间降序排序。

select room_type, room_name, ROUND(AVG((UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(in_time))/60), 2) as duration_min

from(

select user_id, a.room_id, in_time, out_time, room_name, room_type,

IF(out_time='00:00:00', time('24:00:00'), out_time) as end_time

from user_view_tb a

left join room_info_tb b

on a.room_id=b.room_id

)t

group by room_type, room_name

Order by duration_min desc;