SQL日志统计-视频同时观看最高人数

现有一份用户观看视频的日志数据，记录了用户观看或者关闭视频的时间点和行为类型，日志数据如下，请统计每个视频每天同时观看的最高人数。

video_id:视频ID

user_id:用户ID

action_type : 1打开视频,0关闭视频

time: 时间 

CREATE TABLE video_log(
video_id VARCHAR(255),
user_id  VARCHAR(255),
action_type int,
action_time TIMESTAMP
);


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');

1. 根据状态增加计数字段

   select
     video_id,
     user_id,
     action_type,
     case when action_type = 1 then 1 else -1 end as num,
     action_time
   from
     video_log;

2. 排序计算

select
    video_id,
    sum(num) over(partition by video_id order by action_time asc ) as video_num
from
    (select
        video_id,
        user_id,
        action_type,
        case when action_type = 1 then 1 else -1 end as num,
        action_time
     from video_log
    ) t

3. 计算最大同时在线人数

select
    video_id,
    max(video_num) as max_num
from
    (select
        video_id,
        sum(num) over(partition by video_id order by action_time asc ) as video_num
    from
        (select
            video_id,
            user_id,
            action_type,
            case when action_type = 1 then 1 else -1 end as num,
            action_time
         from video_log
        )t
    ) tt
group by video_id;