题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
http://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
因为不知道有row...preceding..这个窗口函数，所以想到了先从原始表中取出自己需要的信息。
- 按日期分组，统计出每天的点赞数，每天的转发量。
- 并且与视频信息表链接，选出tag
#按日期分组
with t as (select tag, date(start_time) dt,
sum(if_like) like_cnt,
sum(if_retweet) retweet_cnt
from tb_user_video_log left join tb_video_info using(video_id)
where year(start_time)=2021
group by tag, date(start_time))
with t as (select tag, date(start_time) dt,
sum(if_like) like_cnt,
sum(if_retweet) retweet_cnt
from tb_user_video_log left join tb_video_info using(video_id)
where year(start_time)=2021
group by tag, date(start_time))
然后从这个虚拟表中选出需要的信息即可
- 条件要求日期在7天之内
- 可以直接写上'2021-10-01'，可以从表格里选出来这个单独的字段
- 按照tag分组
#尝试按照日期给区间计算区间内的7天内点赞总量
select tag, '2021-10-01' d_t,sum(like_cnt), max(retweet_cnt)
from t
where dt between date_sub('2021-10-01', interval 6 day) and '2021-10-01'
group by tag
from t
where dt between date_sub('2021-10-01', interval 6 day) and '2021-10-01'
group by tag
这只是在以国庆节第一天为起始时间的七天内统计出的数据，还有第二三天的，用union all上下拼接
union all
select tag, '2021-10-02' d_t,sum(like_cnt), max(retweet_cnt)
from t
where dt between date_sub('2021-10-02', interval 6 day) and '2021-10-02'
group by tag
from t
where dt between date_sub('2021-10-02', interval 6 day) and '2021-10-02'
group by tag
union all
select tag, '2021-10-03' d_t,sum(like_cnt), max(retweet_cnt)
from t
where dt between date_sub('2021-10-03', interval 6 day) and '2021-10-03'
group by tag
from t
where dt between date_sub('2021-10-03', interval 6 day) and '2021-10-03'
group by tag
不要忘记检查排序
order by tag desc,d_t
