更新日期:2024年09月11日
语法
COUNT(*/[DISTINCT] col ) OVER([PARTITION BY col_name1,col_name2,…] [ORDER BY co
l_name1 [ASC/DESC], col_name2 [ASC/DESC],…] )
功能描述
该函数用于计算分组中的记录数,如果是COUNT(*),不用考虑NULL 值,否则,
不包含参数为NULL 的记录,如果包含DISTINCT,要做去重操作。
示例
示例1:COUNT OVER 函数示例。
gbase> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected
GBase UP 产品手册 5 数据库管理指南
文档版本04(2021-04-21) 南大通用数据技术股份有限公司 586
gbase> CREATE TABLE t2(i int,j int,k int);
Query OK, 0 rows affected
gbase> INSERT INTO t2 VALUES(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);
Query OK, 10 rows affected
Records: 10 Duplicates: 0 Warnings: 0
gbase> SELECT *,COUNT(k) OVER(PARTITION BY i ORDER BY j DESC) AS sum FRO
M t2;
+------+------+------+-----+
| 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
gbase> SELECT *,COUNT(DISTINCT k) OVER(PARTITION BY i) AS sum FROM t2;
+------+------+------+-----+
| i | j | k | sum |
+------+------+------+-----+
| 2 | 3 | 6 | 3 |
| 2 | 3 | 4 | 3 |
| 2 | 5 | 8 | 3 |
| 2 | 1 | 4 | 3 |
| 3 | 2 | 2 | 4 |
| 3 | 2 | 4 | 4 |
| 3 | 2 | 2 | 4 |
| 3 | 4 | 6 | 4 |
| 3 | 1 | 2 | 4 |
| 3 | 5 | 8 | 4 |
+------+------+------+-----+
10 rows in set
GBase UP 产品手册 5 数据库管理指南
文档版本04(2021-04-21) 南大通用数据技术股份有限公司 587