Google Bigquery Legacy SQL：无结果返回空值/零及补全缺失行方法

我来帮你解决这两个BigQuery Legacy SQL的问题：

在Legacy SQL中，处理无结果返回默认值的场景，分两种情况灵活处理：

• 聚合查询（如SUM/COUNT）：如果聚合查询没有匹配到数据，默认会返回NULL，你可以用IFNULL()把它转换成0：

  -- 示例：查询south地区的总收入，无数据则返回0
  SELECT IFNULL(
    (SELECT SUM(revenue) FROM your_table WHERE region = 'south'),
    0
  ) AS total_revenue
  

• 非聚合查询（单条记录）：如果是查询单条记录但无结果，直接子查询会返回空结果集，这时候可以通过左连接一个虚拟的单行表来确保返回一行NULL（或转换为0）：

  -- 示例：查询north地区4月的收入，无数据则返回NULL
  SELECT t.revenue
  FROM (SELECT 1) AS dummy
  LEFT JOIN (SELECT revenue FROM your_table WHERE month = 'apr' AND region = 'north') AS t
  ON 1=1
  
  -- 如果要返回0，加IFNULL即可
  SELECT IFNULL(t.revenue, 0) AS revenue
  FROM (SELECT 1) AS dummy
  LEFT JOIN (SELECT revenue FROM your_table WHERE month = 'apr' AND region = 'north') AS t
  ON 1=1
  

这个需求的核心是先构建包含所有目标月份的完整列表，再与原数据表做左连接，从而自动填充缺失的行。具体实现如下：

完整查询语句 #

WITH -- 第一步：构建需要的完整月份列表
months AS (
  SELECT 'jan' AS month UNION ALL
  SELECT 'feb' UNION ALL
  SELECT 'mar' UNION ALL
  SELECT 'apr' UNION ALL
  SELECT 'may' UNION ALL
  SELECT 'jun'
),
-- 第二步：获取原表中所有唯一的地区（避免硬编码地区名称）
regions AS (
  SELECT DISTINCT region FROM your_table
)
-- 第三步：交叉连接月份和地区，左连接原表补全数据
SELECT
  m.month,
  r.region,
  IFNULL(t.revenue, 0) AS revenue -- 把空值替换为0，需要NULL的话去掉IFNULL即可
FROM months m
CROSS JOIN regions r -- 生成所有月份+地区的组合
LEFT JOIN your_table t
ON m.month = t.month AND r.region = t.region
ORDER BY m.month -- 按月份排序

结果说明 #

执行上述查询后，会自动补全原表中缺失的apr、may、jun行，最终结果和你期望的一致：

month region revenue

---

jan north 100
feb north 150
mar north 250
apr north 0
may north 0
jun north 0

如果你的月份范围更大，也可以用GENERATE_ARRAY生成数字月份后转换为缩写，但对于固定的6个月份，手动构造列表更直观。

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