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

with answer as (
    select
        cid,
        sum(pv) as pv,
        row_number() over(order by sum(pv) desc, release_date desc) as rk
    from (
        select
            c.cid as cid, 
            count(*) as pv,
            release_date
        from
            course_info_tb c
            join play_record_tb p on c.cid = p.cid
        group by c.cid, p.uid, release_date
        having pv > 1
    )as r1
    group by cid, release_date
    order by rk asc
)
select cid, pv, rk from answer where rk <= 3
# 在使用 GROUP BY 时：除了聚合函数中的字段，所有出现在 SELECT 中的字段都必须出现在 GROUP BY 子句中，除非该字段是由 GROUP BY 的字段唯一确定（也就是一一对应）的。

1. 构建r1表。连接course_info_tb与play_record_tb，按c.cid, p.uid, release_date分组，选取出每个视频中每个用户观看的次数，使用having pv > 1筛选出重复观看的视频，此时得到视频id、被不同用户重复观看的次数、发布日期（用于后续排序）。
2. 构建开窗函数answer表。对r1进行分组group by cid, release_date，聚合为每个视频总共被重复观看的次数sum(pv) as pv，并进行排序row_number() over(order by sum(pv) desc, release_date desc) as rk，当次数相等时 ，发布晚的视频在前。最后按rk排名排序。
3. 从开窗函数中取出排名前3的数据。

注意：1.求排名前n的数据，一般都是先构建一张表得到排名rk，再从这张表中使用where取出排名前n的数据；2.在使用 GROUP BY 时：除了聚合函数中的字段，所有出现在 SELECT 中的字段都必须出现在 GROUP BY 子句中，除非该字段是由 GROUP BY 的字段唯一确定（也就是一一对应）的