MySQL8.0窗⼝函数ROW_NUMBER（）OVER（）函数的使
⽤
⼀、窗⼝函数的使⽤场景
作为IT⼈⼠，⽇常⼯作中经常会遇到类似这样的需求：
医院看病，怎样知道上次就医距现在的时间？环⽐如何计算？怎么样得到各部门⼯资排名前N名员⼯列表?查各部门每⼈⼯资占部门总⼯资的百分⽐？
对于这样的需求，使⽤传统的SQL实现起来⽐较困难。这类需求都有⼀个共同的特点，需要在单表中满⾜某些条件的记录集内部做⼀些函数操作，不是简单的表连接，也不是简单的聚合可以实现的，通常会让写SQL的同学焦头烂额、绞尽脑汁，费了⼤半天时间写出来⼀堆长长的晦涩难懂的⾃连接SQL，且性能低下，难以维护。
要解决此类问题，最⽅便的就是使⽤窗⼝函数。
⼆、MySQL窗⼝函数简介
五险一金指的是哪五险哪一金MySQL从8.0开始⽀持窗⼝函数，这个功能在⼤多商业数据库和部分开源数据库中早已⽀持，有的也叫分析函数。
什么叫窗⼝?
窗⼝的概念⾮常重要，它可以理解为记录集合，窗⼝函数也就是在满⾜某种条件的记录集合上执⾏的特殊函数。对于每条记录都要在此窗⼝内执⾏函数，有的函数随着记录不同，窗⼝⼤⼩都是固定的，这种属于静态窗⼝；有的函数则相反，不同的记录对应着不同的窗⼝，这种动态变化的窗⼝叫滑动窗⼝。
窗⼝函数和普通聚合函数也很容易混淆，⼆者区别如下：
聚合函数是将多条记录聚合为⼀条；⽽窗⼝函数是每条记录都会执⾏，有⼏条记录执⾏完还是⼏条。
聚合函数也可以⽤于窗⼝函数中，这个后⾯会举例说明。
下⾯是⼀个窗⼝函数的简单例⼦：
上⾯例⼦中，row_number()over(partition by user_no order by amount desc)这部分都属于窗⼝函数，它的功能是显⽰每个⽤户按照订单⾦额从⼤到⼩排序的序号。
按照功能划分，可以把MySQL⽀持的窗⼝函数分为如下⼏类：
序号函数：row_number() / rank() / dense_rank()
分布函数：percent_rank() / cume_dist()
前后函数：lag() / lead()
头尾函数：first_val() / last_val()
其他函数：nth_value() / nfile()
三、窗⼝函数如何使⽤
窗⼝函数的基本⽤法如下：
函数名（[expr]） over⼦句
其中，over是关键字，⽤来指定函数执⾏的窗⼝范围，如果后⾯括号中什么都不写，则意味着窗⼝包含满⾜where条件的所有⾏，窗⼝函数基于所有⾏进⾏计算；如果不为空，则⽀持以下四种语法来设置窗⼝：
window_name：给窗⼝指定⼀个别名，如果SQL中涉及的窗⼝较多，采⽤别名可以看起来更清晰易读。上⾯例⼦中如果指定⼀个别名w，则改写如下：
select * from
(
select row_number()over w as row_num,
order_id,user_no,amount,create_date
from order_tab
WINDOW w AS (partition by user_no order by amount desc)
)t ;
partition⼦句：窗⼝按照那些字段进⾏分组，窗⼝函数在不同的分组上分别执⾏。上⾯的例⼦就按照⽤户id进⾏了分组。在每个⽤户id 上，按照order by的顺序分别⽣成从1开始的顺序编号。
order by⼦句：按照哪些字段进⾏排序，窗⼝函数将按照排序后的记录顺序进⾏编号。可以和partition⼦句配合使⽤，也可以单独使⽤。上例中⼆者同时使⽤，如果没有partition⼦句，则会按照所有⽤户的订单⾦额排序来⽣成序号。
农业种植什么项目前景好挣钱多frame⼦句：frame是当前分区的⼀个⼦集，⼦句⽤来定义⼦集的规则，通常⽤来作为滑动窗⼝使⽤。⽐如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单⾦额，则可以设置如下frame⼦句来创建滑动窗⼝：
从结果可以看出，order_id为5订单属于边界值，没有前⼀⾏，因此平均订单⾦额为(900+800)/2=850；order_id为4的订单前后都有订单，所以平均订单⾦额为（900+800+300）/3=666.6667，以此类推就可以得到⼀个基于滑动窗⼝的动态平均订单值。此例中，窗⼝函数⽤到了传统的聚合函数avg()，⽤来计算动态的平均值。
对于滑动窗⼝的范围指定，有两种⽅式，基于⾏和基于范围，具体区别如下：
基于⾏：
通常使⽤BETWEEN frame_start AND frame_end语法来表⽰⾏范围，frame_start和frame_end可以⽀持如下关键字，来确定不同的动态⾏记录：
CURRENT ROW 边界是当前⾏，⼀般和其他范围关键字⼀起使⽤
UNBOUNDED PRECEDING 边界是分区中的第⼀⾏一吨等于几公斤
UNBOUNDED FOLLOWING 边界是分区中的最后⼀⾏
expr PRECEDING 边界是当前⾏减去expr的值
expr FOLLOWING 边界是当前⾏加上expr的值
观察的近义词是什么⽐如，下⾯都是合法的范围：基金份额
rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗⼝范围是当前⾏、前⼀⾏、后⼀⾏⼀共三⾏记录。
rows UNBOUNDED FOLLOWING 窗⼝范围是当前⾏到分区中的最后⼀⾏。
rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗⼝范围是当前分区中所有⾏，等同于不写。
基于范围：
和基于⾏类似，但有些范围不是直接可以⽤⾏数来表⽰的，⽐如希望窗⼝范围是⼀周前的订单开始，截⽌到当前⾏，则⽆法使⽤rows来直接表⽰，此时就可以使⽤范围来表⽰窗⼝：INTERVAL 7 DAY PRECEDING。Linux中常见的最近1分钟、5分钟负载是⼀个典型的应⽤场景。
樵有的函数不管有没有frame⼦句，它的窗⼝都是固定的，也就是前⾯介绍的静态窗⼝，这些函数包括如下：
CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()
接下来我们以上例的订单表为例，来介绍每个函数的使⽤⽅法。表中各字段含义按顺序分别为订单号、⽤户id、订单⾦额、订单创建⽇期。
四、序号函数
序号函数——row_number() / rank() / dense_rank()。
⽤途：显⽰分区中的当前⾏号
使⽤场景：希望查询每个⽤户订单⾦额最⾼的前三个订单
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论