首页 > 试题广场 >

牛客直播各科目平均观看时长

[编程题]牛客直播各科目平均观看时长

热度指数：20166 时间限制：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_name	avg_Len
SQL	91.25
R	60.33
Python	58.00

示例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');

输出

SQL|91.25
R|60.33
Python|58.00

算法知识视频讲解

Mysql

尼德勇

select c.course_name,round(t2.zsc/t2.zrs,2)as avg_Len
from

(
select course_id,sum(grsc)as zsc,count(course_id)as zrs
from

(
select course_id,timestampdiff(minute,in_datetime,out_datetime)as grsc
from attend_tb
)t1

group by course_id
)t2



left join course_tb as c on t2.course_id=c.course_id
order by avg_Len desc

发表于 2022-06-01 16:54:32 回复(0)

Mysql

清水卡布

select course_name,round(avg(timestampdiff(minute,in_datetime,out_datetime)),2) as avg_Len
from attend_tb as at
left join course_tb as ct
on at.course_id=ct.course_id
group by course_name
order by avg_Len desc

发表于 2022-01-27 14:47:55 回复(0)

Mysql

张球球

①这个题对观看时长的定义有点问题，没上课但是人在直播间的时间居然也算

②二是重复的user_id居然也算

综上本题明确的定义应该是，对用户“每次进入直播间停留时长”的平均

发表于 2022-01-11 15:40:14 回复(7)

Mysql

墨风lana

select course_name,
    round(
        avg(
            timestampdiff(
                minute,if(time(in_datetime)<"19:00:00",
       concat(date(in_datetime)," 19:00:00"),
       in_datetime),out_datetime)),2) as avg_len
   from attend_tb  as a 
   left join course_tb as b 
   using(course_id)
   group by 1
   order by 2 desc;
   # 针对答案错误错处的修正
   # R课程是从19：00开始的，user_id 为102的用户是从18：58:00开始上课的，19:05:00离开的，
   # 他观看的时长应该是5分钟而不是7分钟。所以R课程的平均观看时长为119+5+55/3=59.67

发表于 2021-12-23 15:57:54 回复(1)

OneShow

答案错了，R课程是从19：00开始的，user_id 为102的用户是从18：58:00开始上课的，19:05:00离开的，他观看的时长应该是5分钟而不是7分钟。所以R课程的平均观看时长为119+5+55/3=59.67

发表于 2021-12-05 15:55:51 回复(2)

Mysql

专业拧螺丝

# 与题意一致的解答，观看时长定义为离开直播间的时间与进入直播间的时间之差
SELECT course_name,round(avg(timestampdiff(SECOND,in_datetime,out_datetime) / 60 ),2) as avg_Len
FROM attend_tb A left join course_tb B on A.course_id = B.course_id
GROUP BY course_name
order by avg_Len desc 

# 与题意不一致的解答，观看时长定义：在课程直播期间，用户看课的有效时长，即早于开课时间，晚于闭课时间不算有效时间

# 考点：字符串函数、日期函数的运用
# 注意题意，这里答案与题意不一样

SELECT course_name,round(avg( timestampdiff(SECOND,begin_time,end_time) / 60),2) as avg_Len
FROM (
SELECT distinct user_id,A.course_id,course_name,
            (case when in_datetime <= date_format(substring_index(course_datetime,'-',3),'%Y-%m-%d %H:%i:%s') then 
            date_format(substring_index(course_datetime,'-',3),'%Y-%m-%d %H:%i:%s') else in_datetime end) as begin_time, 
            (case when out_datetime >= date_format(replace(course_datetime,'19:00-',''),'%Y-%m-%d %H:%i:%s') then 
           date_format(replace(course_datetime,'19:00-',''),'%Y-%m-%d %H:%i:%s') else out_datetime end) as end_time
FROM attend_tb A left join course_tb B on A.course_id = B.course_id) A
group by course_name
order by avg_Len desc

发表于 2022-08-26 10:59:39 回复(3)

牛客226938092号

select course_name,
round(avg(timestampdiff(minute, in_datetime, out_datetime)),2)
as avg_Len from course_tb c
join attend_tb a on c.course_id=a.course_id
where day(out_datetime)=day(in_datetime)
group by course_name
order by avg_Len desc

# 取时间差的函数（后-前）：timestampdiff(minute, in_datetime, out_datetime)

发表于 2021-12-10 07:50:51 回复(0)

Mysql

啊坡糍嘚

select
    a.course_name
    ,round(avg(timestampdiff(minute,in_datetime,out_datetime)),2) as avg_len
from course_tb a,attend_tb b 
where a.course_id=b.course_id
group by a.course_name
order by avg_len desc

发表于 2022-04-10 10:50:15 回复(0)

Mysql

刷题冲冲冲！

题目里说了观看时长定义为 “离开直播间的时间与进入直播间的时间之差”，按题意即可。

select c.course_name,
       round(avg(timestampdiff(minute,a.in_datetime,a.out_datetime)),2) avg_Len
from attend_tb a join course_tb c on a.course_id = c.course_id
group by c.course_name
order by avg_Len desc

发表于 2022-01-04 11:18:06 回复(0)

牛客120674157号

是否需要去重？重新进入的用户是继续计算还是重新计算？

