题解 | #每篇文章同一时刻最大在看人数#
每篇文章同一时刻最大在看人数
http://www.nowcoder.com/practice/fe24c93008b84e9592b35faa15755e48
思路:
第一步: 按照时间 at_time 升序，进来的 加一，离开的减一;
代码如下:
SELECT artical_id, in_time as at_time, 1 as uv FROM tb_user_log
UNION ALL
SELECT artical_id, out_time as at_time, -1 as uv FROM tb_user_log
ORDER BY at_time
结果如下:
+------------+---------------------+----+
| artical_id | at_time | uv |
+------------+---------------------+----+
| 9001 | 2021-11-01 10:00:00 | 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 |
+------------+---------------------+----+
第二步: 利用窗口函数相加，统计出当前最大观看人数
SELECT artical_id, at_time,
SUM(uv) over(PARTITION BY artical_id ORDER BY at_time, uv DESC) as current_max
FROM (
SELECT artical_id, in_time as at_time, 1 as uv FROM tb_user_log
UNION ALL
SELECT artical_id, out_time as at_time, -1 as uv FROM tb_user_log
ORDER BY at_time
) as t_uv_at_time
WHERE artical_id != 0
+------------+---------------------+-------------+
| artical_id | at_time | current_max |
+------------+---------------------+-------------+
| 9001 | 2021-11-01 10:00:00 | 1 |
| 9001 | 2021-11-01 10:00:01 | 2 |
| 9001 | 2021-11-01 10:00:09 | 3 |
| 9001 | 2021-11-01 10:00:11 | 2 |
| 9001 | 2021-11-01 10:00:28 | 3 |
| 9001 | 2021-11-01 10:00:38 | 2 |
| 9001 | 2021-11-01 10:00:51 | 3 |
| 9001 | 2021-11-01 10:00:58 | 2 |
| 9001 | 2021-11-01 10:00:59 | 1 |
| 9001 | 2021-11-01 10:01:50 | 0 |
| 9002 | 2021-11-01 11:00:45 | 1 |
| 9002 | 2021-11-01 11:00:55 | 2 |
| 9002 | 2021-11-01 11:01:11 | 1 |
| 9002 | 2021-11-01 11:01:24 | 0 |
+------------+---------------------+-------------+
第三步:取最大值
SELECT artical_id, MAX(current_max) as max_uv
FROM (
SELECT artical_id, at_time,
SUM(uv) over(PARTITION BY artical_id ORDER BY at_time, uv DESC) as current_max
FROM (
SELECT artical_id, in_time as at_time, 1 as uv FROM tb_user_log
UNION ALL
SELECT artical_id, out_time as at_time, -1 as uv FROM tb_user_log
ORDER BY at_time
) as t_uv_at_time
WHERE artical_id != 0
) as t_artical_cur_max
GROUP BY artical_id
ORDER BY max_uv DESC;
结果如下:
+------------+--------+
| artical_id | max_uv |
+------------+--------+
| 9001 | 3 |
| 9002 | 2 |
+------------+--------+
