例题
有三张表,分别是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