网站的开发方法,百度推广关键词越多越好吗,整人关不掉的网站怎么做,wordpress文章发布首先避坑#xff1a;
Mysql 中两个日期直接相减#xff0c;若在同一天则得到的是秒#xff0c;否则相减得到的并不是秒#xff0c;一定要注意。
函数
TIMESTAMPDIFF(unit,begin,end);
函数返回 begin - end 的结果。
其中 begin 和 end 是 DATE 或 DATETIME 表达式。 …首先避坑
Mysql 中两个日期直接相减若在同一天则得到的是秒否则相减得到的并不是秒一定要注意。
函数
TIMESTAMPDIFF(unit,begin,end);
函数返回 begin - end 的结果。
其中 begin 和 end 是 DATE 或 DATETIME 表达式。
TIMESTAMPDIFF函数允许其参数具有混合类型例如begin是DATE值end可以是DATETIME值。 如果使用DATE值则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。
unit参数是确定(end-begin)的结果的单位表示为整数。 以下是有效单位unit参数是确定(end-begin)的结果的单位表示为整数。 以下是有效单位 MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
栗子
平均播放进度大于60%的视频类别_牛客题霸_牛客网用户-视频互动表tb_user_video_log。题目来自【牛客题霸】https://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef?tpId268tqId2285039ru/exam/ojqru/ta/sql-factory-interview/question-rankingsourceUrl%2Fexam%2Foj
问题计算各类视频的平均播放进度将进度大于60%的类别输出。
注
播放进度播放时长÷视频时长*100%当播放时长大于视频时长时播放进度均记为100%。结果保留两位小数并按播放进度倒序排序。
输出示例
示例数据的输出结果如下
tagavg_play_progress影视90.00%美食75.00%
解释
影视类视频2001被用户101、102、103看过播放进度分别为30秒100%、21秒70%、30秒100%平均播放进度为90.00%保留两位小数
美食类视频2002被用户102、103看过播放进度分别为30秒50%、60秒100%平均播放进度为75.00%保留两位小数
建表语句
DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,uid INT NOT NULL COMMENT 用户ID,video_id INT NOT NULL COMMENT 视频ID,start_time datetime COMMENT 开始观看时间,end_time datetime COMMENT 结束观看时间,if_follow TINYINT COMMENT 是否关注,if_like TINYINT COMMENT 是否点赞,if_retweet TINYINT COMMENT 是否转发,comment_id INT COMMENT 评论ID
) CHARACTER SET utf8 COLLATE utf8_bin;CREATE TABLE tb_video_info (id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增ID,video_id INT UNIQUE NOT NULL COMMENT 视频ID,author INT NOT NULL COMMENT 创作者ID,tag VARCHAR(16) NOT NULL COMMENT 类别标签,duration INT NOT NULL COMMENT 视频时长(秒数),release_time datetime NOT NULL COMMENT 发布时间
)CHARACTER SET utf8 COLLATE utf8_bin;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, CONCAT(avg_play_progress, %) as avg_play_progress
FROM (SELECT tag,ROUND(AVG(IF(TIMESTAMPDIFF(SECOND, start_time, end_time) duration, 1,TIMESTAMPDIFF(SECOND, start_time, end_time) / duration)) * 100, 2) as avg_play_progressFROM tb_user_video_logJOIN tb_video_info USING(video_id)GROUP BY tagHAVING avg_play_progress 60ORDER BY avg_play_progress DESC
) as t;
