首页 > 试题广场 >

牛客直播各科目同时在线人数

[编程题]牛客直播各科目同时在线人数
  • 热度指数:16961 时间限制:C/C++ 1秒,其他语言2秒 空间限制:C/C++ 256M,其他语言512M
  • 算法知识视频讲解
牛客某页面推出了数据分析系列直播课程介绍。用户可以选择报名任意一场或多场直播课。
已知课程表course_tb如下(其中course_id代表课程编号,course_name表示课程名称,course_datetime代表上课时间):
course_id course_name course_datetime
1 Python 2021-12-1 19:00-21:00
2 SQL 2021-12-2 19:00-21:00
3 R 2021-12-3 19:00-21:00
上课情况表attend_tb如下(其中user_id表示用户编号、course_id代表课程编号、in_datetime表示进入直播间的时间、out_datetime表示离开直播间的时间):
user_id course_id in_datetime out_datetime
100 1 2021-12-01 19:00:00
2021-12-01 19:28:00
100 1 2021-12-01 19:30:00
2021-12-01 19:53:00
101 1 2021-12-01 19:00:00
2021-12-01 20:55:00
102 1 2021-12-01 19:00:00
2021-12-01 19:05:00
104 1 2021-12-01 19:00:00
2021-12-01 20:59:00
101 2 2021-12-02 19:05:00
2021-12-02 20:58:00
102 2 2021-12-02 18:55:00
2021-12-02 21:00:00
104 2 2021-12-02 18:57:00
2021-12-02 20:56:00
107 2 2021-12-02 19:10:00
2021-12-02 19:18:00
100 3 2021-12-03 19:01:00
2021-12-03 21:00:00
102 3 2021-12-03 18:58:00
2021-12-03 19:05:00
108 3 2021-12-03 19:01:00
2021-12-03 19:56:00
请你统计每个科目最大同时在线人数(按course_id排序),以上数据的输出结果如下:
course_id course_name max_num
1 Python 4
2 SQL 4
3 R 3
示例1

输入

drop table if exists course_tb;
CREATE TABLE course_tb(
course_id int(10) NOT NULL, 
course_name char(10) NOT NULL,
course_datetime char(30) NOT NULL);
INSERT INTO course_tb VALUES(1, 'Python', '2021-12-1 19:00-21:00');
INSERT INTO course_tb VALUES(2, 'SQL', '2021-12-2 19:00-21:00');
INSERT INTO course_tb VALUES(3, 'R', '2021-12-3 19:00-21:00');

drop table if exists attend_tb;
CREATE TABLE attend_tb(
user_id int(10) NOT NULL, 
course_id int(10) NOT NULL,
in_datetime datetime NOT NULL,
out_datetime datetime NOT NULL
);
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:00:00', '2021-12-1 19:28:00');
INSERT INTO attend_tb VALUES(100, 1, '2021-12-1 19:30:00', '2021-12-1 19:53:00');
INSERT INTO attend_tb VALUES(101, 1, '2021-12-1 19:00:00', '2021-12-1 20:55:00');
INSERT INTO attend_tb VALUES(102, 1, '2021-12-1 19:00:00', '2021-12-1 19:05:00');
INSERT INTO attend_tb VALUES(104, 1, '2021-12-1 19:00:00', '2021-12-1 20:59:00');
INSERT INTO attend_tb VALUES(101, 2, '2021-12-2 19:05:00', '2021-12-2 20:58:00');
INSERT INTO attend_tb VALUES(102, 2, '2021-12-2 18:55:00', '2021-12-2 21:00:00');
INSERT INTO attend_tb VALUES(104, 2, '2021-12-2 18:57:00', '2021-12-2 20:56:00');
INSERT INTO attend_tb VALUES(107, 2, '2021-12-2 19:10:00', '2021-12-2 19:18:00');
INSERT INTO attend_tb VALUES(100, 3, '2021-12-3 19:01:00', '2021-12-3 21:00:00');
INSERT INTO attend_tb VALUES(102, 3, '2021-12-3 18:58:00', '2021-12-3 19:05:00');
INSERT INTO attend_tb VALUES(108, 3, '2021-12-3 19:01:00', '2021-12-3 19:56:00');

