I have a matrix with buckets and elements like below. If an element can fit in a bucket it is 1 in the corresponding cell
For example: If you look at the image, element x can fit in bucket-a,b,c and not in d and e
I want to find the fewest buckets to group my elements. In this case, buckets c and d could group all the elements in just two buckets.
Any idea if i can do this in bigquery dynamically and efficiently ? original data is not as simple as this.
select "element-x" as element , 1 as bucketa, 1 as bucketb, 1 as bucketc, 0 as bucketd, 0 as buckete
union all
select "element-y" as element , 0 as bucketa, 0 as bucketb, 1 as bucketc, 0 as bucketd, 0 as buckete
union all
select "element-z" as element , 1 as bucketa, 0 as bucketb, 1 as bucketc, 0 as bucketd, 0 as buckete
union all
select "element-p" as element , 0 as bucketa, 0 as bucketb, 1 as bucketc, 0 as bucketd, 0 as buckete
union all
select "element-q" as element , 1 as bucketa, 0 as bucketb, 0 as bucketc, 1 as bucketd, 0 as buckete
union all
select "element-r" as element , 0 as bucketa, 1 as bucketb, 0 as bucketc, 1 as bucketd, 1 as buckete
