BigQuery如何统计数组各元素对应id数量且降低数据shuffle？

原写法的性能问题 #

你当前的写法在UNNEST数组时带出了id字段，后续分组Shuffle阶段需要传输所有展开后的id+value行，当数组元素总量很大时，Shuffle开销会非常高。另外如果单条记录的values数组中存在重复元素，还会生成多余的重复行，进一步放大Shuffle数据量。

优化实现方案 #

首先要说明：不存在完全绕开UNNEST的实现逻辑，统计数组内每个元素对应的ID数必须要展开数组元素，但是我们可以通过下推计算、减少传输字段的方式大幅降低Shuffle数据量。

场景1：同一ID的数组内重复元素需重复计数 #

如果业务允许同一ID的values数组中重复出现的value对应多次计数，可直接简化查询，去掉冗余的id字段传输：

SELECT value, COUNT(*) AS id_count
FROM `myproject.mytable`, UNNEST(values) AS value
GROUP BY value

该写法会被BigQuery优化器自动下推UNNEST操作到数据所在的本地worker执行，Shuffle阶段仅需要传输value字段，相比原写法传输量减少50%以上，如果id是长字符串收益更高。

场景2：同一ID的数组内重复元素仅计数1次 #

这是绝大多数业务的统计逻辑，即统计有多少个不同的ID包含对应value，可先对单条记录的数组做本地去重后再展开：

SELECT value, SUM(1) AS id_count
FROM `myproject.mytable`, UNNEST(ARRAY_DISTINCT(values)) AS value
GROUP BY value

该写法先在本地worker对每条记录的values数组做去重，再展开数组，既避免了生成多余的重复行，又不需要传输id字段，Shuffle数据量仅为原写法的10%~30%（根据数组重复率和id长度浮动），性能提升非常明显。

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