题解 | 国庆期间每类视频点赞量和转发量

WITH result AS (
    SELECT
        info.tag,
        DATE(log.start_time) AS dt,
        SUM(log.if_like)    AS like_cnt,
        SUM(log.if_retweet) AS retweet_cnt
    FROM tb_user_video_log log
    LEFT JOIN tb_video_info info
        ON log.video_id = info.video_id
    WHERE log.start_time >= '2021-09-25'
      AND log.start_time <  '2021-10-04'
    GROUP BY info.tag, DATE(log.start_time)
)

-- 第一段：9.25-10.01 
SELECT
    tag,
    '2021-10-01' AS dt,
    SUM(like_cnt)    AS like_cnt,
    max(retweet_cnt) AS retweet_cnt
FROM result
WHERE dt <= '2021-10-01'
GROUP BY tag

UNION ALL

-- 第二段：9.26-10.02
SELECT
    tag,
    '2021-10-02' AS dt,
    SUM(like_cnt),
    max(retweet_cnt)
FROM result
WHERE dt <= '2021-10-02' and dt >= '2021-09-26'
GROUP BY tag

UNION ALL

-- 第三段：9.27-10.03 
SELECT
    tag,
    '2021-10-03' AS dt,
    SUM(like_cnt),
    max(retweet_cnt)
FROM result
WHERE dt <= '2021-10-03' and dt >= '2021-09-27'
group by tag

ORDER BY
    tag DESC,
    dt ASC;;