考查编码和窗口函数的巧妙使用
具体思路: 根据观看进入时间, 和退出时间进行编码,
select ul.artical_id, ul.in_time as view_time, 1 as `code` from tb_user_log ul where ul.artical_id != 0 # 这里需要使用union all, 可能会存在同一个视频观看时间相同的情况 select ul.artical_id, ul.out_time as view_time, -1 as `code` from tb_user_log ul where ul.artical_id != 0
可得: 每个进入时刻的编码为1, 退出时的编码为-1, 那么此时就可以使用窗口函数, 对artical_id进行partition by, 然后根据时间列进行升序排序, 进行累加, 有人进入则+1, 有人退出则-1, 需要注意的是, 当同一时刻有人退出又有人进入时, 先计算进入的. 则还需要根据code
进行降序
select t2.artical_id, max(view_num) as max_uv from ( select t1.artical_id, t1.view_time, t1.code, sum(t1.code) over(partition by t1.artical_id order by t1.view_time, t1.`code` desc) as view_num from ( select ul.artical_id, ul.in_time as view_time, 1 as `code` from tb_user_log ul where ul.artical_id != 0 # 这里需要使用union all union all select ul.artical_id, ul.out_time as view_time, -1 as `code` from tb_user_log ul where ul.artical_id != 0 ) t1 ) t2 group by t2.artical_id order by max_uv desc