SELECT语句中的条件过滤以避免连接(JOIN)。

huangapple go评论71阅读模式
英文:

Conditional filter in SELECT to avoid JOIN

问题

以下是在BigQuery中进行优化且更易读的方式来执行上述操作的SQL代码:

SELECT
  item,
  SUM(cost) AS cost,
  IF(flag = TRUE, SUM(IF(flag = TRUE, cost, 0))) AS flag_true_cost
FROM test.t1
GROUP BY item;

输出结果应如下所示:

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

这段代码将根据条件计算flag_true_cost,并且更加紧凑和易读。

英文:

I'm wondering if there's an optimized, more readable way of doing the following in BigQuery:

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;

Output:

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

Here is some pseudo-code of what I had in mind

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

Here is the sample data

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)
;

答案1

得分: 4

以下是您要翻译的内容:

> Here is some pseudo-code of what I had in mind

You are almost there. Just move the conditional within the aggregate function:

select item, sum(cost) as cost,
    sum(if(flag=true, cost, 0)) as flag_true_cost
from ayima-ad-hoc.test.t1
group by item;
英文:

> Here is some pseudo-code of what I had in mind

You are almost there. Just move the conditional within the aggregate function:

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

huangapple
  • 本文由 发表于 2023年6月26日 23:23:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/76558099.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定