一文会用MySQL的窗口函数 1 窗口函数 1.1 什么是窗口函数 窗口函数，也叫OLAP函数（Online Analytical Processing，联机分析处理），可以对数据库进行实时分析处理，窗口函数的基本语法如下：<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>) <窗口函数>的位置，可以放以下两种函数： 1> 专用窗口函数，包括：rank，dense_rank，row_number等； 2> 聚合函数，如sum，avg，count，max，min等。 因为窗口函数是对where或者group by子句处理后的结果进行操作，原则上窗口函数只能出现在select子句中。 1.2 窗口函数作用 在数据库应用中，经常会遇到分组排名的数据分析需求，例如下面的业务需求： 1> 排名问题：每个部门按业绩来排名； 2> topN问题：找出每个部门排名前N的员工信息； 对于这类需求，使用窗口函数是非常便捷的。 2 数据准备 2.1 表结构 如下有一个学生课程成绩表： 订单表如下： 2.2 测试数据 向学生表里面插入以下测试数据： 向订单表里面插入以下测试数据： 3 函数详解 3.1 序号函数 1> rank()：返回数据集中每个值的排名，排名是根据当前行之前的行数加1，不包含当前行，该函数排序的关联值可能产生顺序上的空隙。例如，查看各科成绩的排名信息，如下： 结果如下图所示：
如上图，顺序间隙是指在出现相同分数时，相同分数的排名相同，但是下一个名次的计数会越过排名相同造成的数量。 2> dense_rank()：返回一组数值中每个数值的排名，该函数排序时不会产生顺序上的空隙。如上例换成使用dense_rank()，如下： 结果如下图所示：
排名之间是没有数据间隔的。 3> row_number()：为每行数据返回一个唯一的顺序的行号，从1开始，根据行在窗口分区内的顺序。如下： 结果如下图所示：
3.2 分布函数 cume_dist()：表示当前行及小于当前行在窗口分区总行数中的占比。例如在订单数据中，分别统计每一天的累计订单总数占历史订单的百分比，则可以使用cume_dist()函数，如下分析： 1> 由测试数据可知，订单总数共28个，历史订单累计数量从2024-03-01到2024-03-10分别为：3，4，6，8，10，11，12，17，21，28，我们最终需要得到的是这10个数字与28的比。 2> 先看下cume_dist()的具体表现，使用如下SQL： 结果如下图所示：
因为是全量累计数据的比，所以在窗口函数中不需要按字段值进行分区，直接排序即可，per的列值给出了order_dt的值对应的百分比，所以只需要在该结果集上进行加工，就能累计订单占总订单的百分比，使用如下SQL： 得到结果如下图：
3.3 偏移函数 1> lag()：向上偏移，返回当前字段前n行的数据； 2> lead()：向下偏移，返回当前字段后n行的数据； 偏移函数通常用于取时间间隔、做记录差值、取某数据前后N行等形式的数据处理需求，该函数可接受三个参数，第一个参数是表达式或者字段（即填充的值），第二个参数是偏移量，第三个参数是控制赋值（即当第一个参数按照第二个参数的偏移量无法确定填充值时，按何规则填充）。 查询每一笔订单的前第2笔订单的内容，没有前第2比订单的用汉字“无”填充，如下SQL：
3.4 头尾函数 头尾函数包含first_value()和last_value()，只选择分组排序中的第一条数据和最后一条数据，求每个学生的课程成绩与最高成绩之间的差距，使用SQL如下： 
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/82452.html
