通过代码

select
    tag,
    dates dt,
    like1 sum_like_cnt_7d,
    max_retweet max_retweet_cnt_7d
from
(
SELECT
       tag,
       date(start_time) dates,
       sum(sum(if_like) )over(
           #order by date(start_time)
           rows  6 preceding
           ) like1,
       max(sum(if_retweet))over(
           #order by date(start_time)
           rows 6 preceding
           )max_retweet
FROM
     tb_user_video_log t_l
join
     tb_video_info t_i
ON
    t_l.video_id = t_i.video_id
group by
    tag, date(start_time)
)t
where dates between '2021-10-01' and '2021-10-03'
#order by
    tag desc ,dt

我为社么又把order by #掉了呢？，当然是这个代码和这个测试数据运行出来的结果本身就是有序且符合题目要求的。

思路

首先我们来看看题目要求：

统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。

那么什么是头三天每天的近一周呢？

也就是说统计的是9.25-10.1、9.26-10.2、9.27-10.3

现在我们了解到了题目的具体要求

现在开始选择方法来完成，

#第一感觉：( •̀ ω •́ )y 这不是滑动窗口吗？来给我整一个双端队列！一看mysql那没事了。

我们可以先把题上的解释表给画出来,帮助我们整理思路

表t

with  t as (
SELECT
       tag,
       date(start_time) dates,
       sum(if_like)     likes,
       sum(if_retweet)  retweet
FROM
     tb_user_video_log t_l
join
     tb_video_info t_i
ON
    t_l.video_id = t_i.video_id
group by
    tag, date(start_time)
)

1.union

我首先想到的是用union，即分别查完这三部分的每一部分，然后两个union将结果连接起来

那么这样子的优点是什么呢？

1.语法简单，无脑复制，改变日期就行，

2.本题目说了：至少每个类别下国庆头3天及之前一周的每天都有播放记录。假如是数据量不够庞大，不是每一天都有播放记录本方法也管用，因为我们是筛选求和以及最值，三次之后进行连接

缺点

1.如果不是3天，而是10天，100天呢？代码必然是巨大的。

2.依照本题目要求排序麻烦，还需要对结果集整个进行排序，也就是说还需要再嵌套一层子查询（本代码没有进行这个）

代码：

select
    tag,
    '2021-10-01' dt,
    sum(likes) likes,
    max(retweet) retweet
from
     t
where
    dates between '2021-9-25' and '2021-10-01'
group by
  tag desc
  
union all
select
    tag,
    '2021-10-02' dt,
    sum(likes) likes,
    max(retweet) retweet
from
    t
where
    dates between '2021-9-26' and '2021-10-02'
group by
  tag desc
  
union all
select
    tag,
    '2021-10-03' dt,
    sum(likes) likes,
    max(retweet) retweet
from
    t
where
    dates between '2021-9-27' and '2021-10-03'
group by
  tag desc

2.join

对t进行自连接，然后筛出来符合要求的日期，即日期差在[0,6]之间，且日期是10月1，2，3的一天。

但是如果说本题要求的是某天前后n天怎么办呢？

还有一个问题我没有想明白：

这个代码一在我自己的数据库跑就直接导致我的数据库断开连接，然后它是可以在判题系统上跑通的。

反正给我是整不会了

select 
  t1.tag,
  t1.dates,
  sum(t2.likes),
  max(t2.retweet)
FROM
    t t1
join
    t t2
on 
  t1.tag=t2.tag
where 
 t1.dates in ('2021-10-01','2021-10-02','2021-10-03')
 and TIMESTAMPDIFF(day,t2.dates,t1.dates) BETWEEN 0 and 6
group by 
  t1.tag,t1.dates

3.rows 窗口函数参数

这就是我最后选择的方法了

先上用法：

select
    *,
    #默认 从起始位置到当前行
    sum(likes)over(
        order by dates
        ) like1,
    #起点到当前
    sum(likes)over(
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) like2,
    #前3行到当前行
    sum(likes)over(
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        ) like31,
    #可以将between and 省略
    sum(likes)over(
        ROWS  3 PRECEDING
        ) like32,
    #前三行，当前行，后一行
    sum(likes)over(
        ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING
        ) like4,
    #当前行以及往后所有行
    sum(likes)over(
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) like5,
    #所有行
    sum(likes)over(
        partition by tag,dates
        ) like6
from
    t

以及相应结果

alt

所以就是

select
    tag,dates,
    sum(likes )over(
        #order by date(start_time)
        rows  6 preceding
        ) like1,
    max(retweet)over(
        #order by date(start_time)
        rows 6 preceding
        )max_retweet
from
    t

改一下就是我的通过代码了

然后这个方法有一个问题：

本题目说：至少每个类别下国庆头3天及之前一周的每天都有播放记录。假如是数据量不够庞大，不是每一天都有播放记录本方法就不管用了，因为我们是前六行嘛。

解决方案：

我们需要将表t改成三表连接，创建一个只有一自增列date，且起始日期与本题一样的表然后将这表进行外连接就会把失去的行给补回来了。

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

通过代码

思路

表t

1.union

2.join

3.rows 窗口函数参数