南大通用GBase 8a OLAP函数移动平均值AVG，移动汇总SUM，移动计数count over等使用样例

本文介绍GBase 8a数据库集群里，对移动平均AVG OVER，移动计数COUNT OVER，移动求和AVG OVER的使用样例。

如果需要限制窗口的大小，需要设置order部分的row 或者range部分。GBase 8a开窗函数子句rows、range的使用方法

参考

GBase 8a窗口函数使用样例索引
GBase 8a开窗函数子句rows、range的使用方法

样例数据

表结构

gbase> desc tt2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| j     | int(11) | YES  |     | NULL    |       |
| k     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)

数据

gbase> select * from tt2;
+------+------+------+
| i    | j    | k    |
+------+------+------+
|    2 |    1 |    4 |
|    2 |    3 |    6 |
|    2 |    3 |    4 |
|    2 |    5 |    8 |
|    3 |    2 |    2 |
|    3 |    2 |    4 |
|    3 |    2 |    2 |
|    3 |    4 |    6 |
|    3 |    1 |    2 |
|    3 |    5 |    8 |
+------+------+------+
10 rows in set (Elapsed: 00:00:00.01)

移动计数 COUNT OVER

语法

COUNT(*/[DISTINCT] col ) OVER(
    [PARTITION BY col_name1,col_name2,…]
 
    [ORDER BY col_name1 [ASC/DESC], col_name2 [ASC/DESC],…]
)

说明

该函数用于计算分组中的记录数，如果是 COUNT(*)，不用考虑 NULL 值，否则，不包含参数为 NULL 的记录，如果包含 DISTINCT，要做去重操作。

样例

本例中，对i列做分区partition, 对j列做排序，对k列做count。可以看到当排序的位置相同时，计数值是以整体累加的，和rank的效果类似。

gbase> SELECT *,COUNT(k) OVER(PARTITION BY i ORDER BY j DESC) AS sum FROM tt2;
+------+------+------+-----+
| i    | j    | k    | sum |
+------+------+------+-----+
|    2 |    5 |    8 |   1 |
|    2 |    3 |    4 |   3 |
|    2 |    3 |    6 |   3 |
|    2 |    1 |    4 |   4 |
|    3 |    5 |    8 |   1 |
|    3 |    4 |    6 |   2 |
|    3 |    2 |    2 |   5 |
|    3 |    2 |    4 |   5 |
|    3 |    2 |    2 |   5 |
|    3 |    1 |    2 |   6 |
+------+------+------+-----+
10 rows in set (Elapsed: 00:00:00.06)

移动总计SUM OVER

语法

SUM([DISTINCT/ALL] expr) OVER(
    [PARTITION BY … ] 
    [ORDER BY …
 [ASC/DESC] ]
)

说明

计算组内表达式的移动累加和。

样例

用例分析：首先会根据 i 分组，在同组内，根据 j 降序排列，从每组的第一个值开始向后累加 k 值，相同的 j 值，对应的累加和相同，都是加到最后一个 j 值对应的 k 值，如果遇到不同组，从 0 开始重新累加。

NULL 值的处理方式同聚合函数 sum 类似，如果全为 NULL 值，则结果为 NULL，否则 NULL 不进行累加。

gbase> SELECT *,sum(k) OVER(PARTITION BY i ORDER BY j DESC) AS sum FROM tt2;
+------+------+------+------+
| i    | j    | k    | sum  |
+------+------+------+------+
|    2 |    5 |    8 |    8 |
|    2 |    3 |    4 |   18 |
|    2 |    3 |    6 |   18 |
|    2 |    1 |    4 |   22 |
|    3 |    5 |    8 |    8 |
|    3 |    4 |    6 |   14 |
|    3 |    2 |    2 |   22 |
|    3 |    2 |    4 |   22 |
|    3 |    2 |    2 |   22 |
|    3 |    1 |    2 |   24 |
+------+------+------+------+
10 rows in set (Elapsed: 00:00:00.04)

移动平均 AVG OVER

语法

AVG([DISTINCT/ALL] expr) OVER(
    [PARTITION BY … ] 
    [ORDER BY …
 [ASC/DESC] ]
)

说明

计算组内表达式的移动平均值。

样例

用例分析：首先会根据 i 分组，在同组内，根据 j 降序排列，从每组的第一个值开始向后累加 k 值，同时记录 count(k)的值，相同的 j 值，对应的累加和、count值相同，都是计算到最后一个 j 值对应的 k 值，如果遇到不同组，从 0 开始重新累加，最后用累加和除以 count 值则是最后的 avg 值。

NULL 值的处理方式同聚合函数 avg 类似，如果全为 NULL 值，则结果为 NULL，否则 NULL 不进行累加，也不计算在 avg内。

gbase> SELECT *,avg(k) OVER(PARTITION BY i ORDER BY j DESC) AS sum FROM tt2;
+------+------+------+--------+
| i    | j    | k    | sum    |
+------+------+------+--------+
|    2 |    5 |    8 | 8.0000 |
|    2 |    3 |    4 | 6.0000 |
|    2 |    3 |    6 | 6.0000 |
|    2 |    1 |    4 | 5.5000 |
|    3 |    5 |    8 | 8.0000 |
|    3 |    4 |    6 | 7.0000 |
|    3 |    2 |    2 | 4.4000 |
|    3 |    2 |    4 | 4.4000 |
|    3 |    2 |    2 | 4.4000 |
|    3 |    1 |    2 | 4.0000 |
+------+------+------+--------+
10 rows in set (Elapsed: 00:00:00.05)

参考

其它OLAP函数请参考
GBase 8a开窗函数子句rows、range的使用方法