题解 | #每篇文章同一时刻最大在看人数#
每篇文章同一时刻最大在看人数
https://www.nowcoder.com/practice/fe24c93008b84e9592b35faa15755e48
思路:
- 先对用户行为打标,进入为1,离开为-1
- 进入表
- 出去表
- union all联结两表
- 按照时间,顺序,累加用户状态,可得到用户同时在线人数。
- 同时在线人数
- sum(tag) over(partition by artical_id order by dt,tag desc) -- 先记录增加,后记录减少,所以要tag desc
- 条件,artical_id!=0
- 取最大,并排序
- artical_id 分组
- 排序
select artical_id, max(uv) as max_uv from( select artical_id, dt, sum(tag) over(partition by artical_id order by dt,tag desc) as uv from( select artical_id, in_time as dt, 1 as tag from tb_user_log union all select artical_id, out_time as dt, -1 as tag from tb_user_log ) t1 where artical_id!=0 ) t2 group by artical_id order by max_uv desc