您现在的位置是:首页 >技术教程 >数据分析师 ---- SQL强化(3)网站首页技术教程

数据分析师 ---- SQL强化(3)

小鱼干儿♛ 2024-06-16 18:01:02
简介数据分析师 ---- SQL强化(3)

数据分析师 ---- SQL强化(3)

题目:每个月Top3的周杰伦歌曲

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

输入例子:
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);

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, '黑键', '林俊杰');

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)
输出例子:
month|ranking|song_name|play_pv
1|1|明明就|4
1|2|说好的幸福呢|4
1|3|大笨钟|2
2|1|明明就|2
2|2|说好的幸福呢|1
2|3|大笨钟|1
例子说明:
1月被18-25岁用户播放次数最高的三首歌为“明明就”、“说好的幸福呢”、“大笨钟”,“明明就”和“说好的幸福呢”播放次数相同,排名先后由两者的song_id先后顺序决定。2月同理。

表:play_log
在这里插入图片描述
表:song_info
在这里插入图片描述
表:user_info
在这里插入图片描述

题目解析:

关于这样的题我们需要找出需要使用的数据,然后再一步一步的解决问题

多表关联:找出每个月周杰伦每一首歌的的播放量

SELECT MONTH(fdate) `month`,s.song_name,count(*) play_pv
FROM play_log p join user_info u
on p.user_id=u.user_id 
JOIN (SELECT song_id,song_name FROM song_info where singer_name="周杰伦") s
on p.song_id = s.song_id
GROUP BY `month`,s.song_name

生成排名:根据每一首歌的播放量生成每个月中的播放量排名

使用的是窗口函数:ROW_NUMBER()over()

	SELECT `month`,
	ROW_NUMBER() over(PARTITION BY month ORDER BY play_pv DESC) ranking,
	song_name,
	play_pv
	FROM (
			SELECT MONTH(fdate) `month`,s.song_name,count(*) play_pv
			FROM play_log p join user_info u
			on p.user_id=u.user_id 
			JOIN (SELECT song_id,song_name FROM song_info where singer_name="周杰伦") s
			on p.song_id = s.song_id
			GROUP BY `month`,s.song_name
		) tmp
	) tmp2

返回排名前3的数据:使用where筛选条件

完整答案

SELECT *  
FROM (
		SELECT `month`,
		ROW_NUMBER() over(PARTITION BY month ORDER BY play_pv DESC) ranking,
		song_name,
		play_pv
		FROM (
				SELECT MONTH(fdate) `month`,s.song_name,count(*) play_pv
				FROM play_log p join user_info u
				on p.user_id=u.user_id 
				JOIN (SELECT song_id,song_name FROM song_info where singer_name="周杰伦") s
				on p.song_id = s.song_id
				GROUP BY `month`,s.song_name
			) tmp
		) tmp2
where ranking<4
ORDER BY `month`,ranking

答案结果:
在这里插入图片描述

注:这里面有一个’播放次数相同,排名先后由两者的song_id先后顺序决定‘这个问题我暂时没有什么好的解决方案,如果你们有好的解决方案可以分享出来。

风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。