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

WITH events AS(
    SELECT 
        artical_id ,
        in_time as dt,
        1 as change_num
    FROM 
        tb_user_log 
    UNION ALL 
    SELECT 
        artical_id,
        out_time as dt ,
        -1 as change_num
    FROM 
        tb_user_log
),
current AS (
    SELECT 
        artical_id, 
        SUM(change_num)OVER(
            partition by artical_id order by dt,change_num desc 
        ) as current_view 
    FROM    
        events 
)
SELECT 
    artical_id ,
    max(current_view) as max_uv
FROM 
    current
where 
    artical_id != 0 
group by 
    artical_id 
order by 
    max_uv desc ; 

这道题有点难度，想到了python的滑窗问题。要建立一个计数器，另外排序的时候，时间相同先进后出。

最后不要忘了artical_id ！= 0 这个条件。这道题也可以统计，实时浏览量