如何使用BigQuery Legacy SQL筛选含多指定值的重复字符串字段行

我太懂你这个痛点了——BigQuery处理repeated字段时自动扁平化的特性，直接用普通WHERE条件根本没法筛选出同时包含多个指定值的行。别慌，咱们用聚合逻辑就能搞定这个需求！

为什么之前的方法行不通？ #

当你查询带repeated字段的表时，BigQuery会自动把每个repeated元素拆成单独的行（也就是扁平化）。这意味着你之前写的WHERE name = 'name1' AND name = 'name4'永远不会返回结果——因为每一行扁平化后只有一个name值，不可能同时等于两个不同的字符串。而IN或者OR的逻辑只会返回只要包含其中一个值的行，自然不符合你要的“同时包含两者”的要求。

正确的解决方案 #

我们可以通过GROUP BY id把每个id对应的所有name聚合起来，再用HAVING子句筛选同时满足两个条件的分组。这里给你两种靠谱的写法：

方法1：用CASE统计目标值的出现次数（更严谨）

这种方法完全不会因为name值存在子串混淆（比如name10和name1）导致误判：

SELECT id
FROM t1
GROUP BY id
HAVING SUM(CASE WHEN name = 'name1' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN name = 'name4' THEN 1 ELSE 0 END) > 0

原理很简单：对每个id分组后，分别统计name1和name4的出现次数，只有当两个次数都大于0时，才说明这个id对应的行同时包含这两个值。

方法2：用GROUP_CONCAT拼接后检查包含关系（更简洁）

如果你能确定所有name值不会有互相包含的情况（比如不会出现name12这种和name1混淆的值），也可以用拼接字符串的方式：

SELECT id, GROUP_CONCAT_UNQUOTED(name) AS all_names
FROM t1
GROUP BY id
HAVING all_names LIKE '%name1%' AND all_names LIKE '%name4%'

这里GROUP_CONCAT_UNQUOTED会把每个id下的所有name拼成一个字符串，然后我们检查这个字符串是否同时包含name1和name4即可。

验证结果 #

这两种写法都会精准返回你需要的id：124和125，完全符合你的需求！

内容的提问来源于stack exchange，提问作者Alexandru R