思路如下:
如果同一个时刻既有进入也有退出,但是要先算进入的,再算退出的,需要row_number() ,因为开窗order by 如果有相同的,窗口大小和最后一条保持一致,下面这题就是这样
SELECT
artical_id,
MAX(uv) AS max_uv
FROM
(
SELECT
artical_id,
SUM(flag) OVER(PARTITION BY artical_id ORDER BY rn) uv
FROM
(
SELECT
artical_id,ts,flag,
ROW_NUMBER() OVER(PARTITION BY artical_id ORDER BY ts) AS rn
FROM
(
SELECT artical_id,in_time AS ts,1 AS flag FROM tb_user_log WHERE artical_id != 0
UNION ALL
SELECT artical_id,out_time AS ts,-1 AS flag FROM tb_user_log WHERE artical_id != 0
) t
) t1
) t2
GROUP BY artical_id
ORDER BY max_uv DESC