题解 | #每篇文章同一时刻最大在看人数#
每篇文章同一时刻最大在看人数
http://www.nowcoder.com/practice/fe24c93008b84e9592b35faa15755e48
select artical_id,
max(viewer_ins) max_uv #先列出需要的列
from(select artical_id,
sum(diff) over(partition by artical_id order by dt, diff desc)
viewer_ins ###是partition by且和order by之间没有，连接##diff desc 能够满足先算in再算out
from(
select artical_id,
in_time dt,
1 diff ###所有的in_time都会被算作1记录
from tb_user_log
where artical_id != 0
union all ###利用union all 会保留全部的数据，这样sum（diff）就可以直接算出人数
select artical_id,
out_time dt,
-1 diff 两个表格之间的列都要相同且不需要用（）修饰
from tb_user_log
where artical_id != 0)a
)b
group by artical_id
order by max_uv desc;
字节跳动开奖326人在聊
