首页 > 试题广场 >

计算歌曲完播率

[编程题]计算歌曲完播率
  • 热度指数：100 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
请根据 user_listen_record、song_library计算出QQ音乐20230306歌曲完播率（播放时长>=听歌时长）输出表结构如下,其中完播率保留小数点后2位小数并按照完播率重小到大排序：

song_playback_history



已知QQ音乐部分用户听歌流水表格式和样例数据如下：

user_listen_record

其中ftime为数据分区时间，uin为用户账号（唯一标识），os_type为设备端分类，song_id为歌曲id，app_ver为应用版本，play_duration为听歌时长（秒）


曲库信息表：

song_library

其中song_id为歌曲id（唯一标识），song_name歌曲名称，duration为歌曲时长（秒），artist_id为歌手id，artist_name为歌手名

示例1

输入

-- ----------------------------
-- Table structure for user_listen_record
-- ----------------------------
DROP TABLE IF EXISTS `user_listen_record`;
CREATE TABLE `user_listen_record` (
  `ftime` bigint(20) DEFAULT NULL,
  `uin` varchar(255) DEFAULT NULL,
  `os_type` varchar(255) DEFAULT NULL,
  `song_id` bigint(20) DEFAULT NULL,
  `app_ver` varchar(255) DEFAULT NULL,
  `play_duration` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of user_listen_record
-- ----------------------------
BEGIN;
INSERT INTO `user_listen_record` (`ftime`, `uin`, `os_type`, `song_id`, `app_ver`, `play_duration`) VALUES (20230306, 'a', 'ios', 1001, '10.0.1', 140);
INSERT INTO `user_listen_record` (`ftime`, `uin`, `os_type`, `song_id`, `app_ver`, `play_duration`) VALUES (20230306, 's', 'android', 1001, '10.0.1', 170);
INSERT INTO `user_listen_record` (`ftime`, `uin`, `os_type`, `song_id`, `app_ver`, `play_duration`) VALUES (20230306, 'm', 'ios', 1003, '10.0.5', 100);
INSERT INTO `user_listen_record` (`ftime`, `uin`, `os_type`, `song_id`, `app_ver`, `play_duration`) VALUES (20230306, 'u', 'android', 1004, '10.0.1', 229);
INSERT INTO `user_listen_record` (`ftime`, `uin`, `os_type`, `song_id`, `app_ver`, `play_duration`) VALUES (20230306, 'm', 'ios', 1002, '10.0.5', 230);
INSERT INTO `user_listen_record` (`ftime`, `uin`, `os_type`, `song_id`, `app_ver`, `play_duration`) VALUES (20230306, 'a', 'ios', 1003, '10.0.1', 257);
INSERT INTO `user_listen_record` (`ftime`, `uin`, `os_type`, `song_id`, `app_ver`, `play_duration`) VALUES (20230306, 'u', 'android', 1001, '10.0.1', 290);
INSERT INTO `user_listen_record` (`ftime`, `uin`, `os_type`, `song_id`, `app_ver`, `play_duration`) VALUES (20230306, 's', 'android', 1003, '10.0.1', 170);
INSERT INTO `user_listen_record` (`ftime`, `uin`, `os_type`, `song_id`, `app_ver`, `play_duration`) VALUES (20230306, 'a', 'ios', 1004, '10.0.1', 229);
COMMIT;

DROP TABLE IF EXISTS `song_library`;
CREATE TABLE `song_library` (
  `song_id` bigint(20) DEFAULT NULL,
  `song_name` varchar(255) DEFAULT NULL,
  `duration` bigint(20) DEFAULT NULL,
  `artist_id` bigint(20) DEFAULT NULL,
  `artist_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of song_library
-- ----------------------------
BEGIN;
INSERT INTO `song_library` (`song_id`, `song_name`, `duration`, `artist_id`, `artist_name`) VALUES (1001, '七里香', 297, 1, '周杰伦');
INSERT INTO `song_library` (`song_id`, `song_name`, `duration`, `artist_id`, `artist_name`) VALUES (1002, '逆战', 230, 235, '张杰');
INSERT INTO `song_library` (`song_id`, `song_name`, `duration`, `artist_id`, `artist_name`) VALUES (1003, '乌梅子酱', 257, 23, '李荣浩');
INSERT INTO `song_library` (`song_id`, `song_name`, `duration`, `artist_id`, `artist_name`) VALUES (1004, '倒数', 229, 25, '邓紫棋');
COMMIT;

输出

ftime|song_id|song_name|play_comp_rate
20230306|1002|逆战|1.00
20230306|1004|倒数|1.00
20230306|1003|乌梅子酱|0.33
20230306|1001|七里香|0.00
算法知识视频讲解
添加回答
牛客菜鸟一号头像 牛客菜鸟一号
select ftime,song_id,song_name,
    round(sum(case when duration-play_duration<=0 then 1 else 0 end)/count(song_id),2) as 'paly_comp_rate'
from user_listen_record join song_library using(song_id)
where ftime='20230306'
group by song_id,song_name
order by paly_comp_rate desc
不知道错哪里，结果是对的，各位大佬帮忙解答下
发表于 2023-09-11 16:06:42 回复(0)
粗心的小饼干在刷代码头像 粗心的小饼干在刷代码
with s1 as ( select distinct s1.song_id,count(s1.song_id) over (partition by s1.song_id) as compar_song
from user_listen_record s1
join song_library s2 on s1.song_id=s2.song_id and ftime='20230306'
where play_duration=duration or play_duration>duration ),
s2 as ( select ftime,s1.song_id,song_name,count(*) as total_song from user_listen_record s1
join song_library s2 on s1.song_id=s2.song_id and ftime='20230306' group by ftime,s1.song_id,song_name ),
s3 as ( select ftime,s2.song_id as song_id,song_name,total_song,ifnull(compar_song,0) as compar_song from s2 left join s1 on s2.song_id=s1.song_id )
select ftime,song_id,song_name,cast(compar_song/total_song as DECIMAL(10,2)) as play_comp_rate from s3 order by play_comp_rate desc;
发表于 2023-07-25 18:16:29 回复(0)
提交观点

问题信息

难度：
2条回答 676浏览

热门推荐

相关试题

计算歌曲完播率

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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