在BigQuery中使用GROUP_CONCAT时，我试图获取不同的值。
我将使用一个更简单的静态示例来重新创建情况：
编辑：我已经修改了示例，以更好地代表我的真实情况：group_concat的2列需要与众不同：
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中则没有。
有任何想法吗？
这是使用UNIQUE范围聚合功能删除重复项的解决方案。注意，为了使用它，首先我们需要构建一个REPEATEDusingNEST聚合：
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
)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句