题解 | 短视频直播间晚上11-12点之间各直播间的在线人数

WITH shift_user AS(
    SELECT 
        room_id,
        user_id,
        in_time,
        HOUR(date_add(in_time ,interval 12 hour)) MOD 24  AS shift_in_time,
        out_time,
        HOUR(date_add(out_time , interval 12 hour)) MOD 24  AS shift_out_time   
    FROM user_view_tb
),-- [23,24] -> [11,12] -> [1,2], 相当流氓的做了一个shift
shift_select AS(
    SELECT
        room_id,
        user_id
    FROM shift_user
    WHERE 
        (shift_in_time<=11 AND shift_out_time between 11 AND 12) OR --[shift_in_time,shift_out_time]与[11,12]窗口有重叠的条件
        (shift_out_time>=12 AND shift_in_time between 11 AND 12) OR
        ((shift_in_time between 11 AND 12) AND (shift_out_time between 11 AND 12))
)
SELECT 
    shift_select.room_id,
    room_info.room_name,
    count(distinct shift_select.user_id) AS user_count
FROM shift_select
LEFT JOIN room_info_tb AS room_info
ON shift_select.room_id=room_info.room_id
GROUP BY 
    shift_select.room_id,
    room_info.room_name;