什么是窗口函数

    窗口函数也称为OLAP 函数。为了让大家快速形成直观印象，才起 了这样一个容易理解的名称（“窗口”的含义我们将在随后进行说明）。 OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据 进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性 商务工作。 窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能.

    窗口函数的语法

    接下来，就让我们通过示例来学习窗口函数吧。窗口函数的语法有些 复杂。 窗口函数

    窗口函数 - 图1

    其中重要的关键字是 PARTITION BY 和 ORDER BY，理解这两个 关键字的作用是帮助我们理解窗口函数的关键。 ■能够作为窗口函数使用的函数 在学习 PARTITION BY 和 ORDER BY 之前，我们先来列举一下能 够作为窗口函数使用的函数。窗口函数大体可以分为以下两种。 ① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN） ② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数 在②中的函数是标准 SQL 定义的 OLAP 专用函数，本书将其统称为“专 用窗口函数”。从这些函数的名称可以很容易看出其 OLAP 的用途。 其中①的部分是我们在第 3 章中学过的聚合函数。将聚合函数书写 在“语法 8-1”的“< 窗口函数 >”中，就能够当作窗口函数来使用了。 总之，聚合函数根据使用语法的不同，可以在聚合函数和窗口函数之间 进行转换。

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

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

    窗口函数 - 图2

    以厨房用具为例，销售单价最便宜的“叉子”排在第 1 位，最贵的“高 压锅”排在第 4 位，确实按照我们的要求进行了排序。 能够得到上述结果的 SELECT 语句请参考代码清单如下。 根据不同的商品种类，按照销售单价从低到高的顺序创建排序表

    窗口函数 - 图3

    PARTITION BY 能够设定排序的对象范围。本例中，为了按照商品 种类进行排序，我们指定了 product_type。 ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售 单价的升序进行排列，我们指定了 sale_price。此外，窗口函数中的 ORDER BY 与 SELECT 语句末尾的 ORDER BY 一样，可以通过关键字 ASC/DESC 来指定升序和降序。 省略该关键字时会默认按照 ASC，也就是 升序进行排序。本例中就省略了上述关键字。 通过下图，我们就很容易理解 PARTITION BY 和 ORDER BY 的作 用了。如图所示，PARTITION BY 在横向上对表进行分组，而 ORDER BY 决定了纵向排序的规则。 PARTITION BY和ORDER BY的作用

    窗口函数 - 图4

    窗口函数兼具之前我们学过的 GROUP BY 子句的分组功能以及 ORDER BY 子句的排序功能。但是，PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能。 因此，使用 RANK 函数并不会减少原表中 记录的行数，结果中仍然包含 8 行数据。 此外，各个窗口在定义上绝对不会包含共通的部分。就像刀切蛋糕一 样，干净利落。这与通过 GROUP BY 子句分割后的集合具有相同的特征。 无需指定PARTITION BY 使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。 其中，PARTITION BY 并不是必需的，即使不指定也可以正常使用窗 口函数。 那么就让我们来确认一下不指定 PARTITION BY 会得到什么样的 结果吧。这和使用没有 GROUP BY 的聚合函数时的效果一样，也就是将 整个表作为一个大的窗口来使用。 不指定PARTITION BY

    窗口函数 - 图5

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

    窗口函数 - 图6

    之前我们得到的是按照商品种类分组后的排序，而这次变成了全部商 品的排序。 像这样，当希望先将表中的数据分为多个部分（窗口），再使 用窗口函数时，可以使用 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 位…… 比较RANK、DENSE_RANK、ROW_NUMBER的结果

    窗口函数 - 图7

    窗口函数 - 图8 将结果中的 ranking 列和 dense_ranking 列进行比较可以发 现，dense_ranking 列中有连续 2 个第 2 位，这和 ranking 列的情 况相同。 但是接下来的“擦菜板”的位次并不是第 4 而是第 3。这就是使 用 DENSE_RANK 函数的效果了。 此外，我们可以看到，在 rownum 列中，不管销售单价（sale price）是否相同，每件商品都会按照销售单价从低到高的顺序得到一 个连续的位次。 销售单价相同时，DBMS 会根据适当的顺序对记录进行排 列。想为记录赋予唯一的连续位次时，就可以像这样使用 ROW_NUMBER 来实现。 使用 RANK 或 ROW_NUMBER 时无需任何参数，只需要像 RANK () 或者 ROW_NUMBER() 这样保持括号中为空就可以了。 这也是专用窗口 函数通常的使用方式，请大家牢记。这一点与作为窗口函数使用的聚合函 数有很大的不同，之后我们将会详细介绍。

    窗口函数的适用范围

    目前为止我们学过的函数大部分都没有使用位置的限制，最多也就是 在 WHERE 子句中使用聚合函数时会有些注意事项。 但是，使用窗口函数 的位置却有非常大的限制。更确切地说，窗口函数只能书写在一个特定的 位置。 在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。 大家仔细想一想就会明白，在得 到用户想要的结果之前，即使进行了排序处理，结果也是错误的。 在得到 排序结果之后，如果通过 WHERE 子句中的条件除去了某些记录，或者使 用 GROUP BY 子句进行了汇总处理，那好不容易得到的排序结果也无法 使用了。 正是由于这样的原因，在 SELECT 子句之外“使用窗口函数是没有 意义的”，所以在语法上才会有这样的限制。

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

    所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。 但大家可能对所能得到的结果还没有一个直观的印象，所以我们还是通过 具体的示例来学习。 将SUM函数作为窗口函数使用

    窗口函数 - 图9

    窗口函数 - 图10 使用 SUM 函数时，并不像 RANK 或者 ROW_NUMBER 那样括号中 的内容为空，而是和之前我们学过的一样，需要在括号内指定作为汇总 对象的列。 本例中我们计算出了销售单价（sale_price）的合计值 （current_sum）。 但是我们得到的并不仅仅是合计值，而是按照 ORDER BY 子句指定 的 product_id 的升序进行排列，计算出商品编号“小于等于自己”的商品 的销售单价的合计值。 因此，计算该合计值的逻辑就像金字塔堆积那样， 一行一行逐渐添加计算对象。 在按照时间序列的顺序，计算各个时间的销 售额总额等的时候，通常都会使用这种称为累计的统计方法。 使用其他聚合函数时的操作逻辑也和本例相同。 将AVG函数作为窗口函数使用

    窗口函数 - 图11

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

    计算移动平均

    窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。 其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。 其语法如下列代码清单所示，需要在ORDER BY子句之后使用指定范围的关键字。 指定“最靠近的3行”作为汇总对象

    窗口函数 - 图13

    窗口函数 - 图14 ●指定框架（汇总范围） 我们将上述结果与之前的结果进行比较，可以发现商品编号为“0004” 的“菜刀”以下的记录和窗口函数的计算结果并不相同。 这是因为我们指定了框架，将汇总对象限定为了“最靠近的 3 行”。 这里我们使用了 ROWS（“行”）和 PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING” 就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”。 ● 自身（当前记录） ● 之前 1行的记录 ● 之前 2行的记录 也就是说，由于框架是根据当前记录来确定的，因此和固定的窗口不 同，其范围会随着当前记录的变化而变化。 将框架指定为截止到当前记录之前2行（最靠近的3行）

    窗口函数 - 图15

    如果将条件中的数字变为“ROWS 5 PRECEDING”，就是“截止 到之前 5 行”（最靠近的 6 行）的意思。 这样的统计方法称为移动平均（moving average）。由于这种方法在希 望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实 时跟踪当中。 使用关键字 FOLLOWING（“之后”）替换 PRECEDING，就可以指 定“截止到之后 ~ 行”作为框架了。 将框架指定为截止到当前记录之后2行（最靠近的3行）

    窗口函数 - 图16

    ●将当前记录的前后行作为汇总对象 如果希望将当前记录的前后行作为汇总对象时，就可以像代码清单 8-7 那样，同时使用 PRECEDING（“之前”）和 FOLLOWING（“之后”）关 键字来实现。 将当前记录的前后行作为汇总对象

    窗口函数 - 图17

    窗口函数 - 图18

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

    窗口函数 - 图19

    两个ORDER BY 最后我们来介绍一下使用窗口函数时与结果形式相关的注意事项，那就是记录的排列顺序。 因为使用窗口函数时必须要在 OVER 子句中使用 ORDER BY，所以可能有读者乍一看会觉得结果中的记录不会按照该 ORDER BY 指定的顺序进行排序。 但其实这只是一种错觉。OVER 子句中的 ORDER BY 只是用来决定 窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。 因此也有可能像上面的代码清单那样，得到一个记录的排列顺序比较混乱的结果。 有些 DBMS 也可以按照窗口函数的 ORDER BY 子句所指定的顺序对结果进行排序，但那也仅仅是个例而已。 无法保证如下SELECT语句的结果的排列顺序

    窗口函数 - 图20

    窗口函数 - 图21 那么，如何才能让记录切实按照 ranking 列的升序进行排列呢？ 答案非常简单。那就是在 SELECT 语句的最后，使用 ORDER BY 子句进行指定。 这样就能保证 SELECT 语句的结果中记 录的排列顺序了，除此之外也没有其他办法了。 在语句末尾使用ORDER BY子句对结果进行排序

    窗口函数 - 图22

    也许大家会觉得在一条 SELECT 语句中使用两次 ORDER BY 会有 点别扭，但是尽管这两个 ORDER BY 看上去是相同的，但其实它们的功 能却完全不同。

    dence_rank()

    排名样式：1、2、2、3 会产生重复排名，但排名不延续。 语法： dence_rank() over(partition by 分组字段 order by 排序字段 desc或asc)

    rank()

    排名样式：1、2、2、4会产生重复排名，但排名延续。 语法： rank() over(partition by 分组字段 order by 排序字段 desc或asc)

    row_number()

    排名样式：1、2、3、4不重复排名 语法： row_number() over(partition by 分组字段 order by 排序字段 desc或asc)