题解 | #某音短视频直播间各个时间段的在线人数#

out time和in time有一个在范围内就可以算做该时间段在线

Select

COUNT(IF((out_time BETWEEN '7:00:00' and '9:00:00') OR(in_time BETWEEN '7:00:00' and '9:00:00'), TRUE, NULL)) AS '早通勤',

COUNT(IF((out_time BETWEEN '18:00:00' and '20:00:00') OR(in_time BETWEEN '18:00:00' and '20:00:00'), TRUE, NULL)) AS '晚通勤',

COUNT(IF((out_time BETWEEN '12:00:00' and '14:00:00') OR(in_time BETWEEN '12:00:00' and '14:00:00'), TRUE, NULL)) AS '午休',

COUNT(IF((out_time BETWEEN '22:00:00' and '24:00:00') OR(in_time BETWEEN '22:00:00' and '24:00:00'), TRUE, NULL)) AS '临睡'

From user_view_tb;