牛客直播各科目出勤率

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

用户行为表behavior_tb如下（其中user_id表示用户编号、if_vw表示是否浏览、if_fav表示是否收藏、if_sign表示是否报名、course_id代表课程编号）：

user_id	if_vw	if_fav	if_sign	course_id
100	1	1	1	1
100	1	1	1	2
100	1	1	1	3
101	1	1	1	1
101	1	1	1	2
101	1	0	0	3
102	1	1	1	1
102	1	1	1	2
102	1	1	1	3
103	1	1	0	1
103	1	0	0	2
103	1	0	0	3
104	1	1	1	1
104	1	1	1	2
104	1	1	0	3
105	1	0	0	1
106	1	0	0	1
107	1	0	0	1
107	1	1	1	2
108	1	1	1	3

上课情况表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

请你统计每个科目的出勤率（attend_rate(%)，结果保留两位小数），出勤率=出勤（在线时长10分钟及以上）人数 / 报名人数，输出结果按course_id升序排序，以上数据的输出结果如下：

course_id	course_name	attend_rate(%)
1	Python	75.00
2	SQL	60.00
3	R	66.67

示例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 behavior_tb;
CREATE TABLE behavior_tb(
user_id int(10) NOT NULL, 
if_vw int(10) NOT NULL,
if_fav int(10) NOT NULL,
if_sign int(10) NOT NULL,
course_id int(10) NOT NULL);

INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(100, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(101, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(101, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(102, 1, 1, 1, 3);
INSERT INTO behavior_tb VALUES(103, 1, 1, 0, 1);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 2);
INSERT INTO behavior_tb VALUES(103, 1, 0, 0, 3);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 1);
INSERT INTO behavior_tb VALUES(104, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(104, 1, 1, 0, 3);
INSERT INTO behavior_tb VALUES(105, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(106, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 0, 0, 1);
INSERT INTO behavior_tb VALUES(107, 1, 1, 1, 2);
INSERT INTO behavior_tb VALUES(108, 1, 1, 1, 3);

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|75.00
2|SQL|60.00
3|R|66.67

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2022-01-23

select c.course_id, course_name, round(count(distinct if(TIMESTAMPDIFF(second,in_datetime,out_datetime) >= 600, a.user_id, null))/ count(distinct if(if_sign=1,b.user_id,null))*100,2)
from attend_tb a right join behavior_tb b
on a.user_id = b.user_id and a.course_id = b.course_id
join course_tb c
on b.course_id = c.course_id
group by c.course_id, course_name
order by c.course_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2021-12-16

select 
att.course_id
,course_name
,round(sum(case when online>=10 then 1 else 0 end)/sum_sign*100,2) as attend_rate
from 
(
     select
     user_id
     ,course_id
     ,sum(timestampdiff(minute,in_datetime,out_datetime)) as online
     from attend_tb
     group by user_id,course_id
) att
left join course_tb ct
on att.course_id=ct.course_id
left join
       (
        select course_id,sum(if_sign) sum_sign
        from behavior_tb
        group by course_id
       ) bt
on bt.course_id=ct.course_id
group by att.course_id,course_name
order by att.course_id

Mysql 解法, 执行用时: 37ms, 内存消耗: 6500KB, 提交时间: 2021-12-14

SELECT b.course_id, c.course_name,
       ROUND(100 * SUM(IF(a.tm >= 10, 1, 0)) / SUM(b.if_sign), 2) AS attend_rate
FROM behavior_tb b
LEFT JOIN course_tb c
ON b.course_id = c.course_id
LEFT JOIN (
    SELECT user_id, course_id, MAX(TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime)) tm
    FROM attend_tb
    GROUP BY user_id, course_id
) AS a
ON a.user_id = b.user_id AND a.course_id = b.course_id
GROUP BY b.course_id, c.course_name
ORDER BY b.course_id

Mysql 解法, 执行用时: 38ms, 内存消耗: 6396KB, 提交时间: 2022-01-26

with t1 as(
select user_id, a.course_id as course_id, course_name,
    sum(timestampdiff(minute,in_datetime,out_datetime )) as attend
from attend_tb c left join course_tb a 
    on a.course_id=c.course_id
group by course_id, course_name, user_id 
having attend >= 10
),

t2 as(
select course_id, sum(if_sign) as cnt from behavior_tb
    group by course_id
)

select t2.course_id, course_name,
round( count(attend)/cnt*100  ,2) 
from t1 join t2 
on t1.course_id=t2.course_id 
group by 1,2
order by 1 asc

Mysql 解法, 执行用时: 38ms, 内存消耗: 6396KB, 提交时间: 2022-01-22

select t1.course_id,t2.course_name
,round(count(distinct(case when timestampdiff(minute,t3.in_datetime,t3.out_datetime)>=10 then t1.user_id else null end ))*100/count(distinct (case when t1.if_sign = 1 then t1.user_id else null end)),2)  as baoming
from behavior_tb as t1
left join attend_tb as t3
on t1.course_id = t3.course_id and t1.user_id = t3.user_id 
left join course_tb as t2 
on t1.course_id = t2.course_id 
group by t1.course_id,t2.course_name

上一题

下一题

详情