---
title: 编组
description: 了解OceanBase数据库在实际应用中关于含聚合函数的增量刷新物化视图使用说明相关的常见问题和使用技巧，帮助您快速解决含聚合函数的增量刷新物化视图使用说明的难题。
image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

划线反馈

# 含聚合函数的增量刷新物化视图使用说明

更新时间：2026-05-25 09:11

适用版本： V4.3.x 内容类型：TechNote  

本文主要提供含聚合函数的增量刷新物化视图示例以便于更好地理解。 OceanBase 数据库 V4.3.x 中，根据增量刷新物化视图的增量刷新基本要求，SELECT 子句中除了包含聚合函数列外，还需要包含对应聚合函数的依赖列和 `COUNT(*)` 列，如使用聚合函数 `SUM(expr)`，还需要包含 `COUNT(*)` 以及 `COUNT(expr)` 列。 OceanBase 数据库 V4.3.x 中，增量刷新物化视图中支持的聚合函数及 SELECT 子句中需要增加的列如下表所示。

| **聚合函数** | **SELECT 子句中需要增加的列** |
| --- | --- |
| COUNT(expr) | COUNT(*) |
| SUM(expr) | COUNT(*)，COUNT(expr) |
| AVG(expr) | COUNT(*), COUNT(expr), SUM(expr) |
| STDDEV(expr) | COUNT(*)，COUNT(expr)，SUM(expr)，SUM(expr * expr) |
| VARIANCE(expr) | COUNT(*)，COUNT(expr)，SUM(expr)，SUM(expr * expr) |

## 详细说明

### 测试用例

创建测试表如下。

```shell
CREATE TABLE test_mv_a (tid INT PRIMARY KEY, aid INT);
INSERT INTO test_mv_a(tid,aid) VALUES (1,1);
INSERT INTO test_mv_a(tid,aid) VALUES (2,2);
INSERT INTO test_mv_a(tid,aid) VALUES (3,1);
COMMIT;

```

创建基表的 mlog。

```shell
CREATE MATERIALIZED VIEW LOG ON test_mv_a WITH PRIMARY KEY, ROWID, SEQUENCE (aid) INCLUDING NEW VALUES;

```

### 用法

**注意事项：** 建议聚合列设置别名以方便后续查询。

#### SQL1 - SQL 仅含 COUNT 函数

**SQL 1.1** 聚合函数仅有 `count(*)`。

```shell
SELECT a.aid,count(*) AS count_all FROM test_mv_a a GROUP BY a.aid;

```

函数 `COUNT(*)` 无依赖列，可以直接创建增量刷新物化视图。

```shell
CREATE MATERIALIZED VIEW mv_test_a_count (PRIMARY KEY(aid))
REFRESH FAST
AS
SELECT a.aid,count(*) AS count_all FROM test_mv_a a GROUP BY a.aid;

```

**SQL 1.2** 聚合函数仅有 `COUNT` 函数（如 `count(a.aid)` 或 `count(1)`），但非 `count(*)`。

```shell
SELECT a.aid,count(a.aid) AS count_aid FROM test_mv_a a GROUP BY a.aid;

```

创建**增量刷新物化视图** SQL 如下，SQL 中需要增加 `COUNT(*)` 列。

```shell
CREATE MATERIALIZED VIEW mv_test_a_count (PRIMARY KEY(aid))
REFRESH FAST
AS
SELECT a.aid,count(*) AS count_all,count(a.aid) AS count_aid FROM test_mv_a a GROUP BY a.aid;

```

创建**增量刷新物化视图** SQL 中，如未包括 `count(*)` 列，报错如下。

```shell
MySQL [test]> CREATE MATERIALIZED VIEW mv_test_a_count (PRIMARY KEY(aid))
REFRESH FAST
AS
SELECT a.aid,count(a.aid) AS count_aid FROM test_mv_a a GROUP BY a.aid;
ERROR 9760 (HY000): cannot fast refresh materialized view mv_test_a_count: a count(*) item is required to be added to the select item list

```

#### SQL 2 - SQL 含 SUM 函数

**SQL 2** SQL 含 SUM 函数。

```shell
SELECT a.aid,sum(a.aid) AS sum_aid FROM test_mv_a a GROUP BY a.aid;

```

