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

SELECT
    course_id,
    course_name,
    MAX(sum_count) AS max_num
FROM
    course_tb
    JOIN (
        SELECT
            course_id,
            count,
            time,
            SUM(count) OVER (
                PARTITION BY
                    course_id
                ORDER BY
                    time
            ) AS sum_count
        FROM
            (
                SELECT
                    user_id,
                    course_id,
                    in_datetime AS time,
                    1 AS count
                FROM
                    attend_tb
                UNION
                SELECT
                    user_id,
                    course_id,
                    out_datetime AS time,
                    -1 AS count
                FROM
                    attend_tb
            ) AS tb1
    ) AS tb2 USING (course_id)
GROUP BY
    course_id,
    course_name
ORDER BY
    course_id

首先将所有的时间UNION到一个table，对于进入的直播间的时间，+1；对于离开直播间的时间 -1；

            SELECT
                user_id,
                course_id,
                in_datetime AS time,
                1 AS count
            FROM
                attend_tb
            UNION
            SELECT
                user_id,
                course_id,
                out_datetime AS time,
                -1 AS count
            FROM
                attend_tb

现在有了一个这样的表，接下来对于这个表中的count进行一个累计求和:

SUM(count) OVER (PARTITION BY course_id ORDER BY time)

用course_id进行分组，order by时间，那我们就有了每个直播间在每个时间段的累计人数。

大概是这样的一个图。

接下来就很简单了，选择题目需要的三个变量，course_id, course_name, MAX(sum_count)用course_tb JOIN 我们刚刚创建出来的表格就行了。