How to split an array in Clickhouse based on custom condition-CodePudding

I am trying to find a way to split my array when the next element is smaller than the previous element. Here is some sample data

[100, 200, 500, 100, 150, 200]

I need to convert this to

[[100, 200, 500],[100, 150, 200]]

I am trying to calculate the running difference of the array and I need to handle the scenario where a counter resets and we start back at 0. My idea is to split the array when that happens and figure out the difference before joining the array.

CodePudding user response：

Try this way:

SELECT
    [100, 200, 500, 100, 150, 200] AS arr,
    arrayMap((x, index) -> if(index = 1, 1, (arr[index]) < (arr[index - 1])), arr, arrayEnumerate(arr)) AS split_rules,
    arraySplit((x, y) -> y, arr, split_rules) AS result

/*
┌─arr───────────────────────┬─split_rules───┬─result────────────────────────┐
│ [100,200,500,100,150,200] │ [1,0,0,1,0,0] │ [[100,200,500],[100,150,200]] │
└───────────────────────────┴───────────────┴───────────────────────────────┘
*/

/* OR */

SELECT
    [100, 200, 500, 100, 150, 200] AS arr,
    arraySplit((x, index) -> if(index = 1, 1, (arr[index]) < (arr[index - 1])), arr, arrayEnumerate(arr)) AS result

/*
┌─arr───────────────────────┬─result────────────────────────┐
│ [100,200,500,100,150,200] │ [[100,200,500],[100,150,200]] │
└───────────────────────────┴───────────────────────────────┘
*/