SQL函数之rank()、dense_rank()、row_number()的用法

例题

有三张表，分别是play_log，song_info，user_info，建表及查询语句如下。

drop table if exists play_log;
create table `play_log` (
    `fdate` date,
    `user_id` int,
    `song_id` int
);
insert into play_log(fdate, user_id, song_id)
values 
('2022-01-08', 10000, 0),
('2022-01-16', 10000, 0),
('2022-01-20', 10000, 0),
('2022-01-25', 10000, 0),
('2022-01-02', 10000, 1),
('2022-01-12', 10000, 1),
('2022-01-13', 10000, 1),
('2022-01-14', 10000, 1),
('2022-01-10', 10000, 2),
('2022-01-11', 10000, 3),
('2022-01-16', 10000, 3),
('2022-01-11', 10000, 4),
('2022-01-27', 10000, 4),
('2022-02-05', 10000, 0),
('2022-02-19', 10000, 0),
('2022-02-07', 10000, 1),
('2022-02-27', 10000, 2),
('2022-02-25', 10000, 3),
('2022-02-03', 10000, 4),
('2022-02-16', 10000, 4);
select * from play_log;

drop table if exists song_info;
create table `song_info` (
    `song_id` int,
    `song_name` varchar(255),
    `singer_name` varchar(255)
);
insert into song_info(song_id, song_name, singer_name) 
values
(0, '明明就', '周杰伦'),
(1, '说好的幸福呢', '周杰伦'),
(2, '江南', '林俊杰'),
(3, '大笨钟', '周杰伦'),
(4, '黑键', '林俊杰');
select * from song_info;

drop table if exists user_info;
create table `user_info` (
    `user_id`   int,
    `age`       int
);
insert into user_info(user_id, age) 
values
(10000, 18)
select * from user_info

要求：从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲。

一、rank() 重复不连续

rank() 会按照排序值相同的为一个序号（以下称为：块），第二个不同排序值将显示所有行的递增值，而不是当前序号加1。

二、dense_rank() 重复且连续

dense_rank() 函数，与 rank() 区别在于，第二个不同排序值，是对当前序号值加1。

select month, 
dense_rank() over(partition by month order by play_pv desc) as ranking, 
song_name,play_pv
from 
(select month(pl.fdate) as month,si.song_name,pl.song_id,
count(pl.user_id) as play_pv
from play_log pl
left join song_info si on pl.song_id = si.song_id
left join user_info ui on pl.user_id = ui.user_id
where si.singer_name = '周杰伦' and ui.age between 18 and 25
group by month, si.song_name, pl.song_id
) a
order by month

三、row_number() 连续不重复

row_number()函数可以为每条记录添加递增的顺序数值序号，即使值完全相同也依次递增序号，不会重复。

select month, 
row_number() over(partition by month order by play_pv desc) as ranking, 
song_name,play_pv
from 
(select month(pl.fdate) as month,si.song_name,pl.song_id,
count(pl.user_id) as play_pv
from play_log pl
left join song_info si on pl.song_id = si.song_id
left join user_info ui on pl.user_id = ui.user_id
where si.singer_name = '周杰伦' and ui.age between 18 and 25
group by month, si.song_name, pl.song_id
) a
order by month