如何找到跨多行共享的每个功能组合

Question

我对使用SQL很陌生(目前通过Big Query使用StandardSQL(，不幸的是，我的Google fu找不到解决这个问题的方法。

我正在使用一个数据集，其中每一行都是不同的人，每一列都是一个属性(姓名、年龄、性别、体重、种族、身高、bmi、教育水平、GPA等(。我将这些人"聚类"为匹配5人或5人以上的所有特征组合。

最初，我用3个功能列手动完成了这项工作，其中我将连接一个"集群名称"列，然后用>5 where子句，然后我将其UNIONed在一起：

• 性别
• 年龄
• 种族
• 性别+年龄
• 性别+种族
• 年龄+种族
• 性别+年龄+种族

^不幸的是，这样做只是夸大了组合的数量，而我预计总共有15个功能这样做似乎真的不可行。我还想通过一种不太手动的方法来实现这一点，这样，如果将来添加新功能，就不需要进行重大编辑即可将其包含在我的集群标识中。

有没有一个功能或现有流程可以完成这样的事情？理想情况下，我希望能够识别所有符合我的组合用户计数最小值的组合(因此，预计相同的行将与这里的多个不同集群匹配。如果有任何建议或帮助，我们将不胜感激！谢谢。

Answer 1

如果只有BQ支持grouping sets或cube，这将很简单。一种非常普遍的方法是枚举7个组，然后使用位来计算聚合内容：

select (case when n & 1 > 0 then gender end) as gender,
(case when n & 2 > 0 then age end) as age,
(case when n & 4 > 0 then ethnicity end) as ethnicity,
count(*)    
from t cross join 
unnest(generate_array(1, 7)) n
group by n, 1, 2, 3;

另一种比较棘手的方法是使用CCD_ 3重建组。类似这样的东西：

select gender, age, ethnicity, count(*)
from t
group by rollup(gender, age, ethnicity);

生成所需的三个组。因此：

select gender, age, ethnicity, count(*)
from t
group by rollup(gender, age, ethnicity)
union all
select gender, null, ethnicity, count(*)
from t
group by gender, ethnicity
union all
select null, age, ethnicity, count(*)
from t
group by rollup (ethnicity, age);

上面使用rollup()重建了您的所有组。