现有某天短视频直播间用户观看直播间的信息表user_view_tb如下,
(其中字段包含用户id:user_id、直播间id:room_id、 进入时间:in_time,离开时间:out_time)
有直播间信息表room_info_tb如下:
请你统计晚上11-12点之间各直播间的在线人数(包含边界值11:00、12:00),并按在线人数降序排序,以上例子输出结果如下:
drop table if exists user_view_tb; CREATE TABLE user_view_tb( user_id int(10) NOT NULL, room_id int(10) NOT NULL, in_time time NOT NULL, out_time time NOT NULL ); INSERT INTO user_view_tb VALUES(1, 1001, '10:00:00', '10:30:00'); INSERT INTO user_view_tb VALUES(2, 1001, '10:01:00', '10:05:00'); INSERT INTO user_view_tb VALUES(3, 1001, '10:05:00', '10:20:00'); INSERT INTO user_view_tb VALUES(1, 1002, '19:05:00', '20:05:00'); INSERT INTO user_view_tb VALUES(2, 1002, '19:15:00', '19:55:00'); INSERT INTO user_view_tb VALUES(2, 1002, '20:15:00', '20:45:00'); INSERT INTO user_view_tb VALUES(3, 1002, '20:15:00', '20:45:00'); INSERT INTO user_view_tb VALUES(4, 1003, '22:15:00', '23:15:00'); INSERT INTO user_view_tb VALUES(1, 1002, '23:15:00', '23:45:00'); INSERT INTO user_view_tb VALUES(4, 1002, '23:10:00', '23:25:00'); INSERT INTO user_view_tb VALUES(3, 1002, '23:00:00', '23:35:00'); INSERT INTO user_view_tb VALUES(4, 1001, '23:10:00', '23:25:00'); INSERT INTO user_view_tb VALUES(3, 1001, '23:00:00', '23:35:00'); INSERT INTO user_view_tb VALUES(4, 1003, '23:10:00', '23:15:00'); INSERT INTO user_view_tb VALUES(1, 1001, '20:10:00', '20:15:00'); INSERT INTO user_view_tb VALUES(1, 1001, '20:00:00', '23:35:00'); drop table if exists room_info_tb; CREATE TABLE room_info_tb( room_id int(10) NOT NULL, room_name varchar(20) NOT NULL, room_type varchar(20) NOT NULL ); INSERT INTO room_info_tb VALUES(1001, '娱乐大王牌', '娱乐'); INSERT INTO room_info_tb VALUES(1002, '声家班', '搞笑'); INSERT INTO room_info_tb VALUES(1003, '嗨嗨嗨', '搞笑');
1001|娱乐大王牌|3 1002|声家班|3 1003|嗨嗨嗨|1
with t1 as ( select distinct user_id, room_id from user_view_tb where out_time <='24:00:00' and out_time>='23:00:00' ) , t2 as( select t1.room_id, count(distinct t1.user_id) as user_count from t1 group by t1.room_id ) select t2.room_id, rit.room_name, t2.user_count from t2 join room_info_tb rit on t2.room_id=rit.room_id order by t2.user_count desc