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

select
    artical_id,
    max(s) max_uv
from
    (
        select
            t1.artical_id artical_id,
			# 利用起始时间对各个时间段有共同观看的人群进行分组
            t1.in_time,
			# 用结束阅读时间点与阅读时间段进行匹配得到是否存在同一时间点一起阅读
            sum(
                case
                    when t1.out_time between t2.in_time and t2.out_time  then 1
                    else 0
                end
            ) s
        from
            tb_user_log t1,
            tb_user_log t2
        where
            t1.artical_id = t2.artical_id
            and t1.artical_id <> 0
        group by
            t1.artical_id,
            t1.in_time
    ) t
group by
    artical_id
order by
    max_uv desc