mysql函数使用总结

计算视频的完播率：
https://www.nowcoder.com/discuss/353150021023506432?urlSource=sitemap
SELECT video_id,
ROUND((100 * comp_play_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt)
/ (TIMESTAMPDIFF(DAY, recently_end_date, cur_date) + 1), 0) as hot_index
FROM (
SELECT video_id,
AVG(IF(
TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0 --start_time:开始播放时间，end_time：播放结束时间，duration：视频时长
)) as comp_play_rate,
SUM(if_like) as like_cnt,
COUNT(comment_id) as comment_cnt,
SUM(if_retweet) as retweet_cnt,
MAX(DATE(end_time)) as recently_end_date, -- 最近被播放日期
MAX(DATE(release_time)) as release_date, -- 发布日期
MAX(cur_date) as cur_date -- 非分组列，加MAX避免语法错误
FROM tb_user_video_log
JOIN tb_video_info USING(video_id)
LEFT JOIN (
SELECT MAX(DATE(end_time)) as cur_date FROM tb_user_video_log
) as t_max_date ON 1
GROUP BY video_id
HAVING TIMESTAMPDIFF(DAY, release_date, cur_date) < 30
) as t_video_info
ORDER BY hot_index DESC
LIMIT 3;