【Hive SQL 每日一题】在线峰值人数计算

测试数据

-- 创建 user_activity 表
DROP TABLE IF EXISTS user_activity ;
CREATE TABLE user_activity (user_id STRING,activity_start TIMESTAMP,activity_end TIMESTAMP
);-- 插入数据
INSERT INTO user_activity VALUES
('user1', '2024-07-11 08:00:00', '2024-07-11 09:00:00'),
('user2', '2024-07-11 08:30:00', '2024-07-11 09:30:00'),
('user3', '2024-07-11 09:00:00', '2024-07-11 10:00:00'),
('user4', '2024-07-11 09:15:00', '2024-07-11 09:45:00'),
('user5', '2024-07-11 09:30:00', '2024-07-11 10:30:00'),
('user6', '2024-07-11 10:00:00', '2024-07-11 11:00:00'),
('user7', '2024-07-11 08:05:00', '2024-07-11 08:55:00'),
('user8', '2024-07-11 08:45:00', '2024-07-11 09:15:00'),
('user9', '2024-07-11 09:05:00', '2024-07-11 10:05:00'),
('user10', '2024-07-11 09:25:00', '2024-07-11 10:25:00'),
('user11', '2024-07-11 08:10:00', '2024-07-11 09:10:00'),
('user12', '2024-07-11 08:20:00', '2024-07-11 09:20:00'),
('user13', '2024-07-11 08:35:00', '2024-07-11 09:35:00'),
('user14', '2024-07-11 08:50:00', '2024-07-11 09:50:00'),
('user15', '2024-07-11 09:10:00', '2024-07-11 10:10:00'),
('user16', '2024-07-11 09:20:00', '2024-07-11 10:20:00'),
('user17', '2024-07-11 09:40:00', '2024-07-11 10:40:00'),
('user18', '2024-07-11 10:05:00', '2024-07-11 11:05:00'),
('user19', '2024-07-11 10:15:00', '2024-07-11 11:15:00'),
('user20', '2024-07-11 10:25:00', '2024-07-11 11:25:00');

需求说明

计算某系统每个时间点的在线峰值人数。

结果示例：

activity_time	max_users
2024-07-11 08	8
2024-07-11 09	9
…	…

结果按 activity_time 升序排列。

其中：

• activity_time 表示统计的时间点；
• max_users 表示该时间点内的最高峰值人数。

需求实现

selectdate_format(activity_time,'yyyy-MM-dd HH') activity_time,max(total_users) max_users
from(selectactivity_time,sum(flag) over(order by activity_time) total_usersfrom(selectactivity_start activity_time,1 flagfromuser_activityunion allselectactivity_end activity_time,-1 flagfromuser_activity)t1)t2
group bydate_format(activity_time,'yyyy-MM-dd HH');

输出结果如下：

本题最核心的地方在于子查询 t2 中的逻辑：

	selectactivity_time,sum(flag) over(order by activity_time) total_usersfrom(selectactivity_start activity_time,1 flagfromuser_activityunion allselectactivity_end activity_time,-1 flagfromuser_activity)t1;

首先，我们在子查询 t1 中将列转为了行，那为什么需要这样做呢？当然是为了方便统计。

我们来想想，当一个用户登录后进入系统，那么人数是不是会 +1，反之当用户退出时，人数是不是会 -1。

当我们把登录和退出时间都放在同一列时，按照时间排序，是不是就可以精准算出每个时刻在线的人数了，这就是子查询 t2 做的事情，通过窗口函数进行累加计算，t2 结果如下所示：

2024-07-11 08:00:00     1
2024-07-11 08:05:00     2
2024-07-11 08:10:00     3
2024-07-11 08:20:00     4
2024-07-11 08:30:00     5
2024-07-11 08:35:00     6
2024-07-11 08:45:00     7
2024-07-11 08:50:00     8
2024-07-11 08:55:00     7
2024-07-11 09:00:00     7
2024-07-11 09:00:00     7
2024-07-11 09:05:00     8
2024-07-11 09:10:00     8
2024-07-11 09:10:00     8
2024-07-11 09:15:00     8
2024-07-11 09:15:00     8
2024-07-11 09:20:00     8
2024-07-11 09:20:00     8
2024-07-11 09:25:00     9
2024-07-11 09:30:00     9
2024-07-11 09:30:00     9
2024-07-11 09:35:00     8
2024-07-11 09:40:00     9
2024-07-11 09:45:00     8
2024-07-11 09:50:00     7
2024-07-11 10:00:00     7
2024-07-11 10:00:00     7
2024-07-11 10:05:00     7
2024-07-11 10:05:00     7
2024-07-11 10:10:00     6
2024-07-11 10:15:00     7
2024-07-11 10:20:00     6
2024-07-11 10:25:00     6
2024-07-11 10:25:00     6
2024-07-11 10:30:00     5
2024-07-11 10:40:00     4
2024-07-11 11:00:00     3
2024-07-11 11:05:00     2
2024-07-11 11:15:00     1
2024-07-11 11:25:00     0

最终按时间点分组聚合，通过 max 函数找出各个时间点内最大的峰值人数，完成~