首页 > 试题广场 >

每个创作者每月的涨粉率及截止当前的总粉丝量

[编程题]每个创作者每月的涨粉率及截止当前的总粉丝量

热度指数：167029 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

用户-视频互动表tb_user_video_log

id	uid	video_id	start_time	end_time	if_follow	if_like	if_retweet	comment_id
1	101	2001	2021-09-01 10:00:00	2021-09-01 10:00:20	0	1	1	NULL
2	105	2002	2021-09-10 11:00:00	2021-09-10 11:00:30	1	0	1	NULL
3	101	2001	2021-10-01 10:00:00	2021-10-01 10:00:20	1	1	1	NULL
4	102	2001	2021-10-01 10:00:00	2021-10-01 10:00:15	0	0	1	NULL
5	103	2001	2021-10-01 11:00:50	2021-10-01 11:01:15	1	1	0	1732526
6	106	2002	2021-10-01 10:59:05	2021-10-01 11:00:05	2	0	0	NULL

（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info

id	video_id	author	tag	duration	release_time
1	2001	901	影视	30	2021-01-01 07:00:00
2	2002	901	美食	60	2021-01-01 07:00:00
3	2003	902	旅游	90	2020-01-01 07:00:00
4	2004	902	美女	90	2020-01-01 08:00:00

（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长, release_time-发布时间）

问题：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

注：

涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序。
if_follow-是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注。

输出示例：

示例数据的输出结果如下

author	month	fans_growth_rate	total_fans
901	2021-09	0.500	1
901	2021-10	0.250	2

解释：

示例数据中表tb_user_video_log里只有视频2001和2002的播放记录，都来自创作者901，播放时间在2021年9月和10月；其中9月里加粉量为1，掉粉量为0，播放量为2，因此涨粉率为0.500（保留3位小数）；其中10月里加粉量为2，掉粉量为1，播放量为4，因此涨粉率为0.250，截止当前总粉丝数为2。

示例1

