如何用BigQuery统计列中与指定列表的交集元素数量？

针对你的需求，这里提供两种高效的BigQuery实现方式：

方法一：子查询计数 #

SELECT
  Response,
  (SELECT COUNT(*) 
   FROM UNNEST(SPLIT(Response, ', ')) AS item
   WHERE item IN ('A', 'C', 'E')) AS Result
FROM your_table

逻辑说明： #

1. SPLIT(Response, ', ')：将每行的Response字符串按, （逗号+空格）分割成元素数组；
2. UNNEST(...) AS item：把数组展开为单行元素的临时数据集；
3. WHERE item IN ('A', 'C', 'E')：筛选出属于指定列表的元素；
4. 子查询中的COUNT(*)统计符合条件的元素数量，作为Result列输出。

方法二：数组长度统计 #

SELECT
  Response,
  ARRAY_LENGTH(ARRAY(
    SELECT item 
    FROM UNNEST(SPLIT(Response, ', ')) AS item
    WHERE item IN ('A', 'C', 'E')
  )) AS Result
FROM your_table

逻辑说明： #

先通过子查询筛选出符合条件的元素组成新数组，再用ARRAY_LENGTH获取数组长度，直接得到目标计数，逻辑更直观。

兼容格式异常的处理 #

如果Response中存在元素前后空格不一致的情况（比如部分数据是"A,B"而非"A, B"），可以用TRIM处理元素：

SELECT
  Response,
  (SELECT COUNT(*) 
   FROM UNNEST(SPLIT(Response, ',')) AS item
   WHERE TRIM(item) IN ('A', 'C', 'E')) AS Result
FROM your_table

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