# 创建分区物化视图
DWS提供多种场景的分区物化视图创建：
- 等比例对齐分区，物化视图分区条件与基表的分区条件一致。
- 时间粒度上卷对齐分区，例如：基表按天分区，物化视图按月分区。
- 多基表对齐分区，多个基表的分区条件完全一致时，都会自动和物化视图进行映射。
- 基表为多分区键时，物化视图选第一个键为分区键创建物化视图。
#### 语法
```
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name
[ ( column_name [, ...] ) ]
[ BUILD { DEFERRED | IMMEDIATE } ]
[ REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ]
[ { ENABLE | DISABLE } QUERY REWRITE ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ]
[ PARTITION BY {(column_name) | date_trunc('time_unit', column_name)} ]
AS query;
```
#### 使用约束
- 必须是range分区方式。
- 物化视图只支持单分区键且为基表第一个分区键，基表可以是组合分区键。
- 上卷方式，只支持时间类型且用date_trunc进行上卷计算，仅支持日，月，季，年的上卷。
- 不支持Outer join中会产生空值的列做分区键的情况。
- 不支持mysql兼容模式下的disable full group by。
- 指定分区的查询不支持重写。
- 分区物化视图基表不支持SPLIT，MERGE。
- 如果union的两边都满足物化视图的分区约束检测，则创建多表绑定的物化视图。
- 使用JOIN 连接基表，并将分区键设置为公共列，并且连接的基表满足物化视图的分区约束检测，则创建多表绑定的物化视图。
- 不支持查询语句中含有子链接。
- 不支持查询语句中含有子计划。
- 不支持DML和DDL修改数据，只能靠刷新更新数据。
 
#### 场景示例
创建事实表fact_table和fact_table1。
```
CREATE TABLE fact_table (start_time TIMESTAMPTZ NOT NULL, id int)PARTITION BY RANGE(start_time)(
PARTITION p1 VALUES LESS THAN ('2024-01-01'),
PARTITION p2 VALUES LESS THAN ('2024-01-02'),
PARTITION p3 VALUES LESS THAN ('2024-02-03'),
PARTITION p4 VALUES LESS THAN ('2024-03-01'),
PARTITION p5 VALUES LESS THAN ('2024-03-21'),
PARTITION p6 VALUES LESS THAN ('2024-04-02'));
```
```
CREATE TABLE fact_table1 (start_time TIMESTAMPTZ NOT NULL, id int)PARTITION BY RANGE(start_time)(
PARTITION p1 VALUES LESS THAN ('2024-01-01'),
PARTITION p2 VALUES LESS THAN ('2024-01-02'),
PARTITION p3 VALUES LESS THAN ('2024-02-03'),
PARTITION p4 VALUES LESS THAN ('2024-03-01'),
PARTITION p5 VALUES LESS THAN ('2024-03-21'),
PARTITION p6 VALUES LESS THAN ('2024-04-02'));
```
创建维度表dimen_table。
```
CREATE TABLE dimen_table (start_time TIMESTAMPTZ NOT NULL, id INT);
```
**场景一：等比例对齐分区。**
创建物化视图level_normal，它的分区与fact_table表分区，分区条件完全一样。对数据类型无特殊要求。
```
CREATE MATERIALIZED VIEW level_normal enable query rewrite 
DISTRIBUTE BY HASH(start_time)
PARTITION BY start_time 
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;
```
![](https://support.huaweicloud.com/devg-911-dws/figure/zh-cn_image_0000002202095325.png "点击放大")
**场景二：时间粒度上卷对齐分区。**
通过在分区键上使用date_trunc参数，创建一个分区时间比基表更粗粒度的物化视图。当检测到基表分区中的数据变更后，DWS数据库将会刷新物化视图中对应的上卷分区。
通过date_trunc('time_unit', column_name)参数指定上卷，物化视图分区范围大于基表分区，比如：基表按天分区，物化视图按月分区。一般通过date_trunc参数，实现时间的向上截取。支持上卷的时间单位：年，季，月，日。数据类型：只支持time, timestamp, timestamptz类型。
```
CREATE MATERIALIZED VIEW level_month enable query rewrite 
DISTRIBUTE BY HASH(start_time)
PARTITION BY date_trunc('month', start_time)
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;
```
![](https://support.huaweicloud.com/devg-911-dws/figure/zh-cn_image_0000002166614600.png "点击放大")
**场景三：多基表对齐分区。**
当多个基表分区键的种类和分区范围一致，即基表使用相同的分区键，可以创建多基表绑定的物化视图。
```
CREATE MATERIALIZED VIEW level_normal enable query rewrite
DISTRIBUTE BY HASH(start_time) 
PARTITION BY start_time
AS SELECT start_time, id FROM fact_table union SELECT start_time, id FROM fact_table1;
```
```
CREATE MATERIALIZED VIEW level_normal enable query rewrite
DISTRIBUTE BY HASH(start_time) 
PARTITION BY start_time
AS SELECT fact_table1.start_time, fact_table.id, fact_table1.id as id1
FROM fact_table inner join fact_table1 on fact_table.start_time=fact_table1.start_time;
```
![](https://support.huaweicloud.com/devg-911-dws/figure/zh-cn_image_0000002166774284.png "点击放大")
**场景四：基表为多分区键时，支持以基表的第一个分区键创建物化视图。**
```
CREATE TABLE IF NOT EXISTS  multi_time_base (
    new_start_time timestamp with time zone NOT NULL,
    id int,
    v1 int
) PARTITION BY RANGE(new_start_time,id) (
    PARTITION p1 VALUES LESS THAN ('2022-01-01',400),
    PARTITION p2 VALUES LESS THAN ('2022-01-05',400),
    PARTITION p3 VALUES LESS THAN ('2022-01-05',500),
    PARTITION p4 VALUES LESS THAN ('2022-02-15',500),
    PARTITION p5 VALUES LESS THAN ('2022-02-16',500),
    PARTITION p6 VALUES LESS THAN ('2022-03-10',500)
); 
CREATE MATERIALIZED VIEW multi_time_mv
PARTITION BY (date_trunc('month', new_start_time))
AS SELECT new_start_time,id FROM multi_time_base;
```