如何在BigQuery中移除查询结果中的空行？

嘿，我来帮你搞定这个空行的问题！你的查询里出现空行，根源在于box_1根本不含蓝色，导致子查询里生成的x数组是空的，最后外层构造的box_containing_blue就成了空数组，这些行就变成了空行。

修复原查询的方案 #

我们可以在分组的子查询里直接过滤掉没有蓝色的盒子，从源头避免生成空行：

WITH table1 AS (
  SELECT "box_1" box, "yellow" colours UNION ALL
  SELECT "box_1" box, "green" colours UNION ALL
  SELECT "box_2" box, "blue" colours UNION ALL
  SELECT "box_2" box, "blue" colours UNION ALL
  SELECT "box_3" box, "red" colours UNION ALL
  SELECT "box_3" box, "green" colours UNION ALL
  SELECT "box_3" box, "blue" colours
)
SELECT array(SELECT box FROM unnest(x) y LIMIT 1) AS box_containing_blue
FROM (
  SELECT box, array_agg(IF(colours="blue", colours, NULL) IGNORE NULLS) x
  FROM table1
  GROUP BY box
  -- 过滤掉没有蓝色的盒子，直接排除空数组的情况
  HAVING array_length(x) > 0
)

更简洁的替代写法 #

其实你的需求是找出所有包含蓝色的盒子，完全可以用更简单高效的方式实现，不用绕数组的弯：

WITH table1 AS (
  SELECT "box_1" box, "yellow" colours UNION ALL
  SELECT "box_1" box, "green" colours UNION ALL
  SELECT "box_2" box, "blue" colours UNION ALL
  SELECT "box_2" box, "blue" colours UNION ALL
  SELECT "box_3" box, "red" colours UNION ALL
  SELECT "box_3" box, "green" colours UNION ALL
  SELECT "box_3" box, "blue" colours
)
SELECT DISTINCT box AS box_containing_blue
FROM table1
WHERE colours = "blue"

这个写法直接筛选出颜色为蓝色的记录，再用DISTINCT去重，就能得到所有包含蓝色的盒子，结果里不会有空行，逻辑也更清晰。

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