IT评测·应用市场-qidao123.com

标题: 什么是 SQL 窗口函数（分析函数） [打印本页]
作者: 温锦文欧普厨电及净水器总代理    时间: 2022-6-19 10:48
标题: 什么是 SQL 窗口函数（分析函数）
目录

本文介绍什么是 SQL 窗口函数，窗口函数可以进行排序、生成序列号等一般的 聚合函数 无法实现的高级操作。
本文重点
一、什么是窗口函数

窗口函数也称为 OLAP 函数 [1]。为了让大家快速形成直观印象，才起了这样一个容易理解的名称（“窗口”的含义我们将在随后进行说明）。
OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。
窗口函数就是为了实现 OLAP 而添加的标准 SQL 功能 [2]。
专栏
窗口函数的支持情况
很多数据库相关工作者过去都会有这样的想法：“好不容易将业务数据插入到了数据库中，如果能够使用 SQL 对其进行实时分析的话，一定会很方便吧。”但是关系数据库提供支持 OLAP 用途的功能仅仅只有 10 年左右的时间。
其中的理由有很多，这里我们就不一一介绍了。大家需要注意的是，还有一部分 DBMS 并不支持这样的新功能。
本文将要介绍的窗口函数也是其中之一，截至 2016 年 5 月，Oracle、SQL Server、DB2、PostgreSQL 的最新版本都已经支持了该功能，但是 MySQL 的最新版本 5.7 还是不支持该功能。
通过前面的学习，我们已经知道各个 DBMS 都有自己支持的特定语法和不支持的语法。标准 SQL 添加新功能的时候也会遇到同样的问题（随着时间推移，标准 SQL 终将能够在所有的 DBMS 中进行使用）。
二、窗口函数的语法

接下来，就让我们通过示例来学习窗口函数吧。窗口函数的语法有些复杂。
语法 1 窗口函数
  1. <窗口函数> OVER ([PARTITION BY <列清单>]
  2.                          ORDER BY <排序用列清单>)
复制代码
※ [] 中的内容可以省略。
其中重要的关键字是 PARTITION BY 和 ORDER BY，理解这两个关键字的作用是帮助我们理解窗口函数的关键。
2.1 能够作为窗口函数使用的函数

在学习 PARTITION BY 和 ORDER BY 之前，我们先来列举一下能够作为窗口函数使用的函数。窗口函数大体可以分为以下两种。
① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
② 中的函数是标准 SQL 定义的 OLAP 专用函数，本文将其统称为“专用窗口函数”。从这些函数的名称可以很容易看出其 OLAP 的用途。
其中 ① 的部分是我们在 SQL 如何对表进行聚合和分组查询并对查询结果进行排序 中学过的聚合函数。将聚合函数书写在“语法 1”的“”中，就能够当作窗口函数来使用了。
总之，聚合函数根据使用语法的不同，可以在聚合函数和窗口函数之间进行转换。
三、语法的基本使用方法——使用 RANK 函数

首先让我们通过专用窗口函数 RANK 来理解一下窗口函数的语法吧。正如其名称所示，RANK 是用来计算记录排序的函数。
例如，对于之前使用过的 Product 表中的 8 件商品，让我们根据不同的商品种类（product_type），按照销售单价（sale_price）从低到高的顺序排序，结果如下所示：
  1. product_name | product_type | sale_price | ranking
  2. -------------+--------------+------------+--------
  3. 叉子        | 厨房用具      |        500 |       1
  4. 擦菜板      | 厨房用具      |        880 |       2
  5. 菜刀        | 厨房用具      |       3000 |       3
  6. 高压锅      | 厨房用具      |       6800 |       4
  7. T恤衫       | 衣服          |       1000 |       1
  8. 运动T恤     | 衣服          |       4000 |       2
  9. 圆珠笔      | 办公用品      |        100 |       1
  10. 打孔器      | 办公用品      |        500 |       2
复制代码
以厨房用具为例，销售单价最便宜的“叉子”排在第 1 位，最贵的“高压锅”排在第 4 位，确实按照我们的要求进行了排序。
能够得到上述结果的 SELECT 语句请参考代码清单 1。
代码清单 1 根据不同的商品种类，按照销售单价从低到高的顺序创建排序表
Oracle SQL Server DB2 PostgreSQL
  1. SELECT product_name, product_type, sale_price,
  2.        RANK () OVER (PARTITION BY product_type
  3.                           ORDER BY sale_price) AS ranking
  4.   FROM Product;
