Clickhouse按CustomerID汇总PurchaseAmount的数组求和查询问题

可以通过arrayMap结合arraySum和arrayFilter实现按每个CustomerID汇总对应的PurchaseAmount，核心逻辑是遍历每个客户ID的下标，筛选出对应匹配的消费金额并求和。

具体查询语句如下：

SELECT
    CustomerID,
    arrayMap(
        idx -> arraySum(
            arrayFilter(
                (pIdx, pAmt) -> pIdx = idx,
                PurchaseIndex,
                PurchaseAmount
            )
        ),
        CustomerIndex
    ) AS TotalPurchaseAmount
FROM (
    SELECT 
        [325, 516, 343, 539] as CustomerID,
        arrayEnumerate(CustomerID) as CustomerIndex,
        [1,2,3,4,1,2,3,3,4,1,1,2,3] as PurchaseIndex,
        [200, 100, 200, 200, 0, 500, 100, 900, 100, 200, 0, 150, 350] as PurchaseAmount
)

语句说明： #

1. arrayEnumerate(CustomerID)：生成客户ID的下标数组[1,2,3,4]，对应每个CustomerID的位置序号。
2. arrayMap遍历CustomerIndex：对每个客户下标idx，执行后续的金额求和操作。
3. arrayFilter：筛选PurchaseIndex中等于当前idx的元素，同时保留对应的PurchaseAmount值。
4. arraySum：将筛选后的金额数组求和，得到该客户的总消费金额。

执行后会得到期望结果：CustomerID数组对应TotalPurchaseAmount数组[400, 750, 1550, 300]。

另外也可以用arrayJoin展开数组后聚合，逻辑更直观：

SELECT
    CustomerID,
    sum(PurchaseAmount) AS TotalPurchaseAmount
FROM (
    SELECT 
        [325, 516, 343, 539] as CustomerID,
        [1,2,3,4,1,2,3,3,4,1,1,2,3] as PurchaseIndex,
        [200, 100, 200, 200, 0, 500, 100, 900, 100, 200, 0, 150, 350] as PurchaseAmount
)
ARRAY JOIN
    arrayElement(CustomerID, PurchaseIndex) AS CustomerID,
    PurchaseAmount
GROUP BY CustomerID
ORDER BY CustomerID

这种方式先通过arrayElement根据PurchaseIndex匹配对应的CustomerID，再展开数组分组求和，最终得到每个客户ID对应的总金额。

内容的提问来源于stack exchange，提问作者MolbosEel