ORACLE查询每月播放量TOP3歌曲

假如要从听歌流水中找到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()函数，根据具体问题需要具体分析，在理解后本题后可以自己多尝试尝试。
如果有更好的查询方式，也可以在下方给出你的意见，共同探讨。