SQL159 每个创作者每月的涨粉率及截止当前的总粉丝量
select author, month, round(fans_add_cnt / play_cnt, 3) as fans_growth_rate,
sum(fans_add_cnt) over(partition by author order by `month`) as total_fans
from (
select author,
DATE_FORMAT(start_time, "%Y-%m") as month,
sum(if(if_follow=2, -1, if_follow)) as fans_add_cnt,
count(1) as play_cnt
from tb_user_video_log a
join tb_video_info b on a.video_id=b.video_id
where YEAR(start_time) = 2021
group by author, month
) as t
order by author, total_fans;
窗口函数
窗口函数常用于执行排名、累计求和、移动平均等分析任务。
下面是一些常用的 MySQL 窗口函数:
- ROW_NUMBER(): 返回结果集中每一行的行号。 RANK(): 计算结果集中每一行的排名。
- DENSE_RANK():计算结果集中每一行的稠密排名,相同排名的行会有相同的排名值。 NTILE(): 将结果集分成指定数量的桶,并为每个桶分配一个编号。
- SUM(), AVG(), MIN(), MAX(): 对窗口中的数据进行求和、平均值、最小值和最大值计算。 LAG(),
- LEAD(): 获取当前行前一行或后一行的值
- FIRST_VALUE(), LAST_VALUE(), NTH_VALUE():获取窗口中第一行、最后一行、第 N 行的值。
如何使用窗口函数计算累计和
窗口函数中的 OVER 子句可以定义分区和排序规则,以满足特定需求。省略了分区定义,将计算所有行的累计和。如果需要按不同的分组计算累计和,可以在 OVER 子句中指定 PARTITION BY 子句。
select sum(fan_add_cnt) over (partition by author order by month)