题解 | #近一个月发布的视频中热度最高的top3视频#

-- 1、找到近一个月发布的视频。 先计算出当天时间，再通过datediff 计算差值 小于等于29，避免了2次范围限定。但是在大数据环境下不可取，无法通过分区字段快速查找数据。
-- 2、计算热度。  热度是针对某个视频而言，应该聚合来算
-- 3、 优化完播率。 使用avg 计算比率，使用timestampdiff 计算间隔
-- 4、date使用。 如果使用了子查询，应该多加个扩号包裹起来。


SELECT
	video_id,
	round(
		(
			100 * watch_avg + 5 * like_cnt + 3 * comm_cnt + 2 * retweet_cnt
		) / (fresh_cnt + 1)
	) AS hot
FROM
	(
		SELECT
			t1.video_id,
			avg(
				TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration
			) AS watch_avg,
			sum(if_like) AS like_cnt,
			sum(if_retweet) AS retweet_cnt,
			DATEDIFF(
				(
					SELECT
						DATE(max(end_time))
					FROM
						tb_user_video_log
				),
				date(max(end_time))
			) AS fresh_cnt,
			count(comment_id) AS comm_cnt
		FROM
			tb_user_video_log t1
		LEFT JOIN tb_video_info t2 USING (video_id)
		WHERE
			DATEDIFF(
				date(
					(
						SELECT
							max(end_time)
						FROM
							tb_user_video_log
					)
				),
				date(release_time)
			) <= 29
		GROUP BY
			t1.video_id
	) t3
ORDER BY
	hot DESC
LIMIT 3