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

-- 分别统计国庆前三天的情况再拼接在一起
SELECT *
FROM
	(SELECT 
		t1.tag,
	 -- 添加时间
		'2021-10-01' AS dt,
		SUM(t1.like_cnt) AS sum_like_cnt,
		MAX(t1.retweet_cnt) AS max_retweet_cnt_7d
	FROM
		(SELECT 
			v.tag,
			SUM(u.if_like) AS like_cnt,
			SUM(u.if_retweet) AS retweet_cnt
		FROM tb_user_video_log u
		JOIN tb_video_info v USING(video_id) 
		 -- 把时间框在10-1及前7天的范围内
		WHERE DATEDIFF('2021-10-01 23:59:59', u.start_time) <= 6 AND DATEDIFF('2021-10-01 23:59:59', u.start_time) >= 0
		GROUP BY v.tag, DAY(u.start_time)) AS t1
	GROUP BY t1.tag
	UNION
	SELECT 
		t2.tag,
		'2021-10-02' AS dt,
		SUM(t2.like_cnt) AS sum_like_cnt,
		MAX(t2.retweet_cnt) AS max_retweet_cnt_7d
	FROM
		(SELECT 
			v.tag,
			SUM(u.if_like) AS like_cnt,
			SUM(u.if_retweet) AS retweet_cnt
		FROM tb_user_video_log u
		JOIN tb_video_info v USING(video_id) 
		WHERE DATEDIFF('2021-10-02 23:59:59', u.start_time) <= 6 AND DATEDIFF('2021-10-02 23:59:59', u.start_time) >= 0
		GROUP BY v.tag, DAY(u.start_time)) AS t2
	GROUP BY t2.tag
	UNION
	SELECT 
		t3.tag,
		'2021-10-03' AS dt,
		SUM(t3.like_cnt) AS sum_like_cnt,
		MAX(t3.retweet_cnt) AS max_retweet_cnt_7d
	FROM
		(SELECT 
			v.tag,
			SUM(u.if_like) AS like_cnt,
			SUM(u.if_retweet) AS retweet_cnt
		FROM tb_user_video_log u
		JOIN tb_video_info v USING(video_id) 
		WHERE DATEDIFF('2021-10-03 23:59:59', u.start_time) <= 6 AND DATEDIFF('2021-10-03 23:59:59', u.start_time) >= 0
		GROUP BY v.tag, DAY(u.start_time)) AS t3
	GROUP BY t3.tag) AS t4
ORDER BY t4.tag DESC, t4.dt;