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

with q0 as (select artical_id,in_time as cur_time,1 as num
from tb_user_log 
union all 
select artical_id,out_time as cur_time,-1 as num
from tb_user_log 
),

q1 as (select 
    artical_id,
    sum(num) OVER(PARTITION BY artical_id ORDER BY cur_time,num desc  ) cur_num
from q0
where artical_id!=0
) 


select artical_id,max(cur_num)
from q1
group by artical_id
order by 2 desc

这题的技巧以前在别的地方看过，将进入时间和出去时间用1和-1表示，用户进入+1，用户出去-1。窗口函数累计计算

1. 转换格式，拆分列。方便后续统计直接计算咯
2. where筛选条件
3. 窗口函数：排序后累加，（进入出去优先级）
4. 求最大