题解 | 牛客直播各科目同时在线人数

# 统计每个科目最大同时在线人数（按course_id排序）
WITH
    t1 AS(
        SELECT
            course_id,
            course_name,
            in_datetime AS dt,
            1 AS diff
        FROM
            attend_tb AS a
            LEFT JOIN course_tb AS c USING (course_id)

        UNION ALL

        SELECT
            course_id,
            course_name,
            out_datetime AS dt,
            -1 AS diff
        FROM
            attend_tb AS a
            LEFT JOIN course_tb AS c USING (course_id)
    ),
    t2 AS(
        SELECT
            course_id,
            course_name,
            SUM(diff) OVER(PARTITION BY course_id ORDER BY dt, diff DESC) AS num
        FROM
            t1
    )

SELECT
    course_id,
    course_name,
    MAX(num) AS max_num
FROM
    t2
GROUP BY
    course_id,
    course_name
ORDER BY
    course_id ASC;

个人习惯选CTE不选子查询写法

注意：

用1和-1分别表示登录和退出，并使用UNION ALL连接；

用SUM()窗口函数来计算Rolling Total；

窗口函数里注意用日期进行排序，不然会不准确。diff排序是锦上添花；

SUM()窗口函数计算滚动值，不需要GROUP BY来聚合。GROUP BY和ORDER BY一起放在最外层。