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

select
    classification_tb.video_id,
    FORMAT (count(judge) / count(*), 3) as avg_comp_play_rate
from
    (
        select
            watch_tb.video_id,
            (
                case
                    when watch_tb.duration <= watch_tb.watch_duration then 1
                    else null
                end
            ) as judge
        from
            (
                select
                    uvl.video_id,
                    vi.duration,
                    (uvl.end_time - uvl.start_time) as watch_duration
                from
                    tb_user_video_log as uvl
                    inner join tb_video_info as vi on uvl.video_id = vi.video_id
                    and year (uvl.start_time) = '2021'
                    and year (uvl.end_time) = '2021'
            ) as watch_tb
    ) as classification_tb
group by
    classification_tb.video_id
order by
    avg_comp_play_rate desc

1. count(*)会统计包括null的行，而count(column)不会统计包括null的行，利用这个来计算比率
2. year要注意，tb_video_info这个表不要求是2021年，因为这个是视频的信息表
3. 我的思路：
1. 针对第一个表：计算每一行的实际观看时长；然后和第二个表进行连接，形成一个新表1返回
2. 针对新表1，使用case语句将实际观看时长>=视频时长的行筛选出来，并赋值为1，形成一个新表2
3. 针对新表2，使用count进行计算