语法参考

创建普通物化视图

基于SQL查询创建物化视图：

CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name
[(<col_name> [COMMENT <col_comment>], ...)]
[COMMENT '...']
[PARTITIONED BY col1, col2, ...]
[ CLUSTERED BY (col_name3, col_name4, ...)
[ SORTED BY (col_name [ ASC | DESC], ...)]
INTO num_buckets BUCKETS]
[TBLPROPERTIES ('key'='value', ...)]
AS <SELECT query>;

样例：

CREATE MATERIALIZED VIEW mv_partitioned
PARTITIONED BY (region, product)
AS SELECT region, product, SUM(amount) AS total
FROM sales GROUP BY region, product

CREATE MATERIALIZED VIEW mv_sorted_bucketed
CLUSTERED BY (id) SORTED BY (amount ASC) INTO 2 BUCKETS
AS SELECT * FROM sales

CREATE MATERIALIZED VIEW mv_sales_with_product AS
SELECT s.region, p.product_name, SUM(s.amount) AS total_amount
FROM sales s
INNER JOIN products p ON s.product_id = p.product_id
GROUP BY s.region, p.product_name

删除物化视图

DROP MATERIALIZED VIEW [IF EXISTS] mv_revenue_per_customer;

样例：

DROP MATERIALIZED VIEW mv_sales

查询物化视图
 查询当前数据库中的所有物化视图
```
SHOW MATERIALIZED VIEWS;
```
查询指定数据库中的所有物化视图
```
SHOW MATERIALIZED VIEWS FROM example_db;
```
查询符合特定模式的物化视图
```
SHOW MATERIALIZED VIEWS LIKE 'mv_*';
```
刷新物化视图
 从基表中重新计算物化视图的数据。
```
REFRESH MATERIALIZED VIEW mv_revenue_per_customer;
```