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

SELECT
    tag,
    '2021-10-01' AS dt,
    SUM(like_cnt) AS sum_like_cnt_7d,
    MAX(retweet_cnt) AS max_retweet_cnt_td
FROM(
    SELECT
        tag,
        DATE(end_time) AS days,
        SUM(if_like) AS like_cnt,
        SUM(if_retweet) AS retweet_cnt
    FROM tb_user_video_log t1
    JOIN tb_video_info t2 ON t1.video_id = t2.video_id
    GROUP BY tag, days
) t1
WHERE days BETWEEN DATE_ADD('2021-10-01', INTERVAL -6 DAY) AND '2021-10-01'
GROUP BY tag
UNION
SELECT
    tag,
    '2021-10-02' AS dt,
    SUM(like_cnt) AS sum_like_cnt_7d,
    MAX(retweet_cnt) AS max_retweet_cnt_td
FROM(
    SELECT
        tag,
        DATE(end_time) AS days,
        SUM(if_like) AS like_cnt,
        SUM(if_retweet) AS retweet_cnt
    FROM tb_user_video_log t1
    JOIN tb_video_info t2 ON t1.video_id = t2.video_id
    GROUP BY tag, days
) t1
WHERE days BETWEEN DATE_ADD('2021-10-02', INTERVAL -6 DAY) AND '2021-10-02'
GROUP BY tag
UNION
SELECT
    tag,
    '2021-10-03' AS dt,
    SUM(like_cnt) AS sum_like_cnt_7d,
    MAX(retweet_cnt) AS max_retweet_cnt_td
FROM(
    SELECT
        tag,
        DATE(end_time) AS days,
        SUM(if_like) AS like_cnt,
        SUM(if_retweet) AS retweet_cnt
    FROM tb_user_video_log t1
    JOIN tb_video_info t2 ON t1.video_id = t2.video_id
    GROUP BY tag, days
) t1
WHERE days BETWEEN DATE_ADD('2021-10-03', INTERVAL -6 DAY) AND '2021-10-03'
GROUP BY tag
ORDER BY tag DESC

用UNION把三天的记录合并起来，虽然是比较笨的方法，但至少算是个能轻易看懂的办法吧，，