如何在BigQuery中用SELECT条件过滤替代JOIN实现更优可读查询？

你可以用条件聚合替代原查询中的两次分组+全连接，这样只需要扫描一次表，性能更优，可读性也更强。

原查询 #

select
  coalesce(a.item, b.item) as item,
  a.cost,
  b.flag_true_cost,
from
(
  select
  item,
  sum(cost) as cost,
  from test.t1
  group by item
) a
full join
(
  select
  item,
  sum(cost) as flag_true_cost
  from test.t1
  where flag = true
  group by item
) b
on a.item = b.item;

优化后的查询 #

你的伪代码思路是对的，只需调整条件聚合的写法即可实现需求，BigQuery支持两种简洁的写法：

写法一（使用BigQuery原生IF函数） #

select
  item,
  sum(cost) as cost,
  sum(if(flag = true, cost, 0)) as flag_true_cost
from test.t1
group by item;

写法二（标准SQLCASE WHEN，兼容性更强） #

select
  item,
  sum(cost) as cost,
  sum(case when flag = true then cost else 0 end) as flag_true_cost
from test.t1
group by item;

逻辑说明 #

• sum(cost)：按item汇总所有记录的cost，和原查询中子查询a的逻辑完全一致
• sum(if(flag = true, cost, 0))/sum(case...)：仅对flag=true的行的cost求和，不符合条件的行贡献0，直接替代原查询中子查询b+全连接的逻辑，避免了两次表扫描和连接操作

测试数据 #

create table test.t1 (item string, flag bool, cost numeric);

insert into test.t1 values
  ('kale', true, 2.3),
  ('kale', false, 18),
  ('apple', true, 1.4),
  ('pumpkin', true, 3.5)
;

查询输出 #

运行优化后的查询，会得到和原查询完全一致的结果：

item    cost    flag_true_cost
pumpkin 3.5     3.5
apple   1.4     1.4
kale    20.3    2.3

优势对比 #

• 性能：原查询需要两次扫描test.t1表并执行全连接，优化后的查询仅扫描一次表、分组一次，数据量越大性能提升越明显
• 可读性：逻辑集中在单个SELECT语句中，结构简洁，每个字段的计算逻辑一目了然

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