BigQuery标准SQL:如何按ARRAY字段分组 [英] BigQuery standard SQL: how to group by an ARRAY field

问题描述

我的表有两列，id和a.列id包含一个数字，列a包含一个字符串数组.我想计算给定数组的唯一ID的数量，将数组之间的相等性定义为相同大小，每个索引使用相同的字符串".

My table has two columns, id and a. Column id contains a number, column a contains an array of strings. I want to count the number of unique id for a given array, equality between arrays being defined as "same size, same string for each index".

使用GROUP BY a时，我得到Grouping by expressions of type ARRAY is not allowed.我可以使用类似GROUP BY ARRAY_TO_STRING(a, ",")的名称，但是随后将两个数组["a,b"]和["a","b"]组合在一起，并且丢失了数组的真实"值(因此，如果以后要在另一个查询中使用它，我会必须拆分字符串).

When using GROUP BY a, I get Grouping by expressions of type ARRAY is not allowed. I can use something like GROUP BY ARRAY_TO_STRING(a, ","), but then the two arrays ["a,b"] and ["a","b"] are grouped together, and I lose the "real" value of my array (so if I want to use it later in another query, I have to split the string).

此字段数组中的值来自用户，因此我不能假定某个字符根本就不会存在(并将其用作分隔符).

The values in this field array come from the user, so I can't assume that some character is simply never going to be there (and use it as a separator).

推荐答案

代替GROUP BY ARRAY_TO_STRING(a, ",")使用GROUP BY TO_JSON_STRING(a)

因此您的查询将如下所示

so your query will look like below

#standardsql
SELECT 
  TO_JSON_STRING(a) arr,
  COUNT(DISTINCT id) cnt
FROM `project.dataset.table`
GROUP BY arr

您可以使用下面的伪数据对其进行测试

You can test it with dummy data like below

#standardsql
WITH `project.dataset.table` AS (
  SELECT 1 id, ["a,b", "c"] a UNION ALL
  SELECT 1, ["a","b,c"]
)
SELECT 
  TO_JSON_STRING(a) arr,
  COUNT(DISTINCT id) cnt
FROM `project.dataset.table`
GROUP BY arr  

结果为

Row     arr             cnt  
1       ["a,b","c"]     1    
2       ["a","b,c"]     1    

根据@Ted的评论进行更新

Update based on @Ted's comment

#standardsql
SELECT 
  ANY_VALUE(a) a,
  COUNT(DISTINCT id) cnt
FROM `project.dataset.table`
GROUP BY TO_JSON_STRING(a)

这篇关于BigQuery标准SQL:如何按ARRAY字段分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！