统计直播间最大同时在线人数

SELECT live_id, max(ct) max_user_count
FROM
(
 SELECT  live_id,sum(flag) over(partition by live_id order by datetime) ct  from
  (select live_id,in_datetime datetime, 1 flag from tb_live_events union all select live_id,out_datetime datetime, -1 flag from tb_live_events) t1
) t2 GROUP BY live_id

步骤解析

1. 将表中的 in_datetime 记录和 out_datetime 记录合并到了一起。对于 in_datetime 对应的行，flag 设为 1 ，表示进入直播间；对于 out_datetime 对应的行，flag 设为 -1 ，表示离开直播间。

select live_id,in_datetime datetime, 1 flag from tb_live_events union all select live_id,out_datetime datetime, -1 flag from tb_live_events

得到的结果如下

2. 这里利用到了mysql中的窗口函数：SUM() OVER()，完整查询语句如下所示。

SELECT  live_id,sum(flag) over(partition by live_id order by datetime) ct  from
  (select live_id,in_datetime datetime, 1 flag from tb_live_events union all select live_id,out_datetime datetime, -1 flag from tb_live_events) t1

这里用到了mysql/sqlserver中的窗口函数：SUM() OVER()，参数解析如下所示：

• SUM(flag)：表示对 flag 字段进行求和操作。flag 只有 1 和 -1 两个值，1 代表进入，-1 代表离开，求和操作是后续计算当前直播状态下在线人数（或类似业务计数 ）的关键。
• OVER：是窗口函数的关键字，标识接下来要定义窗口的相关规则。
• PARTITION BY live_id：按照 live_id 对数据进行分区。也就是说，会将相同 live_id 的数据划分到同一个组内，分别对每个组内的数据进行后续计算。这样可以保证针对每个不同的直播（由 live_id 区分 ）单独计算相关指标。
• ORDER BY datetime：在每个分区（即每个 live_id 对应的组 ）内，按照 datetime 字段进行排序。这很重要，因为后续的求和操作是按照时间先后顺序进行的，比如先进入（flag = 1）再离开（flag = -1），才能准确计算出每个时间点的在线人数情况。
• ct：是给计算得到的结果列起的别名，方便在查询结果中展示和引用。

首先按照live_id进行分组，每个组内按照datetime进行升序排序，得到如下结果

然后对每个组内的数据进行累加计算，当flag=1的时候，累加+1，当flag=-1的时候，累加-1。这里对进入直播间和离开直播间分别定义一个累加标识值intotal，outtotal。

先看flag=1的数据

• 第一条数据，flag=1，由于只有1条数据，所以intotal=1
• 第二条数据，flag=1，此时intotal=1+1=2
• 第三条数据，flag=1，此时intotal=2+1=3
• 第四条数据，flag=1，此时intotal=3+1=4
• 第五条数据，flag=-1，所以intotal=4-1=3
• 第六条数据，flag=-1，此时intotal=3-1=2
• 第七条数据，flag=-1，此时intotal=2-1=1
• 第八条数据，flag=-1，此时intotal=1-1=0

再看flag=2的数据

• 第一条数据，flag=1，由于只有1条数据，所以outtotal=1
• 第二条数据，flag=-1，此时outtotal=1-1=0
• 第三条数据，flag=1，此时outtotal=0+1=1
• 第四条数据，flag=-1，此时outtotal=1-1=0

最终得到的结果如下

3. 在上述2的基础上再对live_id分组，同时取ct最大的值，由前一步结果可知，当live_id为1时，最大ct为4；当live_id为2时，最大ct为1。因此最终得到每个直播间最大同时在线人数分别为4和1。