“ 现有一份用户观看视频的日志数据，记录了用户观看或者关闭视频的时间点和行为类型，日志数据如下，请统计每个视频每天同时观看的最高人数”
题目：现有一份用户观看视频的日志数据，记录了用户观看或者关闭视频的时间点和行为类型，日志数据如下，请统计每个视频每天同时观看的最高人数。
video_id:视频ID
user_id:用户ID
action_type : 1打开视频,0关闭视频
time: 时间
| video_id | user_id | action_type | action_time |
|---|---|---|---|
| v1 | u1 | 1 | 2022-12-22 00:01:33 |
| v1 | u2 | 1 | 2022-12-22 00:02:33 |
| v1 | u1 | 0 | 2022-12-22 00:02:35 |
| v1 | u3 | 1 | 2022-12-22 00:03:22 |
| v1 | u4 | 1 | 2022-12-22 00:04:15 |
| v1 | u2 | 0 | 2022-12-22 00:05:21 |
| v1 | u3 | 0 | 2022-12-22 00:07:42 |
| v1 | u4 | 0 | 2022-12-22 00:09:33 |
| v2 | u5 | 1 | 2022-12-22 00:11:21 |
| v2 | u5 | 0 | 2022-12-22 00:23:31 |
类似的题目还有：有记录进入和离开游泳池的时间记录，求一天中同时在泳池内的人数。
考点：sum(**)over(partion by ** order by **)聚合函数配合开窗使用时，使用了order by 之后,只计算到当前行
处理过程
-- 创建表create table video_log(video_id string,user_id string,action_type int,action_time string);-- 插入数据insert into video_log values('v1','u1',1,'2022-12-22 00:01:33'),('v1','u2',1,'2022-12-22 00:02:33'),('v1','u1',0,'2022-12-22 00:02:35'),('v1','u3',1,'2022-12-22 00:03:22'),('v1','u4',1,'2022-12-22 00:04:15'),('v1','u2',0,'2022-12-22 00:05:21'),('v1','u3',0,'2022-12-22 00:07:42'),('v1','u4',0,'2022-12-22 00:09:33'),('v2','u5',1,'2022-12-22 00:11:33'),('v2','u5',0,'2022-12-22 00:23:33');
根据状态增加计数字段
selectvideo_id,user_id,action_type,case when action_type = 1 then 1 else -1 end as num,action_timefromvideo_log;
2. 排序计算
selectvideo_id,sum(num) over(partition by video_id order by action_time asc ) as video_numfrom(selectvideo_id,user_id,action_type,case when action_type = 1 then 1 else -1 end as num,action_timefrom video_log) t
3. 计算最大同时在线人数
selectvideo_id,max(video_num) as max_numfrom(selectvideo_id,sum(num) over(partition by video_id order by action_time asc ) as video_numfrom(selectvideo_id,user_id,action_type,case when action_type = 1 then 1 else -1 end as num,action_timefrom video_log)t) ttgroup by video_id;
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
上一篇：
把列表中的ID转换成文字描述
请登录后发表评论
登录
全部评论
