题解 | 每篇文章同一时刻最大在看人数

with a as(
select uid,artical_id,in_time as dt, 1 as flag
from tb_user_log
where artical_id !=0
union 
select uid,artical_id,out_time as dt, -1 as flag
from tb_user_log
where artical_id !=0)
select artical_id,max(uv) as max_uv from(
select artical_id,dt,
sum(flag) over(partition by artical_id order by dt, flag desc) as uv
from a
order by artical_id)b
group by artical_id
order by max_uv desc

正确：sum(flag) over(partition by artical_id order by dt,flag desc) as uv：对每个文章，按时间顺序逐行累加 diff。如果同一时间点有多个记录（dt相同），则按 diff 降序排列后累加。最终结果是一个随时间推进的累计值

错误：sum(flag) over(partition by artical_id,dt order by flag desc) as uv：每个文章+时间组合，按 diff 降序逐行累加。由于每个 dt 是独立的分区，不同时间的记录不会互相影响。最终结果是每个时间点内部的累加值，而非跨时间的累计值。