题解 | #国庆期间每类视频点赞量和转发量#【💨超简单版】

思路

• 【表1】求出不同类型视频每天的点赞数和转发数
• 【表2】国庆前三天成为一列
• 根据视频类型tag、国庆前三天的三个日期dt分类

group by tag,start_time

• 判断start_time是否在国庆日期dt的前一周内

if(timestampdiff(day,start_time,dt) between 0 and 6,,)

• sum(if(),点赞数,0)即可获得前一周内累计点赞数
• max(if(),转发数,0)即可获得前一周内最高单日转发数

表1：不同类型视频每天的点赞数和转发数

关键词：group by tag,start_time

select tag,date_format(start_time,'%Y%m%d') start_time,sum(if_like) l,sum(if_retweet) r
from tb_user_video_log join tb_video_info using(video_id)
group by tag,start_time

表2：国庆前三天的表格

关键词：union

select '2021-10-01' dt
union
select '2021-10-02' dt
union
select '2021-10-03' dt

最终结果

关键词：sum(if())&max(if())

select tag,dt,
    sum(if(timestampdiff(day,start_time,dt) between 0 and 6,l,0)) sl,
    max(if(timestampdiff(day,start_time,dt) between 0 and 6,r,0)) sr
from (
    select tag,date_format(start_time,'%Y%m%d') start_time,sum(if_like) l,sum(if_retweet) r
    from tb_user_video_log join tb_video_info using(video_id)
    group by tag,start_time
)a   join ( select '2021-10-01' dt
            union
            select '2021-10-02' dt
            union
            select '2021-10-03' dt) b
group by tag,dt
order by tag desc,dt asc