ClickHouse中如何基于自定义条件拆分数组

数组拆分核心实现 #

该逻辑不需要写自定义函数，用ClickHouse原生数组函数即可高性能实现，实现逻辑共3步：

• 遍历数组标记所有拆分点，相邻元素满足后值<前值时标记为拆分位置
• 对拆分标记做累计求和，给每个元素分配所属的连续分段ID，遇到拆分点分段ID自增
• 按分段ID聚合元素，输出拆分后的二维数组

可直接运行的示例代码如下：

WITH
    [100, 200, 500, 100, 150, 200] AS origin_arr,
    arrayEnumerate(origin_arr) AS arr_pos,
    -- 标记拆分点：当前元素小于前一个元素时标记为1，首个元素无前置值标记为0
    arrayMap((pos, cur_val) -> if(pos = 1, 0, if(cur_val < origin_arr[pos-1], 1, 0)), arr_pos, origin_arr) AS split_marks,
    -- 累计求和生成分组ID，同一段连续递增/非降序的元素共享同一个分组ID
    arrayCumSum(split_marks) AS segment_id
-- 按分组ID聚合得到拆分后的二维数组
SELECT groupArray(cur_val) AS split_result
FROM
(
    SELECT
        segment_id[pos] AS seg_id,
        origin_arr[pos] AS cur_val
    FROM arrayEnumerate(segment_id) AS pos
    ARRAY JOIN pos
)
GROUP BY seg_id
ORDER BY seg_id

运行上述代码会返回你期望的拆分结果：[[100, 200, 500], [100, 150, 200]]。

计数器场景滚动差值计算优化 #

针对你提到的计数器重置后计算滚动差值的业务场景，不需要先拆分数组、计算差值再合并，可以直接在分组步骤内完成差值计算，减少中间步骤提升查询性能：

WITH
    -- 实际使用时替换为你的表中计数器数组字段即可
    [100, 200, 500, 100, 150, 200] AS counter_arr,
    arrayEnumerate(counter_arr) AS arr_pos,
    arrayMap((pos, cur_val) -> if(pos = 1, 0, if(cur_val < counter_arr[pos-1], 1, 0)), arr_pos, counter_arr) AS split_marks,
    arrayCumSum(split_marks) AS segment_id
SELECT groupArray(roll_diff) AS final_rolling_diff
FROM
(
    SELECT
        if(pos_in_seg = 1, cur_val, cur_val - prev_val) AS roll_diff
    FROM
    (
        SELECT
            segment_id[pos] AS seg_id,
            counter_arr[pos] AS cur_val,
            -- 取同分段内前一个位置的计数器值
            lagInFrame(counter_arr[pos]) OVER (PARTITION BY seg_id ORDER BY pos) AS prev_val,
            row_number() OVER (PARTITION BY seg_id ORDER BY pos) AS pos_in_seg
        FROM arrayEnumerate(segment_id) AS pos
        ARRAY JOIN pos
    )
)

上述代码直接返回处理完计数器重置后的滚动差值结果：[100, 100, 300, 100, 50, 50]，无需额外做数组拆分、合并操作。

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