描述
从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲。
流水表 play_log:
日期 (fdate) | 用户 ID (user_id) | 歌曲 ID (song_id) |
---|---|---|
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 |
歌曲表song_info:
歌曲 ID (song_id) | 歌曲名称 (song_name) | 歌手名称 (singer_name) |
---|---|---|
0 | 明明就 | 周杰伦 |
1 | 说好的幸福呢 | 周杰伦 |
2 | 江南 | 林俊杰 |
3 | 大笨钟 | 周杰伦 |
4 | 黑键 | 林俊杰 |
用户表user_info:
user_id | age |
---|---|
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
输入:
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月同理。
答案
解法1:
-- MySQL
with
t1 as (
select
month(t1.fdate) month,
t2.song_name,
count(*) play_pv
from
play_log t1
left join song_info t2 on t1.song_id=t2.song_id
left join user_info t3 on t1.user_id=t3.user_id
where
year(t1.fdate)=2022
and t3.age between 18 and 25
and t2.singer_name = '周杰伦'
group by
month(t1.fdate),
t2.song_name
),
t2 as (
select *,row_number() over (partition by month order by play_pv desc) ranking from t1
)
select month,ranking,song_name,play_pv from t2 where ranking<=3;
解法2:
-- MySQL
WITH t AS (
SELECT
MONTH(p.fdate) AS `month`
,ROW_NUMBER() OVER(PARTITION BY MONTH(p.fdate) ORDER BY COUNT(s.song_name) DESC, p.song_id ASC) AS ranking
,s.song_name
,COUNT(s.song_name) AS play_pv
FROM
play_log p
LEFT JOIN song_info s ON p.song_id = s.song_id
LEFT JOIN user_info u ON p.user_id = u.user_id
WHERE
YEAR(p.fdate) = 2022 -- 在2022年
AND u.age BETWEEN 18 AND 25 -- 18-25岁用户
AND s.singer_name = '周杰伦' -- 周杰伦的歌曲
GROUP BY MONTH(p.fdate), s.song_name, p.song_id -- 窗口函数中用到song_id,所以分组时要加上
)
SELECT * FROM t WHERE ranking <= 3; -- 每个月播放次数top 3
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*