复制代码
PARTITION BY 能够设定排序的对象范围。本例中，为了按照商品种类进行排序，我们指定了 product_type。
ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了 sale_price。
此外，窗口函数中的 ORDER BY 与 SELECT 语句末尾的 ORDER BY 一样，可以通过关键字 ASC/DESC 来指定升序和降序。
省略该关键字时会默认按照 ASC，也就是升序进行排序。本例中就省略了上述关键字 [3]。
通过图 1，我们就很容易理解 PARTITION BY 和 ORDER BY 的作用了。如图所示，PARTITION BY 在横向上对表进行分组，而 ORDER BY 决定了纵向排序的规则。
图 1　PARTITION BY 和 ORDER BY 的作用
窗口函数兼具之前我们学过的 GROUP BY 子句的分组功能以及 ORDER BY 子句的排序功能。但是，PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能。
因此，使用 RANK 函数并不会减少原表中记录的行数，结果中仍然包含 8 行数据。
法则 1
窗口函数兼具分组和排序两种功能。
通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。这也是“窗口函数”名称的由来。[4]
法则 2
通过 PARTITION BY 分组后的记录集合称为“窗口”。
此外，各个窗口在定义上绝对不会包含共通的部分。就像刀切蛋糕一样，干净利落。这与通过 GROUP BY 子句分割后的集合具有相同的特征。
四、无需指定 PARTITION BY

使用窗口函数时起到关键作用的是 PARTITION BY 和 GROUP BY。其中，PARTITION BY 并不是必需的，即使不指定也可以正常使用窗口函数。
那么就让我们来确认一下不指定 PARTITION BY 会得到什么样的结果吧。这和使用没有 GROUP BY 的聚合函数时的效果一样，也就是将整个表作为一个大的窗口来使用。
事实胜于雄辩，下面就让我们删除代码清单 1 中 SELECT 语句的 PARTITION BY 试试看吧（代码清单 2）。
代码清单 2 不指定 PARTITION BY
Oracle SQL Server DB2 PostgreSQL
  1. SELECT product_name, product_type, sale_price,
  2.        RANK () OVER (ORDER BY sale_price) AS ranking
  3.   FROM Product;
复制代码
上述 SELECT 语句的结果如下所示：
  1. product_name | product_type | sale_price | ranking
  2. -------------+--------------+------------+--------
  3. 圆珠笔      | 办公用品     |         100 |       1
  4. 叉子        | 厨房用具     |         500 |       2
  5. 打孔器      | 办公用品     |         500 |       2
  6. 擦菜板      | 厨房用具     |         880 |       4
  7. T恤衫       | 衣服         |        1000 |       5
  8. 菜刀        | 厨房用具     |        3000 |       6
  9. 运动T恤     | 衣服         |        4000 |       7
  10. 高压锅      | 厨房用具     |        6800 |       8
复制代码
之前我们得到的是按照商品种类分组后的排序，而这次变成了全部商品的排序。像这样，当希望先将表中的数据分为多个部分（窗口），再使用窗口函数时，可以使用 PARTITION BY 选项。
五、专用窗口函数的种类

从上述结果中我们可以看到，“打孔器”和“叉子”都排在第 2 位，而之后的“擦菜板”跳过了第 3 位，直接排到了第 4 位，这也是通常的排序方法，但某些情况下可能并不希望跳过某个位次来进行排序。
这时可以使用 RANK 函数之外的函数来实现。下面就让我们来总结一下具有代表性的专用窗口函数吧。
除此之外，各 DBMS 还提供了各自特有的窗口函数。上述 3 个函数（对于支持窗口函数的 DBMS 来说）在所有的 DBMS 中都能够使用。
下面就让我们来比较一下使用这 3 个函数所得到的结果吧（代码清单 3）。
代码清单 3 比较 RANK、DENSE_RANK、ROW_NUMBER 的结果
Oracle SQL Server DB2 PostgreSQL
  1. SELECT product_name, product_type, sale_price,
  2.      RANK () OVER (ORDER BY sale_price) AS ranking,
  3.      DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking,
  4.      ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num
  5. FROM Product;
复制代码
执行结果：

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

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

前面给大家介绍了使用专用窗口函数的示例，下面我们再来看一看把之前学过的 SUM 或者 AVG 等聚合函数作为窗口函数使用的方法。
所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。但大家可能对所能得到的结果还没有一个直观的印象，所以我们还是通过具体的示例来学习。
下面我们先来看一个将 SUM 函数作为窗口函数使用的例子（代码清单 4）。
代码清单 4 将 SUM 函数作为窗口函数使用
Oracle SQL Server DB2 PostgreSQL
  1. SELECT product_id, product_name, sale_price,
  2.     SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
  3.   FROM Product;
