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

-- 思路：

-- 1.对in_time与out_time编码：in观看人数+1，out观看人数-1,然后union all 结果为临时表temp

-- 2.如果同一时刻有进入也有离开时，先记录用户数增加再记录减少

-- 3.结果按最大人数降序

-- 思路：
--       1.对in_time与out_time编码：in观看人数+1，out观看人数-1,然后union all 结果为临时表temp
--       2.如果同一时刻有进入也有离开时，先记录用户数增加再记录减少
--       3.结果按最大人数降序
with
    t as (
        -- 2.如果同一时刻有进入也有离开时，先记录用户数增加再记录减少
        select
            artical_id,
            dt,
            sum(diff) over (
                partition by
                    artical_id
                order by
                    dt,
                    diff desc
            ) as instant_viewer_cnt
        from
            (
                --  1.对in_time与out_time编码：in观看人数+1，out观看人数-1,然后union all 结果为临时表temp    
                select
                    artical_id,
                    in_time as dt,
                    1 as diff
                from
                    tb_user_log
                where
                    artical_id != 0
                union all
                select
                    artical_id,
                    out_time as dt,
                    -1 as diff
                from
                    tb_user_log
                where
                    artical_id != 0
            ) tmp
    )
    -- 3.结果按最大人数降序
select
    artical_id,
    max(instant_viewer_cnt) as max_uv
from
    t
group by
    artical_id
order by
    max_uv desc