题解 | 每类视频近一个月的转发量/率

/*
思路：
要求输出：视频类型、转发量、转发率。保留三位小数
时间窗口：最近一个月内。
现有数据：用户互动表、短视频信息表。
步骤：
    ①清洗出时间窗口内的数据。
        -先用DATE_SUB(MAX(start_time), INTERVAL 29 DAY) 计算出基准日期范围（我们最早要看哪一天的记录）。
        -用CROSS JOIN 把基准日期连接到每一条记录后，再用WHERE过滤掉基准日期范围外的记录。
    ②计算指标：转发量和转发量率。
        -关联短视频信息表，因为需要用短视频信息表中的tag进行分组。
        -使用聚合函数计算转发量和转发率。
*/

WITH
sj AS (             -- 提取基准日期
    SELECT
        DATE_SUB(MAX(DATE(start_time)),INTERVAL 29 DAY) AS md       -- 得出最新日期的30天前是哪一天
    FROM tb_user_video_log
),

qx AS (             -- 清洗得出30天内的数据
    SELECT
        video_id,if_retweet
    FROM tb_user_video_log AS u
    CROSS JOIN sj  
    WHERE DATE(u.start_time) >= sj.md       -- 选定在30天基准内的记录
)

SELECT              -- 主查询，同时计算指标
    v.tag,
    SUM(CASE WHEN if_retweet = '1' THEN 1 ELSE 0 END) AS 转发量,        -- 转发的记作1，0或者其他奇怪的东西都记作0，最后相加得出转发量
    ROUND(SUM(CASE WHEN if_retweet = '1' THEN 1 ELSE 0 END) / COUNT(*),3) AS 转发率     -- 类别的转发量/类别的观看数=类别的转发率
FROM qx
INNER JOIN tb_video_info AS v
    ON (v.video_id = qx.video_id)
GROUP BY v.tag
ORDER BY 转发率 DESC;