CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
Pinky Joe
Pinky Joe

Reputation: 79

Generate all subset of an array bigquery

For an array [1,2,3,4], I want to generate [], [1,2,3,4], [1], [2], [3], [4] , [1,2] ,[1,3],[1,4], [2,3] , [2,4] ,[3,4] , [1,4] and so on..

Upvotes: 1

Views: 837

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

Below is follow up for Gordon's excellent answer [just upvoted it :o)], to address two issues I noticed in it

  • it is a little too verbose with some extra joins
  • it produces correct result only for data with just one row (one array)
with data as (
  select ['a','b','c','d'] as arr union all 
  select ['x', 'y', 'z']
)
select  
  (select array_agg(
    case when n & (1<<pos) <> 0 then arr[offset(pos)] end 
    ignore nulls)
   from unnest(generate_array(0, array_length(arr) - 1)) pos
  ) as combo
from data cross join 
unnest(generate_array(1, cast(power(2, array_length(arr)) - 1 as int64))) n
-- order by array_length(combo), format('%t', combo)

with output

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

One approach to this problem is to generate all the integers between 1 and 2^ - 1. The bit pattern then represents all the combinations.

You can use bit comparisons to extract the combos:

with ar as (
       select  [1,2,3,4] as ar
      )
select n,
       (select array_agg(case when n & (1<<pos) <> 0
                              then ar.ar[offset(pos)]
                         end ignore nulls)
        from ar cross join
             unnest(generate_array(0, x.cnt - 1)) pos
       ) as combo
from ar cross join
     (select count(*) as cnt
      from ar cross join
           unnest(ar.ar) x
     ) x cross join
     unnest(generate_array(1, cast(power(2, x.cnt) - 1 as int64))) n

Upvotes: 2

Related Questions