MySQL | 窗口函数
题1：
从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲
play_log表：
song_info表：
user_info表：
sql代码：
select month,ranking,song_name,play_pv
from (selectmonth,row_number() over(partition by month order by play_pv desc,song_id) as ranking,song_id,song_name,play_pvfrom (selectmonth(fdate) as month,a.song_id,max(song_name) as song_name,count(1) as play_pvfrom play_log ainner join user_info b on a.user_id = b.user_idinner join song_info c on a.song_id = c.song_idwhere year(fdate) = 2022and age between 18 and 25and singer_name = '周杰伦'group by month(fdate), a.song_id) a) a
where ranking<=3
order by month, ranking
窗口函数
窗口函数的基本语法如下：
<窗口函数> OVER ( [PARTITION BY <列名>] [ORDER BY <列名> [ASC|DESC]] [frame_clause]
)- PARTITION BY: 可选，用于指定窗口函数的分区列。如果没有指定，则整个结果集被视为一个单一的分区。
- ORDER BY: 可选，但通常与需要顺序的窗口函数（如
ROW_NUMBER(),LEAD(),LAG()）一起使用，以定义分区内的行顺序。 - frame_clause: 定义窗口帧的进一步细分，用于指定在分区内的哪些行应该包括在窗口函数的计算中。它不是一个必须的部分，但在某些复杂的查询中非常有用。
窗口函数主要分为以下几类：
- 排名函数（Ranking Functions）：
ROW_NUMBER()：为分区内的每一行分配一个唯一的序号，基于ORDER BY子句中指定的顺序。RANK()：为分区内的每一行分配一个排名，如果两行或多行具有相同的排序值，则它们会被赋予相同的排名，并且排名之间的“间隔”会被跳过。DENSE_RANK()：类似于RANK()，但它不会在排名之间留下间隔。如果两行或多行具有相同的排序值，则它们会被赋予相同的排名，但下一个排名将是紧接着的数字。NTILE(n)：将分区内的行分配到指定的n个大致相等的排名组（或“桶”）中。
- 分析函数（Analytic Functions）：
这些函数通常用于计算分区内的聚合值，但与普通的聚合函数（如SUM()、AVG()）不同，它们会为结果集中的每一行返回一个值，而不是为整个分区返回单个值。SUM()、AVG()、MIN()、MAX()、COUNT()等聚合函数在OVER()子句中使用时，就变成了分析函数。- 这些函数可以计算如“到当前行为止的累计总和”、“分区内的平均值”等。
- 偏移函数（Offset Functions）：
LEAD(column, offset, default)：返回当前行之后的第offset行中column的值。如果当前行之后的行数不足offset，则返回default（如果提供了的话），否则返回NULL。LAG(column, offset, default)：与LEAD相反，它返回当前行之前的第offset行中column的值。