首页 > 试题广场 >

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

[编程题]牛客直播各科目同时在线人数
  • 热度指数：17642 时间限制：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
算法知识视频讲解
添加回答
Mysql
yuciaaa头像 yuciaaa 
WITH A AS(
    SELECT course_id, user_id, in_datetime AS time, 1 AS cnt
    FROM attend_tb
    UNION ALL
    SELECT course_id, user_id, out_datetime AS time, -1 AS cnt
    FROM attend_tb
), B AS(
SELECT A.course_id, course_name, SUM(cnt)OVER(PARTITION BY A.course_id ORDER BY time) AS ctt
FROM A
LEFT JOIN course_tb
ON course_tb.course_id = A.course_id
)
SELECT course_id, course_name, MAX(ctt)
FROM B
GROUP BY course_name, course_id
ORDER BY course_id ASC

发表于 今天 14:50:30 回复(0)
Mysql
祖拜尔实习生头像 祖拜尔实习生 
with info as(
    select
        c.course_id,
        c.course_name,
        c.course_datetime,
        a.user_id,
        a.in_datetime,
        a.out_datetime
    from
        course_tb c left join attend_tb a
        on c.course_id = a.course_id
)
, logs as(
    select
        course_id,
        course_name,
        user_id,
        in_datetime,
        1 flag
    from 
        info
    union
    select
        course_id,
        course_name,
        user_id,
        out_datetime,
        -1
    from
        info
)

select
    course_id,
    course_name,
    max(acc_num) max_num
from(
    select
        course_id,
        course_name,
        sum(flag) over(partition by course_id order by in_datetime,flag) acc_num
    from
        logs)t
group by
    course_id,
    course_name
order by
    course_id

编辑于 2024-04-19 11:03:15 回复(0)
Mysql
等灯等灯2024头像 等灯等灯2024 
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)
Mysql
我要offeroffer要我头像 我要offeroffer要我 
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)
Mysql
牛客605111887号头像 牛客605111887号
思路：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)
Mysql
dolen头像 dolen 
-- 请你统计每个科目最大同时在线人数（按course_id排序），以上数据的输出结果如下：
select 
 pp.course_id,
 pp.course_name,
 max(tt.online_cnt) as max_num
from
(
select
course_id,
sum(num)over(partition by course_id order by stattime,num desc) as online_cnt
from 
(
select 
    course_id,
    in_datetime as stattime,
    1 as num
from attend_tb
union all
select 
    course_id,
    out_datetime as stattime,
    -1 as num
from attend_tb
) t1
) tt left join course_tb pp on tt.course_id = pp.course_id
group by  pp.course_id,pp.course_name
order by 1;

发表于 2023-08-11 16:36:22 回复(0)
Mysql
在努力存钱的大老虎很想吃火锅头像 在努力存钱的大老虎很想吃火锅 
-- mysql写法

SELECT  t1.course_id
       ,t2.course_name
       ,MAX(online_count) AS max_num
FROM
(
    SELECT  course_id
           ,SUM(uv) over(partition by course_id ORDER BY occur_time) AS online_count
    FROM
    (
        SELECT  course_id
               ,in_datetime AS occur_time
               ,1           AS uv
        FROM attend_tb
        UNION ALL
        SELECT  course_id
               ,out_datetime AS occur_time
               ,-1           AS uv
        FROM attend_tb
    )t0
)t1
LEFT JOIN course_tb t2 using(course_id)
GROUP BY  course_id,course_name
ORDER BY course_id


-- 大数据写法,实现1次读取，减少io

SELECT  t1.course_id
       ,t2.course_name
       ,MAX(online_count) AS max_num
FROM
(
    SELECT  course_id
           ,SUM(uv) over(partition by course_id ORDER BY occur_time) AS online_count
    FROM
    (
        SELECT 
               ,course_id
               ,if(uv = 1,in_datetime,out_datetime) AS occur_time
               ,uv
        FROM attend_tb lateral view explode(array(1, -1)) AS uv
    )t0
)t1
LEFT JOIN course_tb t2 using(course_id)
GROUP BY  course_id
         ,course_name
