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

select artical_id,max(num) as max_uv from (
    select t1.artical_id,t1.id,
    sum(case when t1.out_time between t2.in_time and t2.out_time then 1 else 0 end) as num
    from tb_user_log t1 left join tb_user_log t2
            on t1.artical_id = t2.artical_id
    where t1.artical_id >0
    group by t1.artical_id,t1.id

    ) t 
group by artical_id
order by max_uv desc

1、这个题目首先想到的思路就是利用自链接，通过文章相连
2、这样同样文章内的内容，就会产生笛卡尔积，每行都可以和该文章的所有行进行对比
3、只要该行的结束时间在其他行开始和结束之间，那么就存在同一时刻，这样每行的记录都能算出他有多少同一时刻一起看的
4、然后利用id,分组，求出每行的同一时刻人数
5、最后使用最值函数，求出同一时刻的峰值人数。

看了评论区后，发现评论区是使用的另一个思路，更方便理解

select artical_id,max(num) as max_uv from (
select artical_id,SUM(tag) over(Partition by artical_id order by dt ,tag desc) as num from (
select artical_id,uid,in_time as dt ,1 as tag from tb_user_log where artical_id >0
union all
select artical_id,uid,out_time as dt ,-1 as tag from tb_user_log where artical_id >0)t)t1
group by artical_id
order by max_uv desc;

这个思路的关键是将用户的进入和离开都做了标识，然后利用窗口函数的累计计算逻辑，进行求和。