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

# SELECT 
#     author,
#     DATE_FORMAT(lo.start_time,'%Y-%m') AS month,
#     ROUND(
#         (SUM(IF(if_follow = '1',1,0)) - SUM(IF(if_follow = '2',1,0)))/
#         COUNT(*) 
#         , 3)AS fans_growth_rate,
#     SUM(IF(if_follow = '1',1,0))AS total_fans
# FROM tb_user_video_log lo
# LEFT JOIN tb_video_info info ON lo.video_id = info.video_id
# WHERE TIMESTAMPDIFF(day,start_time,end_time) <= 30
# GROUP BY author, DATE_FORMAT(lo.start_time,'%Y-%m')
# ORDER BY author,total_fans ASC

SELECT 
    t.author,
    t.month,
    t.fans_growth_rate,
    SUM(month_fans) OVER 
    (PARTITION BY author ORDER BY month) AS total_fans
FROM
(
    SELECT 
    author,
    DATE_FORMAT(start_time,'%Y-%m') AS month,
    ROUND(
        (SUM(IF(if_follow = '1',1,0)) - SUM(IF(if_follow = '2',1,0))) /
        COUNT(*),3) AS fans_growth_rate,
    SUM(IF(if_follow = 1,1,IF(if_follow = 2,-1,0))) AS month_fans
    FROM tb_user_video_log lo
    LEFT JOIN tb_video_info USING(video_id)
    WHERE YEAR(start_time) = 2021
    GROUP BY author,month
)t
ORDER BY author,total_fans
#GROUP BY和窗口函数，聚类违反ONLY_FULL_GROUP_BY
#出现这个错误是因为在启用了 ONLY_FULL_GROUP_BY 的 SQL 模式下，所有 SELECT 子句中的列都需要在 GROUP BY 中明确指定，除非它们是聚合函数的一部分。尽管 if_follow 在窗口函数中使用，它并没有在 GROUP BY 子句中被聚合。

#要解决这个问题，你需要确保你的 SELECT 中的非聚合表达式与 GROUP BY 子句中的列完全对应。此外，fans_growth_rate 的计算应该在一个单独的子查询中完成，以便能够在主查询中使用窗口函数计算 total_fans。