BigQuery中特定请求场景下AVG函数的正确使用方法

你的问题核心是想用AVG函数替代手动计算的方式，得到每个应用的成功请求（状态码200）占比。先看你原来的AVG写法为什么无效：

AVG(CASE WHEN operation_status = 200 THEN operation_status END) AS avg_status_200

这个写法是对所有状态码为200的行取状态码的平均值，比如APP_1有1个200和1个500，这个AVG计算的是200（非200的行CASE返回NULL，AVG会忽略NULL），自然不是你要的占比0.5。

正确的AVG写法 #

要计算占比，你需要让成功的行返回1，失败/其他状态的行返回0，然后对这个结果取平均值——本质就是成功行数除以总行数，和你手动计算的逻辑完全一致。

两种等价写法：

写法1：使用CASE表达式

SELECT
    app_name,
    COUNTIF(operation_status = 200) AS successful_ops,
    COUNTIF(operation_status = 500) AS failed_ops,
    AVG(CASE WHEN operation_status = 200 THEN 1 ELSE 0 END) AS success_rate_avg,
    (COUNTIF(operation_status=200)/COUNT(operation_status)) AS average_manual
FROM
    `requests`
GROUP BY
    app_name;

写法2：使用BigQuery的IF函数（更简洁）

SELECT
    app_name,
    COUNTIF(operation_status = 200) AS successful_ops,
    COUNTIF(operation_status = 500) AS failed_ops,
    AVG(IF(operation_status = 200, 1, 0)) AS success_rate_avg,
    (COUNTIF(operation_status=200)/COUNT(operation_status)) AS average_manual
FROM
    `requests`
GROUP BY
    app_name;

结果验证 #

运行上面的SQL后，success_rate_avg和average_manual会返回完全相同的值：

• APP_1：0.5
• APP_2：0.5
• APP_3：1.0

这里我们用ELSE 0覆盖了所有非200的情况，每一行都有明确的1或0值，AVG计算的就是所有行的平均值，也就是成功行数占总行数的比例。

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