假如要从听歌流水中找到18-25岁用户当年每个月播放次数TOP3的歌曲信息,该如何查询呢?可以先看一下下边给出的表数据,再做分析。
1、数据准备
这里有三张表,,分别为听歌记录、歌曲信息、用户信息,具体数据如下。
listen_record (fdate, user_id, song_id)
song_info (song_id, song_name, singer_name)
user_info (user_id ,age)
2、提取月份
首先,因为要计算每月歌曲播放量的top3,需要使用TO_CHAR函数提取月份数据;又要求用户年龄在18-25岁,因而需要加上筛选。
with
t1 as(
select
to_char(t.fdate,'MM') mon,--月份
t.user_id user_id,
t.song_id song_id
from listen_record t
join user_info a on a.user_id=t.user_id
where a.age>=18 and a.age<=25
)
select * from t1
3、根据月份和歌曲进行分组统计
要计算每个月每首歌的排名,就要计算每个月每首歌的播放量,使用group by分组函数对每个月的各首歌进行分组统计,再计算每组记录数,就是该歌在本月的播放量。
with
t1 as(
select
to_char(t.fdate,'MM') mon,--月份
t.user_id user_id,
t.song_id song_id
from listen_record t
join user_info a on a.user_id=t.user_id
where a.age>=18 and a.age<=25
),
t2 as(
select
mon,
song_id,
count(*) total
from t1 group by mon,song_id
)
select * from t2
4、以月份分组计算序列值
由步骤三我们已经得出每月每首歌的播放总次数,接下来需要以月份分组,并根据分组内部歌曲的播放次数(total字段值)降序、歌曲id(song_id字段值)升序的方式给出序列值。这里对于相同播放次数的歌曲是根据其id进行再次排序的,也可以把相同次数的歌曲给出相同的序列值,请自行思考如何实现。
with
t1 as(
select
to_char(t.fdate,'MM') mon,--月份
t.user_id user_id,
t.song_id song_id
from listen_record t
join user_info a on a.user_id=t.user_id
where a.age>=18 and a.age<=25
),
t2 as(
select
mon,
song_id,
count(*) total
from t1 group by mon,song_id
),
t3 as(
select
c.mon mon,
row_number() over(partition by c.mon order by c.total desc,d.song_id)as ranking,--播放量相同的歌曲排名由歌曲id先后顺序决定
d.song_name song_name,
c.total total
from t2 c
join song_info d on d.song_id=c.song_id
)
select * from t3;
5、得出每月top3歌曲信息
最后一步是没有什么难度的,只要第四步把序列值得出后,再在这里加筛选就可以了。总体上没有什么难度,只要学会使用row_number函数,一步一步查询就OK了。
with
t1 as(
select
to_char(t.fdate,'MM') mon,--月份
t.user_id user_id,
t.song_id song_id
from listen_record t
join user_info a on a.user_id=t.user_id
where a.age>=18 and a.age<=25
),
t2 as(
select
mon,
song_id,
count(*) total
from t1 group by mon,song_id
),
t3 as(
select
c.mon mon,
row_number() over(partition by c.mon order by c.total desc,d.song_id)as ranking,--播放量相同的歌曲排名由歌曲id先后顺序决定
d.song_name song_name,
c.total total
from t2 c
join song_info d on d.song_id=c.song_id
),
t4 as(
select
mon 月份,
ranking 排名,
song_name 歌名,
total 播放总量
from t3 where ranking<=3
)
select * from t4;
这里给出的查询是在oracle下实现的,主要是用了row_nubmer、group by。对于相同播放量的歌曲排序有多种策略,比如相同次数的歌曲可以给相同的序列号,需要使用rank()、dense_rank()函数,根据具体问题需要具体分析,在理解后本题后可以自己多尝试尝试。
如果有更好的查询方式,也可以在下方给出你的意见,共同探讨。