题解 | 最受欢迎的top3课程

WITH
t1 AS (     -- 计算出每个课程发布后的7天是哪几天
    SELECT
        a.cid,
        a.start_time,
        a.end_time,
        score,
        DATE_ADD(b.release_date,INTERVAL 6 DAY) AS rq
    FROM play_record_tb AS a
    INNER JOIN course_info_tb AS b
        ON a.cid = b.cid
),

t2 AS (     -- 计算每个课程在发布7天内的播放次数、评价评分和观看总时长
    SELECT
        cid,
        AVG(score) AS pf,
        COUNT(*) AS bfl,
        ROUND(SUM(TIMESTAMPDIFF(MINUTE,start_time,end_time)),3) AS zsc      -- 如果对计算结果精度要求高，可考虑把MINUTE换成SECOND / 60
    FROM t1
    WHERE DATE(start_time) <= rq
    GROUP BY cid
    HAVING pf >= 3
),

t3 AS (     -- 使用窗口函数对每个课程的受欢迎程度进行排名
    SELECT
        cid,bfl,zsc,
        RANK() OVER(ORDER BY bfl DESC,zsc DESC) AS rk
    FROM t2
)

SELECT      -- 按照受欢迎程度显示排名前三的课程相关信息
    cid,
    bfl AS pv,
    zsc AS time_len
FROM t3
WHERE rk <= 3
ORDER BY rk;