复制代码
执行结果：
  1. product_id | product_name | sale_price | current_sum
  2. ------------+--------------+------------+------------
  3. 0001       | T恤衫        |       1000 |        1000   ←1000
  4. 0002       | 打孔器       |        500 |        1500   ←1000+500
  5. 0003       | 运动T恤      |       4000 |        5500   ←1000+500+4000
  6. 0004       | 菜刀         |       3000 |        8500   ←1000+500+4000+3000
  7. 0005       | 高压锅       |       6800 |       15300              ·
  8. 0006       | 叉子         |        500 |       15800              ·
  9. 0007       | 擦菜板       |        880 |       16680              ·
  10. 0008       | 圆珠笔       |        100 |       16780              ·
复制代码
使用 SUM 函数时，并不像 RANK 或者 ROW_NUMBER 那样括号中的内容为空，而是和之前我们学过的一样，需要在括号内指定作为汇总对象的列。
本例中我们计算出了销售单价（sale_price）的合计值（current_sum）。
但是我们得到的并不仅仅是合计值，而是按照 ORDER BY 子句指定的 product_id 的升序进行排列，计算出商品编号“小于自己”的商品的销售单价的合计值。
因此，计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。
使用其他聚合函数时的操作逻辑也和本例相同。例如，使用 AVG 来代替 SELECT 语句中的 SUM（代码清单 5）。
代码清单 5 将 AVG 函数作为窗口函数使用
Oracle SQL Server DB2 PostgreSQL
  1. SELECT product_id, product_name, sale_price,
  2.      AVG (sale_price) OVER (ORDER BY product_id) AS current_avg
  3.   FROM Product;
复制代码
执行结果：
  1. product_id | product_name | sale_price |     current_avg
  2. -----------+--------------+------------+-----------------------
  3. 0001      | T恤衫        |       1000 | 1000.0000000000000000 ←(1000)/1
  4. 0002      | 打孔器       |        500 | 750.0000000000000000  ←(1000+500)/2
  5. 0003      | 运动T恤      |       4000 | 1833.3333333333333333 ←(1000+500+4000)/3
  6. 0004      | 菜刀         |       3000 | 2125.0000000000000000 ←(1000+500+4000+3000)/4
  7. 0005      | 高压锅       |       6800 | 3060.0000000000000000 ←(1000+500+4000+3000+6800)/5
  8. 0006      | 叉子         |        500 | 2633.3333333333333333               ·
  9. 0007      | 擦菜板       |        880 | 2382.8571428571428571               ·
  10. 0008      | 圆珠笔       |        100 | 2097.5000000000000000               ·
复制代码
从结果中我们可以看到，current_avg 的计算方法确实是计算平均值的方法，但作为统计对象的却只是“排在自己之上”的记录。
像这样以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征。
八、计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
其语法如代码清单 6 所示，需要在 ORDER BY 子句之后使用指定范围的关键字。
代码清单 6 指定“最靠近的 3 行”作为汇总对象
Oracle SQL Server DB2 PostgreSQL
  1. SELECT product_id, product_name, sale_price,
  2.        AVG (sale_price) OVER (ORDER BY product_id
  3.                                ROWS 2 PRECEDING) AS moving_avg
  4.   FROM Product;
复制代码
执行结果（在 DB2 中执行）：
  1. product_id    product_name    sale_price     moving_avg
  2. -----------   -------------  -------------   ------------
  3. 0001         T恤衫                 1000           1000 ←(1000)/1
  4. 0002         打孔器                 500            750 ←(1000+500)/2
  5. 0003         运动T恤               4000           1833 ←(1000+500+4000)/3
  6. 0004         菜刀                  3000           2500 ←(500+4000+3000)/3
  7. 0005         高压锅                6800           4600 ←(4000+3000+6800)/3
  8. 0006         叉子                   500           3433          ·
  9. 0007         擦菜板                 880           2726          ·
  10. 0008         圆珠笔                 100            493          ·
复制代码
8.1 指定框架（汇总范围）

我们将上述结果与之前的结果进行比较，可以发现商品编号为“0004”的“菜刀”以下的记录和窗口函数的计算结果并不相同。
这是因为我们指定了框架，将汇总对象限定为了“最靠近的 3 行”。
这里我们使用了 ROWS（“行”）和 PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”。
也就是说，由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。
图 2　将框架指定为截止到当前记录之前 2 行（最靠近的 3 行）
如果将条件中的数字变为“ROWS 5 PRECEDING”，就是“截止到之前 5 行”（最靠近的 6 行）的意思。
这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。
使用关键字 FOLLOWING（“之后”）替换 PRECEDING，就可以指定“截止到之后~ 行”作为框架了（图 3）。
图 3　将框架指定为截止到当前记录之后 2 行（最靠近的 3 行）
8.2 将当前记录的前后行作为汇总对象

