做题笔记:SQL Sever 方式做牛客SQL的题目--SQL157
2023-12-13 11:25:12
----SQL157 平均播放进度大于60%的视频类别
计算各类视频的平均播放进度,将进度大于60%的类别输出。
注:
播放进度=播放时长÷视频时长*100%,当播放时长大于视频时长时,播放进度均记为100%。
结果保留两位小数,并按播放进度倒序排序。
输出:
tag|avg_play_progress
表的创建及数据插入:
DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log
(
id INT PRIMARY KEY identity,-- '自增ID',
uid INT NOT NULL,-- '用户ID',
video_id INT NOT NULL,-- '视频ID',
start_time datetime,-- '开始观看时间',
end_time datetime,-- '结束观看时间',
if_follow TINYINT,-- '是否关注',
if_like TINYINT,-- '是否点赞',
if_retweet TINYINT,-- '是否转发',
comment_id INT,-- '评论ID'
);
CREATE TABLE tb_video_info
(
id INT PRIMARY KEY identity,-- '自增ID',
video_id INT UNIQUE NOT NULL,-- '视频ID',
author INT NOT NULL,-- '创作者ID',
tag VARCHAR(16) NOT NULL,-- '类别标签',
duration INT NOT NULL,-- '视频时长(秒数)',
release_time datetime NOT NULL,-- '发布时间'
);
INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id)
VALUES (101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:30', 0, 1, 1, null),
(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:21', 0, 0, 1, null),
(103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:20', 0, 1, 0, 1732526),
(102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 1, 0, 1, null),
(103, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 1, 0, 1, null);
INSERT INTO tb_video_info(video_id, author, tag, duration, release_time)
VALUES (2001, 901, '影视', 30, '2021-01-01 7:00:00'),
(2002, 901, '美食', 60, '2021-01-01 7:00:00'),
(2003, 902, '旅游', 90, '2020-01-01 7:00:00');
查询如下:
select tag,
format(CAST(Convert(decimal(18,4),sum(watch_time)*1.0/sum(duration)) * 100 as decimal(10,2)), 'N2') + '%' as avg_play_progress
from (
select tv.tag,
datediff(second,start_time,end_time) as watch_time,
tv.duration
from tb_user_video_log tu
join tb_video_info tv
on tu.video_id = tv.video_id
)t
group by tag
order by avg_play_progress desc
使用嵌套方便在写查询时理清思路,可优化查询如下(不嵌套查询):
select tv.tag,
format(
CAST(
avg(
iif(datediff(second,start_time,end_time) >= duration,100,Convert(decimal(18,2),datediff(second,start_time,end_time)*1.0/duration)*100)
) as decimal(10,2)), 'N2') + '%' as avg_play_progress
from tb_user_video_log tu
join tb_video_info tv
on tu.video_id = tv.video_id
group by tag
order by avg_play_progress desc
做题小结:
① FORMAT函数
FORMAT函数用于将数据格式化为指定的字符串表示形式。它可以应用于各种数据类型,如日期、时间、数值等。
FORMAT函数的基本语法如下:
FORMAT (value, format)
value 是要格式化的表达式或列,format 是定义格式的字符串。
FORMAT
函数的格式字符串的确切用法取决于输入表达式的数据类型和所需的输出格式,可以根据实际需求进行调整。
要注意:FORMAT
函数在处理大量数据时可能会影响性能,如果需要对大型数据集进行格式化,建议在客户端应用程序或报表中进行格式化,而不是在数据库查询中使用FORMAT
函数。
【举个🌰】
有查询如下:
select 0.8500 as '转换前', format(cast(0.8500 * 100 as decimal(10,2)), 'N2') + '%' as '转换后'
执行结果:
转换前 转换后
0.8500 85.00%
在此查询中:
① 将待转换的数值使用 CAST
函数将其乘以 100,转换为 DECIMAL(10,2)
类型,结果为 85.00
。② 使用 FORMAT
函数将结果格式化为带有两位小数的字符串,'N2'
表示将结果作为数字格式化并保留2位小数。具体来说:‘N’ 表示数字格式,‘2’ 表示小数点后保留的位数。
③ 最后在末尾追加百分号 '% '
。
文章来源:https://blog.csdn.net/emmmheng/article/details/134872324
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!