创建**增量刷新物化视图** SQL 如下，SQL 中需要增加 `COUNT(*)` 以及 `COUNT(a.aid)` 列。

```shell
CREATE MATERIALIZED VIEW mv_test_a_sum (PRIMARY KEY(aid))
REFRESH FAST
AS
SELECT a.aid,count(*) AS count_all,count(a.aid) AS count_aid,sum(a.aid) AS sum_aid FROM test_mv_a a GROUP BY a.aid;

```

创建**增量刷新物化视图** SQL 中，如未包括 SUM 函数依赖的 `count(a.aid)` 列，报错如下。

```shell
MySQL [test]> CREATE MATERIALIZED VIEW mv_test_a_sum (PRIMARY KEY(aid))
REFRESH FAST
AS
SELECT a.aid,count(*) AS count_all,sum(a.aid) AS sum_aid FROM test_mv_a a GROUP BY a.aid;
ERROR 9760 (HY000): cannot fast refresh materialized view mv_test_a_sum: when using sum/avg/stddev/variance functions, a standalone count function of the corresponding column is required in the select item list

```

```shell
MySQL [test]> CREATE MATERIALIZED VIEW mv_test_a_sum (PRIMARY KEY(aid))
REFRESH FAST
AS
SELECT a.aid,sum(a.aid) AS sum_aid FROM test_mv_a a GROUP BY a.aid;
ERROR 9760 (HY000): cannot fast refresh materialized view mv_test_a_sum: a count(*) item is required to be added to the select item list

```

#### SQL 3 - SQL 含 AVG 函数

**SQL 3** SQL 含 AVG 函数。

```shell
SELECT a.aid,avg(a.aid) AS avg_aid FROM test_mv_a a GROUP BY a.aid;

```

创建**增量刷新物化视图** SQL 如下，SQL 中需要增加 `COUNT(*)`、`COUNT(a.aid)` 及 `SUM(a.aid)` 列。

```shell
CREATE MATERIALIZED VIEW mv_test_a_avg (PRIMARY KEY(aid))
REFRESH FAST
AS
SELECT a.aid,count(*) AS count_all,count(a.aid) AS count_aid,sum(a.aid) AS sum_aid,avg(a.aid) AS avg_aid FROM test_mv_a a GROUP BY a.aid;

```

#### SQL 4 - SQL 含 STDDEV 函数

**SQL 4** SQL 含 `STDDEV` 函数。

```shell
SELECT a.aid,stddev(a.aid) AS stddev_aid FROM test_mv_a a GROUP BY a.aid;

```

创建**增量刷新物化视图** SQL 如下，SQL 中需要增加 `COUNT(*)`、`COUNT(a.aid)`、`SUM(a.aid)` 及 `SUM(a.aid*a.aid)` 列。

```shell
CREATE MATERIALIZED VIEW mv_test_a_stddev (PRIMARY KEY(aid))
REFRESH FAST
AS
SELECT a.aid,count(*) AS count_all,count(a.aid) AS count_aid,sum(a.aid) AS sum_aid,sum(a.aid*a.aid) AS sum_square_aid,stddev(a.aid) AS stddev_aid FROM test_mv_a a GROUP BY a.aid;

```

#### SQL 5 - SQL 含 VARIANCE 函数

**SQL 5** SQL 含 `VARIANCE` 函数。

```shell
SELECT a.aid,variance(a.aid) AS variance_aid FROM test_mv_a a GROUP BY a.aid;

```

```shell
CREATE MATERIALIZED VIEW mv_test_a_variance (PRIMARY KEY(aid))
REFRESH FAST
AS
SELECT a.aid,count(*) AS count_all,count(a.aid) AS count_aid,sum(a.aid) AS sum_aid,sum(a.aid*a.aid) AS sum_square_aid,variance(a.aid) AS variance_aid FROM test_mv_a a GROUP BY a.aid;

```

## 适用版本

OceanBase 数据库 V4.3.x 版本。

上一篇

[含 LEFT JOIN 增量刷新物化视图使用说明](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003717605)

下一篇

[CDB_OB_TABLE_SPACE_USAGE 查询的 OCCUPY_SIZE 比 REQUIRED_SIZE 大](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003269522) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线