题解 | #国庆期间每类视频点赞量和转发量#

解题的关键是如何统计每天的近一周总点赞量和一周内最大单天转发量

这里需要用到一个关键的rows 6 preceding

是“之前6行”的意思。也就是说每一行得到的结果是自身记录及前2行的求和/最大。

sum()over(partition by … order by … rows …preceding)

max()over(partition by … order by … rows …preceding)

完整sql代码如下：

select *
from (
            select tag,d,
                            sum(if_like)over(partition by tag order by d rows 6 preceding) like_cnt,
                            max(if_retweet)over(partition by tag order by d rows 6 preceding) retweet_cnt
            from (
                        select tag,date(start_time) d,sum(if_like) if_like,sum(if_retweet) if_retweet
                        from tb_user_video_log
                        inner join tb_video_info
                        using(video_id)
                        group by tag,d
                        ) t1
            ) t2
where d between '2021-10-01' and '2021-10-03'
order by tag desc,d