如何在Athena中将每个WITH SELECT的true/false求和到最终结果中？

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

How do I sum for all of true/false of each WITH SELECT to the final result in Athena?

问题

我有一个查询，像这样：

  1. with group1 as (
  2.   select 
  3.     (
  4.       包含(array1, 'element_1')
  5.     ) AS matches, 
  6.     count(*) 
  7.   from 
  8.     // 来自语句 
  9.   where 
  10.     // 来自语句 
  11.   group by 
  12.     1
  13. ), 
  14. group2 as (
  15.   select 
  16.     (
  17.       包含(array1, 'element_2')
  18.     ) AS matches, 
  19.     count(*) 
  20.   from 
  21.     // 来自语句1 
  22.   where 
  23.     // 来自语句1 
  24.   group by 
  25.     1
  26. ) 
  27. select 
  28.   * 
  29. from 
  30.   group1, 
  31.   group2

group1或group2的结果会类似于：

  1. group1
  2. true, 10
  3. false, 20
  1. group2
  2. true, 30
  3. false, 40

我希望最终结果是
true, 40
false, 60

我必须使用WITH语句的原因是两个查询可以有不同的where语句，以及对matches的检查。

英文:

I have my query like this

  1. with group1 as (
  2.   select 
  3.     (
  4.       contains(array1, 'element_1')
  5.     ) AS matches, 
  6.     count(*) 
  7.   from 
  8.     // from statement 
  9.   where 
  10.     // where statement 
  11.   group by 
  12.     1
  13. ), 
  14. group2 as (
  15.   select 
  16.     (
  17.       contains(array1, 'element_2')
  18.     ) AS matches, 
  19.     count(*) 
  20.   from 
  21.     // from statement1 
  22.   where 
  23.     // where statement1 
  24.   group by 
  25.     1
  26. ) 
  27. select 
  28.   * 
  29. from 
  30.   group1, 
  31.   group2

The result of group1 or group2 would look something like this

  1. group1
  2. true, 10
  3. false, 20
  1. group2
  2. true, 30
  3. 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

你可以将这两个组合并，然后再进行一次分组并求和计数。

  1. with group1 as (
  2.   select 
  3.     (
  4.       contains(array1, 'element_1')
  5.     ) AS matches, 
  6.     count(*) as amount
  7.   from 
  8.     -- 这里是from语句
  9.   where 
  10.     -- 这里是where语句
  11.   group by 
  12.     1
  13. ), 
  14. group2 as (
  15.   select 
  16.     (
  17.       contains(array1, 'element_2')
  18.     ) AS matches, 
  19.     count(*) as amount
  20.   from 
  21.     -- 这里是from语句1
  22.   where 
  23.     -- 这里是where语句1
  24.   group by 
  25.     1
  26. ) 
  27. select matches, sum(amount) as amount 
  28. from 
  29. (select * from group1
  30. union all
  31. select * from group2)
  32. group by 1
英文:

You can union the 2 groups together, and then do another group by and sum the counts

  1. with group1 as (
  2.   select 
  3.     (
  4.       contains(array1, 'element_1')
  5.     ) AS matches, 
  6.     count(*) as amount
  7.   from 
  8.     // from statement 
  9.   where 
  10.     // where statement 
  11.   group by 
  12.     1
  13. ), 
  14. group2 as (
  15.   select 
  16.     (
  17.       contains(array1, 'element_2')
  18.     ) AS matches, 
  19.     count(*) as amount
  20.   from 
  21.     // from statement1 
  22.   where 
  23.     // where statement1 
  24.   group by 
  25.     1
  26. ) 
  27. select matches, sum(amount) as amount 
  28. from 
  29. (select * from group1
  30. union all
  31. select * from group2)
  32. group by 1

huangapple
  • 本文由 发表于 2023年6月22日 08:02:17
  • 转载请务必保留本文链接：https://go.coder-hub.com/76527843.html
  • amazon-athena
  • presto
  • sql
匿名

发表评论

匿名网友

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

确定