数据研发岗位常考的问题是求 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)。
欢迎关注公众号。