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

思路:

第一步: 按照时间 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 |
+------------+--------+