如何按年份统计播放量总和并筛选各年度TOP5艺人？

原代码的问题分析 #

• 子查询中使用SUM(play_count) OVER(PARTITION BY artist)会计算艺人所有年份的总播放量，而非按年份统计，不符合需求。
• 排名窗口的ORDER BY未指定降序，导致排名是从低到高排列。
• 缺少筛选TOP5结果的逻辑。

修正后的SQL代码 #

SELECT ranks, artist, year_played, total_plays
FROM (
    SELECT 
        artist,
        year_played,
        SUM(play_count) AS total_plays,
        RANK() OVER(PARTITION BY year_played ORDER BY SUM(play_count) DESC) AS ranks
    FROM daily_listens
    GROUP BY artist, year_played
) AS ranked_artists
WHERE ranks <= 5
ORDER BY year_played, ranks;

代码说明 #

1. 内层子查询：

   • 用GROUP BY artist, year_played按艺人+年份分组，确保统计的是每个艺人单年度的播放量总和。
   • SUM(play_count)计算该艺人当年的总播放量，命名为total_plays。
   • RANK() OVER(PARTITION BY year_played ORDER BY SUM(play_count) DESC)按年份分区，对每个年份内的艺人按播放量降序排名。

2. 外层查询：

   • 筛选ranks <= 5，只保留每个年份的TOP5艺人。
   • 最终按年份和排名排序，结果更规整。

可选调整：处理并列排名 #

如果需要并列名次也保留（比如同一年有多个艺人并列第5，都显示），可以将RANK()替换为DENSE_RANK()：

SELECT ranks, artist, year_played, total_plays
FROM (
    SELECT 
        artist,
        year_played,
        SUM(play_count) AS total_plays,
        DENSE_RANK() OVER(PARTITION BY year_played ORDER BY SUM(play_count) DESC) AS ranks
    FROM daily_listens
    GROUP BY artist, year_played
) AS ranked_artists
WHERE ranks <= 5
ORDER BY year_played, ranks;

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