MySQL高级应用（一）：窗口函数 1 什么是窗口函数 MySQL从8.0开始支持窗口函数，有的也叫分析函数（处理相对复杂的报表统计分析场景），这个功能在大多商业数据库和部分开源数据库中早已支持。 窗口函数：窗口、函数（应用在窗口内的函数）—–窗口类似于窗户，限定一个空间范围 那什么叫窗口呢? 窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，窗口大小都是固定的，这种属于静态窗口；不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。 窗口函数的基本用法如下： 其中，over是关键字，用来指定函数执行的窗口范围，包含三个分析子句：分组(partition by)子句，排序(order by)子句，窗口(rows)子句，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下语法来设置窗口： 知识点总结 sum(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…) avg(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…) A: 需要被加工的字段名称 B: 分组的字段名称 C: 排序的字段名称 D: 计算的行数范围 2 窗口函数应用 一般，我们可以把窗口函数分为两种： 专有窗口函数： rank() dense_rank() row_number() 聚合类窗口函数： 普通场景下，聚合函数往往和group by一起使用，但是窗口环境下，聚合函数也可以应 用进来，那么此时它们就被称之为聚合类窗口函数，属于窗口函数的一种 sum() count() avg() max() min() 窗口函数（专有窗口函数+聚合类窗口函数）和普通场景下的聚合函数也很容易混淆，二者区别如下： 普通场景下的聚合函数是将多条记录聚合为一条（多到一）；窗口函数是每条记录都会执行，有几条记录执行完还是几条（多到多）。 分组(partition by)：记录按照字段进行分组，窗口函数在不同的分组上分别执行。排序(order by)：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整 个表的数据行。 窗口(rows)：就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows子句来创建滑动窗口(rows)。 现有2018～2020某电商平台订单信息表user_trade 表结构如下: 列名释义user_name用户名piece购买数量price价格pay_amount支付金额goods_category商品品类pay_time支付日期 查看前10行数据
——累计计算函数应用、排序函数应用、偏移分析函数应用 2.1 累计计算函数 累计求和：sum() over() 结果如下：
结果如下：
移动平均：avg() over() 结果如下：
最大/小值：max()/min() over() 结果如下；
2.2 排序函数 row_number() over(……) rank() over(……) dense_rank() over(……) 结果如下：
知识点总结 这三个函数的作用都是返回相应规则的排序序号 row_number() over(partition by …A… order by …B… ) rank() over(partition by …A… order by …B… ) dense_rank() over(partition by …A… order by …B… ) A:分组的字段名称 B:排序的字段名称 注意:row_number()、rank() 和dense_rank()紧邻的括号内是不加任何字段名称的。 row_number:它会为查询出来的每一行记录生成一个序号，依次排序且不会重复。 rank&dense_rank:如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样 的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一。 dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。 dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。 在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排 序，有两个第一名时仍然跟着第二名。 ntile(n) over(……) 结果如下：
结果如下：
知识点总结 ntile(n) over(partition by …A… order by …B… ) n:切分的片数 A:分组的字段名称 B:排序的字段名称 ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值 NTILE不支持ROWS BETWEEN。 2.3 偏移分析函数 lag(…) over(……) lead(…) over(……) 结果如下：
结果如下：
知识点总结 Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead) 作为独立的列。 在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。 lag(exp_str,offset,defval) over(partion by ……order by ……) lead(exp_str,offset,defval) over(partion by ……order by ……) exp_str是字段名称。 offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我们所要找的数据行就是表中的第2行(即5-3=2)。offset默认值为1。defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag()函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。补充练习： 结果如下：
结果如下：
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/60887.html
