英文:
How do I sum for all of true/false of each WITH SELECT to the final result in Athena?
问题
我有一个查询，像这样：
with group1 as (
select
(
包含(array1, 'element_1')
) AS matches,
count(*)
from
// 来自语句
where
// 来自语句
group by
1
),
group2 as (
select
(
包含(array1, 'element_2')
) AS matches,
count(*)
from
// 来自语句1
where
// 来自语句1
group by
1
)
select
*
from
group1,
group2
group1或group2的结果会类似于：
group1
true, 10
false, 20
group2
true, 30
false, 40
我希望最终结果是
true, 40
false, 60
我必须使用WITH语句的原因是两个查询可以有不同的where语句，以及对matches的检查。
英文:
I have my query like this
with group1 as (
select
(
contains(array1, 'element_1')
) AS matches,
count(*)
from
// from statement
where
// where statement
group by
1
),
group2 as (
select
(
contains(array1, 'element_2')
) AS matches,
count(*)
from
// from statement1
where
// where statement1
group by
1
)
select
*
from
group1,
group2
The result of group1 or group2 would look something like this
group1
true, 10
false, 20
group2
true, 30
false, 40
I want the final result to be
true, 40
false, 60
The reason that I have to use WITH statement is because the two queries can have different where statements as well as the check to the matches
答案1
得分: 0
你可以将这两个组合并，然后再进行一次分组并求和计数。
with group1 as (
select
(
contains(array1, 'element_1')
) AS matches,
count(*) as amount
from
-- 这里是from语句
where
-- 这里是where语句
group by
1
),
group2 as (
select
(
contains(array1, 'element_2')
) AS matches,
count(*) as amount
from
-- 这里是from语句1
where
-- 这里是where语句1
group by
1
)
select matches, sum(amount) as amount
from
(select * from group1
union all
select * from group2)
group by 1
英文:
You can union the 2 groups together, and then do another group by and sum the counts
with group1 as (
select
(
contains(array1, 'element_1')
) AS matches,
count(*) as amount
from
// from statement
where
// where statement
group by
1
),
group2 as (
select
(
contains(array1, 'element_2')
) AS matches,
count(*) as amount
from
// from statement1
where
// where statement1
group by
1
)
select matches, sum(amount) as amount
from
(select * from group1
union all
select * from group2)
group by 1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论