Mysql 解法, 执行用时: 36ms, 内存消耗: 6400KB, 提交时间: 2021-12-05

select t1.tag,t1.day,sum(t2.likes),max(t2.retweet)
FROM
(select t2.tag ,left(t1.start_time,10) day,sum(t1.if_like) as likes,sum(t1.if_retweet) as retweet
from tb_user_video_log t1 left join 
tb_video_info t2
on t1.video_id=t2.video_id
group by t2.tag,day) t1
left join 
(select t2.tag ,left(t1.start_time,10) day,sum(t1.if_like) as likes,sum(t1.if_retweet) as retweet
from tb_user_video_log t1 left join 
tb_video_info t2
on t1.video_id=t2.video_id
group by t2.tag,day) t2
on t1.tag=t2.tag
where TIMESTAMPDIFF(day,t2.day,t1.day)<7
and TIMESTAMPDIFF(day,t2.day,t1.day)>=0
and t1.day in ("2021-10-01","2021-10-02","2021-10-03")
group by t1.tag,t1.day

Mysql 解法, 执行用时: 36ms, 内存消耗: 6412KB, 提交时间: 2022-01-25

with t as(
    select tag,
    date_format(start_time,'%Y-%m-%d') as dt,
    sum(if_like) as like_cnt,
    sum(if_retweet) as retweet_cnt
    from tb_user_video_log left join tb_video_info using (video_id)
    group by tag,dt
)

select *
from (
    select tag,
    dt,
    sum(like_cnt) over (partition by tag order by dt rows between 6 preceding and current row) as sum_like_cnt_7d,
    max(retweet_cnt) over (partition by tag order by dt rows between 6 preceding and current row) as max_retweet_cnt_7d
    from t
) t1
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt

Mysql 解法, 执行用时: 36ms, 内存消耗: 6420KB, 提交时间: 2022-01-03

SELECT * FROM
(SELECT tag, dt, 
       SUM(lick_cnt) OVER w,
       MAX(retweet_cnt) OVER w
FROM
(SELECT tag, DATE_FORMAT(start_time, '%Y-%m-%d') AS dt,
SUM(if_like) lick_cnt, SUM(if_retweet) retweet_cnt
FROM tb_user_video_log JOIN tb_video_info USING(video_id)
WHERE start_time BETWEEN '20210925' AND '20211004' GROUP BY tag, dt) t
WINDOW w AS (PARTITION BY tag ORDER BY dt DESC rows BETWEEN CURRENT ROW AND 6 FOLLOWING)
) t1
WHERE dt BETWEEN '2021-10-01' AND '2021-10-04'
ORDER BY tag DESC, dt ASC

Mysql 解法, 执行用时: 36ms, 内存消耗: 6444KB, 提交时间: 2022-01-25

# 每一类视频tag类别
#前三天分别是 01-02-03
# 近一周总点赞量if_like
# 近一周最大单日转发量if_retweet
# 视频类别降序
# j
select 
	tag, 
	dt, 
	sum(like_cnt) `sum_like_cnt_7d`, 
	max(tweet) `max_retweet_cnt_7d`
from
(select 
	tag, 
	dt,
	sum(if_like) `like_cnt`,
	sum(if_retweet) `tweet`
from 
(select vi.tag, b.dt, date(vl1.start_time) `st_date`, vl1.if_like, vl1.if_retweet
from tb_video_info vi 
left join tb_user_video_log vl1 on vi.video_id = vl1.video_id, (
	select distinct date(start_time) dt
	from tb_user_video_log vl
	where DATEDIFF(vl.start_time,'2021-10-01')>=0 and DATEDIFF(vl.start_time,'2021-10-01')<3) b
where DATEDIFF(vl1.start_time,b.dt)<=0 and DATEDIFF(vl1.start_time,b.dt)>-7) t
GROUP BY tag, dt, st_date) t1
GROUP BY tag, dt
ORDER BY tag desc, `dt`;

Mysql 解法, 执行用时: 36ms, 内存消耗: 6448KB, 提交时间: 2022-01-22

SELECT *
FROM
(SELECT 
    tag,
    dt,
    SUM(like_cnt) over (partition by tag order by dt ROWS 6 preceding) AS sum_like_cnt_7d,
    MAX(retweet_cnt) over (partition by tag order by dt rows 6 preceding) AS max_retweet_cnt_7d
FROM
(SELECT 
    tag,
    DATE(start_time) AS dt,
    SUM(IF(if_like = 1, 1, 0)) AS like_cnt,
    SUM(if(if_retweet = 1, 1, 0)) AS retweet_cnt
FROM tb_user_video_log u
LEFT JOIN tb_video_info vi
ON u.video_id = vi.video_id
GROUP BY tag,DATE(start_time)) t1
GROUP BY tag, dt) t2
WHERE dt between '2021-10-01' and '2021-10-03'
ORDER BY tag DESC,dt ASC