本文介绍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)