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

SELECT
    author,
    DATE_FORMAT (tuv.end_time, '%Y-%m') month,
    ROUND(SUM(
        CASE 
        WHEN if_follow=1 THEN 1
        WHEN if_follow=2 THEN -1
        ELSE 0 END
    )/COUNT(tuv.video_id),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 (tuv.end_time, '%Y-%m')) total_fans
FROM
    tb_user_video_log tuv
    LEFT JOIN tb_video_info tvi ON tuv.video_id = tvi.video_id
WHERE YEAR(end_time) = 2021
GROUP BY
    author,
    month
ORDER BY
    author,
    total_fans

1.日期输出格式化

2.case函数

3.计算累积粉丝

4.group by 对select中的列名排序