最高峰同时直播人数

有如下数据记录直播平台主播上播及下播时间，根据该数据计算出平台最高峰同时直播人数。

建表语句 

CREATE TABLE IF NOT EXISTS t1_livestream_log (
    user_id INT, -- 主播ID
    start_time varchar(50), -- 开始时间
    end_time varchar(50) -- 结束时间
);
 
insert into t1_livestream_log(user_id, start_time, end_time) values 
(1,'2024-04-29 01:00:00','2024-04-29 02:01:05'),
(2,'2024-04-29 01:05:00','2024-04-29 02:03:18'),
(3,'2024-04-29 02:00:00','2024-04-29 04:03:22'),
(4,'2024-04-29 03:15:07','2024-04-29 04:33:21'),
(5,'2024-04-29 03:34:16','2024-04-29 06:10:45'),
(6,'2024-04-29 05:22:00','2024-04-29 07:01:08'),
(7,'2024-04-29 06:11:03','2024-04-29 09:26:05'),
(3,'2024-04-29 08:00:00','2024-04-29 12:34:27'),
(1,'2024-04-29 11:00:00','2024-04-29 16:03:18'),
(8,'2024-04-29 15:00:00','2024-04-29 17:01:05');

第一步:统计每个时间的人数变化,start_time时为1,end_time时为-1

UNION：默认去除重复的行，只保留唯一的数据行。如果两个查询结果具有相同的行，则只返回其中一个行。这通常需要额外的排序和去重操作，可能会影响性能。

UNION ALL：合并查询结果时保留所有行，包括重复的行。如果两个查询结果具有相同的行，则返回它们两次。这通常不需要额外的排序和去重操作，因此在处理大量数据时可能会有更好的性能

因为多个员工同时上线,都需要统计人数变化,故用union all

select
user_id,
start_time as action_time,
1 as change_cnt
from t1_livestream_log
union all 
select
user_id,
end_time as action_time,
-1 as change_cnt
from t1_livestream_log

结果: 

 

第二步:使用窗口函数,对操作时间排序后进行累积求和 

窗口函数语法:

窗口函数的三个参数:

1. PARTITION BY：如果没有指定PARTITION BY子句，窗口函数将在结果集的全局范围内进行计算。

2. ORDER BY：大多数窗口函数需要一个ORDER BY子句来定义窗口的排序。

3. ROWS或RANGE：这是窗口函数的frame_clause部分，用于定义窗口的开始和结束位置

如果没有指定frame_extent，窗口函数默认使用RANGE子句，这意味着窗口是基于排序列的值的范围来定义的。

如果没有明确指定frame_clause，窗口的默认范围将取决于是否存在ORDER BY子句。

有ORDER BY子句时的默认窗口：

• 默认窗口会从分区的第一行开始，一直到当前行，包括当前行的所有同等行

没有ORDER BY子句时的默认窗口： 

• 默认窗口将包含分区中的所有行，因为缺少ORDER BY子句意味着所有行在该分区中都是同等的。这相当于使用了RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING的窗口定义。

select
user_id,
action_time,
change_cnt,
sum(change_cnt)over(order by action_time asc) as online_cnt
from 
(
select
user_id,
start_time as action_time,
1 as change_cnt
from t1_livestream_log
union all 

select
user_id,
end_time as action_time,
-1 as change_cnt
from t1_livestream_log
) as t

 

第三步求取累计求和中的最大值，即为当天最高峰同时直播人数 查询语句

最终答案:

select max(online_cnt) 
from
(
select action_time , sum(change_cnt) over(order by action_time asc ) as online_cnt
 from 
(select
user_id,
start_time as action_time,
1 as change_cnt
from t1_livestream_log
union all 
select
user_id,
end_time as action_time,
-1 as change_cnt
from t1_livestream_log) as temp 
) as online_table