输入

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    video_id INT NOT NULL COMMENT '视频ID',
    start_time datetime COMMENT '开始观看时间',
    end_time datetime COMMENT '结束观看时间',
    if_follow TINYINT COMMENT '是否关注',
    if_like TINYINT COMMENT '是否点赞',
    if_retweet TINYINT COMMENT '是否转发',
    comment_id INT COMMENT '评论ID'
) CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE tb_video_info (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    video_id INT UNIQUE NOT NULL COMMENT '视频ID',
    author INT NOT NULL COMMENT '创作者ID',
    tag VARCHAR(16) NOT NULL COMMENT '类别标签',
    duration INT NOT NULL COMMENT '视频时长(秒数)',
    release_time datetime NOT NULL COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id) VALUES
   (101, 2001, '2021-09-01 10:00:00', '2021-09-01 10:00:20', 0, 1, 1, null)
  ,(105, 2002, '2021-09-10 11:00:00', '2021-09-10 11:00:30', 1, 0, 1, null)
  ,(101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:20', 1, 1, 1, null)
  ,(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:15', 0, 0, 1, null)
  ,(103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:15', 1, 1, 0, 1732526)
  ,(106, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 2, 0, 0, null);

INSERT INTO tb_video_info(video_id, author, tag, duration, release_time) VALUES
   (2001, 901, '影视', 30, '2021-01-01 7:00:00')
  ,(2002, 901, '影视', 60, '2021-01-01 7:00:00')
  ,(2003, 902, '旅游', 90, '2020-01-01 7:00:00')
  ,(2004, 902, '美女', 90, '2020-01-01 8:00:00');

输出

901|2021-09|0.500|1
901|2021-10|0.250|2

算法知识视频讲解

Mysql

不想睡觉zzz

一锅出了，case when 也是yyds

select v.author,date_format(u.start_time,'%Y-%m') month,
round(avg(case when u.if_follow = 1 then 1
         when u.if_follow = 2 then -1
         else 0 end),3) fans_growth_rate,
sum(sum(case when u.if_follow = 1 then 1
         when u.if_follow = 2 then -1
         else 0 end) ) over (partition by v.author order by date_format(u.start_time,'%Y-%m')) fans_total
from tb_user_video_log u 
inner join tb_video_info v on v.video_id=u.video_id
where year(u.start_time)=2021
and year(u.end_time)=2021
group by v.author,month
order by v.author,fans_total

发表于 2021-12-03 10:23:01 回复(32)

Mysql

Tiffany_tang

select author, month,
round(add_fans/counts,3) fans_growth_rate, 
sum(add_fans)over(partition by author order by month) total_fans
from (select author, 
      DATE_FORMAT(start_time,'%Y-%m') month, 
     sum(case when if_follow=2 then -1 else if_follow end) add_fans,
     count(*) counts
     from tb_user_video_log t1
     join tb_video_info t2 
     on t1.video_id=t2.video_id
     where year(start_time)=2021
     group by author, month) a
ORDER BY author, total_fans

发表于 2021-12-01 12:24:24 回复(8)

Mysql

苏打烟花无糖粉

关于很多解法中用聚合函数嵌套作为窗口函数：

1. 窗口函数是基于group by的数据进行聚合，而不是原表数据

2. 第一层先行，在已经group by的表上先进行计算，得出sum = 2

3. 第二层为正式的窗口函数，相当于 sum(第一层的结果) over ()

类比 max(某一列) over ()

4. 为了让窗口函数更符合我们的结果输出，over的字句写上partition by author，order by month

partition让其对于用户进行分类汇总计算，order则表示以月份累加

SELECT 
author,
month,
round(SUM(if_follow_num) / COUNT(*),3),
SUM(SUM(if_follow_num)) OVER (partition by author
                order by month) "total_fans"
FROM (
    SELECT 
    video_id,
    DATE_FORMAT(start_time,"%Y-%m") "month",
    CASE WHEN if_follow = 2 THEN -1
    ELSE if_follow END "if_follow_num"
    FROM tb_user_video_log vl
    WHERE YEAR(start_time) = 2021) as a
INNER JOIN tb_video_info vi
ON a.video_id = vi.video_id
GROUP BY author,month
ORDER BY author,total_fans

编辑于 2022-03-01 12:40:46 回复(4)

Mysql

酸菜鱼土豆大侠

【场景】：截止当前

【分类】：窗口函数、sum

分析思路

难点：

1.截止当前

（1）统计每个用户的播放量、加粉量、掉粉量

[条件]：year(start_time) = 2021
[使用]：group by author,month；count

（2）计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

当月的总粉丝量 = (上个月)(加粉量 - 掉粉量) + 这个月(加粉量 - 掉粉量)

需要用到聚合窗口函数

[使用]：sum(follow_add-follow_sub) over(partition by author order by month)

求解代码

方法一：

with 子句

with
    main as(
        #统计每个用户的播放量、加粉量、掉粉量
        select 
            author,
            mid(start_time,1,7) as month,
            count(start_time) as b,
            count(if(if_follow = 1, 1, null)) as follow_add,
            count(if(if_follow = 2, 1, null)) as follow_sub
        from tb_user_video_log a, tb_video_info b
        where a.video_id = b.video_id
        and year(start_time) = 2021
        group by author,month
    )
#计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量
select 
    author,
    month,
    round((follow_add-follow_sub)/b ,3) as fans_growth_rate,
    sum(follow_add-follow_sub) over(partition by author order by month) as total_fans
from main
order by author,total_fans

方法二：

from子查询

#计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量
#当月的总粉丝量 = (上个月)(加粉量 - 掉粉量) + 这个月(加粉量 - 掉粉量) 
select 
    author,
    month,
    round((follow_add-follow_sub)/b ,3) as fans_growth_rate,
    sum(follow_add-follow_sub) over(partition by author order by month) total_fans
from(
    #统计每个用户的播放量、加粉量、掉粉量
    select 
        author,
        mid(start_time,1,7) as month,
        count(start_time) as b,
        count(if(if_follow = 1, 1, null)) as follow_add,
        count(if(if_follow = 2, 1, null)) as follow_sub
    from tb_user_video_log a, tb_video_info b
    where a.video_id = b.video_id
    and year(start_time) = 2021
    group by author,month
) main
order by author,total_fans

发表于 2022-11-20 16:37:45 回复(1)

Mysql

清水卡布

select author,date_format(start_time,'%Y-%m') month,
round(sum(case when if_follow=2 then -1  #这里注意不能写成case when if_follow=2 then if_follow=-1
      else if_follow
      end)/count(start_time),3) as fans_growth_rate,
sum(          #外面这个sum是把里面的每个月涨跌数全部再加起来，得到总粉丝数。
    sum(case when if_follow = 2 then -1
        else if_follow
        end)  #里面的sum计算的是每个创作者每个月的涨跌粉丝数
    ) over (partition by author order by start_time) as fans_total
from tb_user_video_log as log
left join tb_video_info as info
on log.video_id=info.video_id
where year(start_time)=2021
and year(end_time)=2021
group by author,month
order by author,fans_total

发表于 2021-12-08 15:42:55 回复(3)

Mysql

小黄鸭不愿吃饼

联结
按照作者和月进行分组：group by author,date_format(start_time,"%Y-%m")
涨粉率：
- 加粉和减粉变化：sum(if(if_follow=2,-1,if_follow))
- 播放量：count(1)
截止当月的总粉丝量
- sum(粉丝变化)over(partition by author order by date_format(start_time,"%Y-%m"))
条件：2021年

select
    author,
    date_format(start_time,"%Y-%m") as month,
    round(sum(if(if_follow=2,-1,if_follow))/count(1),3) as fans_growth_rate,
    sum(sum(if(if_follow=2,-1,if_follow))) over (partition by author order by date_format(start_time,"%Y-%m")) as total_fans
from  tb_user_video_log
left join tb_video_info using(video_id)
where year(start_time) = 2021 
group by 1,2
order by 1,4

发表于 2022-08-30 17:23:34 回复(1)

牛客306041386号

select v.author,
date_format(u.start_time,'%Y-%m') month,
round((sum(case when u.if_follow=1 then 1 else 0 end)-sum(case when u.if_follow=2 then 1 else 0 end))/count(*),3) fans_growth_rate ,
sum(sum(case when u.if_follow = 1 then 1
         when u.if_follow = 2 then -1
         else 0 end) ) over (partition by v.author order by date_format(u.start_time,'%Y-%m')) total_fans
from tb_user_video_log u inner join tb_video_info v
on u.video_id = v.video_id
where year(u.start_time) = '2021'
group by v.author,month
order by v.author,total_fans

发表于 2021-12-27 11:10:41 回复(0)

Mysql

有滋有味小熊鞋

with t as (
select author,
    date_format(start_time,"%Y-%m") as month,
    sum(case when if_follow = 1 then 1
         when if_follow = 2 then -1
         else 0 end) add_fans,
    count(*) counts
from tb_user_video_log t1
join tb_video_info t2 
on t1.video_id=t2.video_id
where year(start_time)=2021
group by author,month
)
select author, month,
round(add_fans/counts,3) fans_growth_rate, 
sum(add_fans) over(partition by author order by month) total_fans
from t 
order by author,total_fans;

发表于 2022-06-01 20:56:22 回复(0)

Mysql

冷凡社长

select 
author,date_format(start_time,'%Y-%m') as month,
round((sum(case when if_follow =1 then 1 else 0 end) -
 sum(case when if_follow =2 then 1 else 0 end))/count(*),3) as fans_growth_rate,
 sum((sum(case when if_follow =1 then 1 else 0 end) -
 sum(case when if_follow =2 then 1 else 0 end))) 
 over(partition by author order by date_format(start_time,'%Y-%m') ) as total_fans
from tb_video_info join tb_user_video_log using(video_id)
where year(start_time) = 2021
group by author,date_format(start_time,'%Y-%m')
order by author,total_fans

重点是日期格式的准备
另外就是大量 case when 的计算

聚合窗口函数的使用，加入orderby后具有累计计算的能力。

[[分组计算]]

发表于 2022-08-24 17:50:08 回复(0)

Mysql

sky0131

with aa as(select
author,
        if_follow,
DATE_FORMAT(start_time,"%Y-%m") as month,
sum(case when if_follow = 0 then 0
when if_follow = 1 then 1
else -1 end )
                over (partition by author order by month(start_time)) total_fans
from
tb_user_video_log a
inner join
tb_video_info b
on
a.video_id = b.video_id
where year(start_time) = 2021)
select
    author,
    month,
    round(sum(case when if_follow = 0 then 0
when if_follow = 1 then 1
else -1 end )/count(*),3) as fans_growth_rate,
    total_fans
from aa
group by author,month,total_fans
order by author,total_fans asc;

发表于 2022-07-22 21:54:59 回复(0)

Mysql

啊坡糍嘚

with new_tab as # 第一步，对原始表数据进行拼接处理成一张新表，
# 其中利用case when 函数将关注设为1，取消关注设为-1，从而方便sum()求和
(select ut.uid,vt.author,ut.if_follow,substring(ut.start_time,1,7) as mth
,(case when ut.if_follow=1 then 1 when ut.if_follow=2 then -1 else 0 end) as new_follow
from tb_user_video_log ut left join tb_video_info vt on ut.video_id=vt.video_id
where year(ut.start_time)='2021')

select distinct author,mth
,round(sum(new_follow)over(partition by author,mth)/count(uid)over(partition by author,mth),3) as fans_growth_rate
,sum(new_follow)over(partition by author order by mth) as total_fans
# 第二步，利用窗口函数解决截止当月的累计粉丝数
from new_tab
order by author,total_fans

发表于 2022-01-01 22:04:34 回复(0)

Matthew_m

这道题没有说最后要用排序，我就没有加排序，提交了好几次就是不通过，看了评论区大家都有加排序，我加了排序才通过，这是怎么回事啊

select tbv.author,date_format(tbu.start_time,'%Y-%m') month,
       round(sum(case when if_follow = 1 then 1
           when if_follow = 2 then -1 else 0 end)/count(1),3) fans_growth_rate,
       sum(sum(case when if_follow = 1 then 1
           when if_follow = 2 then -1 else 0 end)) over(partition by author order by date_format(tbu.start_time,'%Y-%m')) total_fans
from  tb_user_video_log tbu
join tb_video_info tbv on tbu.video_id = tbv.video_id
where year(tbu.start_time) = 2021
group by tbv.author,month
order by tbv.author,total_fans;

发表于 2022-08-10 12:14:13 回复(3)

Mysql

牛客529695852号

与前一题类似，粉丝变化数仅与tuvl.if_follow有关，播放数由count(tuvl.*)可算，分类依据为作者tvi.author和月份left(tuvl.start_time, 7)，两表连接键仍为video_id。

with a as
(select if(tuvl.if_follow=2, -1, tuvl.if_follow) variation, 
    left(tuvl.start_time, 7) month, tvi.author 
from tb_user_video_log tuvl left join tb_video_info tvi 
on tuvl.video_id=tvi.video_id
where left(tuvl.start_time, 4)='2021')

select author, month, round(avg(variation), 3), 
    (select sum(variation) from a b where b.month<=c.month and b.author=c.author) fan_count
from a c
group by author, month
order by author, fan_count

发表于 2022-05-02 17:31:30 回复(0)

想吃火锅的小师弟在发动态

select
    author
    ,month
    ,fans_growth_rate
    ,sum(add_fans) over(partition by author order by month) total_fans
from(
    select
        author
        ,date_format(start_time,'%Y-%m') month
        ,round((sum(if_follow = 1) - sum(if_follow = 2)) / count(*),3) fans_growth_rate
        ,sum(if_follow = 1) - sum(if_follow = 2) add_fans

    from
        tb_user_video_log t1
    inner join tb_video_info t2
    on t1.video_id = t2.video_id
    where year(start_time) = 2021
    group by author,month
)t3
order by author,total_fans

发表于 2022-01-26 14:02:11 回复(0)

牛客596714141号

select t2.author,substring(t1.start_time,1,7) as 'month',
round(sum(if(t1.if_follow=1,1,if(t1.if_follow=2,-1,0)))/count(t1.if_follow),3) as fans_growth_rate,
sum(SUM(IF(if_follow = 2, -1, if_follow))) over(PARTITION BY author order by month(t1.start_time)) as total_fans
from tb_user_video_log t1 left join tb_video_info t2
on t1.video_id = t2.video_id
where year(t1.start_time)=2021
group by author,month(t1.start_time)
order by author,total_fans;

发表于 2021-12-04 16:31:47 回复(0)

Mysql

DonQ

sum 计算 total_fans 不会了:

select 
	t.author,
	t.`month`,
	round(t.new_fans / t.play_num, 3) `fans_growth_rate`,
	sum(new_fans) over (PARTITION by author order by t.`month`) `total_fans`
from (
	select 
		vi.author `author`, 
		DATE_FORMAT(start_time, '%Y-%m') `month`,
		SUM(
			CASE if_follow
			WHEN 2 THEN
				-1
			WHEN 1 THEN 
				1
			ELSE
				0
			END
		) `new_fans`,
		count(*) `play_num`
	from tb_user_video_log vl
	inner join tb_video_info vi
	on vl.video_id = vi.video_id
	where year(vl.start_time) = 2021
	GROUP BY `author`, `month`
	order by `month`
) t
order by `author`, `total_fans`;

发表于 2021-11-30 22:16:24 回复(0)

Yosangta

select
    author,
    date_format(end_time,'%Y-%m') month,
    round(avg(case when if_follow = 2 then -1 else if_follow end *1.0),3) fans_growth_rate,
    sum(sum(case when if_follow = 2 then -1 else if_follow end)) over(partition by author order by date_format(end_time,'%Y-%m') rows between unbounded preceding and
    current row) total_fans
from
    tb_video_info s1
left join
    tb_user_video_log s2
using(video_id)
where 
    year(end_time) = 2021
and 
    s2.id is not null
group by 
1,2
order by 
1,2

凭啥本地能过，牛客上过不了，rinima浪费我时间吧

发表于 2025-06-19 11:09:51 回复(0)

爱刷美剧的柯基在许愿

select author,left(start_time,7)
,round(avg(if(if_follow=2,-1,if_follow)),3)
,sum(sum(if(if_follow=2,-1,if_follow)))over(partition by author order by left(start_time,7)) 
from tb_video_info
join tb_user_video_log using(video_id)
where year(start_time)=2021
group by 1,2
order by 1,4;

发表于 2024-08-01 20:43:29 回复(0)

罗海燕

select author

,month

,fans_growth_rate

,sum(add_fan) over (partition by author order by author, month) as total_fans from

(select author

,date_format(start_time,'%Y-%m') as month

,round((sum(if(if_follow=2,-1,if_follow))/count(uid)),3) as fans_growth_rate

,sum(if(if_follow= 2,-1,if_follow)) as add_fan

from tb_user_video_log as tu left join tb_video_info as tv on tu.video_id=tv.video_id

where year(start_time) =2021

group by author,month)T

order by author ,total_fans

发表于 2024-02-29 15:08:19 回复(0)

牛客563312411号

select author,
date_format(start_time,'%Y-%m') month, 
round(avg(if(if_follow<2,if_follow,-1)),3) fans_growth_rate,
sum(sum(if(if_follow<2,if_follow,-1))) over(partition by author order by date_format(start_time,'%Y-%m')) total_fans 
from tb_user_video_log 
join tb_video_info 
using(video_id)
where year(start_time)='2021'
group by author,date_format(start_time,'%Y-%m') 
order by author,total_fans

select
author,
month,
round(add_fans/video_play,3) fans_growth_rate, 
sum(add_fans)over(partition by author order by month) total_fans
from (select author, 
      date_format(start_time,'%Y-%m') month, 
      sum(if(if_follow=2 ,-1,if_follow)) add_fans,
      count(1) video_play
      from tb_user_video_log 
      join tb_video_info 
      using(video_id)
      where year(start_time)=2021
      group by 1, 2) a
order by 1, 4

with a as (select 
           author,
           date_format(start_time,'%Y-%m') month,
           sum(if(if_follow=2,-1,if_follow)) add_fans,
           count(1) video_play
           from tb_user_video_log
           join tb_video_info 
           using(video_id)
          where year(start_time)=2021
          group by 1,2)
select 
author,
month,
round(add_fans/video_play,3) fans_growth_rate,
sum(add_fans) over(partition by author order by month) total_fans
from a
order by 1,4

编辑于 2024-02-01 18:02:52 回复(0)