首页 > 试题广场 >

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

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

热度指数：15319 时间限制：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

yuciaaa

WITH A AS(
    SELECT user_id, course_name, timestampdiff(minute, in_datetime, out_datetime) AS time
    FROM attend_tb
    LEFT JOIN  course_tb
    ON course_tb.course_id = attend_tb.course_id
)
SELECT course_name, ROUND(AVG(time), 2) AS avg
FROM A
GROUP BY course_name
ORDER BY avg DESC

发表于 2024-04-29 12:57:43 回复(0)

Mysql

祖拜尔实习生

select
    course_name,
    round(avg(timestampdiff(minute, in_datetime, out_datetime)),2)  avg_len
from(
    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
)t
group by
    course_name
order by
    avg_len desc

编辑于 2024-04-19 10:36:37 回复(0)

Mysql

swbl_ww

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

发表于 2023-09-11 15:21:20 回复(0)

Mysql

牛客268263674号

这个重复观看的人用不去重？

发表于 2023-08-16 15:45:35 回复(0)

Mysql

dolen

-- 请你统计每个科目的平均观看时长（观看时长定义为离开直播间的时间与进入直播间的时间之差，单位是分钟），输出结果按平均观看时长降序排序，结果保留两位小数。
-- 注意：单位是分钟
select 
t1.course_name,
round(avg(timestampdiff(second,t2.in_datetime,t2.out_datetime))/60,2) as avg_Len
from course_tb t1 
left join attend_tb t2
on t1.course_id = t2.course_id
group by course_name
order by 2 desc;

发表于 2023-08-11 15:32:57 回复(0)

Mysql

寄了的打工人

SELECT DISTINCT 
course_name,
round(sum((timestampdiff(minute,in_datetime,out_datetime))) OVER(PARTITION BY course_name) / count(course_id) OVER(PARTITION BY course_name),2) as avg_Len

FROM attend_tb a

left join course_tb c USING (course_id)

order by  avg_Len desc

发表于 2023-06-19 16:02:58 回复(0)

Mysql

丑八怪咖

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

难度不大的一道题，思路供大家参考。

建立一个时间差的新表，之后对其进行居合平均值计算即可

发表于 2023-06-08 10:45:29 回复(0)

Mysql

我叫谢小盟，叫我查尔斯好了

select 
course_name,
round(avg(duration),2) 

from 

(select 
c.course_id, 
c.course_name,
timestampdiff(minute, in_datetime, out_datetime) as duration

from

attend_tb as a 
join course_tb as c 
on a.course_id = c.course_id) as t1

group by course_name
order by round(avg(duration),2) desc

发表于 2023-06-01 03:11:25 回复(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)

Mysql

SunniSong

建议timestampdiff 计算单位用second(秒)，再除以60换算分钟，会更精确。而timestampdiff 计算单位直接用minute(分钟)，会默认去尾比如0分30 记成0分钟，直接把秒去掉，以下示例大家可以试一下看看timestampdiff用哪种单位更准确。

-- 换算分钟：间隔30秒，timestampdiff单位minute分钟。
select timestampdiff(minute,time('2022-10-01 11:00:00'),time('2022-10-01 11:00:30'))

-- 换算分钟：间隔30秒，timestampdiff单位second秒。
select timestampdiff(second,time('2022-10-01 11:00:00'),time('2022-10-01 11:00:30')) / 60

发表于 2023-03-04 13:32:47 回复(0)

Mysql

小宏的大牛之路

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

发表于 2023-01-19 17:11:32 回复(0)

Mysql

一颗宏心

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

发表于 2022-11-20 12:45:24 回复(0)

Mysql

力大如刘

这个平均是不需要对用户去重的吗

发表于 2022-11-02 17:10:35 回复(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

牛客876374595号

题目定义不清晰。

本题题目定义应该为：用户观看课程的平均时长（同一个用户进入两次课程，认为是2次观看）

关键：使用时间差函数timestampdiff

select course_name,
round(sum(timestampdiff(minute,in_datetime,out_datetime))/count(user_id),2) avg_len
from attend_tb t1 left join course_tb t2
on t1.course_id=t2.course_id
group by course_name
order by avg_len desc

发表于 2022-10-05 16:57:49 回复(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)

Mysql

西冷牛排

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

发表于 2022-09-10 02:31:14 回复(0)