输出

1|Python|4
2|SQL|4
3|R|3
with new_tab as (select user_id,course_id,in_datetime as time_,if(in_datetime is not null,1,0) as cnt
                from attend_tb
                union all
                select user_id,course_id,out_datetime as time_,if(out_datetime is not null,-1,0) as cnt
                from attend_tb)

select b.course_id,b.course_name,max(num)
from 
    (select *,sum(cnt)over(partition by course_id order by time_) as num
     from new_tab
    ) t 
 left join course_tb b on t.course_id=b.course_id
 group by b.course_id,b.course_name
 order by b.course_id

发表于 2022-04-10 21:05:39 回复(0)
select b.course_id,c.course_name,max(cnt) max_num
from
(select course_id,
sum(num) over (partition by course_id order by dt asc,num desc) cnt
from
(select course_id,in_datetime dt,1 num from attend_tb
union all 
select course_id,out_datetime dt,-1 num from attend_tb) as a) as b
inner join course_tb c on c.course_id=b.course_id
group by b.course_id,c.course_name
order by b.course_id
这道题目和某度信息流里面的SQL9的思路是一致的,虽然这道题中没有提到同一时间即有进来又有出去怎么处理,我就默认先计算进来的在计算出去的哈哈哈。
基本上,我认为这个方法是一个很好用的方法,可以用来求这类的问题
发表于 2021-12-05 08:31:14 回复(3)
最大同时在线人数问题
使用1,-1数据标签和sum over来解决
首先建立基础表以供查询
第一段 用户进入时间 设定值为1
使用union 并上
第二段 用户退出时间 设定值为-1
with temp as
(
select
course_id,
course_name,
user_id,
in_datetime as dt,
1 as flag
from attend_tb a
join course_tb b using(course_id)
union
select
course_id,
course_name,
user_id,
out_datetime as dt,
-1 as flag 
from attend_tb a
join course_tb b using(course_id)
)
然后我们使用sum函数 对1或-1值累计 按照时间顺序排序(重点)!
这样的话,可以有进入就加一,退出就减一,对表中的每一时刻进行了实时在线统计
select 
course_id,
course_name,
sum(flag) over (partition by course_id order by dt) as num
from temp 
在上表的基础上,按照course_id,course_name分组,找出最大值输出就可以了
最后别忘了按照course_id排序

完整代码如下
with temp as
(
select
course_id,
course_name,
user_id,
in_datetime as dt,
1 as flag
from attend_tb a
join course_tb b using(course_id)
union
select
course_id,
course_name,
user_id,
out_datetime as dt,
-1 as flag 
from attend_tb a
join course_tb b using(course_id)
)
select
course_id,
course_name,
max(num) as max_num
from
(
select 
course_id,
course_name,
sum(flag) over (partition by course_id order by dt) as num
from temp 
) t1
group by course_id,course_name
order by course_id


发表于 2022-05-18 23:42:24 回复(1)

【场景】:同时在线人数

【分类】:窗口函数、分组查询、多表连接

分析思路

难点:
1.窗口函数和分组查询要分开求解

(1)统计进出记录表

注:进入是增加一个在线人数,出去是减少一个在线人数

  • [条件]:timestampdiff(minute,in_datetime,out_datetime) >= 10

  • [使用]:union;使用1和-1标记进入、出去

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

  • [使用]:窗口函数 sum() over

(3)统计每个科目最大同时在线人数(按course_id排序)

  • [使用]:max() ; group by course_id,course_name; order by course_id

最终结果

