OLAP window 聚集函数

从查询的结果返回聚集结果的几个函数，诸如总和和平均值，还可用作来自 OLAP window 的上下文的 OLAP 函数。

语法

Window 聚集函数

元素	描述	限制	语法
`column_expr`	聚集函数的列表达式参数	请参阅下面的单独函数的标题	标识符
`alias, synonym, table, view`	包含 `column` 的同义词、表、视图或别名	`Synonym` 以及它指向的 `table` 或 `view` 必须存在	标识符

用法

这些聚集函数不要求 OLAP window 从查询结果集计算聚集值。然而，在调用的上下文中它们的行为像 OLAP window 聚集函数一样，在此，函数表达式中的 OVER 子句定义一个或多个 window 分区，或包括 window ORDER 子句和 window Frame 子句。

重要：当 DISTINCT 或 UNIQUE 关键字是 window 聚集函数规范的一部分时，window 聚集表达式的 OVER 子句不可包括 Window ORDER 子句或 Window Frame 子句。

下列聚集函数可返回关于 OLAP window 分区中的行的信息。

AVG 函数

对于在 OVER 子句中定义的每一分区中的行，AVG 函数返回在查询结果的 window 分区中指定的列或表达式中所有值的平均值。如果 OVER 子句包括 Window Frame 子句，则 AVG 为 window frame 中的每一组行返回一个值。

您仅可将 AVG 函数应用于数值数据类型的列。如果您使用 DISTINCT（或 UNIQUE）关键字，则仅从指定的列或表达式中 distinct 值计算平均值（含义为平均），且 OVER 子句不可包括 Window ORDER 或 Window Frame 子句。

忽略 NULL 值，除非该列或表达式中的每个值都是 NULL。如果每个值都是 NULL。则 AVG 函数为那个列或表达式返回 NULL。

您不可以非数值的列或表达式使用 AVG 函数。

COUNT 函数

对于在 OVER 子句中定义的每一分区，COUNT 函数返回查询结果的 window 分区中指定的列或表达式中非 NULL 值的基数。如果该 OVER 子句包括 Window Frame 子句，则 COUNT 为 window frame 中的每一组行返回一个值。

如果您使用 DISTINCT（或 UNIQUE）关键字，则仅从指定的列或表达式中 distinct 值计算该分区中行的基数，且 OVER 子句不可包括 Window ORDER 或 Window Frame 子句。

忽略 NULL 值，除非该列或表达式中的每个值都是 NULL。如果每个值都是 NULL，则 COUNT 函数为那个列或表达式返回 NULL。

MAX 函数

对于 OLAP window OVER 子句中定义的每一分区中的行，MAX 函数返回查询结果的 window 分区中列或表达式中的最大值。

指定 DISTINCT 或 UNIQUE 关键字，对结果没有影响，但（同其他内建的聚集函数一样）不允许 Window ORDER 或 Window Frame 子句。

如果 OVER 子句包括 Window Frame 子句，则 MAX 为 window frame 中每一组行返回一个值。

当指定列表达式作为 COUNT 的参数时，忽略 NULL 值，除非指定的列表达式中每个值都是 NULL。如果每个值都是 NULL，则 MAX 为那个列或表达式返回 NULL 值。当指定 COUNT(*) 时，如同其他值一样，对 NULL 值计数。

MIN 函数

对于 OLAP window OVER 子句定义的每一分区，MIN 函数返回查询结果的 window 分区中列或表达式中的最小值。如果 OVER 子句包括 Window Frame 子句，则 MIN 函数为每一组行返回一个值。

指定 DISTINCT 或 UNIQUE 关键字，对结果没有影响。但（同其他内建的聚集函数一样）不允许 Window ORDER 或 window Frame 子句。

如果 OVER 子句包括 Window Frame 子句，则 MIN 为 window frame 中的每一组行返回一个值。

忽略 NULL 值，除非指定列表达式中的每个值都是 NULL。如果每个值都是 NULL，则 MIN 为那个列表达式返回 NULL 值。

RANGE 函数

对于 OLAP window OVER 子句定义的每一分区，RANGE 函数返回查询结果的 window 分区中列或表达式中值的范围。如果 OVER 子句包括 Window Frame 子句，则 RANGE 为 window frame 中每一组行返回一个值。

RANGE 函数计算最大值与最小值之间的差，如下：

 range(expr) = max(expr) - min(expr)

您仅可将 RANGE 函数应用于数值的列表达式。下列查询找到人口的年龄的范围：

SELECT RANGE(age) OVER () FROM u_pop;

由于 DATE 值在内部作为整数保存，因此您可对 DATE 列使用 RANGE 函数。使用 DATE 列，返回值是该列表达式中最早日期与最晚日期之间的天数。

