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

# ## 最大同时在线人数：（有一个固定的套路，先记住甚至背下来就行）
# ## 第一步 将用户的进入时间单列出来，记为1，将离开时间单列出来记为-1
# ## 第二步 使用窗口函数对计数 1或-1 进行累计求和，但同一时刻有进有出先算进后算出：先按照时间排序，然后按照计数排序
# ## 第三步 在每个分组里面求最大的累计和 就是最多同时在线的人数了

# ## 第一步:  将in_time和out_time统一记为dt列，值分别为1和-1 记为diff列
# 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
# ORDER BY artical_id,df

# ## 第二步： 加入窗口函数来累加
# SELECT
#     artical,dt,SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt) AS instant_viewer_cnt
# FROM(
#     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 diffFROM tb_user_log
#     WHERE artical_id != 0
#     ORDER BY artical_id,df
# )

## 第三步： 取出最大值
SELECT 
    artical_id,MAX(instant_viewer_cnt) AS max_uv
FROM(
    SELECT
    artical_id,dt,SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt,diff DESC) AS instant_viewer_cnt
    FROM(
        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
         )t1
     )t2
GROUP BY artical_id
ORDER BY max_uv DESC