题解 | #近一个月发布的视频中热度最高的top3视频#

SELECT
    tuv.video_id video_id,
    ROUND(
    	(
        100 * (
            SUM(
                IF (
                    timestampdiff(second,start_time,end_time) >= tvi.duration,
                    1,
                    0
                )
            ) / COUNT(tvi.video_id)
        ) + 5 * SUM(if_like) + 3 * COUNT(comment_id) + 2 * SUM(if_retweet)
    ) * (
        1 / (
            DATEDIFF((
                SELECT
                    DATE(MAX(end_time))
                FROM
                    tb_user_video_log
            ),DATE(MAX(end_time))) + 1
        )
    )) hot_index
FROM
    tb_user_video_log tuv,
    tb_video_info tvi
WHERE
	tuv.video_id = tvi.video_id
    and
    DATEDIFF (
        
        DATE(
            (SELECT
               MAX(end_time)
            FROM
                tb_user_video_log)
        ),DATE(release_time)
    ) <= 29
GROUP BY
    tuv.video_id
order by hot_index desc
limiT 3

思路：

1.连表

2.筛选近一个月发布的 [T-29，整体最新发布时间T]

3.按视频id分组

4.完播率=【如果(每条记录的结束-开始)>= 时长 则 /时长 然后求平均】

5.点赞数、评论数、转发数都求和

6.新鲜度=1/(最近无播放天数+1) 最近无播放天数=整体最大日期-该视频最大日期

7.排序取前三

注意：

1.DATEDIFF参数是日期不是时间

2.输出为整数

3.尽量用函数来日期/时间相减

4.DATEDIFF(d1, d2)和DATEDIFF(d2, d1)的结果并不一样，正或负