题解 | #被重复观看次数最多的3个视频#

这道题目要求我们找出那些用户重复观看次数较高的视频，我们要做的事情如下：

1. 确定总体问题

我们需要找出被重复观看次数排名前三的视频，输出这些视频的课程ID、被重复观看次数和排名。

2. 分析关键问题

• 连接表：将course_info_tb和play_record_tb表连接起来，以便获取每个视频的观看记录。
• 计算重复观看次数：对每个视频的观看记录进行计数。
• 计算排名：根据重复观看次数和发布日期计算排名。
• 筛选和排序：筛选出重复观看次数大于1且排名在前三的视频。

3. 解决每个关键问题的代码及讲解

步骤1：连接表

我们使用JOIN将course_info_tb和play_record_tb表连接起来：

from
    course_info_tb c
    join play_record_tb p on c.cid = p.cid

• JOIN play_record_tb p ON c.cid = p.cid：通过课程ID连接两个表，以便获取每个视频的观看记录。

步骤2：计算重复观看次数

我们使用COUNT函数对每个视频的观看记录进行计数，并使用ROUND函数格式化输出：

select
    p.cid,
    round(count(*) * 1.0, 3) as pv

• COUNT(*)：计算每个视频的观看记录数。
• ROUND(count(*) * 1.0, 3)：将观看次数转换为浮点数并保留三位小数。

步骤3：计算排名

我们使用ROW_NUMBER窗口函数根据重复观看次数和发布日期计算排名：

row_number() over (
    order by
        count(*) desc,
        c.release_date desc
) as rk

• ORDER BY count(*) DESC, c.release_date DESC：按观看次数降序和发布日期降序排序。
• ROW_NUMBER() OVER (...)：为每个视频分配一个排名。

步骤4：筛选和排序

我们使用WHERE子句筛选出重复观看次数大于1且排名在前三的视频，并按排名升序排序：

where
    sub.pv > 1 and sub.rk <= 3
order by
    rk asc

• WHERE sub.pv > 1 AND sub.rk <= 3：筛选出符合条件的视频。
• ORDER BY rk ASC：按排名升序排序。

完整代码

select
    sub.cid,
    sub.pv,
    sub.rk
from
    (
        select
            p.cid,
            round(count(*) * 1.0, 3) as pv,
            row_number () over (
                order by
                    count(*) desc,
                    c.release_date desc
            ) as rk
        from
            course_info_tb c
            join play_record_tb p on c.cid = p.cid
        group by
            p.cid,
            p.uid,
            c.release_date
    ) sub
where
    sub.pv > 1 and sub.rk <=3
order by
    rk asc;