MySQL 窗口函数之 RANK 函数

本文标识 : MQ0016

本文编辑 : 长安月下赏美人儿

编程工具 : MySQL、DBeaver

阅读时长 : 5分钟

1、窗口函数简介

Mysql8.0+ 版本支持窗口函数，该类函数也称为分析函数，对于初学者来说，窗口函数特别容易与分组聚合函数混合。两者的不同之处在于，窗口函数的使用，使得每一行数据都生成一个结果。

（1）应用场景：

• 周环比如何计算？
• 如何把数据分成N组？
• 如何计算某个用户或某个店铺在某段时间内产生的最大/小的订单金额？
• 如何计算各产品的累计售出占比？
• ......

（2）窗口函数分类

• 排序函数：对分析对象进行排序
• 分布函数：对分析对象记录进行比较，类似于统计学中的中位数或四分位数
• 前后函数：对分析对象自身前面/后面一定顺序的数据进行分析
• 头尾函数：分析对象第一/最后值进行分析
• 其他函数：用于给分析对象进行分段

2、排序函数

排序函数，顾名思义就是对数据对象进行排序，但是怎么个排法却有很多小门道。

（1）应用场景

• 用于分析对象记录排序
• 用于对表中不同对象分别排序
• 用于排序之后，取特定需求的数据

（2）排序函数分类及基础语法

rank()over(partition by 分区字段 order by 排序字段 desc/asc);
dense_rank()over(partition by 分区字段 order by 排序字段 desc/asc);
row_number()over (partition by 分区字段 order by 排序字段 desc/asc);

（3）三种排序函数的异同

相同点：均是对分析对象进行排序

差异点：

• rank() 函数，当指定字段数值相同，则会产生相同序号记录，且产生序号间隙
• dense_rank() 函数，当指定字段数值相同，则会产生相同序号记录，且不会产生序号间隙
• row_number() 函数，不区分是否记录相同，产生自然序列

（4）实例比较三种排序函数

SELECT t2.goodsID
      ,t2.dimShopID
      ,SUM(t2.AMT) AS tal_amt
      ,RANK()OVER(PARTITION BY t2.dimShopID ORDER BY SUM(t2.AMT) DESC)AS num1
      ,DENSE_RANK()OVER(PARTITION BY t2.dimShopID ORDER BY SUM(t2.AMT) DESC)AS num2
      ,ROW_NUMBER()OVER(PARTITION BY t2.dimShopID ORDER BY SUM(t2.AMT) DESC)AS num3
FROM fct_sales AS t1
JOIN fct_sales_item AS t2
ON t1.salesID=t2.salesID
WHERE t1.dimDateID BETWEEN 20180808 AND 20180814
      AND t2.dimShopID=34
GROUP BY t2.goodsID
        ,t2.dimShopID
ORDER BY SUM(t2.AMT) DESC;

数据结果：

差异点对比：

• rank() 函数产生的 num1 序列，出现了两个并列的 75 名，其后排名并非 76 名，而是 77 名，验证该函数当指定字段数值相同，则会产生相同序号记录，且产生序号间隙的规则。
• dense_rank() 函数产生的 num2 序列，出现了两个并列的75名，其后序排名为 76 、77 名，验证该函数当指定字段数值相同，则会产生相同序号记录，且不会产生序号间隙的规则。
• row_number() 函数产生的 num3 序列，不区分是否记录相同，产生自然序列。

排序函数小结

• 函数()、over() 是必须存在的，且over() 括号里面的内容是可选的
• over() 用来指定函数执行窗口范围，如果后面括号内无任何内容，则指窗口范围是满足 where 条件所有行
• partition by，指定按照某字段进行分组，窗口函数是在不同的分组分别执行
• order by，指定按照某字段进行排序