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

with LastDate as (
    select Max(end_time) as Last_time
    from tb_user_video_log
),
    vLog as (
        select
            t1.video_id,
            avg(
                case
                    when timestampdiff (second,  t1.start_time,t1.end_time) >= t2.duration            then 1
                    else 0
                end
            ) as complete_rate,# timestapdiff start ahead end_time
            sum(t1.if_like) as thumb_cnt,
            count(t1.comment_id) as subscribe_cnt,
            sum(t1.if_retweet) as repost_cnt,
            min(
                datediff (
                   
                    (
                        select
                            Last_time
                        from
                            LastDate
                    ), t1.end_time
                )
            ) as fresh_rate
        from
            tb_user_video_log t1
            join tb_video_info t2 on t1.video_id = t2.video_id
        where
            t2.release_time >= date_sub((select Last_time from LastDate), Interval 29 Day)
        group by
            t1.video_id
    )
# select
#     video_id,
#     (
#         complete_rate * 100 + thumb_cnt * 5 + subscribe_cnt * 3 + 2 * repost_cnt
#     ) / (fresh_rate + 1) as hot_index
# from
#     vLog
# group by
#     video_id
# order by
#     video_id,
#     hot_index desc
# limit
#     3;


select video_id,round(((
        complete_rate * 100 + thumb_cnt * 5 + subscribe_cnt * 3 + 2 * repost_cnt
    ) / (fresh_rate + 1)),0) as hot_index
from vLog
order by
    
    hot_index desc
limit
    3;

1. 首先要注意Datediff 和TimestampDiff 区别， TimestampDiff 要把开始时间放在end_time 前面，Datediff要反过来
2. 要注意审题，确定order by 的要求
3. 思路是首先单独找出全局最近的发布时间，其次对于每个视频找出以下参数。完播率是指开始结束的时间差大于duration，最近未播放天数可以设置为间隔最短的结束天和全局最近发布天
4. 一个未理解点在于为什么一个视频一个用户多次观看，点赞数可以被允许反复统计？按理说一个用户只能点一次赞