MySQL | 窗口函数

时间：2024-10-01 11:59:37

题1：

从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲

play_log表：

song_info表：

user_info表：

sql代码：

select month,ranking,song_name,play_pv
from (
  select
	  month,
	  row_number() over(partition by month order by play_pv desc,song_id) as ranking,
	  song_id,
	  song_name,
	  play_pv
  from (
	  select
		  month(fdate) as month,
		  a.song_id,
		  max(song_name) as song_name,
		  count(1)     as play_pv
	  from play_log a
	  inner join user_info b on a.user_id  = b.user_id
	  inner join song_info c on a.song_id = c.song_id
	  where year(fdate) = 2022
	  and age between 18 and 25
	  and 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的值。

相关文章