BigQuery SQL如何按条件分组 计算忽略NULL值的列平均值

表结构前提 #

• 产品映射表product：共2个字段
  • product：产品名称
  • category：产品所属品类，当前包含水果、蔬菜、肉类三类，共维护8款产品的映射关系
• 日度价格宽表（示例表名daily_product_price）：
  • 行粒度为单个统计日期（示例字段名stat_date）
  • 除日期列外，每列对应1款产品的当日价格，缺失值为NULL

实现逻辑 #

因为要求不能手动逐列硬编码计算规则，所以用BigQuery原生的动态SQL能力实现自动适配：

1. 运行时自动读取价格宽表的字段列表，过滤出所有产品列
2. 关联产品映射表，按品类归并对应产品列
3. 动态生成聚合逻辑，用AVG()函数计算均价——BigQuery的AVG()本身就会自动跳过NULL值，不需要额外写非空过滤
4. 最终按日期分组输出，后续新增产品、调整品类映射时不需要修改SQL代码，只要更新product映射表即可自动适配

可直接运行的代码 #

注意把代码里的项目名、数据集名、表名、日期字段名替换成你实际业务的命名：

EXECUTE IMMEDIATE FORMAT("""
SELECT
  stat_date,
  %s
FROM `你的项目ID.你的数据集名.daily_product_price`
GROUP BY stat_date
ORDER BY stat_date
""",
-- 动态生成各品类的均价计算子句
(
  SELECT STRING_AGG(
    FORMAT("""
      AVG(CASE %s END) AS avg_price_%s
    """,
    -- 拼接同品类下所有产品的取值分支
    (
      SELECT STRING_AGG(
        FORMAT("WHEN '%s' THEN `%s`", product, product),
        " "
      )
      FROM UNNEST(product_list) AS product
    ),
    category
  ), ",")
  FROM (
    SELECT
      category,
      ARRAY_AGG(product) AS product_list
    FROM `你的项目ID.你的数据集名.product`
    -- 只保留宽表中实际存在的产品列，避免映射表和宽表结构不一致报错
    WHERE product IN (
      SELECT column_name
      FROM `你的项目ID.你的数据集名.INFORMATION_SCHEMA.COLUMNS`
      WHERE
        table_schema = '你的数据集名'
        AND table_name = 'daily_product_price'
        AND column_name != 'stat_date'
    )
    GROUP BY category
  )
)
);

输出说明 #

最终返回的结果集固定包含：

• stat_date：统计日期
• avg_price_水果：当日水果类所有有价格的产品均价
• avg_price_蔬菜：当日蔬菜类所有有价格的产品均价
• avg_price_肉类：当日肉类所有有价格的产品均价
  如果后续新增品类，结果集会自动追加对应品类的均价字段，不需要调整代码。

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