首页 > 试题广场 >

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

[编程题]每篇文章同一时刻最大在看人数
  • 热度指数:72870 时间限制:C/C++ 1秒,其他语言2秒 空间限制:C/C++ 256M,其他语言512M
  • 算法知识视频讲解

用户行为日志表tb_user_log

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-文章ID0表示用户在非文章内容页(比如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
select artical_id,max(cnt) max_uv from 
(select artical_id,
sum(num) over (partition by artical_id order by dt asc,num desc) as cnt
from
(select artical_id,in_time dt,1 num from tb_user_log
where artical_id != 0
union all 
select artical_id,out_time dt,-1 num from tb_user_log
where artical_id != 0 ) as a) as b
group by artical_id
order by max_uv desc
一直以来,都觉得这种球最大的同时在线/阅读人数很难,但是在参考大家的思路后,感觉很清晰,一下子就通透了。具体的步骤是:
①将用户的进入时间单独拎出来,同时记为1;离开时间单独拎出来,同时记为-1,这样就聚合这两个表,按照时间排序,意思就是:进去一个加1,离开一个减1。
②然后利用窗口函数对计数(1或者-1)求累计和,因为题目规定:同一时间有就有出的话先算进来的后算出去的,所以排序的时候就要看好了先按时间排序,再按计数排序!
③然后再在每个分组里面去求最大的累积和就是最多同时在线的人数了!
感谢大佬们的分享,感觉自己通透了哈哈哈哈
发表于 2021-12-05 08:13:48 回复(32)
我就想吐槽一些文章的英语单词拼写错了,不是artical,应该是article吧?
发表于 2022-03-25 11:26:44 回复(4)
求最大同时在线人数的套路其实都差不多,背下来!!!(老文科生了属于是.....)
select artical_id, max(uv) as max_uv
from (select artical_id,
             sum(tag) over (partition by artical_id order by dt,tag desc) as uv
      from (select artical_id, uid, in_time as dt, 1 as tag
            from tb_user_log
            where artical_id != 0
            union
            select artical_id, uid, out_time as dt, -1 as tag
            from tb_user_log
            where artical_id != 0) as t1) as t2
group by artical_id
order by max_uv desc


发表于 2022-02-08 21:57:13 回复(0)
select artical_id,max(num) as max_uv from (
    select t1.artical_id,t1.id,
    sum(case when t1.out_time between t2.in_time and t2.out_time then 1 else 0 end) as num
    from tb_user_log t1 left join tb_user_log t2
            on t1.artical_id = t2.artical_id
    where t1.artical_id >0
    group by t1.artical_id,t1.id

    ) t 
group by artical_id
order by max_uv desc

1、这个题目首先想到的思路就是利用自链接,通过文章相连
2、这样同样文章内的内容,就会产生笛卡尔积,每行都可以和该文章的所有行进行对比
3、只要该行的结束时间在其他行开始和结束之间,那么就存在同一时刻,这样每行的记录都能算出他有多少同一时刻一起看的
4、然后利用id,分组,求出每行的同一时刻人数
5、最后使用最值函数,求出同一时刻的峰值人数。


看了评论区后,发现评论区是使用的另一个思路,更方便理解

select artical_id,max(num) as max_uv from (
select artical_id,SUM(tag) over(Partition by artical_id order by dt ,tag desc) as num from (
select artical_id,uid,in_time as dt ,1 as tag from tb_user_log where artical_id >0
union all
select artical_id,uid,out_time as dt ,-1 as tag from tb_user_log where artical_id >0)t)t1
group by artical_id
order by max_uv desc;

这个思路的关键是将用户的进入和离开都做了标识,然后利用窗口函数的累计计算逻辑,进行求和。

发表于 2022-08-25 15:33:52 回复(2)
明白了,也就是说把两个时间并成一列,进1出-1,按照时间升序(因为时间值越小,进来的越早),再按照进出降序(因为先进再出,不能一个人没有进就先算他出的数据),之后,就把每个artical_id作为一个宫殿,1就是进了一个人,-1就是出了一个人,从头计算每一行的进出值,进加1出-1,最后算每个宫殿artical_id曾经容下最多的人数,就是所求!
发表于 2022-09-19 18:01:44 回复(0)
这类题第一次见一般不会,第二次见就会了,就是一种套路
发表于 2023-04-02 21:44:55 回复(0)
with t as (select t1.artical_id a1,t1.in_time a2,count(1) a3
from tb_user_log t1,tb_user_log t2
where t1.artical_id = t2.artical_id
and (t1.in_time >= t2.in_time
and t1.in_time <= t2.out_time)
and (t1.artical_id !=0
and t2.artical_id !=0)
group by a1, a2
)
select a1 artical_id, max(a3) max_uv
from t 
group by a1
order by max_uv desc
发现自己思路唯一不一样, 同文章同一时刻在线 利用外连接两张表,令文章序号相等。然后判定时刻
显然t1时刻同时在线可解释为 t1及之前有人登录且t1时刻后登出 ,
写入表连接条件进行逐行计量计数
最终找到最大在看人数时刻

然而此方案1/2组用例通过,在观察用例输入后仍不能发现问题所在。错误显示 在于将4条9002记录计数成为8条
百思不得其解, 希望有大神能够帮助我一起找出问题所在,谢谢!
发表于 2022-02-06 20:16:32 回复(5)
将开始时间和结束时间视作相同时间列,分别给予标签1和-1表示在看人数的增加和减少。
用窗口函数按文章分类,求当前时间在看人数(即当前时间之前标签值的总和),要注意若同一时刻有用户加入和退出,则先计算加入再计算退出,因此要在窗口函数的order by处加上flag desc。
最后按文章分类求其最大在看人数。
with t1 as (
select artical_id, in_time dt, 1 flag
from tb_user_log
where artical_id != 0
union all
select artical_id, out_time dt, -1 flag
from tb_user_log
where artical_id != 0
order by artical_id, dt),
t2 as (
select artical_id,
       sum(flag) over(partition by artical_id order by dt, flag desc) cnt
from t1)

select artical_id, max(cnt) max_uv
from t2
group by artical_id
order by max_uv desc


发表于 2021-12-25 19:23:43 回复(2)
    
select b.artical_id, max(b.max_uv) max_uv
FROM
(
    select a.artical_id,sum(a.flag) over (order by a.dt, a.flag desc ) as max_uv
    from 
    (select id,uid,artical_id,in_time dt, 1 flag
    from tb_user_log
    where artical_id <> 0 
    UNION
    select id,uid,artical_id,out_time dt,-1 flag
    from tb_user_log
    where artical_id <> 0
order by uid    ) a) b
    group by b.artical_id
  order by max_uv desc
    
发表于 2021-12-01 22:38:33 回复(1)

【场景】:同一时刻在线人数

【分类】:窗口函数、tag标记、嵌套子查询

分析思路

难点:

1.同一时刻的在线人数 区别于 某个时刻之前的在线人数

(1)用tag标记增加还是减少

进入增加1,出去减少1

  • [使用]:union;1 as tag;-1 as tag

(2)统计每个时刻的在看人数

如果同一时刻有进入也有离开时,先记录用户数增加再记录减少,所以在窗口函数中使用sum()需要order by dt,tag desc

  • [使用]:sum(tag) over (partition by artical_id order by dt,tag desc)

(3)求在看人数的最大值

  • [使用]:group by exam_id;order by uv desc,avg_score

求解代码

方法一:

with 子句

with
    main as(
        #用tag标记增加还是减少
        (select
            artical_id,
            uid,
            in_time as dt,
            1 as tag
        from tb_user_log
        where artical_id != 0)
        union
        (select
            artical_id,
            uid,
            out_time as dt,
            -1 as tag
        from tb_user_log
        where artical_id != 0)
    ),
    main1 as(
        #如果同一时刻有进入也有离开时,先记录用户数增加再记录减少,所以在窗口函数中使用sum()需要order by dt,tag desc
        select
            artical_id,
            sum(tag) over (partition by artical_id order by dt,tag desc) as uv
        from main
    )
#统计每篇文章同一时刻最大在看人数,结果按最大人数降序 
select
    artical_id,
    max(uv) as max_uv
from main1
group by artical_id
order by max_uv desc

方法二:

嵌套子查询 + from子查询

#统计每篇文章同一时刻最大在看人数,结果按最大人数降序 
select
    artical_id,
    max(uv) as max_uv
from(
    #如果同一时刻有进入也有离开时,先记录用户数增加再记录减少,所以在窗口函数中使用sum()需要order by dt,tag desc
    select
        artical_id,
        sum(tag) over (partition by artical_id order by dt,tag desc) as uv
    from(
        #用tag标记增加还是减少
        (select
            artical_id,
            uid,
            in_time as dt,
            1 as tag
        from tb_user_log
        where artical_id != 0)
        union
        (select
            artical_id,
            uid,
            out_time as dt,
            -1 as tag
        from tb_user_log
        where artical_id != 0)
    ) main
) main1
group by artical_id
order by max_uv desc
发表于 2022-11-21 10:13:40 回复(3)
with a as (
    select 
        artical_id,
        in_time dt,
        1 num 
    from tb_user_log
    where artical_id != 0
    union all 
    select 
        artical_id,
        out_time dt,-1 num 
    from tb_user_log
    where artical_id != 0 ),
 b as(
     select 
        artical_id,
        sum(num) over (partition by artical_id order by dt asc,num desc) as cnt
     from a)
select 
    artical_id,
    max(cnt) max_uv from b
group by artical_id
order by max_uv desc
原来可以这么写,写个1,就是1列都是1, union all 全加在一起(纵向数据添加),然后按照时间顺序从小到大,文章id进行分组,进入+1 走掉-1,因为是窗口函数所以,全部在新的一列中显示,再用max 按照分组 把其中最大的数取出66666
发表于 2022-07-26 09:32:08 回复(0)
select artical_id,max(uv) as max_uv from (
            select a.artical_id,a.in_time,SUM(IF(a.in_time BETWEEN b.in_time and b.out_time,1,0)) as uv
                from tb_user_log a
                    inner join tb_user_log b
                ON a.artical_id = b.artical_id
                where a.artical_id != 0
                group by a.artical_id,a.in_time,a.uid ) t1
               group by artical_id
               order by max_uv desc

发表于 2022-01-16 12:48:44 回复(2)
select b.artical_id, max(people) max_uv
from
(select a.artical_id,a.ct,sum(num) over 
(partition by artical_id order by ct, num desc) people
from
(select uid, artical_id, in_time ct, 1 num 
from tb_user_log
UNION
select uid, artical_id, out_time ct, -1 num 
from tb_user_log) a
where a.artical_id <> 0) b
group BY
b.artical_id
order BY
max_uv desc
看了高赞答案,在写的时候也有考虑过是不是select的时候加了uid就不需要union all,只用union就可以了呢?初学者,希望有sql大佬解答,感激!
发表于 2022-01-06 17:23:06 回复(3)
感谢一楼大佬的思路,第一次求同时在线最大人数的题目,没思路。
with t as(
select artical_id,in_time,'1' flag from tb_user_log where artical_id<>0
union all
select artical_id,out_time,'-1' flag from tb_user_log where artical_id<>0)
select 
	artical_id,
	max(cnt) max_uv from (
select
	artical_id,
	sum(flag) over(partition by artical_id order by in_time,flag desc) cnt -- 因为同一时刻计算"先进后出"
from t)t1 group by 1
order by 2 desc


发表于 2021-12-27 08:34:06 回复(0)
select artical_id,max(cnt) max_uv from 
(select artical_id,
sum(num) over (partition by artical_id order by dt asc,num desc) as cnt
from
(select artical_id,in_time dt,1 num from tb_user_log
where artical_id != 0
union all 
select artical_id,out_time dt,-1 num from tb_user_log
where artical_id != 0 ) as a) as b
group by artical_id
order by max_uv desc

发表于 2021-12-21 15:23:24 回复(4)
最大同时在线人数板子题
with total_info as(
    select artical_id,
    in_time as interval_start,
    out_time as interval_end
    from tb_user_log
    where artical_id<>0
)
,
# 准备前缀和
t2 as(
    select *,
    (case when tick=interval_start then +1 when tick=interval_end then -1 else 0 end) as cnt_change,
    (case when interval_start=tick then 1 when interval_end=tick then 2 else 0 end) as sort_util_flag  # 如果某个时刻,有n人进入,有m人出去,同时在线人数的变动为:n个+1,之后m个-1
    # (case when interval_start=tick then 2 when interval_end=tick then 1 else 0 end) as sort_util_flag  # 如果某个时刻,有n人进入,有m人出去,同时在线人数的变动为:m个-1,之后n个+1
    from
    (
        select interval_start as tick from total_info
        union 
        select interval_end as tick from total_info
    )tmp1 
    left join total_info
    on interval_start=tick&nbs***bsp;interval_end=tick
) 

select artical_id,max(online_uv) as max_num from 
(
    select artical_id,
    sum(cnt_change) over(partition by artical_id order by tick,sort_util_flag) as online_uv
    from t2
) t3
group by artical_id
order by max_num desc



发表于 2023-03-23 13:27:39 回复(0)
select artical_id,
max(num) as max_uv
from
(
select artical_id,
sum(view_num) over(partition by artical_id order by act_time asc,view_num desc) as num #第二步:按照时间累加计算该时刻在线人数
from
(
select artical_id,in_time as act_time,1 as view_num
from tb_user_log
where
artical_id <>0
union all #用union会报错,因为在同一时刻有同时进入或出去的情况
select artical_id,out_time as act_time,-1 as view_num
from tb_user_log
where
artical_id <>0
) as tu1 #第一步:先查找每篇文章的进出情况
) as tu2
group by artical_id
order by max_uv desc;

发表于 2023-02-19 12:38:49 回复(0)
select artical_id,
max(max_uv) max_uv
from (select artical_id,
sum(flag) over(partition by artical_id order by dt,flag desc) max_uv
from (select artical_id, in_time dt, 1 flag
from tb_user_log
union all
select artical_id, out_time df, -1 flag
from tb_user_log) t1) t2
where artical_id != 0
group by artical_id
order by max_uv desc
发表于 2022-07-05 15:57:21 回复(0)
--(artical_id,temp_time,进人or出人)
--思路:一列时刻,一列人数变化(进入+1,退出-1)用union all不去重联合
--注意:题目设定“如果同一时刻有进入也有离开时,先记录用户数增加再记录减少”,在窗口函数聚合时,order by 时刻不够,因为同一时刻可能进人可能出人,优先计算进人,需order by 时刻,temp_uv DESC(+1优先)
SELECT artical_id, MAX(total_uv) AS max_uv
FROM(
    SELECT
        artical_id,
        SUM(temp_uv)OVER(PARTITION BY artical_id ORDER BY temp_time,temp_uv DESC) AS total_uv
    FROM(
        SELECT artical_id, in_time AS temp_time, 1 as temp_uv
        FROM tb_user_log
        WHERE artical_id != 0
        UNION ALL
        SELECT artical_id, out_time AS temp_time, -1 as temp_uv
        FROM tb_user_log
        WHERE artical_id != 0)t1)t2
GROUP BY artical_id
ORDER BY max_uv DESC

编辑于 2024-03-17 16:30:43 回复(0)
with t1 as(
    select
    artical_id,
    in_time as time,
    1 as uv
    from tb_user_log
    where artical_id !=0
    union all
    select
    artical_id,
    out_time as time,
    -1 as uv
    from tb_user_log
    where artical_id !=0
),
t2 as (
    select
    artical_id,
    sum(uv)over(partition by artical_id order by time,uv desc) as cnt
    from t1
)
select 
artical_id,
max(cnt) as max_uv
from t2
group by artical_id
order by max_uv desc

注意union和union all区别

发表于 2024-03-01 09:58:12 回复(0)
  • 二维码

    扫描二维码,关注牛客网

  • 二维码

    下载牛客APP,随时随地刷题