PLSQL - 理解聚合函数的分析模式

聚合函数是我们所熟知的非常好用的数据统计函数，例如求和、求取最值等。我们已经知道聚合计算是基于数据分组使用的，简单来说数据分组就是考察表中行的若干特征，特征一致的行分到一个组中。另外从数据分组的原理我们又发现，分组后的数据仅保留了被考察的特征列（如果有的话）以及被聚合的列，如此求取聚合值后，返回结果的行数就可能少于原始数据集的行数。

分析函数又称窗口函数，但实际上分析函数一般含有三个要素：分区子句、排序子句和窗口子句。这里的分区和分组的概念十分相像，同样是将若干特征一致的行分到一个分区中。但与 GROUP BY 分组不同的是，分析函数在对数据进行分区时，不会剔除掉它不关注的列，进而也不改变原始数据集的行数，仅仅是“概念上地”在其划分的分区内执行计算。

一、分析函数的语法

分析函数的一般语法形如：

function(arguments_list) over(<partition-clause>, <sort-clause>, <window-clause>)

其中 OVER 是分析函数极其显著的特征，上文说的分区、排序和窗口三个要素，都在 OVER 子句中声明。

分区子句 <partition-clause> 通常的语法是 partition by col1, col2, col3 ... 表示将指定的列值一致的行分到同一分区。如果不声明分区子句，那么所有的行将被视作一个分区处理。

排序子句 <sort-clause> 通常的语法是 order by col1, col2, col3 ... 指示在分区内部行的排序依据。如果不声明排序子句，则理解为不对分区内的行进行排序。

窗口子句 <window-clause> 表示在有序的分区上限制一定的范围作为函数作用的数据源，这就像是数据装在分区的房子里，窗口子句为房外的分析函数打开了一个观察房内数据的窗口，不在窗口内的数据，分析函数绝看不到。与分区子句一次性决定数据分区不同的是，窗口子句可以对每一行重新划定不同的窗口，所以也称“滑动窗口”。

窗口子句的语法是 rows|range between <s> and <e> 表示把 <s> 到 <e> 之间的数据放进窗口中。这其中：

• <s> :: unbounded preceding | current row | n preceding | n following 分别表示 分区最前一行 | 当前行 | 向前n行 | 向后n行
• <e> :: unbounded following | current row | n preceding | n following 分别表示 分区最末一行 | 当前行 | 向前n行 | 向后n行
• rows 和 range 的概念非常重要，它们是对上述“行”的概念的解释：rows 表示在分区里“物理的”行的概念上开窗，range 则表示要参照当前行的值（所有用于排序的列的值），在分区里所有值在指定范围的行上开窗

特别需要注意的是，窗口子句是依赖于排序子句而存在的，也就是说不声明排序子句的话，不能声明窗口子句。而从另一个角度讲，如果没有声明排序子句，就不会进行数据开窗，所有的行都纳入函数的计算范围中。

二、聚合函数的分析模式

今天我们着重要理解的是，聚合函数的分析模式，也就是把聚合函数像分析函数一样使用。

参照上述分析函数的一般语法，将其中的 function 位置上使用传统的聚合函数，就是聚合函数的分析模式用法了。这里有一个需要我们特别牢记的规定：分析模式下使用聚合函数，其默认的窗口总是 range between unbounded preceding and current row。

我们先来预览一下示例数据：

SQL> SELECT t.demo_id, t.category_id, t.item_id, t.quantity FROM demo_af t;

   DEMO_ID CATEGORY_ID    ITEM_ID   QUANTITY
---------- ----------- ---------- ----------
      1000          10          1        100
      1001          10          2        300
      1002          10          2        500
      1003          20          3         10
      1004          20          3         30
      1005          20          4         50

6 rows selected

例 2.1 求各 category 的数量合计

我们可以看到结果集没有减少行数，这是因为没有对表进行 GROUP BY 分组。与此同时，在使用了聚合函数 SUM 的情况下，Oracle 并没有报错以要求将非聚合列进行分组，这又是由于此处的聚合函数是在分析模式下运行的，可以理解为它“脱离”了数据的原始结构，在虚拟的分区上运行后，又将计算结果返回给了分区的每一行。

SQL> SELECT t.category_id
  2        ,t.item_id
  3        ,t.quantity
  4        ,SUM(t.quantity) over(PARTITION BY t.category_id) s_quantity
  5    FROM demo_af t;

CATEGORY_ID    ITEM_ID   QUANTITY S_QUANTITY
----------- ---------- ---------- ----------
         10          1        100        900
         10          2        300        900
         10          2        500        900
         20          3         10         90
         20          3         30         90
         20          4         50         90

6 rows selected

例 2.2 求各个 item 各行的累计数量

本例我们首先要将 item 进行分区，其次按照某个顺序排列，这里以 demo_id 作为排序依据。另外我们要求累计数量，那么窗口的范围就应是从分区首行到当前行 rows between unbounded preceding and current row。

SQL> SELECT t.demo_id
  2        ,t.category_id
  3        ,t.item_id
  4        ,t.quantity
  5        ,SUM(t.quantity) over(PARTITION BY t.item_id ORDER BY t.demo_id rows BETWEEN unbounded preceding AND CURRENT ROW) total_quantity
  6    FROM demo_af t;

   DEMO_ID CATEGORY_ID    ITEM_ID   QUANTITY TOTAL_QUANTITY
---------- ----------- ---------- ---------- --------------
      1000          10          1        100            100
      1001          10          2        300            300
      1002          10          2        500            800
      1003          20          3         10             10
      1004          20          3         30             40
      1005          20          4         50             50

