英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论