How to cast `AggregateFunction(sumForEach, Array(UInt32))` i

Question

我想用新表替换表，以便类型为AggregateFunction(sumForEach, Array(UInt32))的列将成为AggregateFunction(sumForEach, Array(Int32))。但我有一个问题，而移动数据:DB::Exception: Conversion from AggregateFunction(sumForEach, Array(UInt32)) to AggregateFunction(sumForEach, Array(Int32)) is not supported: while converting source column valuesCol to destination column valuesCol.我应该做些什么来克服这个限制?

UPD: Tables areDistributedoverReplicatedSummingMergeTree.

Answer 1

尝试

• 将中间状态转换为值
• 更改值类型
• 将新值转换为中间状态

SELECT
sumForEachState(new_value) AS new_state,
toTypeName(new_state) AS type_name
FROM 
(
SELECT
sumForEachState(cast([1, 2], 'Array(UInt32)')) AS origin_state,
finalizeAggregation(origin_state) AS origin_value,
cast(origin_value, 'Array(Int32)') AS new_value
)
/*
┌─new_state─┬─type_name───────────────────────────────────┐
│           │ AggregateFunction(sumForEach, Array(Int32)) │
└───────────┴─────────────────────────────────────────────┘
*/

Answer 2

INSERT INTO newTable (
otherColumns,
valuesCol
)
SELECT
otherColumns,
arrayReduce(
'sumForEachState',
[cast(finalizeAggregation(viewMapAbsolutes), 'Array(Int32)')]
) AS valuesCol
FROM oldTable;