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: 定义窗口帧的进一步细分，用于指定在分区内的哪些行应该包括在窗口函数的计算中。它不是一个必须的部分，但在某些复杂的查询中非常有用。

窗口函数主要分为以下几类：

1. 排名函数（Ranking Functions）：
   • ROW_NUMBER()：为分区内的每一行分配一个唯一的序号，基于ORDER BY子句中指定的顺序。
   • RANK()：为分区内的每一行分配一个排名，如果两行或多行具有相同的排序值，则它们会被赋予相同的排名，并且排名之间的“间隔”会被跳过。
   • DENSE_RANK()：类似于RANK()，但它不会在排名之间留下间隔。如果两行或多行具有相同的排序值，则它们会被赋予相同的排名，但下一个排名将是紧接着的数字。
   • NTILE(n)：将分区内的行分配到指定的n个大致相等的排名组（或“桶”）中。
2. 分析函数（Analytic Functions）：
   这些函数通常用于计算分区内的聚合值，但与普通的聚合函数（如SUM()、AVG()）不同，它们会为结果集中的每一行返回一个值，而不是为整个分区返回单个值。
   • SUM()、AVG()、MIN()、MAX()、COUNT()等聚合函数在OVER()子句中使用时，就变成了分析函数。
   • 这些函数可以计算如“到当前行为止的累计总和”、“分区内的平均值”等。
3. 偏移函数（Offset Functions）：
   • LEAD(column, offset, default)：返回当前行之后的第offset行中column的值。如果当前行之后的行数不足offset，则返回default（如果提供了的话），否则返回NULL。
   • LAG(column, offset, default)：与LEAD相反，它返回当前行之前的第offset行中column的值。