select course_name, round(avg(timestampdiff(second,in_datetime,out_datetime))/60,2) avg_Len

from attend_tb

join course_tb using(course_id)

group by course_name

order by avg_Len desc

选择second的原因是不确保其他没有显示的时间是否有其他的秒数，如果加起来求均值有可能不一样，故选择second/60

发表于 2023-04-02 17:29:50 回复(0)

Mysql

Kalina2023

select
    ct.course_name,
    round(avg(timestampdiff(minute,at.in_datetime,at.out_datetime)),2) as avg_Len
from attend_tb at
left join course_tb ct
on at.course_id = ct.course_id
group by ct.course_name
order by round(avg(timestampdiff(minute,at.in_datetime,at.out_datetime)),2) desc

发表于 2023-03-29 14:49:41 回复(0)

4fbd81e

表联结后group by就行

# 先建大宽表
with total_info as(
    select ct.course_name, ct.course_datetime,
    at.* 
    from course_tb ct
    join attend_tb at
    on ct.course_id=at.course_id
)

select course_name,
ROUND(avg(timestampdiff(minute,in_datetime,out_datetime)),2) as avg_Len
from total_info
group by course_id,course_name
order by avg_Len desc

发表于 2023-02-24 08:20:45 回复(0)

Mysql

酸菜鱼土豆大侠

【场景】：观看时长

【分类】：分组查询、多表连接

分析思路

难点：
1.计算（观看时长定义为离开直播间的时间与进入直播间的时间之差，单位是分钟），使用timestampdiff()

（1）请你统计每个科目的平均观看时长，输出结果按平均观看时长降序排序，结果保留两位小数

[使用]：timestampdiff(minute,in_datetime,out_datetime)
[使用]：group by course_name; order by avg_Len desc

最终结果

select 查询结果 [课程名称;平均观看时长]
from 从哪张表中查询数据[多表]
group by 查询条件 [课程名称]
order by 对查询结果排序 [按平均观看时长降序排序];

求解代码

select
    course_name,
    round(avg(timestampdiff(minute,in_datetime,out_datetime)),2) as avg_Len
from course_tb
left join attend_tb using(course_id)
group by course_name
order by avg_Len desc

发表于 2022-10-30 18:04:43 回复(0)

Mysql

无情的刷题机器X

这么简单的题也能踩坑，自闭了，坑点如下：

计算平均观看时长，而不是人均观看时长，，不需要按用户聚合再平均，直接 timestampdiff算出每次观看的时长后，求平均数即可

在有就是直接按minute算时间差即可，不需要按second计算，最后再转回minute

select course_name,
round(avg((timestampdiff(minute,in_datetime,out_datetime))),2) as avg_len 
from attend_tb at
right join course_tb ct
on at.course_id=ct.course_id
where out_datetime>course_datetime
group by 1
order by 2 desc

发表于 2022-09-29 15:49:43 回复(0)

kirua丶

select

course_name

,round(avg(timestampdiff(second,in_datetime,out_datetime)/60),2)

from attend_tb

left join course_tb

using(course_id)

group by 1

order by 2 desc

发表于 2025-05-20 17:42:50 回复(1)

牛客997640975号

按题目要求简单粗暴，先别管业务逻辑不然会想复杂

select
    course_name,
    round(avg(timestampdiff(minute,in_datetime,out_datetime)),2) as avg_Len
from attend_tb
join course_tb
using(course_id)
group by course_name
order by avg_Len desc

发表于 2025-04-16 00:11:15 回复(0)

Mysql

等offer的萝卜

SELECT course_name, ROUND(AVG((TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime))), 2) AS avg_Len

FROM course_tb JOIN attend_tb USING(course_id)

GROUP BY course_name

ORDER BY avg_Len DESC;

发表于 2025-04-10 11:20:19 回复(0)

qscgyjil

select

course_name,round(avg(timestampdiff(minute,in_datetime,out_datetime)),2) as avg_Len

from attend_tb as at

left join course_tb as ct

on at.course_id = ct.course_id

group by course_name

order by avg_Len desc

发表于 2025-04-02 14:44:02 回复(0)

想被叫靓仔的雪碧喜欢摸鱼

select course_name, 
round(avg(timestampdiff(minute, in_datetime, out_datetime)),2) as avg_len
from course_tb
left join attend_tb using(course_id)
group by course_name
order by avg_len desc

想复杂了，本来以为要讨论进入直播的时间与开始的时间的大小比较，然后一看题解，发现自己想得太复杂了

发表于 2025-03-24 16:12:21 回复(0)

牛客775456359号

/*with t1 as(
select course_name
,if(time(in_datetime)<'19:00:00',concat(date(in_datetime),str_to_date('19:00:00','%H:%i:%s')),in_datetime) in_datetime
,out_datetime
from course_tb
left join attend_tb
on course_tb.course_id=attend_tb.course_id
)*/
with t1 as
(
select course_name
,in_datetime
,out_datetime
from course_tb
left join attend_tb
on course_tb.course_id=attend_tb.course_id
)

select course_name 
,round(avg(timestampdiff(second,in_datetime,out_datetime))/60,2) avg_Len
from t1
group by course_name
order by 2 desc

发表于 2025-03-13 10:47:40 回复(0)