BigQuery扩展多字段ARRAY_AGG后UNNEST数据膨胀问题咨询

兄弟，你这问题我太熟了！现在记录数膨胀200倍的原因很明显：你对Data_one和Data_two分别做了ARRAY_AGG，然后要分别UNNEST这两个数组——这相当于让两个20条的数组做笛卡尔积，20*20=400条，可不就膨胀了嘛！

正确的做法是把需要的所有字段打包成一个完整的记录结构体，聚合为一个数组，再只UNNEST这一个数组，这样每组最多保留20条完整的原始记录，不会出现数据膨胀的问题。

给你修正后的代码：

WITH `project.dataset.table` AS (
  SELECT 
    Name name, 
    Genre genre, 
    Data_one,
    Data_two
  FROM `project.dataset.booktable`
), 
search AS (
  SELECT name, genre 
  FROM UNNEST(['Alex','James']) name, 
       UNNEST(['HORROR','COMEDY']) genre
)
SELECT 
  name, 
  genre, 
  sampled_data.Data_one, 
  sampled_data.Data_two
FROM (
  SELECT 
    t.name, 
    t.genre,
    -- 把需要的字段打包成结构体，聚合为最多20条的记录数组
    ARRAY_AGG(STRUCT(t.Data_one, t.Data_two) LIMIT 20) AS sampled_records
  FROM `project.dataset.table` t
  JOIN search s 
    ON LOWER(s.name) = LOWER(t.name) 
    AND LOWER(s.genre) = LOWER(t.genre)
  WHERE RAND() < 0.5
  GROUP BY t.name, t.genre
),
-- 只UNNEST这个单一数组，避免笛卡尔积
UNNEST(sampled_records) AS sampled_data
ORDER BY name, genre, sampled_data.Data_one

几个关键点： #

• 结构体打包：用STRUCT()把你需要的所有字段（比如后续要加的Data_three、Data_four）打包成一个整体，这样ARRAY_AGG聚合的是完整的一行记录，而不是孤立的字段。
• 单一数组UNNEST：只对这个包含完整记录的数组做UNNEST，这样每组最多得到20条完整的原始数据，完全不会出现多数组UNNEST导致的笛卡尔积问题。
• 扩展性拉满：以后要加更多字段，只需要在STRUCT()里添上对应的字段名就行，不用再额外加ARRAY_AGG语句，维护起来特别方便。

如果想更省事，甚至可以直接聚合整行数据（不过分组用的name和genre其实不用包含进去，节省点资源）：

ARRAY_AGG(t LIMIT 20) AS sampled_records

然后UNNEST之后直接用sampled_data.Data_one、sampled_data.Data_two就行，和上面的效果是一样的。

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