Bigquery uncommon elements in array

Question

Let us say you have a column of arrays like this , I am trying to group the rows based on the count of uncommon elements. Once the number of distinct uncommon elements reaches 5, it will be in the next group. In the below example, first three rows will be group 1 because the uncommon elements are ['3','4','6','7'] which is 4 in length, but if you add the next row to the group , the array of distinct uncommon element would be ['1','3','4','5','6','7'] it will exceed the limit of 5 distinct uncommon elements.

with arr as (
       select 1 ord, ['1','2','3','4'] as ar
       union all 
       select 2, ['1','2','3']
       union all 
       select 3,['1','2','6','7']
       union all 
       select 4,['2','4','5','7']
       union all 
       select 5, ['string1','5','6','7','8']
      )

      select * from arr

I am looking for an output like below

Code I have written so far but definitely missing a big piece. Adding it just in case if it is helpful

with arr as (
       select 1 ord, ['1','2','3','4'] as ar,1 subclass
       union all 
       select 2, ['1','2','3'],1
       union all 
       select 3,['1','2','6','7'],1
       union all 
       select 4,['2','4','5','7'],1
       union all 
       select 5, ['string1','5','6','7','8'],1
      )
, history_t as (
      select a.* ,
     ARRAY_AGG(struct(ar)) OVER (PARTITION BY SUBCLASS ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as history
  
      from arr a ) 

, tem2 as (

 select a.* except(history,ar), 
 
 (SELECT COUNT(1) FROM UNNEST(history) AS col ) AS array_cnt
 ,b.ar unnest1
 from history_t a
 ,unnest(history) b 

) 

, tem3 as (
select a.* except(unnest1),sku_lst
from tem2 a , unnest(unnest1) sku_lst
) 

, all_sku_freq as (
select 
ord, array_cnt , sku_lst , subclass,count(*) sku_freq
 from tem3
 group by 1,2,3,4 ) 

, uncommon_sku_cnt as (
 select ord, subclass, count( sku_lst) uncommon_sku_count from all_sku_freq where sku_freq <> array_cnt group by 1,2 ) 

 ,rolling_uncomm_sku_cnt as (

select a.*, sum(uncommon_sku_count) over(partition by subclass order by ord asc range between unbounded preceding and current row  ) roll_uncomm_sku_cnt 
from uncommon_sku_cnt a
 ) 

 select a.* from rolling_uncomm_sku_cnt a

Bigquery uncommon elements in array

0 Answers