在BigQuery中按条件计算MAE：仅统计有效error2样本

表结构 #

FIELD NAME                TYPE
test_id                   string
samples                   repeated
    error1                float
    error2                float
    error2_is_valid       boolean

需求 #

计算两个平均绝对误差(MAE)：

• error1 MAE：统计全部样本的绝对误差平均值
• error2 MAE：仅统计error2_is_valid = true的样本的绝对误差平均值

对应的伪代码逻辑：

error2_mae = 0
error2_valid_samples = 0
for sample in samples:
    if sample.error2_is_valid:
        error2_mae += sample.error2
        error2_valid_samples += 1
error2_mae /= error2_valid_samples

错误尝试 #

1. 未添加过滤条件的SQL（无法筛选error2有效样本）：

SELECT test_id, AVG(ABS(samples.error2)) AS mae
FROM <table-name> as t, UNNEST(t.samples) as samples
GROUP BY test_id 

2. 使用WHERE子句过滤（会丢失error1的部分样本，导致统计不准确）：

SELECT test_id, AVG(ABS(samples.error1)) AS mae1, AVG(ABS(samples.error2)) AS mae2
FROM <table-name> as t, UNNEST(t.samples) as samples
WHERE samples.error2_is_valid
GROUP BY test_id 

正确SQL写法 #

利用条件聚合，在AVG函数内通过CASE WHEN筛选有效样本，既保留全部样本计算error1的MAE，又只对符合条件的error2计算MAE：

SELECT 
    test_id,
    -- 计算error1的MAE：全部样本参与计算
    AVG(ABS(samples.error1)) AS mae1,
    -- 计算error2的MAE：仅error2_is_valid为true的样本参与计算
    AVG(CASE WHEN samples.error2_is_valid THEN ABS(samples.error2) END) AS mae2
FROM <table-name> as t, UNNEST(t.samples) as samples
GROUP BY test_id 

说明 #

• BigQuery中，CASE WHEN不满足条件时返回NULL，而AVG函数会自动忽略NULL值，正好符合需求：只计算有效样本的error2平均值。
• 不需要过滤整个数据集，因此error1的MAE能基于全部样本统计。

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