一篇文章搞定MySQL中的窗口函数 在MySQL中，分组group by一般和聚合函数连用，如group by+sum，这样能够得到每个组的总和，但是每个组内部的信息就被压缩掉了，例如，想知道每个组内的排名情况，以前这个问题不太好处理，有了窗口函数，这个问题就变得很简单了。 Part1：窗口函数有什么用 从 MySQL 8.0 开始，MySQL 加入了窗口函数，窗口函数有什么用呢？ 主要用于解决分组排名问题，最典型的就是 TopN 问题，比如每个地区销售额前三名的地区。 有同学可能会有疑问，如果是分组，用 group by 不就行了？的确没错，可是现在要的是分组后每个组内的排名啊。 如果只用 group by，则只能得到每个分组的第一个值，无法展现出每个组内部的情况，或者只能得到每个组的总和或者均值等。 如何更高效地解决这种 TopN 问题呢？这就需要用到窗口函数了。 简单来说，就是对数据进行分组，每个分组内部其实还有很多记录，每个组相当于一个窗口，然后通过一些函数对每个窗口内的数据进行分析处理。 首先，看一个小例子。有以下销售数据表。
上表中，第一列是 id，第二列是销售人员姓名，第三列示订单日期，第四列是销售额，最后一列是地区。 现在我们希望将每个地区的销售人员按照销售额降序排列，并给出排名。 利用窗口函数可以这样写 SQL 代码。 运行结果：
运行结果中最后一列是每个销售人员的排名，按照销售额降序排名。 上面的代码，核心部分为： 这就是窗口函数的基本格式，由于这里要每个组的排名，所以用到了 rank()函数，partition 表示分块、分组，根据哪个字段进行分组，order by 表示排序。 当然，这里的 rank()函数可以替换为其他函数，根据需要实现的功能来定。 Part2：常用的窗口函数 常用的窗口函数还有 row_number, sum/avg/min/max, cume_dist, lag, first_value, last_value, ntile 等，这么多窗口函数，大致分为以下 5 种类型。排名函数：rank（排名）, row_number（行号，相当于排名）。聚合函数：sum（求和），avg（求均值），min（求最小值），max（求最大值）。分布函数：cume_dist（求累积占比）。前后函数：lag（向前取值），lead（向后取值）。头尾函数：first_value（第一个），last_value（最后一个）。 除了这些函数，还有其他一些函数，如 ntile，能够将有序数据分为 n 个组。 下面，我们通过一些具体的案例来说明这些函数的用法。 1、排名函数 前面的例子已经演示了用 rank 函数分组排名，用 row_number 函数也能实现同样的效果，代码如下。 运行结果：
这里本质上的是行号，如果两个数值相同，用 rank 函数的排名就会相同，而 row_number 则不会。 2、聚合函数 例如，我们希望分部门按照订单日期累加销售额，并将结果作为新的一列,这就要用到 sum 函数，SQL 代码如下。 运行结果： 
最后一列就是累加销售额。 3、分布函数 例如，想分地区按照日期统计每天的销售额累积占比，这就要用到 cume_dist 函数，SQL 代码如下。 运行结果：
这里得到的就是销售额的累积占比。 4、前后函数 例如，想分地区按照销售额排序，查询前一名销售员和当前人员的销售额的差值。 解决这个问题可以分为两步。 第一步： 先找出每个销售人员的销售额的前一个数值，这就要用到 lag 函数取当前值的前一个值。 运行结果：
最后一列得到的就是当前值的前一个值。 第二步： 作差，即用当前人员的销售额减去对应的前一个数值。 运行结果：
最后一列就是销售额的差值。 5、头尾函数 例如，我们希望先按照订单日期进行排序，然后分地区查询第 1 个销售人员的销售额。 这就要用到 first_value 函数去每组里的第一个值，SQL 代码如下。 运行结果：
最后一列就是第 1 个销售人员的销售额。 除了上述用法之外，还有其他一些用法，例如，我们希望按照地区将销售额分为 3 个等级。 这就要用到函数，这个函数能够将有序数据分为 n 个等级。 运行结果：
这样就将每个地区的销售额分为 3 个等级。 以上就是 MySQL 中的窗口函数。
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/70735.html
