求各macro_categoria分类下播放量Top4视频的SQL实现方案

嘿，这个需求我之前做项目时刚好碰到过！要实现每个分类下取播放量Top4视频，用窗口函数是最简洁高效的方案，比嵌套子查询或者多次关联要清晰多了。我先基于常见的表结构给你捋一遍思路，你可以根据自己的实际表结构调整细节~

先明确表结构（我先做个合理假设，你可以对应修改） #

• 主视频表（比如叫videos）：至少包含id（视频ID，关联video_logs的idvideo）、macro_categoria（分类字段）
• 播放日志表video_logs：每条记录对应一次播放行为，字段至少有idvideo（关联视频ID）

解决方案：用窗口函数实现分类TopN #

步骤1：计算每个视频的总播放量

先通过关联两张表，统计每个视频的累计播放次数：

SELECT 
    v.id,
    v.macro_categoria,
    COUNT(vl.id) AS total_plays  -- 如果你的video_logs里有单独的播放数字段（比如play_count），就换成SUM(vl.play_count)
FROM videos v
JOIN video_logs vl ON v.id = vl.idvideo
GROUP BY v.id, v.macro_categoria

步骤2：给每个分类下的视频按播放量排名

用ROW_NUMBER()窗口函数，按分类分组（PARTITION BY macro_categoria），再按播放量降序排序，给每个视频分配一个排名：

WITH video_play_counts AS (
    -- 第一步的统计结果
    SELECT 
        v.id,
        v.macro_categoria,
        COUNT(vl.id) AS total_plays
    FROM videos v
    JOIN video_logs vl ON v.id = vl.idvideo
    GROUP BY v.id, v.macro_categoria
),
ranked_videos AS (
    -- 按分类排名
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY macro_categoria ORDER BY total_plays DESC) AS play_rank
    FROM video_play_counts
)
-- 筛选每个分类下前4的视频
SELECT id, macro_categoria, total_plays
FROM ranked_videos
WHERE play_rank <= 4
ORDER BY macro_categoria, play_rank;

小细节：排名函数的选择 #

如果你希望播放量相同的视频都能入选（比如两个视频都是播放量第一，都算Top4里的），可以把ROW_NUMBER()换成RANK()或者DENSE_RANK()：

• RANK()：并列排名后，下一个排名会跳号（比如两个第1，下一个是第3）
• DENSE_RANK()：并列排名后，下一个排名不会跳号（比如两个第1，下一个是第2）

兼容老版本数据库（比如MySQL 5.x） #

如果你的数据库不支持窗口函数（比如MySQL 5及以下），可以用子查询的方式实现，不过效率会低一些：

SELECT 
    v.id,
    v.macro_categoria,
    COUNT(vl.id) AS total_plays
FROM videos v
JOIN video_logs vl ON v.id = vl.idvideo
WHERE (
    SELECT COUNT(DISTINCT v2.id)
    FROM videos v2
    JOIN video_logs vl2 ON v2.id = vl2.idvideo
    WHERE v2.macro_categoria = v.macro_categoria
      AND (SELECT COUNT(*) FROM video_logs WHERE idvideo = v2.id) >= (SELECT COUNT(*) FROM video_logs WHERE idvideo = v.id)
) <= 4
GROUP BY v.id, v.macro_categoria
ORDER BY v.macro_categoria, total_plays DESC;

如果你的表结构和我假设的不一样（比如字段名、播放量统计逻辑不同），直接调整对应的字段和统计方式就行~

内容的提问来源于stack exchange，提问作者user9025311