doris版本:2.1.2
https://doris.apache.org/zh-CN/docs/table-design/data-model/tips#%E6%A8%A1%E5%9E%8B%E9%80%89%E6%8B%A9%E5%BB%BA%E8%AE%AE
这个文档中 模型选择建议 部分 特别指出 Duplicate表不支持预聚合。此处的预聚合是否指物化视图。
经过测试, 发现如下用例可以通过:
CREATE TABLE IF NOT EXISTS demo_dnp
(
`timestamp` DATETIME NOT NULL COMMENT "日志时间",
`click_id` varchar(60) NOT NULL,
`country` varchar(20) NOT NULL,
`ad_key` varchar(200) NOT NULL,
`ltv` int default 0,
`flag_1` int default 1
)
DISTRIBUTED BY HASH(`click_id`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1"
);
create materialized view country_ltv_cnt as select country, sum(ltv), count(1) from demo_dnp group by country
建表后 创建物化视图返回正常。
+-----------------+---------------+------------------------------------------------+----------------+----------------+------+-------+---------+-------+---------+---------------------------------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+-----------------+---------------+------------------------------------------------+----------------+----------------+------+-------+---------+-------+---------+---------------------------------------+-------------+
| demo_dnp | DUP_KEYS | timestamp | DATETIME | DATETIMEV2(0) | No | true | NULL | | true | | |
| | | click_id | VARCHAR(60) | VARCHAR(60) | No | true | NULL | | true | | |
| | | country | VARCHAR(20) | VARCHAR(20) | No | false | NULL | NONE | true | | |
| | | ad_key | VARCHAR(200) | VARCHAR(200) | No | false | NULL | NONE | true | | |
| | | ltv | INT | INT | Yes | false | 0 | NONE | true | | |
| | | flag_1 | INT | INT | Yes | false | 1 | NONE | true | | |
| | | | | | | | | | | | |
| country_ltv_cnt | AGG_KEYS | mv_country | VARCHAR(65533) | VARCHAR(65533) | No | true | NULL | | true | `country` | |
| | | mva_SUM__CAST(`ltv` AS BIGINT) | BIGINT | BIGINT | Yes | false | NULL | SUM | true | CAST(`ltv` AS BIGINT) | |
| | | mva_SUM__CASE WHEN 1 IS NULL THEN 0 ELSE 1 END | BIGINT | BIGINT | No | false | NULL | SUM | true | CASE WHEN 1 IS NULL THEN 0 ELSE 1 END | |
+-----------------+---------------+------------------------------------------------+----------------+----------------+------+-------+---------+-------+---------+---------------------------------------+-------------+
此处的预聚合是否不是指 物化视图这类特性呢,应该是指什么?