忽略 NULL 值，除非列表达式中的每个值都是 NULL。如果每个列表达式值都是 NULL，则 RANGE 函数为那个列表达式返回 NULL。

STDEV 函数

STDEV 函数返回列或表达式的标准差，使用下列公式：

SQRT((SUM(X_i²) - (SUM(X_i)²)/N)/(N - 1))

在此公式中，X_i 是 OVER 子句指定的 window 分区或 frame 中每一列表达式值，N 是列表达式中非 NULL 值的总数。

如果 OVER 子句包括 Window Frame 子句，则 STDEV 函数为 window frame 中每一组行返回一个值。

忽略 NULL 值，除非指定的列表达式中每个值都是 NULL。如果每个列表达式都是 NULL，则 STDEV 函数为那个列表达式返回 NULL。

您仅可对数值的列表达式应用 STDEV 函数。您不可对 DATE 类型的列表达式使用此函数。

SUM 函数

对于 OLAP window OVER 子句中定义的每一分区中的行，SUM 函数计算并返回查询结果的 window 分区中列表达式的所有值的总和。

如果您使用 DISTINCT（或 UNIQUE）关键字作为参数列表中的第一项，则该总和仅针对于列或表达式中的 distinct 值，且不允许 Window ORDER 或 Window Frame 子句。

忽略 NULL 值，除非每个值都是 NULL。如果每个值都是 NULL，则 SUM 函数为那个列或表达式返回 NULL 值。

您不可以非数值的列或表达式使用 SUM 函数。

VARIANCE 函数

对于跟在 OLAP window VARIANCE 表达式之后的 OVER 子句中定义的查询结果的分区，VARIANCE 函数计算并返回均方差作为对指定的数值列或表达式中值的总体方差的估算。

如果 OVER 子句包括 Window Frame 子句，VARIANCE 函数为每一组行返回一个值，使用下列公式：

(SUM(X_i²) - (SUM(X_i)²)/N)/(N - 1)

在此公式中，

X_i 是 OVER 子句指定的 window 分区或 frame 中每一列值，
N 是该列中非 NULL 值的合计数（除非所有值都是 NULL，在此情况下未逻辑地定义该方差，且 VARIANCE 函数返回 NULL）。

您仅可对数值的列应用 VARIANCE 函数。

示例：带有分区的 AVG 函数

在下列示例中，在 OLAP window 聚集表达式中使用 AVG 函数来返回两个 window 分区在 2012 年期间的移动平均数 closeprice 列值，基于 symbol 列中的 ABC 和 XYZ 值作为分区键。

   SELECT symbol, tradingdate,
     AVG(closeprice) OVER (PARTITION BY symbol
       ORDER BY tradingdate
     ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
   FROM dailystockdata
   WHERE symbol IN ('ABC', 'XYZ')
     AND tradingdate BETWEEN '2012-01-01' AND '2012-12-31';

window ORDER 子句指定 tradingdate 列值作为排序键，且 window Frame 子句定义基于 30 个连续的 tradingdate 值的移动 window，以当前行的 tradingdate 结束。

示例：不带有分区的 AVG

下列查询返回按天排序的存货价格以及当前天、前一天和后一天的平均价格。由于该查询未包括 PARTITION BY 子句，因此结果集不分区。

SELECT price,  
       AVG(price) OVER (ORDER BY tradingday 
                 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM stock_price
WHERE tradingday BETWEEN '2012-11-01' AND '2012-11-07';

         price          (avg)
     18.25     18.31
     18.37     18.31
               18.37
               19.03
     19.03     18.81
     18.59     18.61
     18.21     18.40

avg 列中的第一个值是 price 列中前两个值的平均值，因为对于 price 列的第一个值没有前面的值。

avg 列中的第二个值是 price 列中前两个值的平均值，因为 price 列的第三行没有值。

avg 列中的第三个值等于 price 列中的第二个值，因为 price 列中的第三行和第四行没有值。

示例：COUNT 函数

下列查询返回装运日期、装运费用和按客户排列的每一订单的订单数目。按客户编号对查询结果分区，并限定客户编号小于或等于 110。

SELECT customer_num, ship_date, ship_charge,
       COUNT(*) OVER (PARTITION BY customer_num) 
FROM orders 
WHERE customer_num <= 110;

customer_num ship_date  ship_charge       (count(*)) 

         101 05/26/2008      $15.30                1
         104 05/23/2008      $10.80                4
         104 07/03/2008       $5.00                4
         104 06/01/2008      $10.00                4
         104 07/10/2008      $12.20                4
         106 05/30/2008      $19.20                2
         106 07/03/2008      $12.30                2
         110 07/06/2008      $13.80                2
         110 07/16/2008       $6.30                2

客户 104 在列表中出现四次。客户 104 在 count 列中的值始终是 4。