无法将UInt64转换为AggregateFunction(avg，UInt64)

Question

我是ClickHouse DB的初学者。我通过以下步骤创建一个聚合表：

create table tb_test (
`id` String,
`uid` String,
`x` SimpleAggregateFunction(sum, UInt64),
`y` SimpleAggregateFunction(sum, UInt64),
`z` AggregateFunction(avg, UInt64)
)  ENGINE = AggregatingMergeTree()
ORDER BY (id,uid)

但是当我试图通过insert into tb_test values ('a','b',1,1,1)插入它时，它给了我一个错误：

代码: 53.DB::Exception:无法将UInt64转换为AggregateFunction(avg，UInt64)：同时执行ValuesBlockInputFormat。(TYPE_MISMATCH) (22.2.2.1版)

插入这张桌子的正确方法是什么？

Answer 1

它需要使用查询：

INSERT INTO tb_test SELECT
    'a',
    'b',
    1,
    1,
    avgState(toUInt64(1))

Answer 2

输入函数https://clickhouse.com/docs/en/sql-reference/table-functions/input/

INSERT INTO tb_test 
SELECT id,uid,sum(x), sum(y), avgState(z) 
FROM input('id String, uid String, x UInt64, y UInt64, z UInt64') 
group by id, uid 
format Values ('a','b',1,1,1);

select id, uid, x, y, finalizeAggregation(z) z from tb_test;
┌─id─┬─uid─┬─x─┬─y─┬─z─┐
│ a  │ b   │ 1 │ 1 │ 1 │
└────┴─────┴───┴───┴───┘

发动机空载https://clickhouse.com/docs/en/engines/table-engines/special/null

create table N (id String, uid String, x UInt64, y UInt64, z UInt64) 
Engine Null;

create materialized view mvN to tb_test as
SELECT id,uid,sum(x) x, sum(y) y, avgState(z) z
from N
group by id, uid;

insert into N values ('a','b',1,1,1);

select id, uid, x, y, finalizeAggregation(z) z from tb_test;
┌─id─┬─uid─┬─x─┬─y─┬─z─┐
│ a  │ b   │ 1 │ 1 │ 1 │
└────┴─────┴───┴───┴───┘
┌─id─┬─uid─┬─x─┬─y─┬─z─┐
│ a  │ b   │ 1 │ 1 │ 1 │
└────┴─────┴───┴───┴───┘

optimize table tb_test final;

select id, uid, x, y, finalizeAggregation(z) z from tb_test;
┌─id─┬─uid─┬─x─┬─y─┬─z─┐
│ a  │ b   │ 2 │ 2 │ 1 │
└────┴─────┴───┴───┴───┘