BigQuery标准SQL：CROSS JOIN UNNEST后统计原始嵌套行数

嘿，我懂你遇到的这个痛点——用CROSS JOIN UNNEST之后，同一个学生如果匹配多个目标班级，普通的COUNT(1)会重复计数，而COUNT(DISTINCT studentId)虽然能得到正确结果，但性能拉胯，完全没利用到原始表的结构优势。

下面给你几个更高效的替代方案，核心思路是避免先展开所有嵌套行再去重，而是直接在原始行层面做判断和统计：

方案1：用EXISTS子查询统计符合条件的原始行数 #

如果你的需求是统计「至少有一个班级在('1','2')中的学生总数」，这个方案最直接，性能也最优：

SELECT COUNT(1) AS studentCount
FROM myTable
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(classes) AS cls
  WHERE cls.id IN ('1', '2')
)

这个查询会逐个检查每个学生的班级数组，只要数组里有符合条件的班级，就把这个原始行计入统计，完全不需要展开所有嵌套元素，避免了数据膨胀和后续的去重开销。

方案2：按studentId分组，统计每个学生的原始行计数 #

如果需要按studentId分组，同时得到每个学生是否有符合条件的班级（以及原始行的计数，每个学生只计1次），可以这样写：

SELECT 
  studentId,
  -- 标记该学生是否有符合条件的班级
  CASE WHEN EXISTS (
    SELECT 1 FROM UNNEST(classes) AS cls WHERE cls.id IN ('1', '2')
  ) THEN 1 ELSE 0 END AS has_qualified_class,
  -- 原始行计数，每个学生固定为1
  1 AS original_row_count
FROM myTable
-- 可选：只保留有符合条件班级的学生
WHERE EXISTS (
  SELECT 1 FROM UNNEST(classes) AS cls WHERE cls.id IN ('1', '2')
)

方案3：用数组过滤+ARRAY_LENGTH统计（兼顾班级数量） #

如果还需要同时知道每个学生符合条件的班级数量，又不想展开嵌套行，可以结合数组过滤和长度统计：

SELECT 
  studentId,
  -- 统计符合条件的班级数量
  ARRAY_LENGTH(ARRAY(
    SELECT cls.id FROM UNNEST(classes) AS cls WHERE cls.id IN ('1', '2')
  )) AS qualified_class_count,
  -- 原始行计数
  1 AS original_row_count
FROM myTable
WHERE ARRAY_LENGTH(ARRAY(
  SELECT cls.id FROM UNNEST(classes) AS cls WHERE cls.id IN ('1', '2')
)) > 0

为什么这些方案比COUNT(DISTINCT)快？ #

CROSS JOIN UNNEST会把每个嵌套数组的元素都拆成单独的行，当数组元素多的时候，会瞬间让数据量暴涨几倍甚至几十倍。之后用COUNT(DISTINCT studentId)需要对这些膨胀后的行做去重计算，这会消耗大量的内存和CPU资源。

而上面的方案都是在原始行级别处理：每个学生只被处理一次，不需要展开所有嵌套元素，自然能大幅提升查询效率。

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