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

WITH t1 AS (SELECT uid,artical_id,in_time dt,1 diff FROM tb_user_log WHERE artical_id <> 0	
		UNION
		SELECT uid,artical_id,out_time dt,-1 diff FROM tb_user_log WHERE artical_id <> 0	),
    t2 as(
      
		SELECT 
		artical_id, 
    	sum(diff) over ( PARTITION BY artical_id ORDER BY dt ,diff desc) as sum_diff
		FROM t1)
SELECT
	artical_id,max(sum_diff) max_uv
FROM
	t2
GROUP BY
	artical_id
ORDER BY
	max_uv desc