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

select *

from (

SELECT

tag,

dt,

sum( tmp.like_cn ) over ( PARTITION BY tag ORDER BY dt desc rows BETWEEN current ROW AND 6 following ) as sum_li ,

max(tmp.re_cn) over ( PARTITION BY tag ORDER BY dt desc rows BETWEEN current ROW AND 6 following ) as max_re

FROM

(

SELECT

tag,

date_format( start_time, "%Y-%m-%d" ) dt,

sum( if_like ) like_cn,

sum( if_retweet ) re_cn

FROM

tb_user_video_log tl

JOIN tb_video_info ti ON tl.video_id = ti.video_id

WHERE

start_time BETWEEN '2021-09-25'

AND '2021-10-09'

GROUP BY

tag,

date_format( start_time, "%Y-%m-%d" )) tmp ) tmp1

where dt between '2021-10-01' and '2021-10-03'

order by tag desc, dt