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

WITH t_user_log AS
(
SELECT artical_id
        ,in_time
        ,1 AS diff
FROM tb_user_log
WHERE artical_id != 0
UNION ALL
SELECT artical_id
        ,out_time
        ,-1 AS diff
FROM tb_user_log
WHERE artical_id != 0
) 

SELECT artical_id
        ,MAX(instant_viewer_cnt) AS max_uv
FROM(
    SELECT artical_id
            ,SUM(diff) OVER(PARTITION BY artical_id ORDER BY in_time, diff DESC) AS instant_viewer_cnt
    FROM t_user_log
) data1
GROUP BY artical_id
ORDER BY max_uv DESC

• 创建变量diff，并借助窗口函数SUM求和的行范围数据，求每个in time的最大数据人数
• 求和时，不仅要根据in_time顺序求和，对于in_time相同的条件下，先对diff=1的先求和，ORDER BY in_time, diff DESC
• 求和取得每小时最大人数数据后，对于不同artical_id求对应的最大值即可