select 查询结果 [课程号;课程名称;最大同时在线人数]
from 从哪张表中查询数据[多表]
group bu 分组条件 [课程号;课程名称]
order by 对查询结果排序 [按课程号升序排序];

求解代码

方法一

with子句

with
    main as(
        #进入是增加一个在线人数,出去是减少一个在线人数
        select
            user_id,
            course_id,
            in_datetime as datetime,
            1 as tag
        from attend_tb
        union
        select
            user_id,
            course_id,
            out_datetime as datetime,
            -1 as tag
        from attend_tb
    )
    ,main1 as(
        #统计每个时刻的同时在线人数
        select distinct
            course_id,
            course_name,
            sum(tag) over(partition by course_id order by datetime) as sum_num
        from course_tb
        left join main using(course_id)
    )
#统计每个科目最大同时在线人数(按course_id排序)
select
    course_id,
    course_name,
    max(sum_num) as max_num
from main1
group by course_id,course_name
order by course_id

方法二

多表连接

#统计每个科目最大同时在线人数(按course_id排序)
select
    course_id,
    course_name,
    max(sum_num) as max_num
from(
    select distinct
        course_id,
        course_name,
        sum(tag) over(partition by course_id order by datetime) as sum_num
    from course_tb
    left join(
        #进入是增加一个在线人数,出去是减少一个在线人数
        select
            user_id,
            course_id,
            in_datetime as datetime,
            1 as tag
        from attend_tb
        union
        select
            user_id,
            course_id,
            out_datetime as datetime,
            -1 as tag
        from attend_tb
    ) main using(course_id)
) main1
group by course_id,course_name
order by course_id
发表于 2022-10-30 19:02:23 回复(0)
select c.course_id, c.course_name, count(*) max_num 
from course_tb c 
left join attend_tb a on c.course_id = a.course_id
where a.in_datetime <= (select min(out_datetime) from attend_tb where course_id = c.course_id)
group by c.course_id, c.course_name
发表于 2021-12-24 11:14:01 回复(1)
with zb as (select ct.course_id,ct.course_name,in_datetime,out_datetime,1 as in_num,-1 as leave_num /*将进入时间标记为1,离开记-1*/
from course_tb ct
left join attend_tb at on ct.course_id=at.course_id
where date(course_datetime) = date(in_datetime)) /*建立一个要分割的表*/
select course_id,course_name,max(z_num) max_num
from
(select *,suM(in_num) over(partition by course_id order by in_datetime) z_num /*每科各时间段在线人数*/
from
(select course_id,course_name,in_datetime,in_num
from zb
union all /*上下连接,将时间连续,统计时间段在线人数*/
select course_id,course_name,out_datetime,leave_num
from zb) zb1) zb2
group by course_id,course_name
order by course_id

编辑于 2023-12-28 20:11:52 回复(0)
最大同时在线人数板子题。
我忽略了course_datetime这一列

with total_info as(
    select ct.course_id,ct.course_name,
    at.in_datetime as interval_start,
    at.out_datetime as interval_end
    from course_tb ct
    left join attend_tb at
    using(course_id)
)
,
# 准备前缀和
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再-m
    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 course_id,course_name,max(online_uv) as max_num from 
(
    select course_id,course_name,
    sum(cnt_change) over(partition by course_id order by tick,sort_util_flag) as online_uv
    from t2
) t3
group by course_id,course_name
order by course_id asc






发表于 2023-03-23 13:15:43 回复(0)
# 考点:同时在线人数的统计
#三步走

# 选择最大在线人数
SELECT
  C.course_id,D.course_name,max(total_num) as max_num
FROM
  (
    SELECT 
      # 窗口函数 sum() over() 计算在线人数
      user_id,course_id,pt,tag,
      sum(tag) over(partition by course_id order by pt,tag desc) as total_num
    FROM
      (
        #开始时间、离开时间打标签
          SELECT
          user_id,course_id,pt,tag
        FROM
          (
            SELECT
              user_id,course_id,in_datetime as pt,1 as tag
            FROM
              attend_tb
            union all
            SELECT
              user_id,course_id,out_datetime as pt,-1 as tag
            FROM
              attend_tb
          ) A
        order by pt asc
      ) B
  ) C
  left join course_tb D on C.course_id = D.course_id
