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

SELECT      -- 最外层查询，获取清洗完成后的数据
    cid,round(pv,3),rk
FROM(       -- 第二层查询，使用窗口函数对每个视频按照重复播放次数进行排序
    SELECT cid,pv,
     ROW_NUMBER() OVER(ORDER BY pv DESC,release_date DESC) AS rk
    FROM (
    SELECT      -- 第三层查询，计算每个视频的总共重复播放次数
        p1.cid,c1.release_date,
        SUM(p1.pv) AS pv
    FROM (
        SELECT cid,uid,     -- 第四层查询，计算每个人重复播放每个视频的次数
            CASE WHEN COUNT(*) > 1 THEN COUNT(*) ELSE 0 END AS pv       -- 只看一遍不算重复播放
        FROM play_record_tb
        GROUP BY cid,uid
            ) AS p1
        INNER JOIN course_info_tb AS c1
        ON c1.cid = p1.cid
            GROUP BY p1.cid,c1.release_date
                ) AS p2
                ) AS p3
WHERE rk <=3
ORDER BY rk;