Mysql高级-初识窗口函数

欢迎大家来到IT世界,在知识的湖畔探索吧!

最近在写一个查询的sql，要求是如果有result=1的数据，则展示；没有的话，按EXPIRE_DAY倒序排序，获取第一条数据。其中PRODIST_SKU_NUM是可能重复的，但是最终只能返回一个。

表结构大概如下：

create table XXX ( ID int auto_increment primary key, CUSTOMER_MANAGE_NUMBER varchar(256) null comment '客户经理工号', CUSTOMER_MANAGE_NAME varchar(256) null comment '客户经理名称', PRODIST_SKU_NUM varchar(64) not null comment '订购实例ID', EXPIRE_DAY varchar(64) not null comment '授权结束时间', RESULT int null comment '授权结果1-通过、2-驳回、3-超时未反馈、4-RAM账号创建失败', STATUS varchar(4) null comment '首次登陆是否确认通过，1-是，0-否，2-确认中,3-已注销' )

欢迎大家来到IT世界,在知识的湖畔探索吧!

窗口函数语法

注意：mysql中窗口函数在mysql8.0以后支持；oracle中从8.1.6以后开始支持。

函数名() over(partition by <要分组的列> order by <要排序的列> rows between <数据范围>)

其中：

• partition by:用于分组（与聚合函数group by不同的地方在于它只是将原始数据进行名次排列，能够返回一个分组中的多条记录，而 group by是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条））
• order by:用于排序
• rows between:限制数据范围

其中，over()里的分组以及排序的执行晚于 where、group by、order by 的执行。

常用函数:

• rank()：每个分区内独立执行。对查询结果进行排序并为每个行分配排名。它可以根据指定的排序列对数据进行排序，并为每个行返回一个唯一的排名值。如果存在并列的情况，RANK()函数会为相同的值分配相同的排名，并在随后的排名中跳过相应的名次。例如，如果有两个并列第一，则前两名的排名都是1，第三个人的排名是31
• dense_rank()：每个分区内独立执行。会为相同的行分配相同的排名，并且下一个排名不会跳过，即如果有两个排名为1的值，接下来的值将会被标记为2，而不是3
• row_number()：每个分区内独立执行。从1开始查询结果集中的每一行分配一个唯一的顺序号（行号）
• 聚合函数
• 偏移函数

语法示例

• 使用ROW_NUMBER() OVER (ORDER BY <列名>)进行排序并显示行编码：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT prodist_sku_num, customer_manage_number, expire_day, result, status, ROW_NUMBER() OVER (ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn FROM XXX WHERE customer_manage_number = ?

欢迎大家来到IT世界,在知识的湖畔探索吧!

• 使用ROW_NUMBER() OVER (PARTITION BY<列名> ORDER BY <列名>)进行分组后排序并显示行编码：

SELECT prodist_sku_num, customer_manage_number, expire_day, result, status, ROW_NUMBER() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn FROM XXX WHERE customer_manage_number = ?

我们可以从以上两个sql的执行结果中看出来PARTITION BY分组的效果。即PARTITION BY会对prodist_sku_num进行分组，然后在组内进行排序，从返回的行编码rn也可以看出来，只会在组内进行排序。

• 使用RANK() OVER (PARTITION BY<列名> ORDER BY <列名>)进行分组后排序并显示行编码：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT prodist_sku_num, customer_manage_number, expire_day, result, status, RANK() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn FROM abs_generl.ecloud_authorization_user WHERE customer_manage_number = ?

可以看出来，当有三条数据时，会对这三条数据进行排名，如果有相同排名的，则跳过相应的名次。

• 使用DENSE_RANK() OVER (PARTITION BY<列名> ORDER BY <列名>)进行分组后排序并显示行编码：

SELECT prodist_sku_num, customer_manage_number, expire_day, result, status, DENSE_RANK() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn FROM abs_generl.ecloud_authorization_user WHERE customer_manage_number = ?

可以看出来，当有三条数据时，会对这三条数据进行排名，如果有相同排名的，则标记相同的名次，并且接下来的值不会被跳过。

需求解决

初步认识到窗口函数后，我们来写一下文章刚开始的那个需求：

首先我们对prodist_sku_num分组并按指定字段排序并为每一行生成一个编码：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT prodist_sku_num, customer_manage_number,expire_day, result, status, ROW_NUMBER() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn FROM XXX WHERE customer_manage_number = #{staffNumber}

然后将上面的查询结果，我们只获取rn=1的即可

SELECT DISTINCT prodist_sku_num,customer_manage_number,expire_day, result, status FROM ( SELECT prodist_sku_num, customer_manage_number,expire_day, result, status, ROW_NUMBER() OVER (PARTITION BY prodist_sku_num ORDER BY CASE WHEN result = 1 THEN 0 ELSE 1 END, expire_day DESC) as rn FROM XXX WHERE customer_manage_number = #{staffNumber} ) subquery WHERE rn = 1;