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

WITH tongji AS (
    SELECT 
        a.uid,
        b.tag,
        a.start_time,
        a.end_time,
        a.if_retweet,
        (SELECT MAX(end_time) FROM tb_user_video_log) AS last_time
    FROM tb_user_video_log AS a 
    LEFT JOIN tb_video_info AS b
        ON a.video_id = b.video_id
)
SELECT    
    tag,
    COUNT(CASE WHEN if_retweet = 1 THEN 1 END) AS retweet_cnt,
    ROUND(AVG(if_retweet), 3) AS retweet_rate
FROM tongji
WHERE end_time BETWEEN DATE_SUB(last_time, INTERVAL 30 DAY) AND last_time 
GROUP BY tag
ORDER BY retweet_rate DESC;

#利用临时表进行连表和求出最后一次互动时间
#利用DTAE_SUB(last_time,INTERVAL 30 DAY) AND 来获取30天前的日期