题解 | 各个视频的平均完播率

第一种解法：
select t1.video_id as video_id, 
round(
avg(
case when timestampdiff(second,t1.start_time,t1.end_time)
>=t2.duration 
then 1 
else 0 
end),3) as avg_comp_play_rate
from tb_user_video_log t1,tb_video_info t2 
where t1.video_id=t2.video_id
and date_format(t1.start_time,'%Y')=2021
and date_format(t1.end_time,'%Y')=2021 
group by t1.video_id
order by avg_comp_play_rate desc


第二种解法：
select t1.video_id as video_id,
round(count(case when 
timestampdiff(second,t1.start_time,t1.end_time)>=t2.duration
then 0 else null end)/count(*),3) as avg
from tb_user_video_log t1,tb_video_info t2
where t1.video_id=t2.video_id
and year(t1.start_time)=2021 and year(t1.end_time)=2021
group by t1.video_id
order by avg desc
count原理：在server层维护一个cnt变量，从存储引擎层取数据，如果取到null，cnt不变，如果取到不为null的值，无论是什么值（0，1，2，999，666，。。。。）都对cnt加一。

第三种解法：用sum代替count也行

牢记总体执行顺序：from、 where 、group by、 having、select（如果select中有聚合函数，先计算聚合函数，再having过滤） 、distinc 、order by 、limit

先把两张进行连接，并且过滤掉非2021的记录，再按照视频id进行分组，此时一个分组内既有完播的也有未完播的。

执行到select这里的时候已经分组完成了，故avg、sum这些聚合函数都是针对单个分组的。

case when then else end的基本用法：

--case带上某个字段：

case sex

when'1'then'男'

when'2'then'女'

else'其他'end

--case也可以不带任何字段：

case when sex = '1'then'男'

when sex = '2'then'女'

else'其他'end

我们这里不需要带上任何字段，完播就取1，未完播就取0，然后取avg,最后通过round取3位小数点：

round(

avg(

case when timestampdiff(second,t1.start_time,t1.end_time)

>=t2.duration

then 1

else 0

end)

,3)