Split array by custom slice

Question

I have an array

[('active',1),('active',2),('active',3),('active',4), ('not active',7),('not active',5),('not active',6),('active',7),('active',8),('active',9)]

If there a way to get array like this:

[[('active',1),('active',2),('active',3), ('active',4)],[('not active',7),('not active',5),('not active',6)],[('active',7),('active',8),('active',9)]]

Answer 1

with e as (
    select '1' sku, 'active' status, toInt32('11') dttm
    union all
    select '1' sku, 'active' status, toInt32('12') dttm
    union all
    select '1' sku, 'active' status, toInt32('13') dttm
    union all
    select '1' sku, 'not active' status, toInt32('14') dttm
    union all
    select '1' sku, 'not active' status, toInt32('15') dttm
    union all
    select '1' sku, 'not active' status, toInt32('16') dttm
    union all
    select '1' sku, 'active' status, toInt32('17') dttm
    union all
    select '1' sku, 'active' status, toInt32('18') dttm
    union all
    select '1' sku, 'active' status, toInt32('19') dttm
)
, 'active' as ch
select
    sequence
    , arrayEnumerate(sequence) indexes
    , arraySplit((x, i) -> (sequence.1)[i] = ch and (sequence.1)[i-1] <> ch or (sequence.1)[i] <> ch and (sequence.1)[i - 1] = ch, sequence, indexes) res
from (
    SELECT groupArray(tuple(status, dttm)) as sequence
    from e
    group by sku
) a

Output

+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|sequence                                                                                                                                   |res                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|[('active',11),('active',12),('active',13),('not active',14),('not active',15),('not active',16),('active',17),('active',18),('active',19)]|[[('active',11),('active',12),('active',13)],[('not active',14),('not active',15),('not active',16)],[('active',17),('active',18),('active',19)]]|
+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+