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

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

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

①建表添加数据

USE test2;
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)

 表play_log：

  表song_info：

  表user_info：

②逐步分析：

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

-- 1.多表关联:找出每个月周杰伦每一首歌的的播放量
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

-- 2.生成排名：根据每一首歌的播放量生成每个月中的播放量排名,

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

-- 2.生成排名：根据每一首歌的播放量生成每个月中的播放量排名,
-- 使用的是窗口函数：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.返回排名前3的数据：使用where筛选条件

-- 3.返回排名前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