如果希望将当前记录的前后行作为汇总对象时，就可以像代码清单 7 那样，同时使用 PRECEDING（“之前”）和 FOLLOWING（“之后”）关键字来实现。
代码清单 7 将当前记录的前后行作为汇总对象
Oracle SQL Server DB2 PostgreSQL
  1. Oracle  SQL Server  DB2  PostgreSQL
  2. SELECT product_id, product_name, sale_price,
  3.        AVG (sale_price) OVER (ORDER BY product_id
  4.                                ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
  5.   FROM Product;
复制代码
执行结果（在 DB2 中执行）：
  1. product_id    product_name    sale_price    moving_avg
  2. -----------   -------------   -----------   -----------
  3. 0001         T恤衫                 1000           750   ←(1000+500)/2
  4. 0002         打孔器                 500          1833   ←(1000+500+4000)/3
  5. 0003         运动T恤               4000          2500   ←(500+4000+3000)/3
  6. 0004         菜刀                  3000          4600   ←(4000+3000+6800)/3
  7. 0005         高压锅                6800          3433             ·
  8. 0006         叉子                   500          2726             ·
  9. 0007         擦菜板                 880           493             ·
  10. 0008         圆珠笔                 100           490             ·
复制代码
在上述代码中，我们通过指定框架，将“1 PRECEDING”（之前 1 行）和“1 FOLLOWING”（之后 1 行）的区间作为汇总对象。
具体来说，就是将如下 3 行作为汇总对象来进行计算（图 4）。
如果能够熟练掌握框架功能，就可以称为窗口函数高手了。
图 4　将框架指定为当前记录及其前后 1 行
九、两个 ORDER BY

最后我们来介绍一下使用窗口函数时与结果形式相关的注意事项，那就是记录的排列顺序。
因为使用窗口函数时必须要在 OVER 子句中使用 ORDER BY，所以可能有读者乍一看会觉得结果中的记录会按照该 ORDER BY 指定的顺序进行排序。
但其实这只是一种错觉。OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。
因此也有可能像代码清单 8 那样，得到一个记录的排列顺序比较混乱的结果。有些 DBMS 也可以按照窗口函数的 ORDER BY 子句所指定的顺序对结果进行排序，但那也仅仅是个例而已。
代码清单 8 无法保证如下 SELECT 语句的结果的排列顺序
Oracle SQL Server DB2 PostgreSQL
  1. SELECT product_name, product_type, sale_price,
  2.        RANK () OVER (ORDER BY sale_price) AS ranking
  3.   FROM Product;
复制代码
有可能会得到下面这样的结果：
  1. product_name | product_type | sale_price | ranking
  2. --------------+--------------+------------+--------
  3. 菜刀         | 厨房用具      |       3000 |       6
  4. 打孔器       | 办公用品      |        500 |       2
  5. 运动T恤      | 衣服          |       4000 |       7
  6. T恤衫        | 衣服          |       1000 |       5
  7. 高压锅       | 厨房用具      |       6800 |       8
  8. 叉子         | 厨房用具      |        500 |       2
  9. 擦菜板       | 厨房用具      |        880 |       4
  10. 圆珠笔       | 办公用品      |        100 |       1
复制代码
那么，如何才能让记录切实按照 ranking 列的升序进行排列呢？
答案非常简单。那就是在 SELECT 语句的最后，使用 ORDER BY 子句进行指定（代码清单 9）。
这样就能保证 SELECT 语句的结果中记录的排列顺序了，除此之外也没有其他办法了。
代码清单 9 在语句末尾使用 ORDER BY 子句对结果进行排序
Oracle SQL Server DB2 PostgreSQL
  1. SELECT product_name, product_type, sale_price,
  2.        RANK () OVER (ORDER BY sale_price) AS ranking
  3.   FROM Product
  4. ORDER BY ranking;
复制代码
也许大家会觉得在一条 SELECT 语句中使用两次 ORDER BY 会有点别扭，但是尽管这两个 ORDER BY 看上去是相同的，但其实它们的功能却完全不同。
法则 5
将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录。
原文链接：https://www.developerastrid.com/sql/sql-olap/
（完）

来源：https://www.cnblogs.com/vin-c/archive/2022/06/15/16379156.html
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/) Powered by Discuz! X3.4