标记进入时间为1,退出时间为-1,通过union all将两个标记好的表格合并成一张表格,通过sum()over()窗口函数,可以求出在线人数。最后通过max聚合函数,求出最大的在线人数。具体题目跟代码实现如下:
SELECT artical_id,MAX(sums) max_uv
FROM
(SELECT *,
SUM(temp1.uv)over(PARTITION BY artical_id ORDER BY TIME,uv desc) sums
FROM
(SELECT uid,artical_id,in_time TIME, 1 uv FROM tb_user_log WHERE artical_id!=0
UNION ALL
SELECT uid,artical_id,out_time TIME, -1 uv FROM tb_user_log WHERE artical_id!=0) temp1) temp2
GROUP BY artical_id
ORDER BY max_uv DESC