在数据分析和运营工作中，SQL是处理统计需求的必备技能。本文通过解析三个中等难度的SQL题目，带你掌握时间范围统计、比率计算、窗口函数等实用技巧。文末附完整代码，建议收藏练习！

题目一：统计视频类别的转发率

需求场景

运营同学需要分析最近30天内，每类视频的转发量和转发率（转发量/播放量），按转发率排序，用于优化内容策略。

数据表结构

1. 用户互动表 k5_user_video_log
   • 关键字段：视频ID、开始时间、是否转发(if_retweet)
2. 视频信息表 k6_video_info
   • 关键字段：视频ID、分类标签(tag)

解题思路

1. 时间过滤：筛选最近30天的互动记录（含当天）。
2. 关联分类标签：通过JOIN关联视频分类。
3. 聚合计算：按分类统计总转发量和播放量，计算转发率。

核心代码

SELECT
    tag,
    SUM(if_retweet) AS retweet_count,
    ROUND(SUM(if_retweet) / COUNT(*), 3) AS retweet_rate
FROM (
    SELECT *
    FROM k5_user_video_log
    WHERE start_time >= DATE_SUB('2021-10-01', INTERVAL 29 DAY)
) AS log
JOIN k6_video_info AS info ON log.video_id = info.video_id
GROUP BY tag
ORDER BY retweet_rate DESC;

代码解释

• DATE_SUB('2021-10-01', INTERVAL 29 DAY)：动态计算30天时间范围起点。
• SUM(if_retweet)：统计转发量，COUNT(*)统计播放次数。

示例结果

题目二：计算创作者月度涨粉率及总粉丝量

需求场景

分析2021年各创作者每月的涨粉率（(加粉-掉粉)/播放量）和截止当月的总粉丝量，用于评估创作者运营效果。

关键字段

• if_follow：1（关注）、2（取消关注）、0（无变化）。

核心逻辑

1. 标记加粉/掉粉：使用CASE语句统计每次播放的粉丝变化。
2. 按月聚合：计算每月的净增粉丝和播放量。
3. 累计粉丝量：通过窗口函数累加历史月份数据。

代码实现

SELECT
    author,
    month,
    ROUND((SUM(add_fans) - SUM(lose_fans)) / COUNT(*), 3) AS fans_growth_rate,
    SUM(SUM(add_fans - lose_fans)) OVER (PARTITION BY author ORDER BY month) AS total_fans
FROM (
    SELECT
        info.author,
        DATE_FORMAT(log.start_time, '%Y-%m') AS month,
        CASE WHEN log.if_follow = 1 THEN 1 ELSE 0 END AS add_fans,
        CASE WHEN log.if_follow = 2 THEN 1 ELSE 0 END AS lose_fans
    FROM k7_user_video_log AS log
    JOIN k8_video_info AS info ON log.video_id = info.video_id
    WHERE YEAR(log.start_time) = 2021
) AS tmp
GROUP BY author, month;

结果示例

题目三：计算近一个月发布视频的热度TOP3

复杂公式拆解

热度 = (100×完播率 + 5×点赞 + 3×评论 + 2×转发) × 新鲜度

新鲜度 = 1 / (最近无播放天数 + 1)

实现步骤

1. 完播率计算：播放时长 ≥ 视频时长即完播。
2. 互动指标统计：聚合点赞、评论、转发次数。
3. 新鲜度计算：根据最后播放日期确定无播放天数。

完整代码（节选）

-- 完播率计算
SELECT video_id,
       SUM(IF(play_time >= duration, 1, 0)) / COUNT(*) AS completion_rate
FROM (
    SELECT log.video_id,
           TIMESTAMPDIFF(SECOND, start_time, end_time) AS play_time,
           info.duration
    FROM k11_user_video_log AS log
    JOIN k12_video_info AS info ON log.video_id = info.video_id
) AS t GROUP BY video_id;

-- 新鲜度计算
SELECT video_id,
       1 / (DATEDIFF(MAX(end_time), CURRENT_DATE) + 1) AS freshness
FROM k11_user_video_log
GROUP BY video_id;

热度结果示例

总结

• 时间处理：注意区间闭端点和动态日期计算（如DATE_SUB）。
• 比率计算：避免除零错误，使用ROUND控制精度。
• 窗口函数：累加统计（如总粉丝量）推荐使用SUM() OVER。

掌握这些技巧，轻松应对复杂数据分析需求！如需完整代码或进一步讨论，欢迎留言交流。