group by
  C.course_id,D.course_name
order by
  C.course_id asc

发表于 2022-08-28 11:26:10 回复(0)
SELECT course_id,course_name,
max(sum_tmp) as max_num from
(
SELECT course_id,
sum(tmp) over(PARTITION by course_id order by att_time,tmp) as sum_tmp
from (
SELECT course_id,user_id,in_datetime as att_time,1 as tmp from attend_tb
union all
SELECT course_id,user_id ,out_datetime as att_time,-1 as tmp from attend_tb order by course_id,att_time
)t1
)t2 
join course_tb using(course_id)
group by course_id,course_name;

经典解法

发表于 2024-03-25 23:07:17 回复(0)
select course_id,course_name,max(diff_cnt) max_num
from(
select course_id,course_name,
sum(diff)over(partition by course_id order by dt,diff desc) diff_cnt
from(
select user_id,course_id, in_datetime dt,1 diff
from attend_tb
union all
select user_id,course_id,out_datetime dt,-1 diff
from attend_tb
)t
join course_tb using(course_id))t1
group by course_id,course_name
order by course_id

编辑于 2024-02-22 17:51:34 回复(0)
select
    t1.course_id,
    t3.course_name,
    count(distinct t1.user_id)
from
    (
        select
            *
        from
            attend_tb
    ) t1
    left join (
        select
            max(in_datetime) as last_in,
            min(out_datetime) as first_out
        from
            attend_tb
    ) t2 on t1.in_datetime >= t2.last_in
    and t1.out_datetime <= t2.first_out
    left join (
        select
            course_id,
            course_name
        from
            course_tb
    ) t3 on t1.course_id = t3.course_id
group by
    1,
    2

编辑于 2024-02-19 15:16:09 回复(0)
思路:cross join先做笛卡尔积,然后根据每一个时间点分类,分别计算出包括时间点的时间间隔数量(同时在线人数)
with 
t as (  # cross join筛选需要信息的总表
select course_id as cid, user_id as uid, 
    tt.in_datetime as t, 
    attend_tb.in_datetime as it, out_datetime as ot
from attend_tb 
    cross join (select course_id, in_datetime from attend_tb)tt using(course_id) 
),
t2 as (  # 计算包含时间点t的时间间隔数量
select cid, course_name, t, count(distinct uid) as maxn 
from t inner join course_tb on course_tb.course_id=cid
where timestampdiff(second, it, t)>=0 
    and timestampdiff(second, ot, t)<0
group by cid, course_name, t
)

# max求最大数量
select cid, course_name, max(maxn)
from t2
group by cid, course_name
order by cid;


编辑于 2024-02-13 19:21:20 回复(0)
select
    course_id,
    course_name,
    max(num) max_num
from 
(
    select 
        b.course_id,
        b.course_name,
        sum(sign) over(partition by a.course_id order by a.datetime) num
    from 
    (
        select
            user_id,
            course_id,
            in_datetime datetime,
            1 sign
        from attend_tb
        union all
        select
            user_id,
            course_id,
            out_datetime datetime,
            -1 sign
        from attend_tb
    ) a
    join course_tb b on b.course_id=a.course_id
) t
group by t.course_id,t.course_name
order by t.course_id

编辑于 2024-01-26 09:51:58 回复(0)
with temp as (
select course_id, in_datetime as dt, 1 as cnt
from attend_tb
union all
select course_id, out_datetime as dt, -1 as cnt
from attend_tb
)

