程序员项目外包,吕梁seo排名,网站首页开发收费,江苏分销网站建设----SQL157 平均播放进度大于60%的视频类别 计算各类视频的平均播放进度#xff0c;将进度大于60%的类别输出。 注#xff1a; 播放进度播放时长视频时长*100%#xff0c;当播放时长大于视频时长时#xff0c;播放进度均记为100%。 结果保留两位小数#xff0c;并按播放进…----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_progressfrom (select tv.tag,datediff(second,start_time,end_time) as watch_time,tv.durationfrom tb_user_video_log tujoin tb_video_info tvon tu.video_id tv.video_id)tgroup by tagorder 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_progressfrom tb_user_video_log tujoin tb_video_info tvon tu.video_id tv.video_idgroup by tagorder 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’ 表示小数点后保留的位数。 ③ 最后在末尾追加百分号 % 。
