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

明确题意：

统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序

问题分解：

• 计算每篇文章当前时刻的在看人数（当前已进入-当前已离开=当前在看）：
  • 跟踪每一时刻的人数变动：
    • 每条记录的进入时间表示进入人数多了1：SELECT artical_id, in_time as at_time, 1 as uv FROM tb_user_log
    • 每条记录的离开时间表示离开人数多了1：SELECT artical_id, out_time as at_time, -1 as uv FROM tb_user_log
    • 合并上面两个结果：UNION ALL
  • 过滤掉非文章浏览的记录：WHERE artical_id != 0
  • 计算当前在看人数，在同一篇文章分区内按时间升序按进入优先排序：SUM(uv) over(PARTITION BY artical_id ORDER BY at_time, uv DESC) as current_max
• 按文章ID分组：GROUP BY artical_id
• 计算最大在看：MAX(current_max) as max_uv

细节问题：

• 表头重命名：as
• 按最大人数降序排序：ORDER BY max_uv DESC;

完整代码：

SELECT artical_id, MAX(current_max) as max_uv
FROM (
    SELECT artical_id, at_time,
        SUM(uv) over(PARTITION BY artical_id ORDER BY at_time, uv DESC) as current_max
    FROM (
        SELECT artical_id, in_time as at_time, 1 as uv FROM tb_user_log
        UNION ALL
        SELECT artical_id, out_time as at_time, -1 as uv FROM tb_user_log
        ORDER BY at_time
    ) as t_uv_at_time
    WHERE artical_id != 0
) as t_artical_cur_max
GROUP BY artical_id
ORDER BY max_uv DESC;