ORDER BY course_id

发表于 2023-07-05 19:43:31 回复(0)
Mysql
大炑炑他大姨头像 大炑炑他大姨
select
    C.course_id,
    course_name,
    max_num
from
    (
        select
            course_id,
            max(online_user_cnt) as max_num
        from
            (
                select
                    course_id,
                    time,
                    sum(in_out) over (
                        partition by
                            course_id
                        order by
                            time
                    ) as online_user_cnt
                from
                    (
                        select
                            user_id,
                            course_id,
                            in_datetime as time,
                            1 as "in_out"
                        from
                            attend_tb
                        union all
                        select
                            user_id,
                            course_id,
                            out_datetime as time,
                            -1 as "in_out"
                        from
                            attend_tb
                    ) A
            ) B
        group by
            course_id
    ) C
    left join course_tb on C.course_id = course_tb.course_id;

;
发表于 2023-06-20 14:20:25 回复(0)
Mysql
牛客226836090号头像 牛客226836090号 
with temp as 
(select user_id,a.course_id,course_name,in_datetime as ti,1 as flag from 
attend_tb as a join course_tb as b on a.course_id=b.course_id union
select user_id,a.course_id,course_name,out_datetime as ti,-1 as flag from
attend_tb as a join course_tb as b on a.course_id=b.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 temp.ti) as num from temp) c
group by c.course_id,c.course_name
order by c.course_id
第一步用union，第二步用1和-1做标记，再用sum over开窗函数按照时间排序求最大值


发表于 2023-05-17 20:00:13 回复(0)
Mysql
我叫谢小盟，叫我查尔斯好了头像 我叫谢小盟，叫我查尔斯好了 
select t2.course_id, 
c.course_name,
max(people_cnt)

from 

(select *, 
sum(io)over(partition by course_id order by dt asc) as people_cnt #计算最大瞬时在线人数

from 

(select course_id, 
user_id, 
in_datetime as dt, 
1 as io
from attend_tb

union all 

select course_id, 
user_id, 
out_datetime as dt, 
-1 as io
from attend_tb) as t1) as t2

join course_tb as c using(course_id)

group by t2.course_id, c.course_name
order by t2.course_id

发表于 2023-04-19 18:08:28 回复(0)
Mysql
Kalina2023头像 Kalina2023
思路同SQL163 

select 
    course_id,
    course_name,
    max(num) as max_num
from
(
    select
        course_id,
        course_name,
        sum(score) over(partition by course_id order by time_time asc) as num
    from
    (
    -- 大宽表
    select
        ct.course_id,
        ct.course_name,
        at.in_datetime as time_time,
        1 as score
    from attend_tb at
    left join course_tb ct
    on at.course_id = ct.course_id
    union all
    select
        ct.course_id,
        ct.course_name,
        at.out_datetime as time_time,
        -1 as score
    from attend_tb at
    left join course_tb ct
    on at.course_id = ct.course_id
    ) tmp
) tmp1
group by course_id,
    course_name
order by course_id asc


发表于 2023-03-29 15:08:35 回复(0)
Mysql
zxcv0112358头像 zxcv0112358
最大同时在线人数板子题。
我忽略了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)
Mysql
力大如刘头像 力大如刘 
select 
  course_id,
  course_name,
  max(num) max_num
from 
(
  select
   course_id,
   course_name,
   sum(uv)over(partition by course_id order by dt,uv desc) num
 from
  (
    select
      course_id,
      course_name,
      in_datetime dt,
      1 uv
   from course_tb join attend_tb using(course_id)
   union all
   select 
     course_id,
     course_name,
     out_datetime dt,
     -1 uv
   from course_tb join attend_tb using(course_id)
  )t
)t
group by 1,2
order by 1

发表于 2023-02-17 10:10:44 回复(0)
Mysql
牛客365716483号头像 牛客365716483号 
with t1 as (
select course_id,in_datetime dt,1 num from attend_tb a 
union all 
select course_id,out_datetime dt,-1 num from attend_tb b)


