南大通用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的使用方法