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

select * from(
select
       tag,
       day,
       sum(sum_like_day),
       max(sum_re_day)
from(
select day,
       tag,
       sum(if_like) sum_like_day,
       sum(if_retweet) sum_re_day
from(
select '2021-10-01' day,
       tag,
       if_like,
       if_retweet,
       date
from (
     select
       tag,
       if_like,
       if_retweet,
        date_format(start_time,'%Y-%m-%d') `date`
from tb_user_video_log tuvl
join tb_video_info tvi on  tuvl.video_id=tvi.video_id
         )tmp
where datediff('2021-10-01',date)<=6
and datediff('2021-10-01',date)>=0)t2
group by tag,date
order by  tag desc ,day)t3
group by day, tag
union
select
       tag,
       day,
       sum(sum_like_day),
       max(sum_re_day)
from(
select day,
       tag,
       sum(if_like) sum_like_day,
       sum(if_retweet) sum_re_day
from(
select '2021-10-02' day,
       tag,
       if_like,
       if_retweet,
       date
from (
     select
       tag,
       if_like,
       if_retweet,
        date_format(start_time,'%Y-%m-%d') `date`
from tb_user_video_log tuvl
join tb_video_info tvi on  tuvl.video_id=tvi.video_id
         )tmp
where datediff('2021-10-02',date)<=6
and datediff('2021-10-02',date)>=0)t2
group by tag,date
order by  tag desc ,day)t3
group by day, tag
union
select
       tag,
       day,
       sum(sum_like_day),
       max(sum_re_day)
from(
select day,
       tag,
       sum(if_like) sum_like_day,
       sum(if_retweet) sum_re_day
from(
select '2021-10-03' day,
       tag,
       if_like,
       if_retweet,
       date
from (
     select
       tag,
       if_like,
       if_retweet,
        date_format(start_time,'%Y-%m-%d') `date`
from tb_user_video_log tuvl
join tb_video_info tvi on  tuvl.video_id=tvi.video_id
         )tmp
where datediff('2021-10-03',date)<=6
and datediff('2021-10-03',date)>=0)t2
group by tag,date
order by  tag desc ,day)t3
group by day, tag)t2
order by tag desc ,day