select course_id, course_name, max(num) as max_num
from
(
select a.course_id, a.course_name, sum(b.cnt) over(partition by b.course_id order by dt asc, cnt desc ) as num
from course_tb a left join temp b on a.course_id = b.course_id ) a
group by course_id, course_name
order by course_id asc
发表于 2024-01-23 17:43:13 回复(0)
select t.course_id, course_name, max(num) as max_num
from(
select a1.course_id, count(distinct a2.user_id) as num
from attend_tb a1 join attend_tb a2 
on a1.course_id = a2.course_id and a1.out_datetime > a2.in_datetime and a1.out_datetime <= a2.out_datetime
group by 1, a1.user_id)t join course_tb c on t.course_id = c.course_id
group by 1,2
order by 1

发表于 2023-12-29 11:45:12 回复(0)
with t1 as (select course_id
                 , user_id
                 , course_name
                 , in_datetime as dt
                 , 1           as flag
            from attend_tb
                     join course_tb using (course_id)
            union
            select course_id
                 , user_id
                 , course_name
                 , out_datetime as dt
                 , -1           as flag
            from attend_tb
                     join course_tb using (course_id)),
t2 as ( select
    course_id
    ,course_name
    ,sum(flag)over(partition by course_id order by dt) cnt

from t1)

select
    course_id
    ,course_name
    ,max(cnt) max_num
from t2
group by course_id, course_name

发表于 2023-11-30 09:46:03 回复(0)
select course_id
    ,course_name
    ,max(total) max_num
from(
    select *
        ,sum(num)over(partition by course_id order by dt asc) total
    from(
        select at.course_id course_id
            ,course_name
            ,1 num
            ,in_datetime dt
        from attend_tb at
        join course_tb ct
        using(course_id)
        union all
        select at.course_id course_id
            ,course_name
            ,-1 num
            ,out_datetime dt
        from attend_tb at
        join course_tb ct
        using(course_id)
    ) a
    order by dt
) b
group by course_id
    ,course_name
    order by course_id

发表于 2023-11-27 14:51:44 回复(0)
with t_base as (
    select at.user_id uid, ct.course_name cn, at.course_id cid, at.in_datetime dt, 1 online
    from attend_tb at
             join course_tb ct on ct.course_id = at.course_id
    union all
    select at.user_id uid, ct.course_name cn, at.course_id cid, at.out_datetime dt, -1 online
    from attend_tb at
             join course_tb ct on ct.course_id = at.course_id
)
select cid, cn, max(num)
from (
         select cid, cn, sum(online) over (partition by cn,cid order by dt) num
         from t_base tb
     ) t_b
group by cid, cn
order by cid

发表于 2023-11-20 16:02:21 回复(0)
# 既然横向的时间段构成的时间轴不太好处理,那何不给他做成一个纵向的时间点构成的时间序列,让每个客户登录的时间和退出时间变成两个不同的时间点,按照时间从小到大的顺序有序排列,登录的时候就给在线人数+1,退出的时候在线人数-1

select
c.course_id,c.course_name,max(uv_count) as max_num
from
(select 
a.course_id,
sum(a.uv) over(partition by a.course_id order by a.time) as uv_count
from
(select user_id,course_id,in_datetime as time,1 as uv from attend_tb
union
select user_id,course_id,out_datetime as time,-1 as uv from attend_tb) as a
) b
join course_tb c
on b.course_id=c.course_id
group by c.course_id,c.course_name

发表于 2023-10-18 22:49:34 回复(0)
with cte as(
select course_id,in_datetime  as dt,1 as num
from attend_tb 
union all 
select course_id,out_datetime as dt,-1 as num
from attend_tb),
cte1 as(
select course_id,sum(num)over(partition by course_id order by dt,num desc)
as numm 
from cte)
select course_id,course_name,ifnull(max(numm),0) as max_num
from course_tb left join cte1 using(course_id)
group by course_id,course_name
order by course_id

发表于 2023-10-10 09:47:31 回复(1)
  • 二维码

    扫描二维码,关注牛客网

  • 二维码

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