MySQL学习之窗口函数

一、窗口函数

窗口函数也称为OLAP（Online Anallytical Processing）函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

窗口函数语法格式：
 

< 窗口函数 > OVER ([ PARTITION BY < 列清单 >]

                                        ORDER BY <排序用列清单>)

注：[ ] 中的内容可以省略

SQL语言中的窗口函数是一种功能强大的工具，用于对结果集中的数据进行分组、排序和聚合操作。窗口函数兼具分组和排序两种功能。

窗口函数适用场景: 对分组统计结果中的每一条记录进行计算的场景下。

通过 PARTITION BY 分组后的记录集合称为 窗口 。此处的窗口并非“窗户”的意思，而是代表 范围 。这也是“窗口函数”名称的由来。
 

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为 框架 。

以下是一些常见的窗口函数及其函数说明和用法结构：

 1、RANK( )序号函数-并列排序:

函数说明：为结果集中的每一行分配一个排名值，相同值的行具有相同的排名，并且可能有间隔。RANK 是用来计算记录排序的函数。（1，2，2，4，5）
函数用法结构：RANK( ) OVER (  PARTITION BY column1
                                                        ORDER BY column2 DESC )

1. PARTITION BY 能够设定排序的对象范围。
2. ORDER BY 能够指定按照哪一列、何种顺序进行排序。
3. 由于专用窗口函数无需参数，因此通常括号中都是空的。
4. 将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录。

SELECT product_name, product_type, sale_price,
RANK () OVER (PARTITION BY product_type
              ORDER BY  sale_price
             ) AS ranking
FROM Product;

使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。其中，PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。

SELECT product_name, product_type, sale_price, 
RANK () OVER (ORDER BY sale_price) AS ranking
FROM Product;

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

 2、ROW_NUMBER( )序号函数-顺序排序: 

函数说明：为结果集中的每一行分配一个唯一的数字标识。（1，2，3，4，5）
函数用法结构：ROW_NUMBER() OVER (PARTITION BY column1
                                                                         ORDER BY column2 )

注：赋予唯一的连续位次。

例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

 3、DENSE_RANK()序号函数-并列排序:

函数说明：为结果集中的每一行分配一个排名值，相同值的行具有相同的排名，没有间隔。 函数用法结构：DENSE_RANK() OVER (PARTITION BY column1

                                                                ORDER BY column2  )

注：即使存在相同位次的记录，也不会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

比较RANK、DENSE_RANK、ROW_NUMBER的结果 

SELECT product_name, product_type, sale_price, 
 RANK () OVER (ORDER BY sale_price) AS ranking, # 并列排序
 DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking, #并列排序
 ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num #顺序排序
FROM Product;

 注：使用 RANK 或 ROW_NUMBER 时无需任何参数，只需要像 RANK ()或者 ROW_NUMBER() 这样保持括号中为空就可以了。

 4、NTILE()分位数函数:

函数说明：用于将数据分成指定数量的近似相等的组。这个函数常用于数据的分位数计算或                        者等级划分。

函数用法结构：NTILE(n) OVER (  
                    PARTITION BY column1   
                    ORDER BY column2  
                )

 5、LAG()前后函数:

函数说明：获取结果集中当前行之前指定偏移量的行的值。返回当前行的前n行的exper的值
函数用法结构：LAG ( COLUMN, OFFSET, default_value ) 
                        OVER ( PARTITION BY column1 
                                     ORDER BY column2 
                         )

 6、LEAD()前后函数:

函数说明：用于获取当前行之后的一行或多行的值，返回当前n行的expr的值。
函数用法结构：LEAD(column, offset, default) OVER (  
    [PARTITION BY partition_expression, ... ]  
    ORDER BY sort_expression [ASC | DESC], ...  
)

 7、FIRST_VALUE()头尾函数:

函数说明：获取结果集中每个分组的第一行的值。
函数用法结构：FIRST_VALUE(column)

                         OVER (  
                                    [PARTITION BY partition_expression ]  
                                    ORDER BY sort_expression [ASC | DESC]
                                )

 8、LAST_VALUE()头尾函数:

函数说明：获取结果集中每个分组的最后一行的值。
函数用法结构：LAST_VALUE(column) OVER (  
    [PARTITION BY partition_expression, ... ]  
    ORDER BY sort_expression [ASC | DESC], ...  
)

SELECT   
    product_id,   
    product_name,   
    price,   
    LAST_VALUE(price) OVER (PARTITION BY product_category ORDER BY price DESC) as last_price  
FROM   
    products;

 上述查询将返回每个产品的产品ID、产品名称、价格以及按产品类别分组并按价格降序排序的最后一个价格。通过使用 LAST_VALUE() 函数，我们可以轻松地获取窗口中当前行的最后一个值。

二、窗口函数使用的聚合函数 

所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。 

 1、SUM()求和函数:

函数说明：计算结果集中每个分组的指定列的总和。
函数用法结构：SUM(column) OVER (PARTITION BY column1 ORDER BY column2)

我们得到的并不仅仅是合计值，而是按照 ORDER BY 子句指定的 product_id 的升序进行排列。

在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为 累计 的统计方法。

SELECT product_id, product_name, sale_price,
 SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
FROM Product;

 2、AVG()求平均值函数:

函数说明：计算结果集中每个分组的指定列的平均值。
函数用法结构：AVG(column) OVER (PARTITION BY column1 ORDER BY column2)

SELECT product_id, product_name, sale_price,
 AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
FROM Product;

三、窗口函数的适用范围 

目前为止我们学过的函数大部分都没有使用位置的限制，最多也就是在 WHERE 子句中使用聚合函数时会有些注意事项。但是，使用窗口函数的位置却有非常大的限制。

 原则上窗口函数只能在SELECT子句中使用。

 原因：在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP  BY 子句处理后的“结果”进行的操作。在得到排序结果之后，如果通过 WHERE 子句中的条件除去了某些记录，或者使用GROUP BY 子句进行了汇总处理，则排序结果也无法使用。

 在 SELECT 子句之外“使用窗口函数是没有意义的”

窗口函数可以根据指定的PARTITION BY子句对数据进行分组，然后按照指定的ORDER BY子句排序。它们可以在SELECT语句中使用，为结果集中的每一行计算一个值，并将结果作为额外的列返回。窗口函数可以在聚合函数内使用，也可以与其他普通列一起使用，以实现更复杂的数据分析和报表需求。

上述列举的函数是标准 SQL 定义的 OLAP 专用函数，本书将其统称为“专用窗口函数”。