amazon-athena - 汇总动态列的值并在新列中应用公式

Question

我有一个下表结构：

CREATE EXTERNAL TABLE IF NOT EXISTS `samplecontrolid`(
  `controlId` string, 
  `status` string, 
 )

样本数据 ：

controlid       status  
CloudTrail.1    PASSED      
CloudTrail.1    PASSED      
CloudTrail.1    PASSED      
CloudTrail.1    PASSED  
CloudTrail.1    FAILED  
CloudTrail.1    FAILED  
IAM.8           PASSED  
IAM.8           PASSED      
IAM.8           FAILED      
IAM.8           FAILED  
IAM.8           FAILED      
IAM.8           FAILED  

我想计算每个 ControlID 的通过和失败次数：

我写的查询：

SELECT
   ControlId
  ,sum(case Status when 'PASSED' then 1 else 0 end) Passed
  ,sum(case Status when 'FAILED' then 1 else 0 end) Failed
 from samplecontrolid
 group by ControlId

输出：

ControlId    Passed Failed
CloudTrail.1    4   2
IAM.4           4   3
IAM.8           2   4
CIS3.2          5   0

现在，我想要汇总通过/失败值的总列。由于通过/失败的列值是动态生成的，我不能直接在这些列上应用 Sum 函数。我如何即时添加这些值。如下所示：

ControlId    Passed Failed  Total
CloudTrail.1    4   2        6 
IAM.4           4   3        7
IAM.8           2   4        6
CIS3.2          5   0        5

最后，计算一次总价值。我需要另一列计算百分比。

Percentage formula =  Passed / Total * 100
Example for CloudTrail.1 = 4/6 * 100 = 66.66 

最终输出：

ControlId    Passed Failed  Total  Percentage
CloudTrail.1    4   2        6       66.66
IAM.4           4   3        7       57.14
IAM.8           2   4        6       33.33
CIS3.2          5   0        5       100

标签： amazon-athenaansi-sql

Answer 1

不需要第二遍

例子

SELECT  ControlId
       ,sum(case Status when 'PASSED' then 1 else 0 end) Passed
       ,sum(case Status when 'FAILED' then 1 else 0 end) Failed
       ,sum(1) Total
       ,avg( case when Status='PASSED' then 100.0 else 0.0 end) Pct
 from samplecontrolid
 group by ControlId

结果