BigQuery计算值与多列之间的重叠百分比 [英] BigQuery Calculating Percent Overlap Between Values Multiple Columns
本文介绍了BigQuery计算值与多列之间的重叠百分比的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我对SQL和BigQuery相当陌生，现在正在处理一个大约有140万行的数据集。
我当前感兴趣的值是CATEGORY_NAME(字符串)、ITEM_ID(字符串)。我感兴趣的是计算CATEGORY_NAME中每个值的不同Item_id(此列总共有269个不同的值)。基本上，在我的基本数据集中，每行数据都包含一个Item_id实例，其中它显示在单个CATEGORY_NAME中，其中Item_ID每天可以有多行，具体取决于当天出现的CATEGORY_NAMES的数量。 我已经成功地运行了一个查询，为CATEGORY_NAME的每个值添加了一个不同的Item_id的新列，现在的最终输出应该是我不知道该怎么做，那就是计算1个CATEGORY_NAME中也出现在其他CATEGORY_NAME中的DISTINCT ITEM_ID的百分比。因此，基本上我在寻找一个新列(如透视表)，该列将计算2个CATEGORY_NAME的匹配的Item_ID，然后将该计数除以1个CATEGORY_NAME中不同的Item_id的总数。因此，基本上每个CATEGORY_NAME都有269个新列，每一行都表示基本CATEGORY_NAME与其他CATEGORY_NAME的重叠百分比。这是我的表中当前我感兴趣的数据
category_name | item_id
---------------|------------
category1 | item1
category2 | item1
category3 | item1
category1 | item2
category4 | item2
category1 | item3
category5 | item3
category5 | item2
category6 | item4
category3 | item5
category3 | item6
category1 | item6
category2 | item5
category1 | item4
以下是我当前的查询结果
category_name | distinct_items
---------------|-----------------
category1 | 5
category2 | 2
category3 | 3
category4 | 1
category5 | 2
category6 | 1
以下是我希望最终输出是什么样子：
category_name | category1 | category2 | category3 | category4 | category5 | category6
--------------------------------------------------------------------------------------------------------
category1 | 100% | 20% | 40% | 20% | 40% | 20%
category2 | 50% | 100% | 100% | 0% | 0% | 0%
category3 | 66.67% | 66.67% | 100% | 0% | 0% | 0%
category4 | 100% | 0% | 0% | 100% | 100% | 0%
category5 | 100% | 0% | 0% | 50% | 100% | 0%
category6 | 100% | 0% | 0% | 0% | 0% | 100%
如果我可以在Google data studio中使用一个更简单的公式来进行这个聚合，那么这个公式也会很有效，因为data studio中的最终输出应该是带有x轴和y轴的散点图，CATEGORY_NAMES和气泡是%重叠，所以本质上只是用散点图可视化数据透视表结果。如果我的描述和问题中有任何地方没有意义，或者需要更清楚，请随时给我贴标签，让我知道什么是令人困惑的。如有任何帮助，我们将不胜感激！谢谢
推荐答案
下面是BigQuery Standard SQL
第1步-动态生成查询文本，因此您无需手动输入全部269个类别.
#standardSQL
SELECT '''SELECT category_name, ''' ||
STRING_AGG(DISTINCT
' MAX(IF(category_name2 = "' || category_name || '", percent, NULL)) AS ' || category_name
) || '''
FROM (
SELECT t1.category_name, t2.category_name category_name2,
ROUND(100 * COUNTIF(t1.item_id = t2.item_id) / COUNT(DISTINCT t1.item_id), 2) percent
FROM `project.dataset.table` t1
CROSS JOIN `project.dataset.table` t2
GROUP BY t1.category_name, t2.category_name
)
GROUP BY category_name
'''
FROM `project.dataset.table`
如果您针对问题中的示例数据运行上述查询，您将获得以下查询的平面化版本
SELECT category_name,
MAX(IF(category_name2 = "category1", percent, NULL)) AS category1,
MAX(IF(category_name2 = "category2", percent, NULL)) AS category2,
MAX(IF(category_name2 = "category3", percent, NULL)) AS category3,
MAX(IF(category_name2 = "category4", percent, NULL)) AS category4,
MAX(IF(category_name2 = "category5", percent, NULL)) AS category5,
MAX(IF(category_name2 = "category6", percent, NULL)) AS category6
FROM (
SELECT t1.category_name, t2.category_name category_name2,
ROUND(100 * COUNTIF(t1.item_id = t2.item_id) / COUNT(DISTINCT t1.item_id), 2) percent
FROM `project.dataset.table` t1
CROSS JOIN `project.dataset.table` t2
GROUP BY t1.category_name, t2.category_name
)
GROUP BY category_name
第二步-复制第一步中的查询结果并将其作为查询运行-就这样！
如果要将此应用于问题中的样本数据-输出将为
Row category_name category1 category2 category3 category4 category5 category6
1 category1 100.0 20.0 40.0 20.0 40.0 20.0
2 category2 50.0 100.0 100.0 0.0 0.0 0.0
3 category3 66.67 66.67 100.0 0.0 0.0 0.0
4 category4 100.0 0.0 0.0 100.0 100.0 0.0
5 category5 100.0 0.0 0.0 50.0 100.0 0.0
6 category6 100.0 0.0 0.0 0.0 0.0 100.0
注2：在您的简化示例中，我主要使用的是数据。在您的实际案例中，您可能需要一些细微的调整-如果您对此有任何问题-请发布新问题
这篇关于BigQuery计算值与多列之间的重叠百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
