MySQL之窗口函数

目录

1、窗口函数基本概念

2、窗口函数和普通聚合函数的区别

3、常见的窗口函数

4、窗口函数的使用

5、示例代码

聚合函数也可以作为窗口函数（聚合函数VS窗口函数）

常见窗口函数的使用

​编辑给窗口指定别名

---

        怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？累计求和如何计算？
        对于这样的需求，使用传统的SQL实现起来比较困难。这类需求不是简单的聚合可以实现的，费时费力且性能低下，难以维护。要解决此类问题，最方便的就是使用窗口函数。

1、窗口函数基本概念

        MySQL从8.0开始支持窗口函数，这个功能在大多数据库中早已支持，有的也叫分析函数。窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数对于每条记录都要在此窗口内执行函数。简单的说窗口函数就是对于查询的每一行，都使用与该行相关的行进行计算。

        窗口函数也叫OLAP函数(Online Analytical Processing,联机分析处理)，可以对数据进行实时分析处理。窗口函数和group by有类似之处，其区别在于窗口会对每个分组之后的数据进行分别操作，而group by一般对分组之后的函数使用聚合函数汇总。 

        窗口的定义：

2、窗口函数和普通聚合函数的区别

        ①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

        ②聚合函数也可以用于窗口函数。

3、常见的窗口函数

名称	参数	描述
ROW_NUMBER()	否	当前行在其分组内的序号.不管其排序结果中是否出现重复值.其排序结果都为:1,2.3.4.5.
DENSE_RANK()	否	不间断的组内排序.使用这个函数时,可以出现1.1.2.2这种形式的分组。
RAN()	否	间断的组内排序。其排序结果可能出现如下结果:1.1.3.4.4.6
PERCENT_RANK()	否	累计百分比。该函数的计算结果为:小于该条记录值的所有记录的行数/该分组的总行数-1.所以该记录的返回值为[0,1]。
CUME_DIST()	否	累计分布值。即分组值小于等于当前值的行数与分组总行数的比值。取值范围为(0.1]。
LAG()	lag(expr,[N,[detaut]])	从当前行开始往前取第N行，如果N缺失，默认为1。如果不存在前一行.则默认返回default。default默认值为NULL。
LEAD()	lead(expr,[N,[defaut]])	从当前行开始往后取第N行。函数功能与lag()相反.其余与lag()相同。
FIRST_VALUE()	frst_value(expr)	返回分组内截止当前行的第一个值。
LAST_VALUE()	last_value(expr)	返回分组内截止当前行的最后一个值。
NTH_VALUE()	nth_value(expr.N)	返回分组内截止当前行的第N行。与first_value\last_value\nth_ value函数功能相似，只是返回分组内截止当前行的不同行号的数据。
NTILE()	ntile(N)	返回当前行在分组内的分桶号。在计算时要先将该分组内的所有数据划分成N个桶。之后返回每个记录所在的分桶号。返回范围从1到N。

注：‘参数’列说明该函数是否可以加参数。“否”说明该函数的括号内不可以加参数。expr即可以代表字段，也可以代表在字段上的计算，比如sum(col)等。

        将上述函数按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

• 序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
• 分布函数：PERCENT_RANK()、CUME_DIST()
• 前后函数：LAG()、LEAD()
• 头尾函数：FIRST_VALUE()、LAST_VALUE()
• 其它函数：NTH_VALUE()、NTILE()

        序号函数：

ROW_NUMBER()：顺序排序——1、2、3，

RANK()：并列排序，跳过重复序号——1、1、3，
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2。

        分布函数：

每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

        LAG和LEAD分析函数可以在同一次查询中取出同一字段的前N行的数据(LAG)和后N行的数据(LEAD)作为独立的列。

        NTH_VALUE(expr, n)：

其中NTH_VALUE(expr, n)中的第二个参数是指这个函数取排名第几的记录，返回窗口中第n个expr的值。

        NTILE(n)函数：

将分区中的有序数据分为n个等级，记录等级数。

4、窗口函数的使用

        窗口函数的语法

        window_function(expr)
        OVER( partition by ……
                    order by ……
                    frame_clause ……
                    )

        如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：

• partition by子句：按照指定字段进行分区，两个分区由边界分隔，窗口函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
• order by子句：按照指定字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
• frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

5、示例代码

聚合函数也可以作为窗口函数（聚合函数VS窗口函数）

        上述是聚合函数sum()作为窗口函数的例子，类似地avg、mean、max、min等聚合函数也可以作为窗口函数使用。 

常见窗口函数的使用

 row_number()

 first_value()、lead()

 sum()

avg() 

给窗口指定别名