【SQL】数据岗常考题之分类别 topN

  数据研发岗位常考的问题是求 topN。对于这类问题的解法必须烂熟于心。下面通过面试中的一道题目来学习如何求解每个类别的 topN 这类问题。

有两张表：

• music_song： id，name，artistid，playcount
• music_artist：id，name

music_song 的 artistid 是 music_artist 中的 id，求每个歌手播放量 top3 的歌曲的id，名字，播放次数。

with t1 as(
    select 
        ma.id artist_id,
        ma.name artist_name,
        ms.id song_id,
        ms.name song_name,
        ms.playcount playcount,
        row_number() over(partition by ma.id order by ms.playcount desc) rn
    from music_song ms join music_artist ma
    on ms.artistid=ma.id
)
select * from t1
where rn<=3;

music_song 表如下：

music_artist 表如下：

t1 的结果如下图所示：

答案如下：

建表语句在下面给出：

# 建表语句
drop table if exists music_song;
CREATE TABLE music_song (
    id int(11) NOT NULL,
    name varchar(32) NOT NULL,
    artistid int(11) NOT NULL,
    playcount int(11) NOT NULL
);

drop table if exists music_artist;
CREATE TABLE music_artist (
    id int(11) NOT NULL,
    name varchar(32) NOT NULL
);

INSERT INTO music_song VALUES
(1,'song1',1,15),
(2,'song2',1,13),
(3,'song3',1,21),
(4,'song4',1,79),
(5,'song5',2,11),
(6,'song6',2,18),
(7,'song7',2,22),
(8,'song8',2,74);


INSERT INTO music_artist VALUES
(1,'Tony'),
(2,'Mary');

  从这道题我们可以看出，解决分类别 topN 的关键在于，首先得到要求解的字段，然后对该字段进行排序，排序的时候注意按照类别进行 partition，比如这道题中需要统计每个歌手的 top3，所以是partition by music_artist.id，partition 之后再按照要求解的字段进行排序，本题中是对歌手歌曲的播放量进行排序，即 order by ​playcount desc，排序之后再给它赋予一个编号 row_number 便可以得到播放量的排名，最终筛选出前三即可。
  总结一下就是：row_number() over(partition by xxx order by xxx)。
欢迎关注公众号。