题解 | #每篇文章同一时刻最大在看人数#
每篇文章同一时刻最大在看人数
https://www.nowcoder.com/practice/fe24c93008b84e9592b35faa15755e48
1.根据用户进入和离开文章,进入用1表示,离开用-1表示,使用窗口函数sum() over(partitioin by …… order by……)实现当前在看人数的累加。
partition by 后面是要统计的组,order by后面是累加时的全表数据排序方式。
2.首先要获得1和-1表示的用户进入和离开的信息,使用union 关键字完成(注意判断0值)
3.组合sql句子,得到最终答案
#求每个文章的瞬时最大在看人数 select t2.artical_id,max(t2.curr_cnt) as max_uv from (select t1.artical_id, #按照artical_id分组,组内进行累加,累加方向:按照mark_time升序、flag倒序 sum(t1.flag) over(partition by t1.artical_id order by mark_time,flag desc) as curr_cnt from #构造1和-1表示的用户进入、离开文章的详细信息 (select artical_id,in_time as mark_time, 1 as flag from tb_user_log where artical_id!=0 union all select artical_id,out_time as mark_time, -1 as flag from tb_user_log where artical_id!=0 order by artical_id,mark_time) as t1) as t2 #取每个文章的最大值 group by t2.artical_id order by max_uv desc