6 rows selected

这个例子还有值得我们反思的地方，比如说我们是否有必要声明窗口子句呢？严格来说，基于例题的要求，语法上我们是应该声明这样的窗口子句的。但是我们思考一下聚合函数分析模式下的默认窗口 range between unbounded preceding and current row 在这里难道就不适用吗？排序列 demo_id 是表的主键，每一行本来就是不一样的，所以即便是以其值的范围开窗也实际上如同是以行本身的范围开窗效果是一样的了。

SQL> SELECT t.demo_id
  2        ,t.category_id
  3        ,t.item_id
  4        ,t.quantity
  5        ,SUM(t.quantity) over(PARTITION BY t.item_id ORDER BY t.demo_id) total_quantity
  6    FROM demo_af t;

   DEMO_ID CATEGORY_ID    ITEM_ID   QUANTITY TOTAL_QUANTITY
---------- ----------- ---------- ---------- --------------
      1000          10          1        100            100
      1001          10          2        300            300
      1002          10          2        500            800
      1003          20          3         10             10
      1004          20          3         30             40
      1005          20          4         50             50

6 rows selected

例 2.3 按 item_id 排列展示，并在各行求累计数量

在分析模式下使用聚合函数时一定要考虑其默认窗口子句的问题。前例虽然省去了窗口子句，但那只是逻辑上我们把它省略的，并不是语法上本身不需要写，或者说并不是 Oracle 能自动理解我们的需求。比如本例声明窗口子句就是很有必要的。

SQL> -- 错误的示范
SQL> SELECT t.demo_id
  2        ,t.category_id
  3        ,t.item_id
  4        ,t.quantity
  5        ,SUM(t.quantity) over(ORDER BY t.item_id) total_quantity
  6    FROM demo_af t;

   DEMO_ID CATEGORY_ID    ITEM_ID   QUANTITY TOTAL_QUANTITY
---------- ----------- ---------- ---------- --------------
      1000          10          1        100            100
      1001          10          2        300            900
      1002          10          2        500            900
      1003          20          3         10            940
      1004          20          3         30            940
      1005          20          4         50            990

6 rows selected

请注意上面的代码是一个错误的示范，它使用了默认的窗口子句，导致在第二行计算累计值时，实际的窗口已经按照 item_id 的值 2，把所有 item_id 小于等于 2 的行都纳入了。后面第四行的情况也是同理的。

SQL> SELECT t.demo_id
  2        ,t.category_id
  3        ,t.item_id
  4        ,t.quantity
  5        ,SUM(t.quantity) over(ORDER BY t.item_id rows BETWEEN unbounded preceding AND CURRENT ROW) total_quantity
  6    FROM demo_af t;

   DEMO_ID CATEGORY_ID    ITEM_ID   QUANTITY TOTAL_QUANTITY
---------- ----------- ---------- ---------- --------------
      1000          10          1        100            100
      1001          10          2        300            400
      1002          10          2        500            900
      1003          20          3         10            910
      1004          20          3         30            940
      1005          20          4         50            990

6 rows selected

除了声明窗口子句，我们还能参照例 2.2 的经验，从逻辑上下手，避免语法规则带来的问题：把唯一的 demo_id 列放在排序子句的第二关键字，既不影响第一关键字 item_id 的排序，又使得各行在排序列值上各不相同（取列值范围即等同于取行序范围）

SQL> SELECT t.demo_id
  2        ,t.category_id
  3        ,t.item_id
  4        ,t.quantity
  5        ,SUM(t.quantity) over(ORDER BY t.item_id, t.demo_id) total_quantity
  6    FROM demo_af t;

   DEMO_ID CATEGORY_ID    ITEM_ID   QUANTITY TOTAL_QUANTITY
---------- ----------- ---------- ---------- --------------
      1000          10          1        100            100
      1001          10          2        300            400
      1002          10          2        500            900
      1003          20          3         10            910
      1004          20          3         30            940
      1005          20          4         50            990

6 rows selected

三、在 GROUP BY 分组的基础上使用分析模式

SELECT 语句中分析函数的执行次序是仅先于 ORDER BY 子句的，而 GROUP BY 分组是在数据筛选的过程中就已经实现，所以在有 GROUP BY 分组基础的 SELECT 语句中，以分析模式运行的聚合函数，已经不同于传统模式的聚合函数。分析模式或者说开窗其本质是跨行引用，所以这里聚合函数的分析模式也是在 GROUP BY 分组后（可以理解为行数已经减少后）的数据集上跨行引用。从而它的参数列，以及分区子句、排序子句和窗口子句中引用的列，都只能是 GROUP BY 分组的列（传统模式聚合函数所聚合的列，不能为分析模式使用）。

SQL> SELECT t.item_id, SUM(t.quantity), SUM(t.quantity) over()
  2    FROM demo_af t
  3   GROUP BY t.item_id;
SELECT t.item_id, SUM(t.quantity), SUM(t.quantity) over()
  FROM demo_af t
 GROUP BY t.item_id

ORA-00979: 不是 GROUP BY 表达式

SQL> SELECT t.item_id, SUM(t.quantity) sum_qty, MAX(t.item_id) over() max_item
  2    FROM demo_af t
  3   GROUP BY t.item_id;

   ITEM_ID    SUM_QTY   MAX_ITEM
---------- ---------- ----------
         1        100          4
         2        800          4
         3         40          4
         4         50          4