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

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;

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

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

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;

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

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;

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

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

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;

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

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

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

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;

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

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;

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

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;