sql - 在 SQL Big Query 中的类别中查找重叠

Question

我正在尝试做与此类似的事情，但在 BigQuery 中。我有几个用户可能有 1 个或多个类别。我必须找到类别内的重叠。像这样的东西：

我想要的结果是这样的：

也就是说，例如，只有一个用户只有类别 D（而没有其他用户），两个用户有类别 10 和 30，依此类推。

主要问题是我有很多类别（超过 40 个）。以前我做过类似的事情：

SELECT sum(cat1), sum(cat2), sum(cat3)
FROM  table
where cat1 = 0 and cat2 = 1 and cat3 = 0

这种方法很有效，但是太手动了，现在不可能，因为我有很多类别。如果可能的话，想使用 BigQuery。

标签： sqlgoogle-bigqueryoverlapping

Answer 1

主要问题是我有很多类别（超过 40 个）。

考虑以下（BigQuery）方法 - 适用于任何合理数量的类别

execute immediate (
select '''
  select * from (
    select distinct t1.usr, 
      t1.categories category, t2.categories category2
    from `your_table` t1 left join `your_table` t2 
    on t1.usr = t2.usr and t1.categories != t2.categories
    union all
    select usr, any_value(categories) category, any_value(categories) category2
    from `your_table`
    group by usr
    having count(1) = 1
  )
  pivot (count(usr) cat for category2 in (''' || list || '''))
  order by category
'''
from (
  select string_agg("'" || categories || "'" order by categories) list 
  from (select distinct categories from `your_table`)
  )
)     

如果应用于您问题中的样本数据 - 输出是