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

select
    video_id,
    round(
        (
            100 * avg_comp_play_rate + 5 * like_num + 3 * comment_num + 2 * retweet_num
        ) / (TIMESTAMPDIFF (DAY, recently_date, cur_date) + 1),
        0
    ) as hot
from
    (
        select
            tvi.video_id as video_id,
            avg(
                if (
                    TIMESTAMPDIFF (second, start_time, end_time) >= duration,
                    1,
                    0
                )
            ) as avg_comp_play_rate,
            sum(if_like) as like_num,
            count(comment_id) as comment_num,
            sum(if_retweet) as retweet_num,
            max(date (end_time)) as recently_date,
            MAX(DATE (release_time)) as release_date,
            MAX(cur_date) as cur_date
        from
            tb_user_video_log as tuvl
            left join tb_video_info tvi on tvi.video_id = tuvl.video_id
            left join (
                select
                    MAX(DATE (end_time)) as cur_date
                from
                    tb_user_video_log
            ) as tmd on 1
        group by
            tvi.video_id
        having
            TIMESTAMPDIFF (DAY, release_date, cur_date) < 30
    ) as df
order by
    hot desc
limit
    3

• 热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度； 
• 新鲜度=1/(最近无播放天数+1)；

视频完播率

avg(
                if (
                    TIMESTAMPDIFF (second, start_time, end_time) >= duration,
                    1,
                    0
                )
            ) as avg_comp_play_rate

点赞数

sum(if_like) as like_num

评论数

count(comment_id) as comment_num

转发数

sum(if_retweet) as retweet_num

最近被播放的日期

max(date (end_time)) as recently_date

发布时间

MAX(DATE (release_time)) as release_date

当前日期

MAX(cur_date) as cur_date

TIMESTAMPDIFF计算两个日期之间的间隔，可以计算second，hour，day，month，year