SQL练习

用户-视频互动表tb_user_video_log

id	uid	video_id	start_time	end_time	if_follow	if_like	if_retweet	comment_id
1	101	2001	2021-10-01  10:00:00	2021-10-01  10:00:30	0	1	1	NULL
2	102	2001	2021-10-01  10:00:00	2021-10-01  10:00:24	0	0	1	NULL
3	103	2001	2021-10-01  11:00:00	2021-10-01  11:00:34	0	1	0	1732526
4	101	2002	2021-09-01  10:00:00	2021-9-01  10:00:42	1	0	1	NULL
5	102	2002	2021-10-01  11:00:00	2021-10-01  10:00:30	1	0	1	NULL

（uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID）

短视频信息表tb_video_info

id	video_id	author	tag	duration	release_time
1	2001	901	影视	30	2021-01-01 07:00:00
2	2002	901	美食	60	2021-01-01 07:00:00
3	2003	902	旅游	90	2021-01-01 07:00:00

（video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长（秒）, release_time-发布时间）

问题：计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序

注：视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。

输出示例：

示例数据的结果如下：

video_id	avg_comp_play_rate
2001	0.667
2002	0.000

解：

select l.video_id,
round
    (
    avg(
        case when timestampdiff(second,start_time,end_time)>=duration
        then 1
        else 0
        end
    )
    ,3)avg_comp_play_rate
from tb_user_video_log l
join tb_video_info i
on l.video_id = i.video_id
where year(start_time) = 2021
group by l.video_id
order by avg_comp_play_rate desc

1. 我们从两个表 tb_user_video_log（包含用户观看视频的日志信息）和 tb_video_info（包含视频信息，包括预期播放时长）中检索数据。

2. 在 SELECT 子句中，我们选择了 video_id 列，并使用 ROUND 函数将平均完整播放率四舍五入到小数点后三位，然后将其命名为 avg_comp_play_rate。

3. 在 AVG 函数内部，我们使用了 CASE WHEN 表达式来计算完整播放率。如果视频的实际播放时长大于或等于预期播放时长，则条件为真，我们返回1，表示视频已经完整播放。否则，条件为假，我们返回0，表示视频没有完整播放。

4. 我们在 WHERE 子句中筛选出 start_time 列中的年份为 2021 的记录，以限制数据集只包含 2021 年的观看日志。

5. 我们使用 GROUP BY 子句将结果按照 video_id 分组，以便计算每个视频的平均完整播放率。

6. 最后，我们使用 ORDER BY 子句将结果按照平均完整播放率降序排列，以便找出哪些视频的完整播放率最高。

知识点：

1.TIMESTAMPDIFF 函数是用于计算两个日期或时间之间的差异的SQL函数。

TIMESTAMPDIFF(unit, start_date, end_date)

• unit：表示要计算的时间单位，可以是以下之一：

  • SECOND：秒
  • MINUTE：分钟
  • HOUR：小时
  • DAY：天
  • WEEK：周
  • MONTH：月
  • QUARTER：季度
  • YEAR：年

• start_date：起始日期或时间。

• end_date：结束日期或时间。

函数返回一个整数，表示两个日期或时间之间的差异值。差异值的单位由 unit 参数确定。

2.在 SQL 中，YEAR 是一个日期和时间函数，用于从日期或日期时间值中提取年份部分。

YEAR(date)
date：要提取年份的日期或日期时间值。

3.

ROUND 是 SQL 中用于对数字进行四舍五入的函数。它可以接受一个或两个参数，具体的用法取决于您想要的精度和舍入方向。

一般的语法如下：

ROUND(number, decimal_places)

• number：要进行四舍五入的数字。
• decimal_places：可选参数，表示要保留的小数位数。如果不提供此参数，ROUND 将对数字进行整数舍入。

在这一题里avg如何用case when完成完播率的计算的?

在 avg 函数内部，使用了 CASE WHEN 表达式，该表达式根据满足条件的行计算平均值。在这种情况下，条件是检查每个视频是否被完全播放，如果是，就将其视为1，否则视为0。