【SQL基础教程】窗口函数

Posted on | In

什么是窗口函数

窗口函数也称为OLAP函数。
OLAP是Online Analytical Processing的简称，意思是对数据库数据进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。
窗口函数就是为了实现OLAP而添加的标准SQL功能。

窗口函数的语法

1
<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)

其中重要的关键字是PARTITION BY和ORDER BY。

在学习PARTITION BY和ORDER BY之前，先来列举一下能够作为窗口函数使用的函数。窗口函数大体可以分为以下两种。

  1. 能够作为窗口函数的聚集函数(SUM、AVG、COUNT、MAX、MIN)
  2. RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数

语法的基本使用方法——使用RANK函数

首先通过专用窗口函数RANK来理解一下窗口函数的语法。正如其名称所示，RANK是用来计算记录排序的函数。
例如，对于Product表中的8件商品，根据不同的商品种类(product_type)，按照销售单价(sale_price)从低到高的顺序排序，结果如下所示。

eJhQSS.png

能够得到上述结果的SELECT语句：

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

PARTITION BY能够设定排序的对象范围。本例中，为了按照商品种类进行排序，指定了product_type。
ORDER BY能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，指定了sale_price。此外，窗口函数中的ORDER BY与SELECT语句末尾的ORDER BY一样，可以通过关键字ASC/DESC来指定升序和降序。省略该关键字时会默认按照ASC，也就是升序进行排序。

通过图，就很容易理解PARTITION BY和ORDER BY的作用了。如图所示，PARTITION BY在横向上对表进行分组，而ORDER BY决定了纵向排序的规则。

eJogDe.png

窗口函数兼具GROUP BY子句的分组功能以及ORDER BY子句的排序功能。但是PARTITION BY子句并不具备GROUP BY子句的汇总功能。因此，使用RANK函数并不会减少原表中记录的行数，结果中仍然包含8行数据。

通过PARTITION BY分组后的记录集合称为窗口。此处的窗口代表范围。这也是“窗口函数”名称的由来。
此外，各个窗口在定义上绝对不会包含共通的部分。这与通过GROUP BY子句分割后的集合具有相同的特征。

无需指定PARTITION BY

使用窗口函数时起到关键作用的是PARTITION BY和GROUP BY。其中，PARTITION BY并不是必需的，即使不指定也可以正常使用窗口函数。
这和使用没有GROUP BY的聚合函数时的效果一样，也就是将整个表作为一个大的窗口来使用。

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

上述SELECT语句的结果如下所示。

eJ7UYR.png

之前得到的是按照商品种类分组后的排序，而这次变成了全部商品的排序。像这样，当希望先将表中的数据分为多个部分（窗口），再使用窗口函数时，可以使用PARTITION BY选项。

专用窗口函数的种类

从上述结果中可以看到，“打卡器”和“叉子”都排在第2位，而之后的“擦菜板”跳过了第3位，直接排到了第4位，这也是通常的排序方法，但某些情况下可能并不希望跳过某个位次来进行排序。
这时可以使用RANK函数之外的函数来实现。下面就来总结一下具有代表性的专用窗口函数。

  • RANK函数

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
例）有3条记录排在第1位时：1位、1位、1位、4位…

  • DENSE_RANK函数

同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
例）有3条记录排在第1位时：1位、1位、1位、2位…

  • ROW_NUMBER函数

赋予唯一的连续位次。
例）有3条记录排在第1位时：1位、2位、3位、4位…

除此之外，各DBMS还提供了各自特有的窗口函数。上述3个函数在所有的DBMS中都能够使用。下面就来比较一下使用这3个函数所得到的结果吧。

1
2
3
4
5
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;

eJvXO1.png

使用RANK或ROW_NUMBER时无需任何参数，只需要像RANK()或者ROW_NUMBER()这样保持括号中为空就可以了。这也是专用窗口函数通常的使用方式。这一点与作为窗口函数使用的聚合函数有很大的不同。

窗口函数的适用范围

使用窗口函数的位置有非常大的限制。更确切地说，窗口函数只能书写在一个特定的位置。
这个位置就是SELECT子句之中。反过来说，就是这类函数不能再WHERE子句或者GROUP BY子句中使用。

其理由就是，在DBMS内部，窗口函数是对WHERE子句或者GROUP BY子句处理后的“结果”进行的操作。在得到用户想要的结果之前，即使进行了排序处理，结果也是错误的。在得到排序结果之后，如果通过WHERE子句中的条件除去了某些记录，或者使用GROUP BY子句进行了汇总处理，那好不容易得到的排序结果也无法使用了。
正是由于这样的原因，在SELECT子句之外“使用窗口函数是没有意义的”，所以在语法上才会有这样的限制。

作为窗口函数使用的聚合函数

前面介绍了使用专用窗口函数的示例，下面再来看一看把SUM或者AVG等聚合函数作为窗口函数使用的方法。

先来看一个将SUM函数作为窗口函数使用的例子。

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

eYPKhV.png

使用SUM函数时，并不像RANK或者ROW_NUMBER那样括号中的内容为空，而是需要在括号内指定作为汇总对象的列。本例中计算出了销售单价(sale_price)的合计值(current_sum)。
但是得到的并不仅仅是合计值，而是按照ORDER BY子句指定的product_id的升序进行排列，计算出商品编号“小于自己”的商品的销售单价的合计值。因此，计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。

使用其他聚合函数时的操作逻辑也和本例相同。例如，使用AVG来代替SELECT语句中的SUM。

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

eYPBcD.png

从结果中可以看到，current_avg的计算方法确实是计算平均值的方法，但作为统计对象的却只是“排在自己之上”的记录。像这样以“自身记录（当前记录）”所谓基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征。

计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
其语法需要在ORDER BY子句之后使用指定范围的关键字。

1
2
3
SELECT product_id, product_name, sale_price, 
       AVG (sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg
FROM Product;

etWO4x.png

指定框架（汇总范围）

这里使用了ROWS（“行”）和PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前～行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前2行”，也就是将作为汇总对象的记录限定为如下的“最靠近的3行”。

  • 自身（当前记录）
  • 之前1行的记录
  • 之前2行的记录

也就是说，由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。

这样的统计方法称为移动平均(moving average)。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。

使用关键字FOLLOWING（“之后”）替换PRECEDING，就可以指定“截止到之后～行”作为框架了。

将当前记录的前后行作为汇总对象

如果希望将当前记录的前后行作为汇总对象时，就可以同时使用PRECEDING（“之前”）和FOLLOWING（“之后”）关键字来实现。

1
2
3
SELECT product_id, product_name, sale_price, 
       AVG (sale_price) OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM Product;

et4HbQ.png

在上述代码中，通过指定框架，将“1 PRECEDING”（之前1行）和“1 FOLLOWING”（之后1行）的区间作为汇总对象。具体来说，就是将如下3行作为汇总对象来进行计算。

  • 之前1行的记录
  • 自身（当前记录）
  • 之后1行的记录

两个ORDER BY

最后来介绍一下使用窗口函数时与结果形式相关的注意事项，那就是记录的排列顺序。
OVER子句中的ORDER BY只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。因此也有可能得到一个记录的排列顺序比较混乱的结果。有些DBMS也可以按照窗口函数的ORDER BY子句所指定的顺序对结果进行排序，但那也仅仅是个例而已。

那么，如何才能让记录切实按照ranking列的升序进行排列呢？
答案非常简单。那就是在SELECT语句的最后，使用ORDER BY子句进行指定。这样就能保证SELECT语句的结果中记录的排列顺序了。

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