题解 | #牛客直播各科目同时在线人数#
WITH uv_tb AS (
SELECT course_id, user_id, in_datetime AS dt, 1 AS uv
FROM attend_tb
UNION ALL
SELECT course_id, user_id, out_datetime, -1 AS uv
FROM attend_tb
),
cumulative_uv AS (
SELECT
u.course_id,
c.course_name,
u.dt,
SUM(u.uv) OVER (PARTITION BY u.course_id ORDER BY u.dt) AS uv_cnt
FROM uv_tb u
JOIN course_tb c ON c.course_id = u.course_id
)
SELECT
course_id,
course_name,
MAX(uv_cnt) AS max_num
FROM cumulative_uv
GROUP BY 1, 2
ORDER BY 1;
当我们想要解决“同时在线”问题时，我们的目标是计算每门课程在任何给定时间点的在线人数，并找出这个数字的最大值。为了实现这一点，我们可以利用窗口函数，它使我们能够对一组行执行计算，这些行以某种方式与当前行相关联，而不必将它们分割成不同的组。
在这个具体的问题中，我们使用了窗口函数SUM() OVER()来累积每门课程的在线人数。这里是如何操作的：
-
构建时间点数据： 首先，我们需要知道每个用户何时进入或离开课程，这就是为什么我们要从attend_tb表中获取in_datetime和out_datetime，并分别赋予它们+1（表示进入）和-1（表示离开）的uv值。这样，每当有人进入课程，计数就会增加，每当有人离开，计数就会减少。
-
使用窗口函数累积在线人数： 接下来，我们使用SUM() OVER()窗口函数来累积在线人数。通过指定PARTITION BY course_id，我们告诉数据库，我们要对每个课程分别进行计算。然后，我们使用ORDER BY dt来确保在线人数是根据时间顺序累加的。因此，对于每个课程的每个时间点，uv_cnt都表示从课程开始到那个时间点为止的总在线人数。
-
找到最大值： 有了这个累积的在线人数，我们就可以很容易地找到每门课程的最大同时在线人数。在外层查询中，我们使用MAX(uv_cnt)来选出每门课程的最大值，这就是我们想要的结果。
通过这种方式，窗口函数使我们能够在不断变化的时间序列中跟踪和计算在线人数的累积值，而无需对原始数据进行复杂的分组或多次扫描。
字节跳动一面1279人在聊
查看11道真题和解析