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

感谢题解的各位大佬

-- 每篇文章 
-- 同一时刻
-- 最大在看人数
SELECT
  artical_id,
  MAX(instant_viewer_cnt) max_uv
FROM (
SELECT
    artical_id,t1.dt,t1.diff,
    SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC) instant_viewer_cnt
  FROM (
    SELECT
      artical_id, in_time dt, 1 diff
    FROM tb_user_log
    WHERE artical_id != 0
    UNION ALL
    SELECT
      artical_id, out_time dt, -1 diff
    FROM tb_user_log
    WHERE artical_id != 0) t1
	ORDER BY t1.dt
) t2
GROUP BY 1
ORDER BY 2 DESC

    SELECT
      artical_id, in_time dt, 1 diff
    FROM tb_user_log
    WHERE artical_id != 0
    UNION ALL
    SELECT
      artical_id, out_time dt, -1 diff
    FROM tb_user_log
    WHERE artical_id != 0

设置in_time时间点为1，out_time时间点为-1

9001	2021-11-01 10:00:01	1
9001	2021-11-01 10:00:09	1
9001	2021-11-01 10:00:11	-1
9001	2021-11-01 10:00:28	1
9001	2021-11-01 10:00:38	-1
9001	2021-11-01 10:00:51	1
9001	2021-11-01 10:00:58	-1
9001	2021-11-01 10:00:59	-1
9001	2021-11-01 10:01:50	-1
9002	2021-11-01 11:00:45	1
9002	2021-11-01 11:00:55	1
9002	2021-11-01 11:01:11	-1
9002	2021-11-01 11:01:24	-1

2.计算每个时间节点的人数变化

SELECT
    artical_id,t1.dt,t1.diff,
    SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC) instant_viewer_cnt
  FROM (
    SELECT
      artical_id, in_time dt, 1 diff
    FROM tb_user_log
    WHERE artical_id != 0
    UNION ALL
    SELECT
      artical_id, out_time dt, -1 diff
    FROM tb_user_log
    WHERE artical_id != 0) t1
	ORDER BY t1.dt

9001	2021-11-01 10:00:00	1	1
9001	2021-11-01 10:00:01	1	2
9001	2021-11-01 10:00:09	1	3
9001	2021-11-01 10:00:11	-1	2
9001	2021-11-01 10:00:28	1	3
9001	2021-11-01 10:00:38	-1	2
9001	2021-11-01 10:00:51	1	3
9001	2021-11-01 10:00:58	-1	2
9001	2021-11-01 10:00:59	-1	1
9001	2021-11-01 10:01:50	-1	0
9002	2021-11-01 11:00:45	1	1
9002	2021-11-01 11:00:55	1	2
9002	2021-11-01 11:01:11	-1	1
9002	2021-11-01 11:01:24	-1	0

最后在外面加group by

SELECT
  artical_id,
  MAX(instant_viewer_cnt) max_uv
from(...)
GROUP BY 1
ORDER BY 2 DESC