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

WITH schedule_user AS (
    SELECT uid, artical_id, in_time as tt, 1 AS UV
    FROM tb_user_log
    UNION ALL
    SELECT uid, artical_id, out_time as tt, -1 AS UV
    FROM tb_user_log
),
t2 as (
    select artical_id, tt,sum(UV) over (partition by artical_id order by tt,UV desc) as max_uv
    from schedule_user
)



select artical_id,
        max(max_uv) as max_uv
from t2
where artical_id<>0
group by artical_id
order by max_uv desc;

1. 统计分析人流量时候使用Union all 可以把相同用户的操作看作是独立事件
2. 窗口函数 SUM 窗口函数是说每个文章的总流量是由按照时间和从大到小的UV来累加执行

sum(UV) over (partition by artical_id order by tt,UV desc)