BigQuery中GROUP BY后对数组使用approx_top_count的正确写法

错误原因 #

你写的查询中，approx_top_count 的参数使用了 (select * from unnest(arr))，这个子查询会展开数组并返回多个元素，而 approx_top_count 要求输入单个可迭代的元素流，而非多元素的子查询结果，因此触发了“Scalar subquery produced more than one element”错误。

正确查询写法 #

with base as(
  select 'row1' as row_, [1,2,2,3,3,3] as arr, 1 as val
  union all
  select 'row1', [4,4,4,4,5,5,5,5,5], 1
  union all
  select 'row2', [6,6,6,6,6,6,7,7,7,7,7,7,7], 1
)
select
  row_,
  sum(val) as val,
  -- 先合并同组的所有数组，再展开统计高频值
  (select approx_top_count(element, 2) from unnest(array_concat_agg(arr)) element) as top_values
from base
group by row_

逻辑说明 #

1. 外层按 row_ 分组，sum(val) 直接计算每组的 val 总和，完全避免了提前 unnest 导致的行数膨胀问题。
2. 子查询中，array_concat_agg(arr) 将同一 row_ 下的所有数组合并成一个大数组，再通过 unnest 展开，最后用 approx_top_count 统计前2个出现频率最高的元素。

执行后会得到你期望的结果：

row_ | val |       top_values       |
-------------------------------------
row1 |  2  | [{4,4}, {5,5}]        |
row2 |  1  | [{6,6}, {7,7}]        |

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