文章目录
- 测试数据
- 需求说明
- 需求实现
测试数据
-- 创建 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
表示该时间点内的最高峰值人数。
需求实现
select
date_format(activity_time,'yyyy-MM-dd HH') activity_time,
max(total_users) max_users
from
(select
activity_time,
sum(flag) over(order by activity_time) total_users
from
(select
activity_start activity_time,
1 flag
from
user_activity
union all
select
activity_end activity_time,
-1 flag
from
user_activity)t1
)t2
group by
date_format(activity_time,'yyyy-MM-dd HH');
输出结果如下:
本题最核心的地方在于子查询 t2
中的逻辑:
select
activity_time,
sum(flag) over(order by activity_time) total_users
from
(select
activity_start activity_time,
1 flag
from
user_activity
union all
select
activity_end activity_time,
-1 flag
from
user_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
函数找出各个时间点内最大的峰值人数,完成~