-- MySQL 答案 select * from ( SELECT b.tag, date_format (a.start_time, '%Y-%m-%d') as dt, SUM(sum(a.if_like)) over ( partition by tag order by date_format (a.start_time, '%Y-%m-%d') ROWS between 6 PRECEDING and current row ) as like_cnt, max(sum(a.if_retweet)) over ( partition by tag order by date_format (a.start_time, '%Y-%m-%d') ROWS between 6 PRECEDING and current row ) as ret_cnt FROM tb_user_video_log a LEFT JOIN tb_video_info b ON a.video_id = b.video_id WHERE DATEDIFF ( '2021-10-03', date_format (a.start_time, '%Y-%m-%d') ) < 9 group by b.tag, date_format (a.start_time, '%Y-%m-%d') order by b.tag desc, date_format (a.start_time, '%Y-%m-%d') asc ) t where t.dt >= '2021-10-01' and t.dt <= '2021-10-03' -- SQL SERVER答案 select * from ( SELECT b.tag, CONVERT(varchar(10), a.start_time, 120) as dt, SUM(sum(a.if_like)) over ( partition by tag order by CONVERT(varchar(10), a.start_time, 120) asc ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as like_cnt, max(sum(a.if_retweet)) over ( partition by tag order by CONVERT(varchar(10), a.start_time, 120) asc ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as ret_cnt FROM tb_user_video_log a LEFT JOIN tb_video_info b ON a.video_id = b.video_id WHERE DATEDIFF ( day, CONVERT(varchar(10), a.start_time, 120), '2021-10-03' ) < 9 group by b.tag, CONVERT(varchar(10), a.start_time, 120) ) t where t.dt >= '2021-10-01' and t.dt <= '2021-10-03' order by t.tag desc, t.dt asc