在ClickHouse中使用窗口函数计算带上限的累计求和

要实现你描述的sumWithCeil功能（累计求和不超过指定上限，且达到上限后不再继续累加），直接用sum(least(probability, 1)) over (...)或者least(sum(probability) over (...), 1)都不行——前者是先对单个值取最小再累加，后者只是对当前累计值做截断，但后续行仍会继续累加超出上限的部分再截断，都达不到你要的效果。

方法一：用数组函数实现（兼容所有ClickHouse版本） #

通过数组批量处理每个分组的累计值，确保一旦累计到上限，后续所有值都保持上限：

SELECT 
    groupId,
    probability,
    datetime,
    runningSum,
    desiredSum
FROM (
    SELECT 
        groupId,
        -- 计算原始累计和数组
        arrayCumSum(probability) AS runningSumArray,
        -- 生成带上限的累计数组：前一个累计值+当前值，超过上限就固定为1
        arrayMap(
            (prev_sum, curr_val) -> least(prev_sum + curr_val, 1),
            -- 在累计数组前补0，对应第一个值的前序累计为0
            arrayPushFront(arrayCumSum(probability), 0),
            probability
        ) AS desiredSumArray,
        probability,
        datetime,
        -- 生成索引用于后续展开数组
        arrayEnumerate(probability) AS idx
    FROM myTable
    GROUP BY groupId
    ORDER BY groupId, datetime ASC
)
-- 展开数组，还原成每行数据
ARRAY JOIN 
    runningSumArray AS runningSum,
    desiredSumArray AS desiredSum,
    idx
ORDER BY groupId, datetime ASC

方法二：用自定义聚合函数+状态窗口函数（ClickHouse 21.8+） #

如果你的ClickHouse版本够新（21.8及以上），可以自定义一个聚合函数，结合runningAccumulate来模拟你想要的sumWithCeil：

首先创建自定义聚合函数：

CREATE AGGREGATE FUNCTION sum_with_ceil(max_value Float64)
RETURNS Float64
AGGREGATES (state Float64)
INITIALIZE WITH 0
UPDATE WITH (state + value) -> least(state + value, max_value)
MERGE WITH (a, b) -> least(a + b, max_value)
FINALIZE WITH state;

然后直接在窗口函数中调用：

SELECT 
    groupId,
    value AS probability,
    datetime,
    sum(probability) OVER (PARTITION BY groupId ORDER BY datetime ASC) AS runningSum,
    runningAccumulate(sum_with_ceil(1))(probability) OVER (PARTITION BY groupId ORDER BY datetime ASC) AS desiredSum
FROM myTable t;

两种方法的区别 #

• 方法一不用依赖高版本，逻辑直观，适合所有环境；
• 方法二的调用方式更贴近你虚构的sumWithCeil，代码更简洁，但要求ClickHouse版本支持自定义聚合函数。

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