每篇文章同一时刻最大在看人数

id	uid	artical_id	in_time	out_time	sign_cin
1	101	9001	2021-11-01 10:00:00	2021-11-01 10:00:11	0
2	102	9001	2021-11-01 10:00:09	2021-11-01 10:00:38	0
3	103	9001	2021-11-01 10:00:28	2021-11-01 10:00:58	0
4	104	9002	2021-11-01 11:00:45	2021-11-01 11:01:11	0
5	105	9001	2021-11-01 10:00:51	2021-11-01 10:00:59	0
6	106	9002	2021-11-01 11:00:55	2021-11-01 11:01:24	0
7	107	9001	2021-11-01 10:00:01	2021-11-01 10:01:50	0

（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）

场景逻辑说明：artical_id-文章ID代表用户浏览的文章的ID，artical_id-文章ID为0表示用户在非文章内容页（比如App内的列表页、活动页等）。

问题：统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序。

输出示例：

示例数据的输出结果如下

artical_id	max_uv
9001	3
9002	2

解释：10点0分10秒时，有3个用户正在浏览文章9001；11点01分0秒时，有2个用户正在浏览文章9002。

示例1

输入:

DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:11', 0),
  (102, 9001, '2021-11-01 10:00:09', '2021-11-01 10:00:38', 0),
  (103, 9001, '2021-11-01 10:00:28', '2021-11-01 10:00:58', 0),
  (104, 9002, '2021-11-01 11:00:45', '2021-11-01 11:01:11', 0),
  (105, 9001, '2021-11-01 10:00:51', '2021-11-01 10:00:59', 0),
  (106, 9002, '2021-11-01 11:00:55', '2021-11-01 11:01:24', 0),
  (107, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0);

输出:

9001|3
9002|2

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6440KB, 提交时间: 2021-12-09

with t as
(SELECT artical_id, in_time dt, 1 diff #开始等待，人数+1
FROM tb_user_log
where artical_id !=0
union all
SELECT artical_id, out_time dt, -1 diff #开始等待，人数+1
FROM tb_user_log
where artical_id !=0
)

select artical_id,max(cnt)ca
from
(SELECT artical_id,dt,SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt,diff desc) cnt
from t)t1
group by artical_id
order by ca desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6484KB, 提交时间: 2022-01-22

SELECT artical_id, max(t1) as t2
FROM(
    SELECT artical_id,t,
    sum(m)over(partition by artical_id order by t,m desc) as t1
    from(
        select artical_id,in_time as t,1 as m
        from tb_user_log
        where artical_id<>0
        UNION ALL
        select artical_id,out_time as t,-1 as m
        from tb_user_log
        where artical_id<>0
    )a
)b
group by artical_id
order by t2 desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6516KB, 提交时间: 2021-12-18

with t as ((select
                artical_id,
                in_time tm,
                1 as uv
            from
                tb_user_log t
            where
                artical_id != '0')
            union all
            (select
                artical_id,
                out_time tm,
                -1 as uv
            from
                tb_user_log t
            where
                artical_id != '0'))

select 
    artical_id, max(sum_uv) as max_uv
from
    (select 
        artical_id,
        tm,
        sum(uv) over(partition by artical_id order by tm asc, uv desc) sum_uv
    from 
        t)t1
group by
    artical_id
order by
    max_uv desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6528KB, 提交时间: 2021-12-18

with t2 as
(
    select artical_id,sum(x) over(partition by artical_id order by time,x desc) sum_x
    from
    (
        select artical_id,in_time time,1 x from tb_user_log
        where artical_id like '9%'
        union all 
        select artical_id,out_time time,-1 x from tb_user_log
        where artical_id like '9%'
        order by time,x desc
    ) t1
)
select distinct artical_id,sum_x
from t2 t
where sum_x >= all(select sum_x from t2 where t2.artical_id=t.artical_id)
order by sum_x desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6536KB, 提交时间: 2021-12-07

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;

上一题

下一题

详情