sql - BigQuery - 如何查找 1 个或多个类别中唯一重叠值的计数（分类值计数）？

Question

我对 BigQuery 和标准 SQL 非常陌生。我可能无法找出解决问题的正确方法。请帮帮我。

请帮助我更改此代码以获得所需的输出。

我有一个颜色列和一个 ID 列。示例如下所示：

颜色 | ID
蓝色 | id_1
蓝色 | id_5
蓝色 | id_6
蓝色 | id_2
蓝色 | id_7
绿色 | id_1
绿色 | id_2
绿色 | id_3
红色 | id_3
红色 | id_5
红色 | id_2
红色 | id_4
红色 | id_7
黄色 | id_3
黄色 | id_4

我想要如下所述的结果输出：

• 我想看看 id_1 在多种颜色中重复了多少次，并将颜色的数量捕获为一个桶。
• 我不是在寻找“颜色”名称，而是在寻找颜色的数量

输入和预期输出/结果的参考。

样本输出：

品牌数量 || 1 色 :|: 2 色 :|: 3 色 :|: 4 色
唯一ID 计数 || 1 | 5 | 1 | 0 |

提前感谢您的耐心等待和时间。

我遇到的困难是当然存在重叠，因此 GROUP BY 或 DISTINCT 可以动态地连续填充 1 种颜色、2 种颜色、3 种颜色等。

SELECT id, Color, COUNT(1) number
FROM project.dataset.table
GROUP BY id, Color

标签： sqlgroup-bygoogle-bigquerycounting

Answer 1

我会在单独的行而不是列上执行此操作：

select cnt, count(*) as num_colors
from (select id, count(*) as cnt
      from t
      group by id
     ) i
group by cnt
order by cnt

如果您希望按列显示，可以使用条件聚合：

select countif(cnt = 1),
       countif(cnt = 2),
       countif(cnt = 3),
       countif(cnt = 4)
from (select id, count(*) as cnt
      from t
      group by id
     ) i;

注意：这些假设 id/color 行在原始数据中是唯一的。否则，使用count(distinct color) as cnt.