BigQuery中approx_quantiles为不同函数设置独立WHERE过滤条件的方法

结论 #

这个需求完全可以在BigQuery中实现，你期望的条件聚合逻辑可以通过BigQuery标准SQL支持的FILTER子句实现，语法和你写的伪代码逻辑完全一致。

实现语法 #

你只需要在聚合函数后添加FILTER (WHERE 过滤条件)即可，不符合过滤条件的行对应字段值会自动排除，不参与本次聚合计算。

完整可运行代码 #

with t1 as (
    select 'a' as category, 0.25 as stat1, 2 as stat1ct, 0.82 as stat2, 3 as stat2ct union all
    select 'a' as category, 0.35 as stat1, 4 as stat1ct, 0.68 as stat2, 5 as stat2ct union all
    select 'a' as category, 0.45 as stat1, 3 as stat1ct, 0.74 as stat2, 4 as stat2ct union all
    select 'a' as category, 0.28 as stat1, 0 as stat1ct, 0.72 as stat2, 0 as stat2ct union all
    select 'a' as category, 0.36 as stat1, 0 as stat1ct, 0.65 as stat2, 4 as stat2ct union all
    select 'a' as category, 0.63 as stat1, 1 as stat1ct, 0.53 as stat2, 3 as stat2ct union all
    select 'a' as category, 0.18 as stat1, 5 as stat1ct, 0.52 as stat2, 1 as stat2ct union all
    select 'a' as category, 0.43 as stat1, 3 as stat1ct, 0.57 as stat2, 2 as stat2ct
)

select
    approx_quantiles(stat1, 100) FILTER (WHERE stat1ct > 2) as atr2FgPct,
    approx_quantiles(stat2, 100) FILTER (WHERE stat2ct > 2) as paint2FgPct
from t1

逻辑说明 #

上述代码中：

• 计算atr2FgPct时，仅会使用stat1ct > 2的4行数据对应的stat1值参与分位数计算，其余行自动忽略
• 计算paint2FgPct时，仅会使用stat2ct > 2的5行数据对应的stat2值参与分位数计算，其余行自动忽略

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