select course_id,course_name,max(rn) max_num
from(
select course_id,course_name,dt,num,
sum(num) over(partition by course_id,course_name order by dt asc,num desc) rn
from t1 inner join course_tb ct using(course_id)) c
group by course_id,course_name

发表于 2023-02-11 17:17:18 回复(0)
Mysql
小宏的大牛之路头像 小宏的大牛之路 
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,flag desc) as num
    from (
        select course_id,course_name,in_datetime as dt,1 as flag
        from attend_tb
        left join course_tb using(course_id)
        union all 
        select course_id,course_name,out_datetime as dt,-1 as flag
        from attend_tb
        left join course_tb using(course_id)
    )t1
)t2
group by course_id,course_name
order by course_id

发表于 2023-01-20 16:48:55 回复(0)
Mysql
西南第一深情头像 西南第一深情 
#又是典中典，最大同时在线人数
with t1 as(
select 
    course_id,
    time,
    cnt,
    sum(cnt) over(partition by course_id order by time,cnt desc)online_cnt
from
(
select 
    course_id,
    user_id,
    in_datetime time,
    1 cnt 
from attend_tb
union
select 
    course_id,
    user_id,
    out_datetime time,
    -1 cnt 
from attend_tb
) t
)
select t1.course_id,c.course_name,max(online_cnt) max_num
from t1 left join course_tb c using(course_id)
group by t1.course_id,c.course_name
order by t1.course_id

发表于 2023-01-04 02:30:35 回复(0)
Mysql
一颗宏心头像 一颗宏心 
select course_id,course_name,max(uv_cnt) as max_num
from (
    select course_id,course_name,
    sum(uv) over(partition by course_id order by dt,uv desc) uv_cnt
    from (
    select course_id,in_datetime dt,1 as uv from attend_tb
    union all
    select course_id,out_datetime dt,-1 as uv from attend_tb
    ) as uv_tb
    inner join course_tb using(course_id)
)t1
group by course_id,course_name
order by course_id;

   

发表于 2022-11-21 09:56:35 回复(0)
Mysql
萧瑟仙贝勇夺冠军头像 萧瑟仙贝勇夺冠军
刷到这里的聚聚们，基本这种题目，都不带思考的就可以动手了。通过率也比SQL163高得多。 
SELECT c.course_id, c.course_name, MAX(res.accum) max_num
FROM
(
	SELECT user_id, course_id, ti, 
		SUM(dif) OVER(PARTITION BY course_id ORDER BY ti, dif DESC) accum
	FROM
	(
	SELECT user_id, course_id, in_datetime ti, 1 dif
	FROM attend_tb
	UNION
	SELECT user_id, course_id, out_datetime ti, -1 dif
	FROM attend_tb
	) t
) res
LEFT JOIN course_tb c
ON res.course_id = c.course_id
GROUP BY c.course_id, c.course_name
ORDER BY c.course_id


发表于 2022-11-10 10:10:49 回复(0)
Mysql
酸菜鱼土豆大侠头像 酸菜鱼土豆大侠

【场景】：同时在线人数

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

分析思路

难点：
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)
Mysql
牛客440608019号头像 牛客440608019号 
select a.course_id,b.course_name,max(ann_num) from (

select 
		course_id
		,sum(num) over(partition by course_id order by io_time asc,num desc) ann_num
from (
			select course_id,in_datetime as io_time,1 as num from attend_tb

			union all 

			select course_id,out_datetime as io_time ,-1 as num  from attend_tb
)TMP



)a left join course_tb b on a.course_id=b.course_id 
group by a.course_id,b.course_name
order by a.course_id

发表于 2022-10-26 11:19:15 回复(0)
提交观点

问题信息

难度：
71条回答 948浏览

热门推荐

通过挑战的用户

查看代码

相关试题

  • 二维码

    扫描二维码，关注牛客网

  • 二维码

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号