liuanxin commented on issue #9346:
URL: https://github.com/apache/doris/issues/9346#issuecomment-1558961369
`count(*)` 会扫描每个聚合 key 导致性能会差一点认了, `count(distinct varchar_column)`
为什么也不支持(居然只能支持数字字段?),
数据会重复导入, `unique` 模型不支持函数, `aggregate` 模型对函数的支持也是一堆限制, 那 `duplicate`
模型要怎么基于重复数据做 `sum(int)` 呢?
2.0 也依然没有解决这些很基础的 sql 问题
```sql
drop table if exists temp;
create table temp (
id varchar(8),
`date` date,
user_id varchar(8),
channel_id varchar(8) replace, /* 如果改成 unique 或 duplicate 模型则去掉这里的
replace */
`amount` int replace
)
aggregate key (id, `date`, user_id) /* unique = aggregate + 上面的 replace */
partition by range(`date`) (
partition p202305 values less than ("2023-06-01")
)
distributed by hash(id) buckets 1
properties (
/* "enable_unique_key_merge_on_write" = "true", -- 如果用 unique key 可以使用 */
"replication_allocation" = "tag.location.default: 1"
);
insert into temp(id, `date`, user_id, channel_id, amount) values
('a', '2023-05-01', 'aa', '123a', '50'),
('a', '2023-05-01', 'aa', '123a', '50'),
('b', '2023-05-01', 'aa', '123b', '10'),
('c', '2023-05-02', 'cc', '123c', '20'),
('d', '2023-05-03', 'dd', '123d', '30');
create materialized view mv1 as /* 如果是 aggregate 或 unique 模型无法建立 */
select `date`, user_id, count(distinct channel_id) /* 只有 duplicate 模型 +
bitmap_union(to_bitmap(col)) 可以, 然而 col 不能是 varchar 这样基础类型 */
from temp
group by `date`, user_id
order by `date` desc, user_id;
/* 报错信息也很奇怪: SQL error(1105): errCode = 2, detailMessage = The function
count must match pattern:count(column) */
create materialized view mv1 as /* 如果是 aggregate 或 unique 模型无法建立 */
select `date`, user_id, sum(amount) /* 如果是 duplicate 模型无法去重 */
from temp
group by `date`, user_id
order by `date` desc, user_id;
```
限制实在是太多了, 我有理由相信, 支持的只有文档中给出来的示例
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
