WITH t1 AS (
SELECT course_id,
user_id,
in_datetime,
1 AS uv
FROM attend_tb
UNION ALL
SELECT course_id,
user_id,
out_datetime,
-1 AS uv
FROM attend_tb
),
t2 AS (
SELECT course_id,
in_datetime,
SUM(uv)
OVER (PARTITION BY course_id
ORDER BY in_datetime ASC,
uv DESC) AS time_num
FROM t1
),
t3 AS (
SELECT course_id,
max(time_num) AS max_num
FROM t2
GROUP BY course_id
),
t4 AS (
SELECT a.course_id,
a.course_name,
max_num
FROM course_tb a
LEFT JOIN t3
ON a.course_id = t3.course_id
ORDER BY a.course_id ASC
)
SELECT * FROM t4;
# 这里的细节是：按照课程编号分组，时间升序，uv降序，因为先进入直播间再出去直播间！
