distinct - 如何使用 Google Big Query 在 GROUP_CONCAT 上获取不同的值

Question

在 BigQuery 中使用 GROUP_CONCAT 时，我试图获得不同的值。

我将使用一个更简单的静态示例重新创建这种情况:

编辑:我修改了示例以更好地代表我的真实情况:2 列 group_concat 需要不同:

SELECT 
  category, 
  GROUP_CONCAT(id) as ids, 
  GROUP_CONCAT(product) as products
FROM 
 (SELECT "a" as category, "1" as id, "car" as product),
 (SELECT "a" as category, "2" as id, "car" as product),
 (SELECT "a" as category, "3" as id, "car" as product),
 (SELECT "b" as category, "4" as id, "car" as product),
 (SELECT "b" as category, "5" as id, "car" as product),
 (SELECT "b" as category, "2" as id, "bike" as product),
 (SELECT "a" as category, "1" as id, "truck" as product),
GROUP BY 
  category

此示例返回:

Row category    ids products
1   a   1,2,3,1 car,car,car,truck
2   b   4,5,6   car,car,bike

我想删除找到的重复值，返回如下:

Row category    ids products 
1   a   1,2,3   car,truck
2   b   4,5,6   car,bike

在 MySQL 中， GROUP_CONCAT 有一个 DISTINCT OPTION，但在 BigQuery 中没有。

有任何想法吗？

Answer 1

这是使用 UNIQUE 的解决方案用于删除重复项的范围聚合函数。注意，为了使用它，首先我们需要构建一个 REPEATED使用 NEST聚合:

SELECT 
  GROUP_CONCAT(UNIQUE(ids)) WITHIN RECORD,
  GROUP_CONCAT(UNIQUE(products)) WITHIN RECORD 
FROM (
SELECT 
  category, 
  NEST(id) as ids, 
  NEST(product) as products
FROM 
 (SELECT "a" as category, "1" as id, "car" as product),
 (SELECT "a" as category, "2" as id, "car" as product),
 (SELECT "a" as category, "3" as id, "car" as product),
 (SELECT "b" as category, "4" as id, "car" as product),
 (SELECT "b" as category, "5" as id, "car" as product),
 (SELECT "b" as category, "2" as id, "bike" as product),
 (SELECT "a" as category, "1" as id, "truck" as product),
GROUP BY 
  category
)